Archive

Archive for the ‘Trick And Tips’ Category

How delete duplicate rows from a Table

Creating Table and Insert some sample Data

CREATE TABLE PROC_DATA_LOG
  (
    CN      VARCHAR2(20 BYTE),
    A_DATA  VARCHAR2(400 BYTE)
  );
Select * from PROC_DATA_LOG;

CN                   A_DATA                                          
-------------------- ------------
1                    Test Data 1
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

SQL to delete the duplicate rows

DELETE FROM PROC_DATA_LOG  
WHERE ROWID NOT IN (
                    SELECT MAX (ROWID)
                    FROM PROC_DATA_LOG
                    GROUP BY CN
                   );

All duplicate row base on CN is deleted

Select * from PROC_DATA_LOG;

CN                   A_DATA                                                   
-------------------- ------------
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

Delete Duplicate Rows using Analytic functions

DELETE FROM PROC_DATA_LOG
WHERE ROWID IN ( 
        SELECT ROWID
        FROM (
              SELECT
              ROW_NUMBER() OVER (PARTITION BY CN ORDER BY CN) rnk
              FROM   PROC_DATA_LOG
             )
        WHERE rnk>1
        );

How to Insert and Update ampercent(‘&’) character in the Oracle table

February 6, 2010 Leave a comment
SQL> CREATE TABLE TEST ( TEST_ID  NUMBER,   TEST_DATA   VARCHAR2(50));
Table created.

To Insert or Update & into character field do the follow

SQL> show ESCAPE
escape OFF
SQL> Set ESCAPE "/"
SQL> show ESCAPE
escape "/" (hex 2f)

Now Insert Into the Test Table

SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (1,'Hospital /& Healthcare');

1 row created. 

Now Update Into the Test Table

SQL> UPDATE TEST
 2  Set TEST_DATA = 'Outsourcing /& Offshoring'
 3  WHERE TEST_ID = 1;

1 row updated.

Use the 10g Quoting mechanism:

Syntax q'[QUOTE_CHAR]Text[QUOTE_CHAR]'

N.B: Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Oracle’s ‘quoted’ text field & I like Oracle}’ FROM DUAL;
SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (2,q'{ Aviation & Aerospace}');

1 row created.

SQL> UPDATE TEST
 2  Set TEST_DATA = q'{Information Technology & Services}'
 3  WHERE TEST_ID = 2;

1 row updated.
SQL> Select * from test;

 TEST_ID    TEST_DATA
---------- --------------------------------------------------
 1    Outsourcing & Offshoring
 2    Information Technology & Services

How to determine Oracle Database And OS version

November 16, 2009 Leave a comment

The following query determine you the database version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production

Alternatively, you can query from product_component_version.

SQL> col product for a40
SQL> col version for a11
SQL> col status for a15
SQL> select * from product_component_version;
PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- ---------------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
TNS for 32-bit Windows:                  10.2.0.1.0  Production

By using export import data pump command you can determine client version

C:\>expdp help=y
Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:33:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.

C:\>impdp help=y
Import: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 1:32:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Both in case of Data Pump Export and Import first few digits show it version.

How to determine database compatibility level

COMPATIBILITY allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
In order to determine your current database compatibility level you can query from view database_compatible_level.

SQL> col value for a11
SQL> col description for a50
SQL> select * from database_compatible_level;
VALUE          DESCRIPTION
-----------    -----------------------------------------------------------------
10.2.0.1.0     Database will be completely compatible with this software version

Alternatively you can check your initialization parameter COMPATIBLE setting.

SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
compatible                           string      10.2.0.1.0

You can also query from v$parameter.

SQL> select value from v$parameter where name='compatible';

VALUE
-----------
10.2.0.1.0

Listener Version

$ lsnrctl version

Version of OPATCH

$ perl $ORACLE_HOME/OPatch/opatch.pl version

Sun Solaris Version

$ cat /etc/release

RedHat Linux Version

vi /etc/redhat-release

Perl Version

$ perl -v
Or
$ perl -version

Java Version

$ java -version

Version of Installed packages on Solaris

$ pkginfo -igrep perl

Version of Installed packages on Linux

$ rpm -qagrep

Kernel Version of Unix

$ uname -a

Bit of Operating System

$ isainfo -b

Bit of your Oracle Software

To check if your Oracle Binary is 32 bit or 64 bit you can use the file command on any of the oracle executable like

$ file $ORACLE_HOME/bin/oracle

Finding Oracle data block size

SQL> SHOW PARAMETER DB_BLOCK_SIZE

Finding O/S block size

df -g grep "block size" (in Solrieas)

Check my Operating System 64-bit?

Solaris

$/usr/bin/isainfo –kv

Linux

$uname -m