Lets Develop in Oracle

ORA-00027 cannot kill current session

2012-02-14T21:10:00.000+05:30

ORA-00027 cannot kill current session
Cause: An attempt was made to use ALTER SYSTEM KILL SESSION to kill the
current session.
Action: If it is necessary to kill the current session, do so from another session.

ORA-00027 comes under "Oracle Database Server Messages".

These messages are generated by the Oracle database server when running any Oracle program.

How to kill session:
1. identify which session to kill using following query:
 select 
  s.sid,
  s.serial#,
  spid,
  trim(s.machine) machine,
  trim(s.module) module,
  status
 from
  v$session s,
  v$process p
 where
  paddr=addr
  and module is not null
 order by 1,2
 
2. Killing a session
 ALTER SYSTEM KILL SESSION 'sid,serial#';
 
 or you can kill at OS level on linux using
 kill -9 spid

ORA-00257 archiver error. Connect internal only, until freed

2012-01-23T21:08:00.000+05:30


ORA-00257 archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redolog. 
If the problem is not resolved soon, the database will stop executing transactions. 
The most likely cause of this message is the destination device is out of space 
to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem.
Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST 
is set up properly for archiving.

ORA-00257 comes under "Oracle Database Server Messages". 
These messages are generated by the Oracle database server when running any Oracle program.
How to free archive destination device:
Most probably archive destination is full. We should backup archive logs, then remove them.

We can use following steps for this
1. find the location of Archive destination by
 show parameter archive_dest
 
 lets say it provide  LOCATION=/u10/oradata/mydb/arch

2. move some files to some other location using os command
 cd /u10/oradata/mydb/arch
 mv /u10/oradata/mydb/arch/* /u15/oradata/mydb/arch-bkp/
 

Or it can be done using RMAN also

rman target /

RMAN> backup archive log all format '/u15/oradata/mydb/arch-bkp';

RMAN> delete archive until time 'trunc(sysdate)';

ORA-23404 refresh group does not exist

2011-11-23T17:16:00.001+05:30

ORA-23404 refresh group does not exist
Cause: A refresh group name was given that is not in SYS.RGROUP$.
Action: Provide a refresh group name that is in SYS.RGROUP$ or DBS_RGROUP.

Reference: Oracle Documentation


Example:

Today I faced this error on my DB, as I was trying to refresh a MVIEW using DBMS_REFRESH.REFRESH
 - EXEC DBMS_REFRESH.REFRESH('MYMVIEW');
But when I explorered and find DBMS_REFRESH.REFRESH refreshes a refresh group.

So I canged my statemanet to 
 - EXEC DBMS_MVIEW.REFRESH('MYMVIEW');
and it worked !!!

Another way is to make a Refresh Group first and then Refresh it using DBMS_REFRESH.REFRESH as
 - EXEC DBMS_REFRESH.MAKE(NAME=>'MY_GRP', LIST=>'MYMVIEW', NEXT_DATE => SYSDATE, INTERVAL => 'NULL');
 - EXEC DBMS_REFRESH.REFRESH('MY_GRP');

ORA-00018 maximum number of sessions exceeded

2011-11-11T10:13:00.001+05:30

ORA-00018 maximum number of sessions exceeded
Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

Reference: Oracle Documentation

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated by the 
Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba
 sqlplus / as sysdba
 
2. Check Current Setting of Parameters
 sql> show parameter sessions
 sql> show parameter processes
 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase 
"processes and "transactions" parameters
 A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1
  
4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) 
and bounce the instance.
 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup

Two Dimension Array in PL/SQL

2011-11-10T12:39:00.000+05:30

A Simple Example of 2D Array in PL/SQL

create or replace type Arr1D_Type is table of Number(2);

create or replace type Arr2D_Type is table of Arr1D_Type;

declare
 Arr2D Arr2D_Type;
begin
 Arr2D := new Arr2D_Type(
  Arr1D_Type( 1,2,3,4 ),
  Arr1D_Type( 5,6,7,8 ),
  Arr1D_Type( 9,10,11,12 ),
  Arr1D_Type( 13,14,15,16 )
    );

 DBMS_OUTPUT.PUT_LINE('     OUTPUT     ');
 DBMS_OUTPUT.PUT_LINE('----------------');
 
 for x in 1..Arr2D.Count
 loop
  for y in 1..Arr2D(x).Count
  loop
   DBMS_OUTPUT.PUT(rpad(Arr2D(x)(y),4));
  end loop;
  DBMS_OUTPUT.PUT_LINE('');
 end loop;
end;
/

OUTPUT
----------------
1   2   3   4
5   6   7   8
9   10  11  12
13  14  15  16

ORA-00001 unique constraint violated

2011-11-09T14:08:00.000+05:30

ORA-00001 unique constraint violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.

ORA-00001 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

Reference: Oracle Documentation

ORA-00000 normal, successful completion

2011-11-09T11:25:00.000+05:30

ORA-00000 normal, successful completion

Cause: An operation has completed normally, having met no exceptions.
Action: No action required.

ORA-00000 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

Create CSV file using PL/SQL

2011-09-30T14:01:00.000+05:30

To create a file, we need to create a directory and have the read write permission as

1) create or replace directory MYCSV as '/home/oracle/mycsv';
Note: /home/oracle/mycsv has to be physical location on disk.
2) grant read, write on directory MYCSV to scott;

Following is the pl/sql sample code to create CSV file

DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
    C1_R C1%ROWTYPE;
BEGIN
    F := UTL_FILE.FOPEN('MYCSV','EMP_DEPT.CSV','w',32767);
    FOR C1_R IN C1
    LOOP
        UTL_FILE.PUT(F,C1_R.EMPNO);
        UTL_FILE.PUT(F,','||C1_R.ENAME);
        UTL_FILE.PUT(F,','||C1_R.SAL);
        UTL_FILE.PUT(F,','||C1_R.DEPTNO);
        UTL_FILE.PUT(F,','||C1_R.DNAME);
        UTL_FILE.NEW_LINE(F);
    END LOOP;
    UTL_FILE.FCLOSE(F);
END;
/

After the execution of above procedure, a file (EMP_DEPT.CSV) would have been created at "/home/oracle/mycsv/" location.

You may check it on linux by

cd /home/oracle/mycsv
cat EMP_DEPT.CSV

7369,SMITH,800,20,RESEARCH
7499,ALLEN,1600,30,SALES
7521,WARD,1250,30,SALES
7566,JONES,2975,20,RESEARCH
7654,MARTIN,1250,30,SALES
7698,BLAKE,2850,30,SALES
7782,CLARK,2450,10,ACCOUNTING
7788,SCOTT,3000,20,RESEARCH
7839,KING,5000,10,ACCOUNTING
7844,TURNER,1500,30,SALES
7876,ADAMS,1100,20,RESEARCH
7900,JAMES,950,30,SALES
7902,FORD,3000,20,RESEARCH
7934,MILLER,1300,10,ACCOUNTING

Extracting text between html tags (removing html tags)

2011-09-07T11:43:00.000+05:30

In ORACLE 10G, we can simply use REGEXP_REPLACE to extract all data between HTML TAGS

Examples
--------------------------------------------------------------------------------------------------------------
WITH T AS
(
SELECT
    '<HTML><HEAD>THIS IS HEAD.</HEAD><BODY>THIS IS BODY.<P>THIS IS P.</P><H1>THIS IS H1.</H1></BODY></HTML>' STR
FROM
    DUAL
)
SELECT REGEXP_REPLACE(STR,'<.*?>') FROM T

WITH T AS
(
SELECT
    '<HTML><HEAD>THIS IS HEAD.</HEAD><BODY>THIS IS BODY.<P>THIS IS P.</P><H1>THIS IS H1.</H1></BODY></HTML>' STR
FROM
    DUAL
)
SELECT REGEXP_REPLACE(STR, '<[^>]+>') FROM T

ORACLE 11G has provided us a simple way to extract text between a specified HTML tag.

Example
--------------------------------------------------------------------------------------------------------------
WITH T AS
(
SELECT
    '<HTML><HEAD>THIS IS HEAD.</HEAD><BODY>THIS IS BODY.<P>THIS IS P.</P><H1>THIS IS H1.</H1></BODY></HTML>' STR
FROM
    DUAL
)
SELECT XMLTYPE(STR).EXTRACT('/HTML/BODY/P/text()').GETSTRINGVAL() FROM T

Related Links:

Oracle: Extract Initials of Names
http://nimishgarg.blogspot.com/2011/01/oracle-extract-initials-of-name.html

Oracle: Extract Numbers from String (Ex: Pin from Address)
http://nimishgarg.blogspot.com/2010/04/oracle-sql-extract-numbers-from-string.html

ORA-01779: cannot modify a column which maps to a non key-preserved table

2011-08-02T15:14:00.000+05:30

USING INSTEAD OF TRIGGER TO AVOID ORA-01779 WHILE UPDATING VIEW

1. Create a view using EMP and DEPT

SQL> CREATE VIEW EMP_DEPT_VW AS
2 SELECT EMPNO, ENAME, SAL, D.DEPTNO, DNAME
3 FROM SCOTT.EMP E, SCOTT.DEPT D
4 WHERE E.DEPTNO=D.DEPTNO
5 ORDER BY EMPNO, D.DEPTNO;

View created.

2. if we try to Update DNAME, it will throw an execption

SQL> update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20;
update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20
                       *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

3. Need to create a INSTEAD of Trigger on View

CREATE OR REPLACE TRIGGER EMP_DEPT_VW
INSTEAD OF UPDATE ON EMP_DEPT_VW
FOR EACH ROW
DECLARE
    V_DEPTNO SCOTT.DEPT.DEPTNO%TYPE;
BEGIN
    SELECT DEPTNO INTO V_DEPTNO FROM SCOTT.EMP
    WHERE EMPNO=:NEW.EMPNO;

    UPDATE SCOTT.DEPT
    SET DNAME = :NEW.DNAME
    WHERE DEPTNO=V_DEPTNO;

    UPDATE SCOTT.EMP
    SET ENAME = :NEW.ENAME,
    SAL=:NEW.SAL
    WHERE EMPNO=:NEW.EMPNO;
END;
/


4. Now Update will work fine

SQL> update EMP_DEPT_VW set DNAME='Rnd' where DEPTNO=20;

5 rows updated.

ORA-16000: database open for read-only access

2011-07-08T16:24:00.000+05:30

Lets suppose we have two databases opened in Read Only mode.
1. DB1
2. DB2

and on DB1, we have a DBLINK on DB2 named DB2.

Now, if we try to execute following query on DB1

SELECT EMPNO, ENAME, DNAME, SAL
FROM scott.emp EMP, scott.DEPT@DB2 DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO

it will throw following error
ORA-16000: database open for read-only access

to avoid this we must set our transaction as read only using follwoing command

set transaction read only;

Example:

Examples of Rollup and Grouping Sets

2011-06-14T12:53:00.000+05:30

ROLLUP
-------------------------------------------------------------------------------------------
select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by rollup(dname, job)

GROUPING SETS

-----------------------------------------------------------------------------------------------------------

select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal

from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by grouping sets(dname, (dname, job))

Related Link:
http://nimishgarg.blogspot.com/2010/07/oracle-rollup-query.html

Oracle DBA Daily Checklist

2011-05-17T12:19:00.003+05:30

Oracle DBA Daily Checklist

1. Check that all instances are up.

2. Monitor alert log entries (using tail -f)

3. Check that dbsnmp(SNMP subagent for Oracle DB) is running.

4. Check all last night backups were successful.

5. Check all database archiving are done.

6. Check tablespaces should not be used more that 95%.

7. Check all crons and Oracle Jobs are completed without any error

8. Verify resources for acceptable performance.

9. Identify bad growth of Segments.

10. Identify atleast 1 top resource consuming query

Set SQLPROMPT - Customizing SQL Prompt

2011-04-20T14:18:00.000+05:30

----------------------------------------------------------
SQL Prompt With Time
----------------------------------------------------------
SQL> set time on
09:50:16 SQL>
09:50:19 SQL> select sysdate from dual;

SYSDATE
---------
20-APR-11

09:50:34 SQL>

----------------------------------------------------------
SQL Prompt With USER
----------------------------------------------------------
SQL> set sqlprompt "_USER> "
SCOTT>
SCOTT>
SCOTT> select sysdate from dual;

SYSDATE
---------
20-APR-11

SCOTT>

----------------------------------------------------------
SQL Prompt With SID
----------------------------------------------------------
SQL> set sqlprompt "_CONNECT_IDENTIFIER> "
mydb>
mydb>
mydb> select sysdate from dual;

SYSDATE
---------
20-APR-11

mydb>

----------------------------------------------------------
SQL Prompt With USER@SID
----------------------------------------------------------
SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL> "
SCOTT@mydb SQL>
SCOTT@mydb SQL>
SCOTT@mydb SQL> select sysdate from dual;

SYSDATE
---------
20-APR-11

SCOTT@mydb SQL>

----------------------------------------------------------
SQL Prompt With DATE
----------------------------------------------------------
SQL> set sqlprompt "_DATE SQL> "
20-APR-11 SQL>
20-APR-11 SQL>
20-APR-11 SQL> select sysdate from dual;

SYSDATE
---------
20-APR-11

20-APR-11 SQL>

----------------------------------------------------------
SQL Prompt With Custom String
----------------------------------------------------------
SQL> set sqlprompt "Hello World> "
Hello World>
Hello World>
Hello World> select sysdate from dual;

SYSDATE
---------
20-APR-11

Hello World>

Alpha Numeric Counter Or Sequence

2011-04-06T12:23:00.000+05:30

Here is a very simple example of how to create a basic Alpha Numeric Counter (Sequence) in Oracle.

Lets try to create Alpha Numeric Counter (Sequence) as
A000
A001
A002
.
.
A999
B000
B001
.
.
.
Z999

query to create a alpha numeric sequence may be as

SELECT
CHR(65 + (LEVEL-1)/1000) || TO_CHAR(MOD(LEVEL-1,1000),'000') ALNUM_CNT
FROM
DUAL
CONNECT BY LEVEL <= 1000*26;

Related Posts:

Oracle: Fibonacci Series by SQL
http://nimishgarg.blogspot.com/2010/08/oracle-fibonacci-series-by-sql.html

Oracle: Get All Month or Week Days Names
http://nimishgarg.blogspot.com/2010/01/oracle-sql-get-all-month-names-jan-to.html

Column to Rows - Number Seprated By Any Character

2011-03-28T10:19:00.000+05:30

Value: '374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236'

Expected Output:
VALUE
------------
374627467
92876
5674
988276
87234687
8974232
3746
984027374
32742
3746236

with t as (
select '374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236' x from dual
)
select
    substr(
        ','||x,regexp_instr(','||x,'[^[:digit:]]',1,level)+1,
        regexp_instr(x||',','[^[:digit:]]',1,level) - regexp_instr(','||x,'[^[:digit:]]',1,level)
    ) value
from
    t connect by level <= length(x) - length(regexp_replace(x,'\D')) + 1;


Related Links

1. Oracle: Comma Seprated String To Rows 10g Onwards
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html

2. Oracle: Comma Seprated String To Rows 11g
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows.html

3. Oracle: Nth Record from Comma Seprated String
http://nimishgarg.blogspot.com/2010/06/oracle-nth-record-from-comma-seprated.html

Oracle: Comma Seprated String To Rows
http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

SYS_CONTEXT

2011-03-11T17:32:00.000+05:30

SYS_CONTEXT returns the value of parameter associated with the context namespace.
USERENV is an Oracle provided namespace that describes the current session.

1. sid of current session
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
OR
SELECT USERENV('SID') FROM DUAL

2. name of host from which client has connectd to oracle
SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL;

3. os username of the client process that has started the session.
SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM DUAL;

4. ip address from which the client is connected.
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL;

5. name of the instance
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') FROM DUAL;

6. name of the database as db_name initialization parameter.
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;

7. domain of the database as db_domain initialization parameter.
SELECT SYS_CONTEXT('USERENV', 'DB_DOMAIN') FROM DUAL;

8. db user name by which the current user is authenticated.
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

9. get sid and serial# from current session using the auditing session identifier
SELECT SID, SERIAL# FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID');

Reference: http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/functions150.htm

Playing With Truncate and Date

2011-03-03T12:00:00.000+05:30

current date
SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') CDATE FROM DUAL;

CDATE
--------------------
01-MAR-2011 16:54:25

date truncated to the 1st day of the year
SQL> SELECT TRUNC(SYSDATE, 'YEAR') YEAR FROM DUAL;

'YEAR'
-------------------
01-JAN-11

date truncated to the 1st day of the quarter
SQL> SELECT TRUNC(SYSDATE, 'Q') QUARTER FROM DUAL;

QUARTER
-------------------
01-JAN-11

date truncated to the 1st day of the month
SQL> SELECT TRUNC(SYSDATE, 'MONTH') MONTH FROM DUAL;

MONTH
-------------------
01-MAR-11

date truncated to the 1st day of the week
SQL> SELECT TRUNC(SYSDATE, 'DAY') DAY FROM DUAL;

DAY
-------------------
27-FEB-11

date truncated to the day
SQL> SELECT TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY HH24:MI:SS') TDATE FROM DUAL;

TDATE
--------------------
01-MAR-2011 00:00:00


date truncated to the hour
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'HH'),'DD-MON-YYYY HH24:MI:SS') THOUR FROM DUAL;

THOUR
--------------------
01-MAR-2011 16:00:00


date truncated to the minute
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'MI'),'DD-MON-YYYY HH24:MI:SS') TMINUTE FROM DUAL;

TMINUTE
--------------------
01-MAR-2011 16:53:00

date truncate to last monday
SQL> SELECT TRUNC(SYSDATE -1,'DAY')+1 FROM DUAL;

TRUNC(TO_
---------
21-FEB-11

Oracle: Some Important Date Queries
http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html

Oracle: Dates Difference in days, hours, minutes & seconds
http://nimishgarg.blogspot.com/2009/12/get-dates-difference-in-days-hours.html

Oracle SQL: Date Difference in Days, Months and Years
http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html

Top N query - 3rd highest salary

2011-02-24T12:02:00.000+05:30

Lets suppose we want to find a employee who is getting 3rd highest salary

We can try it with rownum but it may output worng result if first 2 employees have same salary

1. select * from (select emp.*, rownum rn from (select * from scott.emp order by sal desc) emp) where rn = 3

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20          3

2. select * from (select emp.*, row_number() over (order by sal desc) rn from scott.emp) where rn = 3

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          3

But the better solution is dense_rank

3. select * from (select emp.*, dense_rank() over (order by sal desc) rn from scott.emp) where rn = 3

      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          3

4. select * from scott.emp b where (select count(distinct sal) from scott.emp a where a.sal >= b.sal) = 3 order by deptno, sal

      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

Using Statspack Report

2011-02-18T12:11:00.000+05:30

Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package

Note: All sql scripts are store in $ORACLE_HOME/rdbms/admin

To Install statspack
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spcreate

To schedule an hourly snapshot
cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@spauto.sql

To take a snapshot manually
cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
exec statspack.snap;
Or
exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

To generate statspack report
cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@spreport.sql

to delete snapshots
cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@sppurge;

To list all generated snapshots
cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
select
    snap_id,
    snap_level,
    to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Snap_Time"
from
    stats$snapshot,
    v$database
order by
    snap_id
/

To uninstall statspack
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spdrop

ORA-01436: CONNECT BY loop in user data

2011-02-11T11:03:00.000+05:30

Lets assume our data is:

select * from emp;

when we execute a hierarchical query, it throws an error as

select empno, ename, sys_connect_by_path(ename,' -> ') tree from emp connect by prior empno=mgrno;

To trace this error of Connet by loop using connect_by_iscycle, we may execute following query:

select emp.*, connect_by_iscycle from emp where connect_by_iscycle = 1 connect by nocycle prior empno=mgrno;

and now we can correct this cyclic loop as:

update emp set mgrno=2497 where empno=3061;

and execute our previous hierarchical query

select empno, ename, sys_connect_by_path(ename,' -> ') tree from emp connect by prior empno=mgrno;

Related Links
http://nimishgarg.blogspot.com/2010/02/oracle-connect-by-prior-hierarchical.html

Oracle: Getting Simulated Cumulative Sum (Running Total)

2011-02-03T14:21:00.000+05:30

Using Analytical Functions
select
    ename,
    dname,
    sal,
    sum(sal) over (partition by dname) dept_wise_total,
    sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
    sum(sal) over () total,
    sum(sal) over (order by dname, ename) running_total
from
    scott.emp e,
    scott.dept d
where
    e.deptno=d.deptno
order by
    dname, ename

---------------------------------------------------------------------------------------------------
Simulated Version
select
    ename,
    dname,
    sal,
    sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
    (select sum(sal) from scott.emp b where b.deptno=e.deptno and b.ename <= e.ename) sim_dept_runtotal,
    sum(sal) over (order by dname, ename) running_total,
    (select sum(sal) from scott.emp b where b.ename <= e.ename) sim_running_total
from
    scott.emp e,
    scott.dept d
where
    e.deptno=d.deptno
order by
    dname, ename

Output

Related Links

http://nimishgarg.blogspot.com/2010/02/oracle-getting-cumulative-sum-using.html

Oracle: Simulating Rank Analytical Functions (Manual Rank)

http://nimishgarg.blogspot.com/2010/06/oracle-simulating-rank-analytical.html

Oracle: Simulating Dense_Rank Analytical Functions (Manual Dense_Rank)

http://nimishgarg.blogspot.com/2010/06/oracle-simulating-denserank-analytical.html

http://nimishgarg.blogspot.com/2010/06/oracle-simulating-analytical-functions.html

Oracle: Simulating Lead Analytical Functions (Manual Lead)

http://nimishgarg.blogspot.com/2010/06/oracle-simulate-lead-analytical.html

Oracle: Delete duplicate rows from table

2011-01-25T09:53:00.000+05:30

Lets say out table is
SQL> desc emp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                                NOT NULL             NUMBER(4)
ENAME                                                                                 VARCHAR2(10)
JOB                                                                                        VARCHAR2(9)
MGR                                                                                      NUMBER(4)
HIREDATE                                                                            DATE
SAL                                                                                        NUMBER(7,2)
COMM                                                                                  NUMBER(7,2)
DEPTNO                                                                               NUMBER(2)

1. Simple & Easy Way
delete from emp where rowid not in (select max(rowid) from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno);

2. Optimized with outer Join
delete from emp where rowid in
(
select rid_d from
(select rowid rid_d from emp) a,
(select max(rowid) rid from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno) b
where rid_d = rid (+)
and rid is null
)

3. Alternate way to delete duplicate with analytical function
delete from emp
where rowid in
(
select
  rid
from
(
  select
   rowid rid,
   row_number() over (partition by empno,ename,job,mgr,hiredate,sal,comm,deptno order by rowid) rn
  from emp
)
where rn <> 1
)

Oracle: Extract Initials of Names

2011-01-17T17:11:00.000+05:30

Lets Say we have a table "Employee" with following data

SQL> select employeename from employee;

Use following query to extract Initials of names.

SQL> select employeename, upper(regexp_replace(employeename,'(^| )([^ ])([^ ])*','\2')) Initials from Employee;

Related Links:

Extracting text between html tags (removing html tags)
http://nimishgarg.blogspot.com/2011/09/extracting-text-between-html-tags.html

Oracle: Extract Numbers from String (Ex: Pin from Address)
http://nimishgarg.blogspot.com/2010/04/oracle-sql-extract-numbers-from-string.html

ORA-01723: zero-length columns are not allowed

2011-01-05T12:23:00.000+05:30

Peoblem:

SQL> Create table mytable as
select
        ename,
        null age,
    null doj,
    null mgrname
from
        scott.emp ;

        null age
        *
ERROR at line 4:
ORA-01723: zero-length columns are not allowed

Solution:
To avoid this error use cast:
SQL> Create table mytable as
select
        ename,
        cast(null as number) age,
        cast(null as date) doj,
        cast(null as varchar2(10)) mgrname
from
        scott.emp ; 2    3    4    5    6    7    8

Table created.

SQL> desc mytable
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ENAME                                              VARCHAR2(10)
AGE                                                NUMBER
DOJ                                                DATE
MGRNAME                                            VARCHAR2(10)