Query to check table size

Use following query to check table size in your schema by changing schema name in WHERE Clause.


select segment_name,segment_type,bytes/1024/1024/1024 GB,ds.*
 from dba_segments ds
 where segment_type='TABLE' and owner='SCOTT' order by 3 desc



Replace "SCOTT" with your schema/user name to get objects from your schema.

EXPDP-- Export Dump

Hi,

Lets see some basic steps to do Export data from one  schema another schema.

To do export data ,we have Oracle Utility i.e. EXPDP and same way to do import in another schema ,we can use IMPDP .

Dump file which  is created by EXPDP can be imported by using only IMPDP utility.


Basic Requirements:
1) Need to have directory ==> When we do EXPDP then need to specify directory name.In this directory only DUMP file will be stored and can be used further to do import.


Check  here to create directory.


2) Privilege ==>  Need to get Privilege from DBA to do Export/Import in any schema.

Those are..

SQL> grant EXP_FULL_DATABASE to CNYCBAIND;

Grant succeeded


SQL> grant IMP_FULL_DATABASE to CNYCBAIND;

Grant succeeded.


You can query below table to check whether your schema got privilege or not.

select * from dba_role_privs where grantee='SCOTT';

here 'SCOTT' is schema name,replace this with your schema name.

Now  you are ready to do export or import.

1) How to do Full export.

By default ,oracle Expdp will do full database export.

username ==> need to specify your schema credentials
Directory==> Directory name which you created in 1st step
Dumpfile==> Specify name whichever you want.Dumpfile will be  created with same name which you mentioned here.
Logfile ==> Specify name whichever you want.Log file is to check your Export or import activity status and errors etc.
Parallel ==> How many process to run parallel for your export or import activity.

Please note, mention all options names in  CAPS only.

expdp USERNAME=Username/Password DIRECTORY=CBADUMP
DUMPFILE=expdp_full.dmp
LOGFILE=Export_Full.LOG
PARALLEL=8



2) How to do particular table export.

expdp USERNAME=Username/Password DIRECTORY=CBADUMP
DUMPFILE=expdp_table.dmp
LOGFILE=Export_table.LOG
PARALLEL=8
TABLES=ICTB_ENTRIES_HISTORY

TABLES-->  Specify which table you want to export,suppose if you want to export multiple tables then use ','(comma) between tablenames.

ORA-24005: Inappropriate utilities used to perform DDL on AQ table

We cant directly drop queue tables using drop command .

Follow below steps to drop Queue table:

Syntax:


BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE('name of queue table',TRUE);
END;
/

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

You may be facing error "ORA-30036" while doing  import since it will through if your tablespace doesn't have sufficient space  in it.

let's check this error and resolve it.

From above error,its telling that in "UNDOTBS1" tablespace is full and doesn't have space to store any extra data.

You can see in below screenshot that its shows empty space.

Use below command to add data file(Space to tablespace).

ALTER tablespace  UNDOTBS1 add datafile '/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'  size 10000M

--here "/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf'" is your data file ,which you are adding to tablespace.

Now run below  query  to see list of datafiles in your tablespace.

SQL> select df.file_name,
  2            df.tablespace_name,
  3            ROUND(sum(bytes) / 1024 / 1024 / 1024, 2) Size_In_GB
  4       from dba_data_files df
  5       join dba_TABLESPACES dt on dt.tablespace_name = df.tablespace_name
  6      Where dt.tablespace_name = 'UNDOTBS1'
  7      group by df.file_name, df.tablespace_name
  8  ;

FILE_NAME                                                   TABLESPACE_NAME                SIZE_IN_GB
-------------------------------------------------------------------------------- ------------------------------ ----------
/mnt/devops_0/dba/oradata/APAC11R2/undotbs03.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs02.dbf            UNDOTBS1                             4.88
/mnt/devops_0/dba/oradata/APAC11R2/undotbs05.dbf            UNDOTBS1                             9.77
/mnt/devops_0/dba/oradata/APAC11R2/undotbs01.dbf            UNDOTBS1                               32
/mnt/devops_0/dba/oradata/APAC11R2/undotbs04.dbf            UNDOTBS1                             4.88

Query:   select b.tablespace_name, tbs_size Size_Gb, a.free_space Free_gb
                                from (select tablespace_name,
                                   round(sum(bytes) / 1024 / 1024 / 1024, 2) as free_space
                        from dba_free_space    group by tablespace_name) a, (select tablespace_name,                                       sum(bytes) / 1024 / 1024 / 1024 as tbs_size  from dba_data_files
group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
   and b.tablespace_name = 'UNDOTBS1';

Query to check locked objects

Below query will provide list of objects are under particular Schema.

SELECT a.sid,
       a.serial#,
       a.username,
       c.os_user_name,
       a.program,
       a.logon_time,
       a.machine,
       a.terminal,
       b.object_id,
       substr(b.object_name, 1, 40) object_name,
       DECODE(c.locked_mode,
              1,
              'No Lock',
              2,
              'Row Share',
              3,
              'Row Exclusive',
              4,
              'Shared Table',
              5,
              'Shared Row Exclusive',
              6,
              'Exclusive') locked_mode
  from v$session a, dba_objects b, v$locked_object c
 where a.sid = c.session_id
   and b.object_id = c.object_id
--   and a.USERNAME = 'CNYCBAIND'.

Now use Below command to kill session.

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

SQL> ALTER SYSTEM KILL SESSION '483,6849' immediate;

session will be killed.

How to create Directory

let's check out create directory syntax .


SQL> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/scratch/Dump_CBA';

Directory created

SQL> grant read,write,execute on directory TEST_DIR to SCOTT;

Grant succeeded

SQL> select * from all_directories where directory_name='TEST_DIR';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ -------------------------------------------------------------
SYS                            TEST_DIR                                     /scratch/Dump_CBA

ORA-14452

We cant drop temporary table directly. If you try  to drop you would get below error.

So truncate this table first and then try to drop it.you will be succeed.

ORA-00942: table or view does not exist

Some times you may trying to  drop table which is exist in database but unfortunately you would get weird error saying that "table/view doesn't exist" even though table exist in database.


Here,one thing you need to observe.i.e.Check whether table is stored with upper case or lower case or mixed by querying in User_tables.

SQL> SELECT * FROM USER_TABLES;


By default all objects(i.e. table,view,procedures etc ) will be stored with upper case unless you specify case sensitive.

If you had created table or any object with with case sensitive then you cant drop those tables or objects directly with normal command.


You need specify double quotes " " both sides of your object while drop table/object.


lets check one example:

EXIT In Oracle

Exit: 

In Oracle,Exit statement most commonly used to terminate LOOP statements.

Syntax: 

EXIT (when CONDITION);

here when condition is optional.


Example 1:

Loop
v_sal := sal * comm;
exit when v_sal >10000;
End loop;


In this example ,loop would be terminated when v_sal becomes more than 10000.

 Example 2:


LOOP

v_sal := sal * comm;
  IF v_sal >10000 THEN
    EXIT;
  END IF;
END LOOP;

WHILE LOOP

While Loop:


In Oracle,we use while loop if we are not sure how many times we need to execute particular LOOP or that LOOP may not execute even once also.


Syntax:
WHILE (CONDITION)
LOOP
STATEMENTS;
END LOOP;


Here condition is to pass the LOOP,if condition is satisfied then it ill continue LOOP otherwise it will terminate LOOP.


Example:

while (v_sal < 10000)
loop
v_sal := sal * comm;
End loop;


here while loop will check condition first,here condition is v_sal should be less than 10000.So until v_sal becomes 1000 this loop will get executed but once it becomes 10000 then it ill terminate LOOP.

Query to Find tablespace size

Hi,


Using below query ,we can find all table spaces sizes in respective Instance.


select b.tablespace_name, tbs_size Size_Gb, a.free_space Free_gb

from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space

       from dba_free_space

       group by tablespace_name) a,

      (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size

       from dba_data_files

       group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name;

Script for Dropping all objects from Schema

Guys,Use below script to drop all objects from schema but be careful when you are firing below command any schema,Check whether you are executing following script in correct schema.


Begin
For c1 in(select a.object_name object_name,a.object_type object_type from user_objects a where a.object_type not in('LOB','INDEX','TABLE PARTITION','TRIGGER','INDEX PARTITION','PACKAGE BODY','JOB',
'JAVA CLASS') And upper(object_name) = object_name)

LOOP
IF C1.object_type ='TABLE' THEN
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' CASCADE CONSTRAINTS ';
ELSIF C1.object_type ='JAVA SOURCE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
ELSIF C1.object_type ='TYPE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' FORCE'; 
ELSE
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
END IF;
End Loop;
Exception
When Others then
Dbms_Output.put_line('Msg '||SQLERRM );
End;


Don't forget to purge the recyclebin,

Execute below command:

SQL> Purge Recyclebin;

Recyclebin is same as our windows recycle bin which will store all deleted files.


If you dont want to move dropping items to recycle bin then you can make it off for particular session using below command.

SQL> ALTER SESSION SET RECYCLEBIN=OFF;

(Or ) --For On

SQL > ALTER SYSTEM SET RECYCLEBIN= ON;

Below command is for entire Database

SQL > ALTER SYSTEM SET RECYCLEBIN= OFF;

(or)

SQL > ALTER SESSION SET RECYCLEBIN= OFF;

You can cross check whether recycle bin is On/Off as of now using below query.

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

(Or) 

SQL > SHOW PARAMETER RECYCLEBIN;

and you can query recyclebin to check dropped objects.

SQL> Select * from Recyclebin;

Objecttypes which will be stored in recyclebin:

 

Before/After purging recyclebin:

And use below  program to drop Oracle JOBS from your database.

 

Script For compiling invalid objects

Hi Frds,You may face a situation to compile invalid objects but it would be difficult to compiling one  by one object.

To do in single step,we can use following anonymous block.

1) 
Begin
 For C1 IN(select a.object_name,a.object_type from User_Objects a where  status='INVALID')
LOOP
IF C1.OBJECT_TYPE='PACKAGE BODY' THEN

EXECUTE IMMEDIATE 'ALTER PACKAGE '||C1.object_name||' COMPILE BODY';
ELSE

EXECUTE IMMEDIATE 'ALTER '||C1.OBJECT_TYPE||' '||C1.object_name||' COMPILE';

End If;
End Loop;
Exception
When Others then

Dbms_Output.put_line('Msg '||SQLERRM);
End;  


Above script can be modified to compile specific object type i.e like synonym,view,package etc by using where clause.

If still don't want to execute above script then you can use below utility provided by Oracle to compile full schema.

2) 

SQL > exec dbms_utility.compile_schema(schema => 'Schema Name');




Below one is one more simple method to compile full schema.

3)

SQL> utl_recomp.recomp_parallel('20',<schema name>);

How to connect through SQL Developer

In earlier post ,we have seen how to install SQL developer and now lets check out how can we add connection/schema to SQL developer.


Open SQL developer and click on new connection as shown below.

now pop up window ill open to ask you for details as below.

here mainly you have to concentrate on
1) username and password ==> These can be get it from DBA .
2) SID==> it means you need to mention your database name
3) Host name ==> Let it be local host if  database is installed in your machine otherwise you have provide IP address of where database is installed.

Now you can do testing of your connection by clicking on "Test" and you can see status .
If its success then you can click on connect and use it for your work.

How to Install Oracle SQL Developer

Hi Guys,


After installing Oracle Database ,we search for tools like TOAD,PLSQL developer etc which can be used to work with database but you have to pay for it orelse you can get trail version.

By default Oracle will provide Oracle SQL developer which can be found as below.

Click on SQL developer and then it will ask for java path as below.

Java path can be found from your oracle installed path itlself,it would be similar to below path...

C:\app\Venky\product\11.2.0\dbhome_1\jdk\bin

In above path you have search for java.exe file and provide that path as below.

once you click on "ok" ,it ill be installed on your machine for your use and you can connect to database using this tool.

Group Functions

GROUP FUNCTIONS

a)    Sum
b)    Avg
c)    Max
d)    Min
e)    Count

Group functions will be applied on all the rows but produces single output.

a) SUM

  This will give the sum of the values of the specified column.

     Syntax: sum (column)

     Ex:   SQL> select sum(sal) from emp;

  SUM(SAL)
   ----------
     38600

b) AVG

     This will give the average of the values of the specified column.

     Syntax: avg (column)

     Ex:     SQL> select avg(sal) from emp;

   AVG(SAL)
   ---------------
   2757.14286

c) MAX

     This will give the maximum of the values of the specified column.

     Syntax: max (column)

     Ex:   SQL> select max(sal) from emp;

    MAX(SAL)
   ----------
     5000

d) MIN

     This will give the minimum of the values of the specified column.

     Syntax: min (column)

     Ex:    SQL> select min(sal) from emp;

   MIN(SAL)
   ----------
     500

e) COUNT

     This will give the count of the values of the specified column.

     Syntax: count (column)

     Ex:    SQL> select count(sal),count(*) from emp;

COUNT(SAL)               COUNT(*)
--------------         ------------
        14                   14

Conversion and Miscellaneous Functions

MISCELLANEOUS FUNCTIONS

a)    Uid
b)    User
c)    Vsize
d)    Rank
e)    Dense_rank

a) UID

     This will returns the integer value corresponding to the user currently logged in.

     Ex: SQL> select uid from dual;

       UID
    ----------
       319

b) USER

     This will returns the login’s user name.

     Ex:  SQL> select user from dual;


USER
--------
RAM

c) VSIZE

     This will returns the number of bytes in the expression.

     Ex:    SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual;

VSIZE(123)         VSIZE('COMPUTER')        VSIZE('12-JAN-90')
-------------  -----------------------  ----------------------
         3                     8                          9

d) RANK

     This will give the non-sequential ranking.

     Ex:    SQL> select rownum,sal from (select sal from emp order by sal desc);
    ROWNUM        SAL
    ---------- ----------
         1          5000
         2          3000
         3          3000
         4          2975
         5          2850
         6          2450
         7          1600
         8          1500
         9          1300
        10          1250
        11          1250

     SQL> select rank(2975) within group(order by sal desc) from emp;

RANK(2975)WITHINGROUP(ORDERBYSALDESC)
---------------------------------------------------------
                                    4

d) DENSE_RANK

     This will give the sequential ranking.
    
Ex:    SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
-----------------------------------------------------------------
                                          3

CONVERSION FUNCTIONS

a)    Bin_to_num
b)    Chartorowid
c)    Rowidtochar
d)    To_number
e)    To_char
f)    To_date

a) BIN_TO_NUM

     This will convert the binary value to its numerical equivalent.

     Syntax: bin_to_num( binary_bits)

     Ex:    SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)
------------------------
                6

1)    If all the bits are zero then it produces zero.
2)    If all the bits are null then it produces an error.

b) CHARTOROWID

     This will convert a character string to act like an internal oracle row identifier or rowid.

c) ROWIDTOCHAR

    This will convert an internal oracle row identifier or rowid to character string.

d) TO_NUMBER

    This will convert a char or varchar2 to number.

e) TO_CHAR

    This will convert a number or date to character string.

f) TO_DATE

    This will convert a number, char or varchar2 to a date.

Date Functions

In previous posts,we learnt about Numeric Functions 
and String Functions ,Now lets check out Date Functions in SQL.

DATE FUNCTIONS

a)    Sysdate
b)    Current_date
c)    Current_timestamp
d)    Systimestamp
e)    Localtimestamp
f)    Dbtimezone
g)    Sessiontimezone
h)    To_char
i)    To_date
j)    Add_months
k)    Months_between
l)    Next_day
m)    Last_day
n)    Extract
o)    Greatest
p)    Least
q)    Round
r)    Trunc
s)    New_time
t)    Coalesce

Oracle default date format is DD-MON-YY.
We can change the default format to our desired format by using the following command.

SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’;
        But this will expire once the session was closed.

a) SYSDATE

     This will give the current date and time.
      Ex:    SQL> select sysdate from dual;

SYSDATE
-----------
24-DEC-06

b) CURRENT_DATE

     This will returns the current date in the session’s timezone.

      Ex:   SQL> select current_date from dual;

CURRENT_DATE
------------------
     24-DEC-06

c) CURRENT_TIMESTAMP

     This will returns the current timestamp with the active time zone information.

      Ex:   SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.42.41.383369 AM +05:30

d) SYSTIMESTAMP

     This will returns the system date, including fractional seconds and time zone of the
      database.

      Ex:     SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.49.31.830099 AM +05:30

e) LOCALTIMESTAMP

     This will returns local timestamp in the active time zone information, with no time
     zone information shown.

      Ex:       SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.44.18.502874 AM

f) DBTIMEZONE

 This will returns the current database time zone in UTC format. (Coordinated Universal
    Time)

    Ex:   SQL> select dbtimezone from dual;

DBTIMEZONE
---------------
   -07:00 

g) SESSIONTIMEZONE

    This will returns the value of the current session’s time zone.

    Ex:    SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
------------------------------------
+05:30

h) TO_CHAR

    This will be used to extract various date formats.
    The available date formats as follows.

    Syntax: to_char (date, format)
   
    DATE FORMATS
   
    D          --    No of days in week
    DD         --    No of days in month
    DDD        --    No of days in year
    MM         --    No of month
    MON        --    Three letter abbreviation of month
    MONTH      --    Fully spelled out month
    RM         --     Roman numeral month
    DY         --    Three letter abbreviated day
    DAY        --    Fully spelled out day
    Y          --    Last one digit of the year
    YY         --    Last two digits of the year
    YYY        --    Last three digits of the year
    YYYY       --    Full four digit year
    SYYYY      --    Signed year
    I          --    One digit year from ISO standard
    IY         --    Two digit year from ISO standard
    IYY        --    Three digit year from ISO standard
    IYYY       --    Four digit year from ISO standard
    Y, YYY     --    Year with comma
    YEAR       --    Fully spelled out year
    CC         --    Century
    Q          --    No of quarters
    W          --    No of weeks in month
    WW         --     No of weeks in year
    IW         --    No of weeks in year from ISO standard
    HH         --    Hours
    MI         --    Minutes
    SS         --    Seconds
    FF         --    Fractional seconds
    AM or PM   --    Displays AM or PM depending upon time of day
    A.M or P.M --    Displays A.M or P.M depending upon time of day
    AD or BC   --    Displays AD or BC depending upon the date
    A.D or B.C --    Displays AD or BC depending upon the date
    FM         --    Prefix to month or day, suppresses padding of month or day
    TH         --    Suffix to a number
    SP         --    suffix to a number to be spelled out
    SPTH       --    Suffix combination of TH and SP to be both spelled out
    THSP       --    same as SPTH

Ex:    
SQL> select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;

TO_CHAR(SYSDATE,'DD MONTH YYYYHH:MI
----------------------------------------------------
24 december  2006 02:03:23 pm sun   

SQL> select to_char(sysdate,'dd month year') from dual;


TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
24 december  two thousand six

SQL> select to_char(sysdate,'dd fmmonth year') from dual;

TO_CHAR(SYSDATE,'DD FMMONTH YEAR')
-------------------------------------------------------
24 december two thousand six

SQL> select to_char(sysdate,'ddth DDTH') from dual;

TO_CHAR(S
------------
24th 24TH

SQL> select to_char(sysdate,'ddspth DDSPTH') from dual;

TO_CHAR(SYSDATE,'DDSPTHDDSPTH
------------------------------------------
twenty-fourth TWENTY-FOURTH

SQL> select to_char(sysdate,'ddsp Ddsp DDSP ') from dual;

TO_CHAR(SYSDATE,'DDSPDDSPDDSP')
------------------------------------------------
twenty-four Twenty-Four TWENTY-FOUR


i) TO_DATE

    This will be used to convert the string into data format.

    Syntax: to_date (date)

    Ex:  
SQL> select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day')
                 from dual;

TO_CHAR(TO_DATE('24/DEC/20
--------------------------
24 * december  * Sunday

-- If you are not using to_char oracle will display output in default date format.
   
j) ADD_MONTHS

    This will add the specified months to the given date.

    Syntax: add_months (date, no_of_months)

    Ex:   
SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), 5) from dual;

ADD_MONTHS
----------------
11-JUN-90

SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), -5) from dual;

ADD_MONTH
---------------
11-AUG-89          

1)    If no_of_months is zero then it will display the same date.
2)    If no_of_months is null then it will display nothing.k) MONTHS_BETWEEN

    This will give difference of months between two dates.

    Syntax: months_between (date1, date2)

    Ex:         
SQL> select months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-  jan-1990','dd-mon-yyyy')) from dual;

MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DD-MON-YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY'))
-----------------------------------------------------------------------------------------------
                                                                                        7
           
SQL> select months_between(to_date('11-jan-1990','dd-mon-yyyy'), to_date('11-aug-1990','dd-mon-yyyy')) from dual;
 
MONTHS_BETWEEN(TO_DATE('11-JAN-1990','DD-MON-YYYY'),TO_DATE('11-AUG-1990','DD-MON-YYYY'))
-------------------------------------------------------------------------------------------------
                                                                                       -7

l) NEXT_DAY

    This will produce next day of the given day from the specified date.

    Syntax: next_day (date,  day)

Ex:  
SQL> select next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;

NEXT_DAY(
-------------
31-DEC-06

-- If the day parameter is null then it will display nothing.

m) LAST_DAY

    This will produce last day of the given date.

    Syntax: last_day (date)
Ex:     
SQL> select last_day(to_date('24-dec-2006','dd-mon-yyyy')) from dual;
LAST_DAY(
-------------
31-DEC-06

n) EXTRACT
    This is used to extract a portion of the date value.
    Syntax: extract ((year | month | day | hour | minute | second), date)
    Ex:   
SQL> select extract(year from sysdate) from dual;
 
EXTRACT(YEARFROMSYSDATE)
------------------------------------
                    2006
-- You can extract only one value at a time.

o) GREATEST
     This will give the greatest date.
     Syntax: greatest (date1, date2, date3 … daten)

Ex:    
SQL>select greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon-yy'),to_date('11-apr-90','dd-mon-yy')) from dual;

  GREATEST(
  -------------
   11-APR-90

p) LEAST

     This will give the least date.
     Syntax: least (date1, date2, date3 … daten)

Ex:
SQL> select least(to_date('11-jan-90','dd-mon-yy'), to_date('11-mar-90','dd-mon-yy'),to_date('11-apr-90','dd-mon-yy')) from dual;

  LEAST(
  -------------
   11-JAN-90

q) ROUND

    Round will rounds the date to which it was equal to or greater than the given date.

    Syntax: round (date, (day | month | year))

    If the second parameter was year then round will checks the month of the given date in the following ranges.

JAN     --    JUN   
JUL     --    DEC

    1) If the month falls between JAN and JUN then it returns the first day of the current year.
    2) If the month falls between JUL and DEC then it returns the first day of the next year.

    3) If the second parameter was month then round will checks the day of the given date in the following ranges.

    1      --    15   
    16     --    31

    If the day falls between 1 and 15 then it returns the first day of the current month.
    If the day falls between 16 and 31 then it returns the first day of the next month.

    If the second parameter was day then round will checks the week day of the given date in the following ranges.

    SUN    --    WED
    THU    --    SUN

    If the week day falls between SUN and WED then it returns the previous sunday.
    If the weekday falls between THU and SUN then it returns the next sunday.

1)    If the second parameter was null then it returns nothing.
2)    If the you are not specifying the second parameter then round will resets the time to the begining of the current day in case of user specified date. 
3)    If the you are not specifying the second parameter then round will resets the time to the the time to the ining of the next day in case of sysdate.
Ex:  
SQL> select round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-06','dd-mon-yy'),'year') from dual;

ROUND(TO_ ROUND(TO_
------------   ---------------
01-JAN-05   01-JAN-06

SQL> select round(to_date('11-jan-04','dd-mon-yy'),'month'), round(to_date('18- jan-04','dd-mon-yy'),'month') from dual;  

ROUND(TO_ ROUND(TO_
-------------  ---------------
01-JAN-04    01-FEB-04

SQL> select round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-06','dd-mon-yy'),'day') from dual;
  
ROUND(TO_ ROUND(TO_
--------------  --------------
24-DEC-06     31-DEC-06

SQL> select to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss am') from dual;

TO_CHAR(ROUND(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am
r) TRUNC

    Trunc will chops off the date to which it was equal to or less than the given date.

    Syntax: trunc (date, (day | month | year))

1)    If the second parameter was year then it always returns the first day of the current year.
2)    If the second parameter was month then it always returns the first day of the current month.
3)    If the second parameter was day then it always returns the previous sunday.
4)    If the second parameter was null then it returns nothing.
5)    If the you are not specifying the second parameter then trunk will resets the time to the begining of the current day.

Ex:   
SQL> select trunc(to_date('24-dec-04','dd-mon-yy'),'year'), trunc(to_date('11-mar-06','dd-mon-yy'),'year') from dual;

TRUNC(TO_ TRUNC(TO_
-------------  --------------
01-JAN-04    01-JAN-06

SQL> select trunc(to_date('11-jan-04','dd-mon-yy'),'month'), trunc(to_date('18-jan-04', 'dd-mon-yy'),'month') from dual;



TRUNC(TO_ TRUNC(TO_
-------------  -------------
01-JAN-04    01-JAN-04
  
SQL> select trunc(to_date('26-dec-06','dd-mon-yy'),'day'), trunc(to_date('29-dec-06','dd-mon-yy'),'day') from dual;

TRUNC(TO_ TRUNC(TO_
-------------  --------------
24-DEC-06 24-DEC-06
         
SQL> select to_char(trunc(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss am') from dual;

TO_CHAR(TRUNC(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am

s) NEW_TIME

     This will give the desired timezone’s date and time.

     Syntax: new_time (date, current_timezone, desired_timezone)

     Available timezones are as follows.

    TIMEZONES

        AST/ADT    --    Atlantic standard/day light time
        BST/BDT    --    Bering standard/day light time
        CST/CDT    --    Central standard/day light time
        EST/EDT    --    Eastern standard/day light time
        GMT        --    Greenwich mean time
        HST/HDT    --    Alaska-Hawaii standard/day light time
        MST/MDT    --    Mountain standard/day light time
        NST        --    Newfoundland standard time
        PST/PDT    --    Pacific standard/day light time
        YST/YDT    --    Yukon standard/day light time

    Ex:   SQL> select to_char(new_time(sysdate,'gmt','yst'),'dd mon yyyy hh:mi:ss am') from dual;

TO_CHAR(NEW_TIME(SYSDAT
-----------------------------------
24 dec 2006 02:51:20 pm

 SQL> select to_char(new_time(sysdate,'gmt','est'),'dd mon yyyy hh:mi:ss am') from            dual;

TO_CHAR(NEW_TIME(SYSDAT
-----------------------
24 dec 2006 06:51:26 pm

t) COALESCE

    This will give the first non-null date.

    Syntax: coalesce (date1, date2, date3 … daten)

    Ex: SQL> select coalesce('12-jan-90','13-jan-99'), coalesce(null,'12-jan-90','23-mar-98',null) from dual;

COALESCE( COALESCE(
-------------  ------------
12-jan-90     12-jan-90

String Functions

In Our Previous post,we learnt about Numeric Functions and now lets learn about string functions.


STRING FUNCTIONS 
 We have following types of string functions in SQL.

a)    Initcap
b)    Upper
c)    Lower
d)    Length
e)    Rpad
f)    Lpad
g)    Ltrim
h)    Rtrim
i)    Trim
j)    Translate
k)    Replace
l)    Concat  ( ‘ || ‘ Concatenation operator)
m)    Ascii
n)    Chr
o)    Substr
p)    Instr
q)    Decode
r)    Greatest
s)    Least
t)    Coalesce

a) INITCAP

     This will capitalize the initial letter of the string.

     Syntax: initcap (string)

     Ex:    SQL> select initcap('computer') from dual;

INITCAP
-----------
Computer

b) UPPER

     This will convert the string into uppercase.

     Syntax: upper (string)

     Ex:     SQL> select upper('computer') from dual;
UPPER
--------
COMPUTER

c) LOWER

     This will convert the string into lowercase.

     Syntax: lower (string)

     Ex:    SQL> select lower('COMPUTER') from dual;

LOWER
---------
computer

d) LENGTH:-     
       This will give length of the string.

     Syntax: length (string)

     Ex:     SQL> select length('computer') from dual;

LENGTH
----------
       8

e) RPAD
 
This will allows you to pad the right side of a column with any set of characters.

     Syntax: rpad (string, length [, padding_char])

     Ex:  SQL> select rpad('computer',15,'*'), rpad('computer',15,'*#') from dual;

RPAD('COMPUTER'          RPAD('COMPUTER'
----------------------  ----------------------
computer*******           computer*#*#*#*

-- Default padding character was blank space.


f) LPAD

This will allows you to pad the left side of a column with any set of characters.
     Syntax: lpad (string, length [, padding_char])

     Ex:
          SQL> select lpad('computer',15,'*'), lpad('computer',15,'*#') from dual;

LPAD('COMPUTER'            LPAD('COMPUTER'
---------------------   ---------------------
*******computer           *#*#*#*computer

-- Default padding character was blank space.

g) LTRIM

This will trim off unwanted characters from the left end of string.

     Syntax: ltrim (string  [,unwanted_chars])

     Ex:
          SQL> select ltrim('computer','co'), ltrim('computer','com') from dual;

LTRIM(  LTRIM
--------  ---------
mputer   puter

SQL> select ltrim('computer','puter'), ltrim('computer','omputer') from dual;

LTRIM('C      LTRIM('C
----------   ----------
computer       computer
          
           -- If you haven’t specify any unwanted characters it will display entire string.

h) RTRIM

This will trim off unwanted characters from the right end of string.

     Syntax: rtrim (string [, unwanted_chars])

     Ex:
          SQL> select rtrim('computer','er'), rtrim('computer','ter') from dual;
RTRIM(     RTRIM
--------  ---------
comput      compu

SQL> select rtrim('computer','comput’), rtrim('computer','compute') from dual;

RTRIM('C  RTRIM('C
----------   ----------
computer   computer
           -- If you haven’t specify any unwanted characters it will display entire string.

i) TRIM

This will trim off unwanted characters from the both sides of string.

     Syntax: trim (unwanted_chars from string)

     Ex:
          SQL> select trim( 'i' from 'indiani') from dual;

TRIM(
-----
ndian
         
SQL> select trim( leading'i' from 'indiani') from dual;    -- this will work as LTRIM

TRIM(L
------
ndiani

SQL> select trim( trailing'i' from 'indiani') from dual;    -- this will work as RTRIM

TRIM(T
------
Indian

j) TRANSLATE

     This will replace the set of characters, character by character.

     Syntax: translate (string, old_chars, new_chars)

     Ex:    SQL> select translate('india','in','xy') from dual;

TRANS
--------
xydxa

k) REPLACE

     This will replace the set of characters, string by string.

     Syntax: replace (string, old_chars [, new_chars])

     Ex:  SQL> select replace('india','in','xy'), replace(‘india’,’in’) from dual;

REPLACE   REPLACE
-----------  -----------
Xydia         dia


l) CONCAT:-     
      This will be used to combine two strings only.

    Syntax: concat (string1, string2)

    Ex:     SQL> select concat('computer',' operator') from dual;

CONCAT('COMPUTER'
-------------------------
computer operator
 
If you want to combine more than two strings you have to use concatenation operator(||).

    SQL> select 'how' || ' are' || ' you' from dual;

'HOW'||'ARE
---------------
how are you

m) ASCII

This will return the decimal representation in the database character set of the first character of the string.

    Syntax: ascii (string)

    Ex:    SQL> select ascii('a'), ascii('apple') from dual;

ASCII('A')  ASCII('APPLE')
------------  ------------------
        97             97

n) CHR
 
This will return the character having the binary equivalent to the string in either the database character set or the national character set.

    Syntax: chr (number)

    Ex:    SQL> select chr(97) from dual;

CHR
-----
   a

o) SUBSTR

     This will be used to extract substrings.

     Syntax: substr (string, start_chr_count [, no_of_chars])

     Ex:SQL> select substr('computer',2), substr('computer',2,5), substr('computer',3,7)
        from dual;

SUBSTR(  SUBST  SUBSTR
----------  -------   --------
omputer  omput   mputer

 1)If no_of_chars parameter is negative then it will display nothing.

 2)If both parameters except string are null or zeros then it will display nothing.

 3)If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the original string length.

 4)If start_chr_count is negative then it will extract the substring from right end.

1    2    3    4    5    6    7    8

C    O    M    P    U    T    E    R

-8    -7    -6    -5    -4    -3    -2    -1
p) INSTR
This will allows you for searching through a string for set of characters.

     Syntax: instr (string, search_str [, start_chr_count [, occurrence] ])

     Ex:   SQL> select instr('information','o',4,1), instr('information','o',4,2) from dual;

INSTR('INFORMATION','O',4,1) INSTR('INFORMATION','O',4,2)
------------------------------------  -------------------------------------
                           4                                   10
1) If you are not specifying start_chr_count and occurrence then it will start search from the beginning and finds first occurrence only. 
2) If both parameters start_chr_count and occurrence are null, it will display nothing.

q) DECODE

Decode will act as value by value substitution.

For every value of field, it will checks for a match in a series of if/then tests.

    Syntax: decode (value, if1, then1, if2, then2, ……. else);

    Ex:    SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;

       SAL     DECODE
        -----    ---------
      500      Low
      2500     Medium
      2000     Medium
      3500     Medium
      3000     Medium
      5000     High
      4000     Medium
      5000     High
      1800     Medium
      1200     Medium
      2000     Medium
      2700     Medium
      2200     Medium
      3200     Medium

SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual;


DECODE(1,1,3) DECODE(1,2,3,4,4,6)
-----------------  ------------------------
            3                       6
1) If the number of parameters are odd and different then decode will display nothing.
2) If the number of parameters are even and different then decode will display last value.
3) If all the parameters are null then decode will display nothing.                                                        
4) If all the parameters are zeros then decode will display zero.

r) GREATEST

     This will give the greatest string.

     Syntax: greatest (strng1, string2, string3 … stringn)  

     Ex:      SQL> select greatest('a', 'b', 'c'), greatest('satish','srinu','Ram') from dual;


GREAT GREAT
-------  -------
    c        srinu

1) If all the parameters are nulls then it will display nothing. 
2) If any of the parameters is null it will display nothing.

s) LEAST

    This will give the least string.

    Syntax: greatest (strng1, string2, string3 … stringn)  

    Ex:     SQL> select least('a', 'b', 'c'), least('satish','srinu','Ram') from dual;

LEAST LEAST
-------  -------
    a        Ram

1)If all the parameters are nulls then it will display nothing.                                                        
2) If any of the parameters is null it will display nothing.
  
t) COALESCE

    This will gives the first not-null string.

    Syntax: coalesce (strng1, string2, string3 … stringn)  

    Ex:   SQL> select coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual;

COALESCE COALESCE
-----------   -----------
       a            a