Oracle Concurrent connections limit

The number of sessions/concurrent connections the database (oracle) was configured to allow:
    SELECT name, value FROM v$parameter WHERE name = 'sessions';

The number of sessions currently active:
    SELECT COUNT(*) FROM v$session;

Complete Query:
    SELECT
      'Currently, ' 
      || (SELECT COUNT(*) FROM V$SESSION)
      || ' out of ' 
      || VP.VALUE 
      || ' connections are used.' AS DATA
    FROM 
      V$PARAMETER VP
    WHERE VP.NAME = 'sessions';

OUTPUT:
DATA
Currently, 93 out of 172 connections are used.

Resource Name and Connections:
    select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

Want to increase the concurrent connections/sessions, open init.ora file inside oracle setup and increase "processes, cursor_limit and sessions".
However this change require Oracle Restart.

Row Count in Table wise in Oracle

Finding Total Table Count in a Schema in Oracle:
    SELECT COUNT(*) Total_Tables FROM DBA_TABLES WHERE OWNER = 'EGRD_HC_2A';
    
Tables with Row Count:
1) SELECT TABLE_NAME, NUM_ROWS, TABLESPACE_NAME, AVG_ROW_LEN, COMPRESSION FROM DBA_TABLES WHERE OWNER = 'EGRD_HC_2A' ORDER BY NUM_ROWS desc;
2) SELECT TABLE_NAME, NUM_ROWS, TABLESPACE_NAME, AVG_ROW_LEN, COMPRESSION FROM USER_TABLES WHERE TABLESPACE_NAME IN('SYSTEM','EGRD_HC_2A') ORDER BY NUM_ROWS desc;

3)
    DECLARE 
    val NUMBER;
    BEGIN
    FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || i.table_name INTO val;
    DBMS_OUTPUT.PUT_LINE('Table: ' ||i.table_name || ', Rows: ' || val );
    END LOOP;
    END;
    /

JOIN in ORACLE

The JOIN operations are the very confusing queries between two tables. However some join results can be achieved by 
using an explicit equality test also in a WHERE clause, such as "WHERE t1.col1 = t2.col2".

1. The purpose of a join is to combine the data across tables.
2. A join is actually performed by the where clause which combines the specified rows of tables.
3. If a join involves in more than two tables then Oracle joins first two tables based on the joins condition and then compares the 
   result with the next table and so on.
   
Type of JOIN Operations:
1     Equi-join  or  Using clause  or  On clause
2     Non-Equi join
3     Self join
4     Natural join
5     Cross join
6     Inner join
7     Outer join 
            Left outer 
            Right outer 
            Full outer 

---------DB Tables and Data Populations--------------        
    
CREATE TABLE DEPT (
DEPTNO NUMBER(3) NOT NULL,
DNAME VARCHAR2(50) NOT NULL,
LOC VARCHAR2(50) NULL,
CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO) 
);

CREATE TABLE EMP(
EMPNO NUMBER(3) NOT NULL,
ENAME VARCHAR2(50) NOT NULL,
JOB VARCHAR2(30) NOT NULL,
MGR NUMBER(3) NOT NULL,
DEPTNO NUMBER(3),
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),  
CONSTRAINT EMP_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

INSERT INTO DEPT VALUES('10','INVENTORY','HYBD');
INSERT INTO DEPT VALUES('20','FINANACE','BGLR');
INSERT INTO DEPT VALUES('30','HR','MUMBAI');
INSERT INTO DEPT VALUES('40','IT','DELHI');
INSERT INTO DEPT VALUES('50','ENGINEERING','BGLR');
INSERT INTO DEPT VALUES('60','PROD_SUPPORT','BGLR');

INSERT INTO EMP VALUES('111','Deepak','Engineer','114','50');
INSERT INTO EMP VALUES('112','Rajesh','Manager','113','20');
INSERT INTO EMP VALUES('113','Chandan','Systems','112','40');
INSERT INTO EMP VALUES('114','Devansh','Analyst','111','10');
INSERT INTO EMP VALUES('115','Saransh','HR_Team','115','30');
INSERT INTO EMP VALUES('116','Mummy','HR_Team','115','30');        
    
---------DB Tables and Data Populations--------------
            
1. EQUI JOIN, Using Clause and ON Clause gives same return.
     EQUI Join: A Join which contains an equal to ‘=’ operator in the joins condition.
     Using clause: Use of Using Clause.
     On Clause: Use of On Clause.
        
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;    //DEPTNO=60 Missing from Result.        
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,DEPTNO FROM EMP E JOIN DEPT D USING (DEPTNO);           //DEPTNO=60 Missing from Result.    
        Ex: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);  //DEPTNO=60 Missing from Result.            

2. NON-EQUI JOIN: A join which contains an operator other than equal to ‘=’ in the joins condition, means < or >.
   However you should be careful while using such queries on common column.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO > D.DEPTNO;

3. SELF JOIN: Joining the table itself is called self join.
   Ex: SQL> SELECT E1.EMPNO,E2.ENAME,E1.JOB,E2.DEPTNO FROM EMP E1,EMP E2 WHERE E1.EMPNO=E2.MGR;

4. NATURAL JOIN: Natural join compares all the common columns. If a column is matched (DEPTNO here), data will display in ASC Order of DEPTNO.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC,DEPTNO FROM EMP NATURAL JOIN DEPT; //Same AS EQUI JOIN

5. CROSS JOIN: This will gives the cartesial product.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP CROSS JOIN DEPT;   //Total m*n records. m FROM EMP, n FROM DEPT table.

6. INNER JOIN: This will display all the records that have matched. Same as EQUI Join.
   Ex: SQL> SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP INNER JOIN DEPT USING (DEPTNO);
   
7. OUTER JOIN: Outer join gives the non-matching records along with matching records.
   LEFT OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from the first table (as null) along with All matched Rows.
   RIGHT OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from the second table (as null) along with All matched Rows.
   FULL OUTER JOIN: A join between two tables with an explicit join clause, Showing unmatched rows from both tables (as null) along with All matched Rows.
   
   LEFT OUTER JOIN:
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E LEFT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO); //Unmatched DEPT rows will show as null.
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+);  //Unmatched DEPT rows will show as null.
    
   RIGHT OUTER JOIN:
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);  //Unmatched EMP rows will show as null.
    SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;   //Unmatched EMP rows will show as null.
   
   FULL OUTER JOIN: SELECT EMPNO,ENAME,JOB,DNAME,LOC,E.DEPTNO,D.DEPTNO FROM EMP E FULL OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);


For more details, please refer: http://www.javatpoint.com/oracle-create-table
--------------------------------END------------------------------------ 

Java Home and Oracle Home setting in Unix

Setting Java Home in Unix:
==========================================
export JAVA_HOME=/usr/lib/jvm/jdk1.7.0_60
export CLASSPATH=$JAVA_HOME/lib/tools.jar:.:
export PATH=$JAVA_HOME/bin:$PATH:
==========================================


Setting Oracle Home in Unix:
==========================================
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH:.

echo $ORACLE_HOME   (to display Oracle Home)
==========================================

UNIX_LOGIN_USER@enstageSYSTEM:~$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 5 13:39:53 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: USER_NAME
Enter password:  password

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UNIX_LOGIN_USER@enstageSYSTEM:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 5 13:40:30 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup   (To start Oracle Database, hopefully root user is required to start DB)
SQL> exit
UNIX_LOGIN_USER@enstageSYSTEM:~$ lsnrctl start  (To start Oracle Database Listener)

==========================================
SQL> set linesize 300;  (For proper visibility)
SQL> set pagesize 2000; (For proper visibility)
==========================================

Database, Tablespace, Datafiles and Related Issues

Dear friend,
We are going to learn 4 things here:
1) Basics of Databases, Tablespaces and Datafiles.
2) Getting tablespace sizes for all the schema available in oracle database.
3) Space used by a table or index.
4) Extend the tablespace size in case your application has stopped inserting/updating data in database. 
   The issue is "ORA-01653: unable to extend table".
5) Create a duplicate table without copying data in Oracle

------------------------------------------
1) Basics: Databases, Tablespaces and Datafiles are closely related, but they have important differences.

Database:   An Oracle database consists of one or more logical storage units called tablespaces, 
            which collectively store all of the database's data.
            
Tablespace: Each Tablespace in Oracle database consists of one or more files called datafiles, which 
            are physical files in the operating system in which Oracle is running. Tablespace provides a layer of abstraction 
            between physical and logical data and serves to allocate storage for all DBMS managed segments/objects. Database 
            segment/object are those which occupies physical space, eg: Table data and Indexes. Hence tablespace only specify 
            database storage location. A common use of tablespaces is to optimize performance. For example, a heavily used index 
            can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely 
            accessed could be stored on a less expensive but slower SATA disk.
            
Datafiles:  A database's data is collectively stored in the datafiles that constitute each tablespace of the database. 
            This means one tablespace can have multiple datafiles. For example, the simplest Oracle database would have 
            one tablespace and one datafile. Another database can have three tablespaces, each consisting of two 
            datafiles, total of six datafiles.

2) Below query will reveal the "Used Space" and "Free Space" in all the Tablespaces present in your database:

SELECT DF.TABLESPACE_NAME "TABLESPACE",
      DF.BYTES / (1024 * 1024) "SIZE (MB)",
      SUM(FS.BYTES) / (1024 * 1024) "FREE (MB)",
      NVL(ROUND(SUM(FS.BYTES) * 100 / DF.BYTES),1) "% FREE",
      ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
 FROM DBA_FREE_SPACE FS,
      (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES
         FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME (+)  = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME,DF.BYTES;

3) To know the space used by a Table, Index or anything use below query:
SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'INDEX_NAME_OF_TABLE';

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TABLE_NAME';

4) If you face any of the below issues in production/uat environement:

java.sql.BatchUpdateException: ORA-01654: unable to extend index SCHEMA_NAME.INDEX_NAME by 1024 in tablespace SCHEMA_NAME
java.sql.SQLException: ORA-01653: unable to extend table SCHEMA_NAME.TABLE_NAME by 128 in tablespace SCHEMA_NAME

Which shows Tablespace size need to be extended. You need to add one tablespace file for existing SCHEMA_NAME whose space has become full.
So get the FILE_NAME first:

SELECT * FROM  DBA_DATA_FILES WHERE TABLESPACE_NAME='SCHEMA_NAME';  
OR 

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SCHEMA_NAME';

FILE_NAME will be like as : 
/home/oracle/dbf/SCHEMA_NAME-01.DBF
/home/oracle/dbf/SCHEMA_NAME-02.DBF
Or in Windows:
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SCHEMA_NAME.DBF

The above data shows there are already 2 dbf files, so add one more. New file: /home/oracle/dbf/SCHEMA_NAME-03.DBF
Open Oracle in command prompt:

For Windows:
SQL> alter tablespace SCHEMA_NAME add datafile 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SCHEMA_NAME-03.DBF' size 1G autoextend on maxsize 2G;

For Unix:
SQL> alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 2G autoextend on maxsize 32G;
alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 2G autoextend on maxsize 32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

It means, that big size (2G) file can't be added in your system. There may be other reason too. Change the size to below (500M or 1G):
SQL> alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 500M autoextend on maxsize 10G;
Tablespace altered.

Run above query again with new DBF file name, 500M or 1G again will be added. Now run below query again, you will see new DBF files:
SELECT * FROM  DBA_DATA_FILES WHERE  TABLESPACE_NAME='SCHEMA_NAME';

5) Create a duplicate table without creating Data from original table:
CREATE TABLE DUPLICATE_TABLE as select * from ORIGINAL_TABLE where 1=0;
-----------------------------END-----------------------------

Execution Plan of a Query in Oracle

Working with Oracle in Unix and getting Execution Plan of a Query:

1) Login to Unix Machine where Oracle is installed.

2) You need to check whether Oracle is running (Listener service is running). To check this type below command:
        oracle@companyName:~$ lsnrctl status (press ENTER)
   This will give around 30-Lines output, at the end, it will show whether this running/ready or not. 
   However if it is not running, start it using below command:         
        oracle@companyName:~$ lsnrctl start (press ENTER)

3) Type below command in Unix (this will set Oracle_Home path and export it):  
        oracle@companyName:~$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 (press ENTER)
        oracle@companyName:~$ export ORACLE_SID=orcl (press ENTER)
        oracle@companyName:~$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH:. (press ENTER)

4) Now you need to login to Database, so type below command: 
        oracle@companyName:~$ sqlplus (press ENTER)
            Enter user-name: YOUR_ORACLE_USER_NAME  (press ENTER)
            Enter password:  YOUR_ORACLE_PASSWORD (press ENTER)
        SQL> (You are logged in now...)

/*
For Logging as SYS DBA, required only for DBA activities, don't run oftenly:        
        oracle@companyName:~$ sqlplus / as sysdba
        SQL>
        SQL> exit
*/

5) Set Linesize and Pagesize as Unix(Oracle) will show fragmented pages. Type below commands:
        SQL> set linesize 300;
        SQL> set pagesize 2000;

6) Now to print Execution Plan of a Query to know CPU Utilization, IO Cost, Bytes Read, Index Scan etc, use below queries:
        SQL> SET autotrace ON;
        SQL> SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */  * FROM TABLE_NAME WHERE 
             TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
             ORDER BY CARD_NO ASC;

             Your output will look like below:    
ROW DATA 1........             
ROW DATA 2........
ROW DATA 3........
3 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1246977483

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |    18 |  4122 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |                           |    18 |  4122 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE_NAME            |    18 |  4122 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INDEX_TIME_STAMP |    18 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TIME_STAMP">=TIMESTAMP' 2014-01-10 00:04:20' AND "TIME_STAMP"<=TIMESTAMP'
              2014-01-15 17:04:20')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       4397  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> exit

7) Now the same above query output, can be achieved in ORACLE related UI interfaces like below 
    (above all execution happened in UNIX Black color command line interface), execution below 2 queries in UI interface like SQL Tools:

    explain plan for
    SELECT /*+ index(TABLE_NAME INDEX_TIME_STAMP) */  * FROM TABLE_NAME WHERE 
    TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
    ORDER BY CARD_NO ASC;

    SELECT PLAN_ID,CPU_COST,IO_COST,TIME,BYTES,COST,DEPTH,OPTIMIZER,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,OPTIONS,OPERATION,
    TIMESTAMP FROM PLAN_TABLE;
    
==========================================END==============================================    

Oracle DBA Queries

Dear Reader,
I am writing an article on important DBA queries for Oracle Database. This is basically for my reference however 
you will find it useful.

Oracle DBA Queries:

1) To check latest SQLs executed in DB, if Oracle AWR Report is not giving proper results:
    SELECT LAST_LOAD_TIME, SQL_TEXT FROM  V$SQL ORDER BY LAST_LOAD_TIME DESC;
    SELECT * FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT * FROM ENSTAGE_TABLE%' ORDER BY LAST_ACTIVE_TIME DESC;

2) Check All Databases (Table Spaces) sizes and used memory: 
   If you have the proper permission, below query will execute:

    SELECT DF.TABLESPACE_NAME "TABLESPACE", TOTALUSEDSPACE "USED MB", (DF.TOTALSPACE - TU.TOTALUSEDSPACE) "FREE MB",
    DF.TOTALSPACE "TOTAL MB", ROUND(100 * ( (DF.TOTALSPACE - TU.TOTALUSEDSPACE)/ DF.TOTALSPACE)) "PCT. FREE"
    FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM DBA_DATA_FILES 
    GROUP BY TABLESPACE_NAME) DF, (SELECT ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE, TABLESPACE_NAME
    FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) TU WHERE DF.TABLESPACE_NAME = TU.TABLESPACE_NAME;
    

3) Table Size in Database in Megabytes:
    SELECT segment_name "Table_Name", bytes/1024/1024 "Megabytes", Blocks FROM USER_SEGMENTS
    WHERE segment_type = 'TABLE' AND segment_name IN ('ENSTAGE_TABLE')
    ORDER BY segment_name, tablespace_name;
    
4) List of Table_Spaces available (remove Where clause):
   SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'EGRD_DEEPAK';

5) Count of Tables, Indexes etc in a Table_Space:
   SELECT SEGMENT_TYPE, COUNT(SEGMENT_TYPE) FROM USER_SEGMENTS WHERE TABLESPACE_NAME='EGRD_DEEPAK' GROUP BY SEGMENT_TYPE;

6) To know if your table Spaces or tables are compressed:
   SELECT DEF_TAB_COMPRESSION, COMPRESS_FOR FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'EGRD_DEEPAK';   
   Output: DISABLED  <null>  (Means not compressed).
   
   SELECT COMPRESSION, COMPRESS_FOR FROM DBA_TABLES WHERE TABLE_NAME = 'ENSTAGE_TABLE';
   Output: DISABLED  <null>  (Means not compressed).

7) To compress a table in Oracle using OLTP:
   ALTER TABLE ENSTAGE_TABLE move COMPRESS FOR OLTP;   
   Output: ENABLED   OLTP   (Means not compressed).
   
   After compressing, use Above Query(Point number: 2) to see the Size of Table, now it should be reduced.
   
   If you want to revert back the changes: means no-compression, use below query:
   ALTER TABLE OBSERVATION_201312 NOCOMPRESS;
   
8) To know BIND variables passed while executing a DML Query via Application:
   SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'UPDATE MGMT_TARGETS SET %';
   
   //Note-down this SQL_ID and replace the value in next query.
   SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='57pfs5p8xc07w';

   To know complete details of the above query:
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('57pfs5p8xc07w',NULL,NULL,'ADVANCED'));
   
   And of course you can join these two queries in one step:
   SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED FROM V$SQL_BIND_CAPTURE A, V$SQL B
        WHERE A.SQL_ID = B.SQL_ID AND SQL_TEXT LIKE 'UPDATE HISTORY_201312 SET FINAL_STATUS%'
        ORDER BY LAST_CAPTURED;

9) Check Indexes or Index validation after Compression: 
   SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='MY_TABLE_NAME';

   Rebuild unused/invalid index: 
   ALTER INDEX INDEX NAME REBUILD ONLINE;


At last giving a complete example again to see Query Execution Plan, Hint and CPU/IO costs:

EXPLAIN PLAN for
    SELECT /*+ index(TABLE_NAME INDEX_CARD_NO) */  * FROM TABLE_NAME WHERE 
    TIME_STAMP>=To_Date('10/01/2014 00:04:20','dd/mm/yyyy hh24:mi:ss') AND 
    TIME_STAMP<=To_Date('15/01/2014 17:04:20','dd/mm/yyyy hh24:mi:ss')
    ORDER BY CARD_NO ASC; 

    SELECT LAST_LOAD_TIME, SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%TABLE_NAME%' ORDER BY 
    LAST_ACTIVE_TIME DESC;   

    SELECT PLAN_ID,CPU_COST,IO_COST,TIME,BYTES,COST,DEPTH,OPTIMIZER,OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER,
    OPTIONS,OPERATION,TIMESTAMP FROM PLAN_TABLE;

-----------------------------END------------------------------------

AWR Report in Oracle

How to take AWR Report in Oracle:
AWR: Automatic Workload Repository
The AWR is used to collect performance statistics including:
1. Wait events used to identify performance problems.
2. Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
3. Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
4. Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
5. Object usage statistics.
6. Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:
1. Automatic Database Diagnostic Monitor
2. SQL Tuning Advisor
3. Undo Advisor
4. Segment Advisor
So basically you need to run SNAP_SHOT two times to get AWR Report in between. One before your application
runs and one after your application runs.
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in
format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential
the same output but the awrrpti.sql allows you to select a single instance.

How To Take AWR Report:
Login to Unix Systems where Oracle is running:
oracle@DBDomain:~$ sqlplus / as SYSDBA
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 22 14:42:42 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;
//Remember only the last record (last SNAP_ID, it will be a numeric value).
...........................................
RUNNNNNNNNNNNNN YOUR APPLICATION Who populates DATA into DBBBBBBBBBBBBBB..........
...........................................
SQL>EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SQL>SELECT SNAP_ID, DBID from DBA_HIST_SNAPSHOT order by SNAP_ID;
//Remember the last two records (one SNAP_ID before your application run, second after run).
//Now Run below commands:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2835430333 ENSDB 1 ENSDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2835430333 1 ENSDB ENSDB DBDomain

Using 2835430333 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ --------- ------------------ -----
ENSDB ENSDB 119 22 Oct 2013 13:00 1
120 22 Oct 2013 14:01 1
121 22 Oct 2013 14:39 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 120
Begin Snapshot Id specified: 120

Enter value for end_snap: 121
End Snapshot Id specified: 121


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_120_121.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html
===============================================================
That's it. File has been created as "/home/oracle/DeepakModi/AWR_Report_22_OCT_SUCCESSFUL_100TPS.html".

===================END, Thank You.=================================

Oracle Query to see Used and Free TableSpace Size

Dear friend,
Below query will reveal the "Used Space" and "Free Space" in all the Tablespaces present in your database:

SELECT DF.TABLESPACE_NAME "TABLESPACE",
      DF.BYTES / (1024 * 1024) "SIZE (MB)",
      SUM(FS.BYTES) / (1024 * 1024) "FREE (MB)",
      NVL(ROUND(SUM(FS.BYTES) * 100 / DF.BYTES),1) "% FREE",
      ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
 FROM DBA_FREE_SPACE FS,
      (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES
         FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME (+)  = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME,DF.BYTES;


To know the space used by a Table, Index or anything use below query:

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'INDEX_NAME_OF_TABLE';

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TABLE_NAME';

Few important SQL in Oracle

Dear reader,
I am writing few sql queries in this blog article which is used for interview purposes,
I hope you will find useful. I have given table creation, seed data and all needed sql 
query for the first Question only as it will help to start. I mean from "Question 2"
onwards only necessary query has been provided. All queries I have tested on my machine.

Q.1) Given a table EMPLOYEE with data below:
    --------------
    ID    EMPNAME        MANAGERID
    110    Saikumar    114
    111    Deepak        114
    112    Srikanth    114
    113    Gudeti_Shekhar    114
    114    Sandeep        118
    115    Kotesh        118
    118    Rishi        120
    119    Khem        120
    120    Ganesh        null
    --------------
   Assuming MANAGERS are also EMPLOYEE, write a query to fetch ID, EMPNAME, MANAGERNAME.

Ans): This can be done in three ways: Either SELF JOIN, Normal JOIN operation or Sub-query.
 Writing TABLE creation and SEED DATA entry first:
--------------
    CREATE TABLE EMPLOYEE(ID NUMBER, EMPNAME VARCHAR(20), MANAGERID NUMBER);
    ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_ID PRIMARY KEY (ID);

    INSERT INTO Employee VALUES(110,'Saikumar',114);
    INSERT INTO Employee VALUES(111,'Deepak',114);
    INSERT INTO Employee VALUES(112,'Srikanth',114);
    INSERT INTO Employee VALUES(113,'Gudeti_Shekhar',114);
    INSERT INTO Employee VALUES(114,'Sandeep',118);
    INSERT INTO Employee VALUES(115,'Kotesh',118);
    INSERT INTO Employee VALUES(118,'Rishi',120);
    INSERT INTO Employee VALUES(119,'Khem',120);
    INSERT INTO Employee VALUES(120,'Ganesh',null);
--------------
Fetching Query 1:
 SELECT a.id eid, a.empname empname, b.empname MANAGER FROM EMPLOYEE a, EMPLOYEE b WHERE a.managerid=b.id;

Fetching Query 2:
 SELECT A.ID, A.EMPNAME, B.EMPNAME AS MANAGER FROM EMPLOYEE A JOIN EMPLOYEE B ON A.MANAGERID = B.ID;
 
Fetching Query 3:
 SELECT A.ID,A.EMPNAME,B.EMPNAME AS MANAGER FROM
 (SELECT ID,EMPNAME,MANAGERID FROM EMPLOYEE)A,
 (SELECT ID,EMPNAME FROM EMPLOYEE)B
 WHERE A.MANAGERID=B.ID;

Output from all 3 queries:
    ID    EMPNAME            MANAGER
    113    Gudeti_Shekhar    Sandeep
    112    Srikanth        Sandeep
    111    Deepak            Sandeep
    110    Saikumar        Sandeep
    115    Kotesh            Rishi
    114    Sandeep            Rishi
    119    Khem            Ganesh
    118    Rishi            Ganesh

NOTE: Here the last inserted record where there is no manager (super boss) is not 
      showing in the output. To show such null records too, I have modified the "Fetching Query 2" 
      and added LEFT OUTER JOIN as below:
      
      SELECT A.ID, A.EMPNAME, B.EMPNAME AS MANAGER FROM EMPLOYEE A LEFT OUTER JOIN EMPLOYEE B 
      ON A.MANAGERID = B.ID;

      Now all the records will show properly. Can be fine tuned more not to show NULL values:
      
      SELECT A.ID, A.EMPNAME, NVL(B.EMPNAME,'NO MANAGER') AS MANAGER FROM EMPLOYEE A LEFT OUTER JOIN 
      EMPLOYEE B ON A.MANAGERID = B.ID;
      
    
==================================
Q.2) Write a query to find 2nd Highest salary employee.
 Table data:
  EMP_ID    EMP_NAME    EMP_SAL
    1    Anees    10000
    2    Rick    12000
    3    John    11000
    4    Stephen    13000
    5    Maria    14000
    6    Deepak    13500
    7    Rajesh    12500
Ans:)
Max Salary:  SELECT Max(emp_sal) FROM employee_test;
2nd Max salary: 
SELECT EMP_ID, emp_sal, EMP_NAME FROM employee_test WHERE emp_sal=
 (
  SELECT Max(emp_sal) FROM employee_test WHERE emp_sal IN
    (
    SELECT emp_sal FROM employee_test WHERE emp_sal< (SELECT Max(emp_sal) FROM employee_test)
    )
  );
//Output 
EMP_ID    EMP_SAL    EMP_NAME
6    13500    Deepak    
==================================

String functions in Oracle

Dear reader, 
Writing few String functions available in Oracle:

--------------LENGTH------------------
SELECT EMPNAME, LENGTH(EMPNAME), LENGTH('MANAGER') FROM EMPLOYEE;
//Output
EMPNAME     LENGTH(EMPNAME)  LENGTH('MANAGER')
-------     --------------   -----------------
Saikumar        8            7
Deepak          6            7

--------------TRIM------------------
SELECT Length('deepak '), TRIM('deepak '),Length(TRIM('deepak ')) FROM tab;
7  deepak  6

SELECT TRIM(LEADING '#' FROM 'rajesh##') FROM dual;    //rajesh##
SELECT TRIM(LEADING '#' FROM '##rajesh##') FROM dual;  //rajesh##
SELECT TRIM('#' FROM '##rajesh##') FROM dual;          //rajesh
SELECT TRIM('#' FROM '##raj##esh##') FROM dual;        //raj##esh

--------------SUBSTR----------------
Source_Str, Start_Pos, End_Pos

Value of Start_Position     
Positive ==    Counts forward from the first character in source_string
Zero (0) ==    Counts forward from the first character in source_string (that is, 
            treats a start_position of 0 as equivalent to 1)
Negative ==    Counts backward from an origin that immediately follows the last character in source_string A 
            value of -1 returns the last character in source_string.

SELECT SUBSTR('ABCDEFG',0,6)  FROM dual; //ABCDEF
SELECT SUBSTR('ABCDEFG',0,7)  FROM dual; //ABCDEFG
SELECT SUBSTR('ABCDEFG',2,3)  FROM dual; //BCD
SELECT SUBSTR('ABCDEFG',6)  FROM dual;   //FG
SELECT SUBSTR('ABCDEFG',7)  FROM dual;   //G
SELECT SUBSTR('ABCDEFG',-2,7)  FROM dual; //FG
SELECT SUBSTR('ABCDEFG',-3,7)  FROM dual; //EFG

--------------INSTR------------------
INSTR is used to find the position of any particular character in a word which 
returns numeric value. It IS CASE sensitive.

SELECT instr('KUMAR','e') FROM dual;  //0, e IS NOT present
SELECT instr('KUMAR','m') FROM dual;  //0, NOT matched, CASE sensitive
SELECT instr('KUMAR','M') FROM dual;  //3, INDEX starts FROM 1 here.

--------------REPLACE----------------
Source_Str, OLD_VAL, NEW_VAL
SELECT REPLACE('Deepak kumar', 'De', 'Ra')  FROM dual; //Raepak kumar

--------------LPAD-------------------
Source_Str, Total_No_Char, Filling_Char
SELECT LPAD('Here we are', 11, '-_') FROM dual;  //Here we are
SELECT LPAD('Here we are', 16, '-_') FROM dual;  //-_-_-Here we are  
//SIMILAR is RPAD

Normalization in Database

Normalization
It is a process of refining the data model by ER diagram.

It includes
  --> Refinement of ER model.
  --> Segregation of data over groups, entities/tables with min redundancy).
  --> Converts ER diagram to tables.

Advantages
  --> min data redundancy.
  --> retreieve information easily.

Need for normalization
*) improves data base design.
*) ensures min redundancy of data
*) reduces need to reorganize data when design is deleted/enhanced.

Unnormalized tables
--> contain redundant data
--> disorganized data
--> problems arise with insertion,updation and deletion.


First Normalization 
Steps
1) identify repeating groups of fields.
2) remove repeating groups of fields to a seperate table.
3) identify the keys for the table.
4) key of parent table is brought as a part of concatenated key of second table.

Second Normalization
Steps
1) check if all fields are dependent on whole key.
2) remove fields that are dependent on partial key.
3) group partially dependent fields as a seperate table.
4) name the table.
5) identify key / keys of the table.

Third Normalization
Steps
1) removes fields that depend on other non key attribute 
2) can be calculated or derived from logic.
3) group independent fields as seperate tables,identify the key.

Oracle programming code_Part2

//Oracle tutorial_part2
//Cursor programming
==========================================
//Explicit cursor
/* using cursor display the details of all those emp from emp whose sum of sal and comm is>3000; */
declare
 vempno emp.empno%type;
 vename emp.ename%type;
 vsal emp.sal%type;
 vdeptno emp.deptno%type;
 cursor c1 is 
   select empno,ename,sal,deptno from emp where sal+nvl(comm,0)>3000;
 begin
   open c1;
   loop
     fetch c1 into vempno,vename,vsal,vdeptno;
     if c1%FOUND then
       dbms_output.put_line(vempno||'    '||vename||'    '||vsal||'       '||vdeptno);
     else
       exit;
     end if;
   end loop;
  close c1;
end;
-----------------------------
declare
 name emp.ename%type;
 no  emp.empno%type;
 cursor empc is select ename,empno from emp;
begin
 open empc;
 dbms_output.put_line('rowcount '||empc%ROWCOUNT);
 loop
  fetch empc into name,no;
  dbms_output.put_line('name '||name||' no'||no);
  exit when empc%NOTFOUND;
 end loop;
 dbms_output.put_line('rowcount'||empc%ROWCOUNT);
 close empc;
end;
-----------------------------
//Implicit cursor (here cursor keyword is not used):
begin
 delete from dept where deptno=100;
 if sql%notfound then
  raise_application_error(-20303,'No such department in the dept table'); 
 end if;
end;
/
//Other commands used in implicit cursor==> sql%FOUND
==========================================

Table creation with constraints:
create table Doctors(
doctor_id number(6) primary key,
doctor_name varchar2(20) not null,
doct_address varchar2(35),
doct_ph_no number(10)
);

create table Test (
test_id number(4) primary key,
test_name varchar2(20),
doctor_id number(6),
constraint fk_doctor_id foreign key(doctor_id) references Doctors
);
==========================================

//Defining values
DEFINE P_SALARY=50000;
DEFINE P_BONUS=10;
DECLARE
RESULT NUMBER:=0;
BEGIN
RESULT:=(P_SALARY*P_BONUS/100)+P_SALARY;
DBMS_OUTPUT.PUT_LINE(RESULT);
END;
/
==========================================
//Stored Procedures:
CREATE OR REPLACE PROCEDURE Prof_Chng_Merchant_To_Silver(INPUT_MOBILENUMBER VARCHAR2)
AS
  Src_memberid   NUMBER(24);
  Src_account_id NUMBER(24);
  Src_programnameref VARCHAR(20);
  Check_programnameref VARCHAR(20);
BEGIN
  SELECT mac.memberid,acc.id INTO Src_memberid,Src_account_id FROM account acc, memberaccountrole mac
  WHERE acc.id = mac.accountid AND acc.devicenumber LIKE INPUT_MOBILENUMBER;

  SELECT Value INTO Check_programnameref FROM account_dtl WHERE code='OP_FR_PROG' AND account_id IN
  (SELECT id FROM account WHERE devicenumber=INPUT_MOBILENUMBER);

  IF Check_programnameref='MERCHANTSVA' then

    SELECT programnameref INTO Src_programnameref FROM account WHERE devicenumber LIKE INPUT_MOBILENUMBER;
    UPDATE ACCOUNT SET PROGRAMNAMEREF = Decode(Src_programnameref,'MBILL','MBILL','SILVER') 
 WHERE DEVICENUMBER LIKE INPUT_MOBILENUMBER;

    IF SQL%ROWCOUNT = 1 THEN     
      UPDATE account_dtl SET Value = 'SILVER' WHERE code LIKE'OP_FR_PROG' AND ACCOUNT_ID = Src_account_id;
      UPDATE bank_temp_upload_customer  SET PRODUCT = 'SILVER' WHERE ACCOUNT_ID = Src_account_id
        AND customer_id = (SELECT Max(customer_id) FROM bank_temp_upload_customer WHERE account_id = Src_account_id);
      Insert into ACCOUNT_LOCK_LOG (id, account_id, reason_code, lock_reason, createdt,
        createdby, modifydt, modifiedby, auth_user_id, locked_by_user_type, action_type)
      Values
        ((SELECT Max(id)+1 FROM ACCOUNT_LOCK_LOG), Src_account_id, 'NOTE', 
  'Coverted the Product type from MERCHANT to SILVER', sysdate, NULL, sysdate, 'PROCEDURE', NULL, NULL, 'NOTE');
    END IF;
  END IF;

  dbms_output.put_line ('No MerchantSVA customer found with this mobile number');

  EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO AUDIT_LOG (
      AUDIT_LOG_ID, TRANSACTION_ID, TRANSACTION_DATE,
      OBJECT_NAME, EVENT_CATEGORY, EVENT_TYPE, OBJECT_PK_VALUE,
      COLUMN_NAME, OLD_VALUE, NEW_VALUE,ADMIN_USER, EVENT_DATA)
    VALUES ( seq_audit_log_id.nextval, null, SYSDATE
      ,'Prof_Chng_Merchant_To_Silver','PLEXCEPTION', 'PROFILE',NULL
      ,'OTHERS' ,NULL, NULL, 'PROCEDURE', INPUT_MOBILENUMBER);

END Prof_Chng_Merchant_To_Silver;
/

//Way to execute
EXEC Prof_Chng_Merchant_To_Silver('919902029064');
COMMIT;
==========================================
create or replace procedure area(length in number,breadth in number)
is
v_area number;
begin
v_area:=length*breadth;
dbms_output.put_line('The area is '||v_area);
end area;
/
-----------------------------
CREATE or replace procedure proc1(p_id in number)
is
emp_record emp%rowtype;
begin
select * into emp_record from emp where empno =p_id;
dbms_output.put_line(emp_record.ename||'   '||emp_record.job);
end proc1;
/
-----------------------------
//Way to execute
begin
  area(6,6);
  proc1(80);
end;
/
OR
Execute area(4,5);
-----------------------------
//Calling another stored procedure
create or replace procedure up_fees(id number)
is
begin
update stud set fees=fees+500
where sl=id;
end up_fees;
/
-----------------------------
create or replace procedure proc7(pid number)
is
begin
up_fees(pid);
exception
when no_data_found then
dbms_output.put_line('No Such data in student table');
end proc7;
/
==========================================

//Function
CREATE or replace function func3(psal number)
return number
is
begin 
insert into emp (empno,ename,job,sal,deptno) values(3455,'Deepak','SSE',4500,20);
return (psal+500);
end func3;
/
-----------------------------
create or replace function day(num IN number)
return varchar2 
is
begin
  case num
           when 1 then return  'SUNDAY'
           when 2 then return  'MONDAY'
           when 3 then return 'TUESDAY'
           when 4 then return 'WENESDAY'
           when 5 then return 'THURSDAY'
           when 6 then return 'FRIDAY'
           when 7 then return 'SATURDAY'
         else return 'INVALID NUMBER'
  END;
 END day;
/
-----------------------------
create or replace function wish(name varchar2)
return varchar2
is
begin
if name='deepak' or name='kumar' or name='modi' then
 return 'Welcome to the creation of function in oracle';
else
return 'Not a valid user of scott';
end if;
end wish;
/
==========================================
declare
 pName bank_temp_upload_customer.product%type;
 PPID  bank_temp_upload_customer.PROGRAM_PROFILE_ID%type;
 custId  bank_temp_upload_customer.CUSTOMER_ID%type;
 custIdMax NUMBER(24);
 mobNumber bank_temp_upload_customer.mobile_number%TYPE; 
 cursor tempCursor is 
    select product,program_profile_id,mobile_number,customer_id from bank_temp_upload_customer 
    WHERE mobile_number IN ('919686680837','919686680836') AND
    pi_master_id=(SELECT pi_master_id FROM PI_MASTER WHERE pi_code='YBL') 
    ORDER BY mobile_number desc;
begin
 open tempCursor; 
 loop
  fetch tempCursor into pName,PPID,mobNumber,custId;
  SELECT Max(customer_id) INTO custIdMax FROM bank_temp_upload_customer WHERE mobile_number=mobNumber;
  IF custId=custIdMax then
     dbms_output.put_line('ProductName ==>'||pName||', Profile_ID==>'||PPID||', Max_Customer_Id==>'||custId||',
  Mobile_Number==>'||mobNumber);     
  UPDATE bank_temp_upload_customer SET PROGRAM_PROFILE_ID=(SELECT ID FROM program_profile WHERE 
  partner_code='YBL' AND PROGRAM_NAME =pName) 
      WHERE MOBILE_NUMBER=mobNumber AND customer_id=custIdMax; 
  END IF;
  exit when tempCursor%NOTFOUND;
 end loop;
 dbms_output.put_line('Task completed');
 close tempCursor;
end;

==========================================

//Triggers in Oracle
Can be seen here too: http://deepakmodi2006.blogspot.com/2011/01/database-triggers-in-oracle.html
CREATE OR REPLACE TRIGGER SECURE_EMP
 BEFORE INSERT ON EMPLOYEES
 BEGIN
 IF
 (TO_CHAR(SYSDATE,'DY') IN  ('THU','FRI')) OR
 (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '17:00')
 THEN RAISE_APPLICATION_ERROR(-20500,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
 END;
/
------------------------------
CREATE OR REPLACE TRIGGER SECURE_EMP1
 BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
 BEGIN
  IF
   (TO_CHAR(SYSDATE,'DY') IN  ('WED','SUN')) OR
   (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
  THEN 
IF DELETING THEN 
RAISE_APPLICATION_ERROR(-20501,'YOU MAY DELETE INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF INSERTING THEN 
RAISE_APPLICATION_ERROR(-20502,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF UPDATING ('DATEOFJOINING') THEN
RAISE_APPLICATION_ERROR(-20503,'YOU MAY UPDATE DOJ INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSE
RAISE_APPLICATION_ERROR(-20504,'YOU MAY DO TRANSACTION INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
END IF;
END;
/
------------------------------
create or replace trigger tri_emp
after insert or update or delete on employee
for each row
begin
  insert into audit_table values(user,sysdate,:old.empno,:old.ename,:new.ename,
  :old.job,:new.job,:old.sal,:new.sal);
end;
/
==========================================

//Package and Package body
//Package is like Interface in java, only declaration. Package body is having basically details of those.
//One full example to start with:
create or replace package summul as
  procedure pr1(a number,b number);
  function fr1(a number,b number)return number;
  c number;
end summul;

create or replace package body summul
 as
procedure pr1 (a number,b number) 
 is
 sumValue number(4);
 begin
  sumValue:=a+b;
  dbms_output.put_line(sumValue);
end pr1;

function fr1(a number,b number) return number is
mul number;
begin
 mul:=a*b;
 return (mul);
end fr1;
end summul;

//execute summul.pr1(10,20);
//select summul.fr1(10,20) from dual;
SQL> execute summul.pr1(10,20);
     PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> execute summul.pr1(10,20);
30
PL/SQL procedure successfully completed.
SQL> select summul.fr1(12,34) from dual;
SUMMUL.FR1(12,34)
-----------------
408
==========================================

create or replace package var_pack
is
function g_del return number;
procedure set_g_del (p_val in number);
end var_pack;
/
-------------------
create or replace package body var_pack is
gv_del number :=0;

function g_del return number is 
begin
 return gv_del;
end;

procedure set_g_del (p_val in number) is 
begin
if p_val=0 then
  gv_del := p_val;
else 
  gv_del :=gv_del +1;
end if;
end set_g_del;
end var_pack;
/
------------------------------
//Another package and package body
create or replace package dept_pack
 is 
 v_dept number;
 procedure dept_proc;
 end dept_pack;
/
-------------------
create or replace package body dept_pack
 is
 procedure dept_proc
  is
  v_name varchar2(20);
  v_loc varchar2(30);
 begin
  select dname,loc into v_name,v_loc from dept where deptno=v_dept;
  dbms_output.put_line(v_name||'  '||v_loc);
 end dept_proc;
 begin
  select deptno into v_dept from emp where ename='FORD';end dept_pack;
/
==================END=======================

Generating Reports in Oracle

sql * plus allows the user the flexibility for formatting the results in the form of reports.

It uses sql to retrieve information from the oracle data base and lets the user create polished,well formatted reports.
//Commands used in generation of reports
1. REM
SQLPlus ignores anything on a line that begins with the letters REM. It is used to add comments,
documnetation and explanations etc to a FILE.

2. SET HEAD SEP
SET HEAD SEP is used for head seperator .SQLPLUS will indicate to break a page title
or a column heading that no longer than one line.

3. TTILE AND BTITLE
TTITLE is used to set header for each page and BTITLE is used to set the footer for each page.

4. COLUMN
Column allows us to change the heading and format of any column in a select statement.

5. BREAKON <Column- name> SKIP page <n>
breakon to skip n lines every time the item is changed. breakon command and order by clause must be used together.

6. computesum
compute sum command used to calculate the sum for a particular column and always works with
breakon command.

7. set linesize
set Line size determines the max no of characters that appear in a single line.

8. set pagesize
set page size command sets the total no of lines PLSQL will place on each page including 
ttitle,btitle,column headingsand blank lines it prints.

9. set NewPage
set new page print blank lines before the top line of each page of the reports.

10. list<n>
list command list the buffer contents depending on n.

11. set pause on 
set pause on command is used to view the contents page by page.

12. set pause off
set pause off command is used to disable the pause  on command.

13. spool <file-name>  all the information displayed on the screen is displayed in the specified file
After setting all the above formatting commands if we execute the select query then those commands
will be applied to sql query and will generate a report.

14. spool off : will stop spooling.

15. spool out : close the listed file and prints the file to the system printer.

To reset all the above statements ,execute the following
16. ttitle off;
17. btitle off;


//Sample Example:
ttitle 'Deepak writes a good blog having Account report | Account report';
btitle 'Day begins with hello, good morning';
column Name heading 'Name';
column ProductType heading 'Product Type';
column Status heading 'Status';
break on Status;
spool Account_report.doc;
select Name, ProductType,Status from Account where ProductType='PLATINUM';
spool off;
spool out;
ttitle off;
btitle off;
column Name clear;
column ProductType clear;
column Status clear;
clear breaks;
------------------------------End-----------------------

Oracle programming code_Part1

Oracle programming codes_Part_1

//If-else block
declare
a number;
b number;
begin
a := &a;  --input from console
b := &b;  --input from console
if a<b  then
   dbms_output.put_line('a is smallest');
else
   dbms_output.put_line('b is smallest');
end if;
end;
-----------------------
declare
a number;
b number;
c number;
begin
 a := &a;
 b := &b;
 c := &c;
 if a>b and a>c then
   dbms_output.put_line('a is  >');
 elsif b>c then
   dbms_output.put_line('b is  >');
 elsif a=b and b=c then
   dbms_output.put_line('a=b=c');
 else
   dbms_output.put_line('c is  >');
 end if;
end;
========================

//Simple input output
declare
a number;
b number;
c number;
begin
  a := &a;
  b := &b;
  c := a+b;
  dbms_output.put_line('a+b = ' || c);
end;
=========================

//Loop programs
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  if c = 10 then exit;
  end if;
 end loop;
end;
--------------------
declare
c number;
begin
 c := 0;
 until c>11 loop
  c:=c+1;
  dbms_output.put_line(c);
 end loop;
end;
--------------------
declare
c number;
begin
for c in 1..10  loop
 dbms_output.put_line(c);
end loop;
end;
---------------------
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  exit when c = 10;
 end loop;
end;

==========================

//Rowtype
declare
x emp%rowtype;
begin
 select * into x from emp where empno=7839;
 if(x.sal >3000) then
  update temp2 set comm=300 where x.empno=7839;
 elsif (x.sal < 6000) then
  update temp2 set comm=600 where x.empno=7839;
 else
  update temp2 set comm=100 where x.empno=7839;
 end if;
 
 dbms_output.put_line('job done');
end;
--------------------------
//Type
declare
 x emp.sal%type;
 e emp.ename%type;
 eno  emp.empno%type;
begin
 eno := &eno;
 select sal into x from emp where empno=eno;
 select ename into e from emp     where empno=eno;
 if x>3000 then
  dbms_output.put_line(e||'s salary is greater than 3000'); 
 else
  dbms_output.put_line(e||'s salary is less than 3000'); 
end if;
end;
==========================
//Exception in oracle
//User defined exception
declare
 a number;
 b number;
 c number;
 my_zero_divide exception;
begin
 a := &a;
 b := &b;
 if b=0 then
   raise my_zero_divide; 
 else
   c := a/b;
   dbms_output.put_line('a/b = ' || c);
 end if;

Exception
when my_zero_divide then
  dbms_output.put_line('you tried to divide a number by zero');

dbms_output.put_line('There was an exception');
end;
--------------------------
//System defined exception
declare
 vename emp.ename%type;
 vjob emp.job%type;
begin
 select ename ,job into vename,vjob from emp where sal>2000;

 Exception
 when NO_DATA_FOUND then
    dbms_output.put_line('NO_DATA_FOUND SQLCODE '||SQLCODE||'SQLERRM '||SQLERRM);
 when TOO_MANY_ROWS then
    dbms_output.put_line('TOO_MANY_ROWS'||'sqlcode   '||sqlcode||'   SQLERRM '||SQLERRM);
 when others then 
    dbms_output.put_line('other Exception');
end;
==========End of chapter1==================

Primitive data types in Java

Java's Primitive Data Types

boolean
    1-bit. May take on the values true and false only.
    "true" and "false" are defined constants of the language and are not the same as 
    True and False, TRUE and FALSE, zero and nonzero, 1 and 0 or any other numeric value. 
    Booleans may not be cast into any other type of variable nor any other variable can be cast into a boolean.

byte
    1 signed byte (two's complement). Covers values from -128 to 127 (From 2^8 to 2^7).

short
    2 bytes, signed (two's complement), -32,768 to 32,767  (From 2^16 to 2^15)

int
    4 bytes, signed (two's complement). -2,147,483,648 to 2,147,483,647. Like all numeric types integers may 
    be cast into other numeric types (byte, short, long, float, double). When lossy casts are done 
    (e.g. int to byte) the conversion is done modulo the length of the smaller type.

long
    8 bytes signed (two's complement). Ranges from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

float
    4 bytes, IEEE 754. Covers a range from 1.40129846432481707e-45 to 3.40282346638528860e+38 (positive or negative).
    Like all numeric types floats may be cast into other numeric types (byte, short, long, int, double). 
    When lossy casts to integer types are done (e.g. float to short) the fractional part is truncated and 
    the conversion is done modulo the length of the smaller type.

double
    8 bytes IEEE 754. Covers a range from 4.94065645841246544e-324d to 1.79769313486231570e+308d (positive or negative). 

char
    2 bytes, unsigned, Unicode, 0 to 65,535
    Chars are not the same as bytes, ints, shorts or Strings.

Database Triggers in Oracle

                                          Triggers in Oracle
A Database trigger is:
•    A stored PL/SQL block associated with a table, a schema, or the database or
•    An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
Oracle Database automatically executes a trigger when specified conditions occur.
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement.
Prerequisites 
Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
•    To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
•    To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
•    In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
Insert Triggers:
BEFORE INSERT Trigger
AFTER INSERT Trigger

Update Triggers:
BEFORE UPDATE Trigger
AFTER UPDATE Trigger

Delete Triggers:
BEFORE DELETE Trigger
AFTER DELETE Trigger

Drop Triggers:
Drop a Trigger

Disable/Enable Triggers:
Disable a Trigger
Disable all Triggers on a table
Enable a Trigger
Enable all Triggers on a table

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax for an BEFORE INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2),
total_cost number(8,2), create_date date,created_by varchar2(10) );
We could then create a BEFORE INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
-- Update create_date field to current system date
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
END;

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.
The syntax for an AFTER INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2),
total_cost number(8,2));
We could then create an AFTER INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, username )
VALUES ( :new.order_id,:new.quantity,:new.cost_per_item,:new.total_cost, v_username );
END;

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.
The syntax for an BEFORE UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.
For example: If you had a table created as follows:
CREATE TABLE orders 
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2),
updated_date    date,
updated_by    varchar2(10)
);

We could then create a BEFORE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE on the table
SELECT user INTO v_username
FROM dual;
-- Update updated_date field to current system date
:new.updated_date := sysdate;
-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;
END;

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);

We could then create an AFTER UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;     -- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity_before, quantity_after, username )
VALUES ( :new.order_id,:old.quantity, :new.quantity, v_username );
END;

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax for an BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create a BEFORE trigger on a view.
•    You can update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);
We could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, delete_date, deleted_by )
VALUES( :old.order_id,:old.quantity,:old.cost_per_item,:old.total_cost,sysdate, v_username );
END;

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.
The syntax for an AFTER DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
Restrictions:
•    You can not create an AFTER trigger on a view.
•    You can not update the :NEW values.
•    You can not update the :OLD values.

For example: If you had a table created as follows:
CREATE TABLE orders
(    order_id    number(5),
quantity    number(4),
cost_per_item    number(6,2),
total_cost    number(8,2)
);
We could then create an DELETE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, delete_date, deleted_by)
VALUES( :old.order_id, :old.quantity, :old.cost_per_item, :old.total_cost, sysdate, v_username );
END;
Dropping a trigger:-
If you had a trigger called orders_before_insert, you could drop it with the following command:
DROP TRIGGER orders_before_insert;
Disabling and Enabling a trigger:-
If you had a trigger called orders_before_insert, you could disable it with the following command:
ALTER TRIGGER orders_before_insert DISABLE;
ALTER TABLE orders DISABLE ALL TRIGGERS;
ALTER TRIGGER orders_before_insert ENABLE;
ALTER TABLE orders ENABLE ALL TRIGGERS;
Firing Triggers One or Many Times (FOR EACH ROW Option):- 
The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.
For example, you define the following trigger:
CREATE TABLE Emp_log (Emp_id NUMBER, Log_date DATE, New_salary NUMBER, Action VARCHAR2(20));

CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Sal > 1000)
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');
END;

Then, you enter the following SQL statement:
UPDATE Emp_tab SET Sal = Sal + 1000.0 WHERE Deptno = 20;
If there are five employees in department 20, then the trigger fires five times when this statement is entered, because five rows are affected.
The following trigger fires only once for each UPDATE of the Emp_tab table:
CREATE OR REPLACE TRIGGER Log_emp_update
AFTER UPDATE ON Emp_tab
BEGIN
INSERT INTO Emp_log (Log_date, Action)
VALUES (SYSDATE, 'Emp_tab COMMISSIONS CHANGED');
END;

InsteadOf Triggers:-
Direct Triggers can’t be applied on views. For them InsteadOf triggers are applied. Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable.

Delete the Duplicate rows from a table:-

DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
Q. How many valid / invalid objects exist owned by this oracle user? 
Often we should know, how many valid and invalid objects an oracle user owes. Especially if we compare a schema on two different databases. 
SELECT DISTINCT (object_type) object, status, COUNT(*) FROM user_objects GROUP BY object_type, status;
This line implements sleep function in PL/SQL code. Put either in exception or inside <begin> <end> block.
DBMS_LOCK.SLEEP(10);

Get version of Oracle:-
select * from v$version;

Some tips on oracle

Decode function:
Link : http://www.techonthenet.com/oracle/functions/decode.php