connection usage

  public void createTable() throws SQLException {
    String createString =
      "create table COFFEES " + "(COF_NAME varchar(32) NOT NULL, " +
      "SUP_ID int NOT NULL, " + "PRICE numeric(10,2) NOT NULL, " +
      "SALES integer NOT NULL, " + "TOTAL integer NOT NULL, " +
      "PRIMARY KEY (COF_NAME), " +
      "FOREIGN KEY (SUP_ID) REFERENCES SUPPLIERS (SUP_ID))";
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void populateTable() throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian', 00101, 7.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast', 00049, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Espresso', 00150, 9.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement =
      "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      if (updateSales != null) { updateSales.close(); }
      if (updateTotal != null) { updateTotal.close(); }
      con.setAutoCommit(true);
    }
  }

  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      while (uprs.next()) {
        float f = uprs.getFloat("PRICE");
        uprs.updateFloat("PRICE", f * percentage);
        uprs.updateRow();
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


  public void modifyPricesByPercentage(String coffeeName, float priceModifier,
                                       float maximumPrice) throws SQLException {
    con.setAutoCommit(false);

    Statement getPrice = null;
    Statement updatePrice = null;
    ResultSet rs = null;
    String query =
      "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE COF_NAME = '" +
      coffeeName + "'";

    try {
      Savepoint save1 = con.setSavepoint();
      getPrice =
          con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      updatePrice = con.createStatement();

      if (!getPrice.execute(query)) {
        System.out.println("Could not find entry for coffee named " +
                           coffeeName);
      } else {
        rs = getPrice.getResultSet();
        rs.first();
        float oldPrice = rs.getFloat("PRICE");
        float newPrice = oldPrice + (oldPrice * priceModifier);
        System.out.println("Old price of " + coffeeName + " is " + oldPrice);
        System.out.println("New price of " + coffeeName + " is " + newPrice);
        System.out.println("Performing update...");
        updatePrice.executeUpdate("UPDATE COFFEES SET PRICE = " + newPrice +
                                  " WHERE COF_NAME = '" + coffeeName + "'");
        System.out.println("\nCOFFEES table after update:");
        CoffeesTable.viewTable(con);
        if (newPrice > maximumPrice) {
          System.out.println("\nThe new price, " + newPrice +
                             ", is greater than the maximum " + "price, " +
                             maximumPrice +
                             ". Rolling back the transaction...");
          con.rollback(save1);
          System.out.println("\nCOFFEES table after rollback:");
          CoffeesTable.viewTable(con);
        }
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (getPrice != null) { getPrice.close(); }
      if (updatePrice != null) { updatePrice.close(); }
      con.setAutoCommit(true);
    }
  }


  public void insertRow(String coffeeName, int supplierID, float price,
                        int sales, int total) throws SQLException {
    Statement stmt = null;
    try {
      stmt =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

      uprs.moveToInsertRow();

      uprs.updateString("COF_NAME", coffeeName);
      uprs.updateInt("SUP_ID", supplierID);
      uprs.updateFloat("PRICE", price);
      uprs.updateInt("SALES", sales);
      uprs.updateInt("TOTAL", total);

      uprs.insertRow();
      uprs.beforeFirst();

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int[] updateCounts = stmt.executeBatch();
      this.con.commit();

    } catch (BatchUpdateException b) {
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch (SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      if (stmt != null) { stmt.close(); }
      this.con.setAutoCommit(true);
    }
  }
 
  public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);

      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }

  public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString(1);
        int supplierID = rs.getInt(2);
        float price = rs.getFloat(3);
        int sales = rs.getInt(4);
        int total = rs.getInt(5);
        System.out.println(coffeeName + ", " + supplierID + ", " + price +
                           ", " + sales + ", " + total);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (stmt != null) { stmt.close(); }
    }
  }


Reference-1 : http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

JDBC - PreparedStatement usage

PreparedStatement pstmt = null;
try {
    // Prepare a statement to insert a record
    String sql = "INSERT INTO mysql_all_table("
        + "col_boolean,"
        + "col_byte,"
        + "col_short,"
        + "col_int,"
        + "col_long,"
        + "col_float,"
        + "col_double,"
        + "col_bigdecimal,"
        + "col_string,"
        + "col_date,"
        + "col_time,"
        + "col_timestamp,"
        + "col_asciistream,"
        + "col_binarystream,"
        + "col_blob) "
        + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    pstmt = connection.prepareStatement(sql);

    // Set the values
    pstmt.setBoolean(1, true);
    pstmt.setByte(2, (byte)123);
    pstmt.setShort(3, (short)123);
    pstmt.setInt(4, 123);
    pstmt.setLong(5, 123L);
    pstmt.setFloat(6, 1.23F);
    pstmt.setDouble(7, 1.23D);
    pstmt.setBigDecimal(8, new BigDecimal(1.23));
    pstmt.setString(9, "a string");
    pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
    pstmt.setTime(11, new Time(System.currentTimeMillis()));
    pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));

    // Set the ascii stream
    File file = new File("infilename1");
    FileInputStream is = new FileInputStream(file);
    pstmt.setAsciiStream(13, is, (int)file.length());

    // Set the binary stream
    file = new File("infilename2");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(14, is, (int)file.length());

    // Set the blob
    file = new File("infilename3");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(15, is, (int)file.length());

    // Insert the row
    pstmt.executeUpdate();
} catch (SQLException e) {
} catch (FileNotFoundException e) {
}
finally {
    try {
        if (pstmt != null)
            pstmt.close();
        if (connection != null)
            connection.close();
    }
    catch (Exception ignore) {}
}


  public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement =
      "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      if (updateSales != null) { updateSales.close(); }
      if (updateTotal != null) { updateTotal.close(); }
      con.setAutoCommit(true);
    }
  }

Reference-1 : http://www.exampledepot.com/egs/java.sql/InsertPs.html
Reference-2 : http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

Advanced SQL Queries

For Tables Defined in : http://dbms101.blogspot.com/2012/01/tutorial-db.html

1. Get list of customers who have not placed an order yet.
SQL Query:
-------------
select *
from customers left outer join orders on customers.customer_id = orders.customer_id
where orders.order_id is NULL ;

1    Robin Smith    100 Oracle Plaza    Sunnyvale    CA           
2    John Lincoln    200 Oracle Plaza    Sunnyvale    CA           
4    David Crow    400 Oracle Plaza    Sunnyvale    CA           
5    Test User1    500 Oracle Plaza    Santa Clara    CA           
7    Test User3    700 Oracle Plaza    Palo Alto    CA           
8    Test User4    800 Oracle Plaza    Palo Alto    CA          

Analysis:
---------
When you just do
select *
from customers left outer join orders on customers.customer_id = orders.customer_id;
- We get all customer rows.
By adding condition where orders.order_id is NULL, we get the solution.

REFERENCES : http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=27

How to write a SQL - Query

REFERENCE : http://www.ifadey.com/2010/11/best-way-to-write-sql-query/

 

Clause Order

Thinking and writing SQL query clauses in particular order (in which database engine executes the query) matters a lot because problem (getting required data) gets much simplified by creating query in such an order. Actually the problem is automatically divided and simplified when writing query in execution order. Let’s take a look how the database engine executes the query.

1. The Source of Data

The first clause which gets executed is FROM clause. It means the first step when you write a query is to know FROM which table(s) you want to get the data. If the required data is in multiple tables, then think about the JOINs in first step. When FROM clause gets executed, all rows are fetched from the tables specified. Let’s call them Individual Rows.
Click here to read more about SQL JOINs.

2. Filter Individual Rows

Second step is to think about WHERE clause which is used to filter Individual Rows on particular condition(s).

3. Grouping of Individual Rows

GROUP BY is the third clause which gets executed. So think about it in the third step when writing a query. This clause group Individual Rows (fetched using FROM and filtered using WHERE) on the basis and order of columns specified.
Click here to read more about SQL GROUP BY.

4. Filter Group Rows

HAVING clause is used to filter Group Rows based on the condition(s) specified in it. It’s fourth one which gets executed.

5. Filter Columns

The fifth clause which gets executed is SELECT. This clause is used to filter columns which are not required.

6. Ordering of Rows

The final step in execution of query is to order the rows based on specified column(s).

DataSource Connection for Pooling - XE

Data Source for Pooled Connection and Distributed Transactions.

Tutorial-1: http://www.java2s.com/Code/Java/Database-SQL-JDBC/OracleConnectionPoolDataSource.htm
Tutorial-2: http://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html
Tutorial-3:  http://docs.oracle.com/cd/B14117_01/java.101/b10979/urls.htm
Tutorial-4: http://www.orafaq.com/wiki/JDBC



package com.struts1.tutorials.datasource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class DataSourceConnPoolTest {
   
    private static final String SQL_QUERY1 = "SELECT * fROM customers";
    private static final String QUERY_FIELD1 = "CUSTOMER_NAME";
    private static final String SQL_QUERY2 = "SELECT count(*) FROM v$session WHERE username = 'SYS'";
    public static void main(String[] args) {
        PooledConnection pc_1 = null;
        PooledConnection pc_2 = null;
        Connection conn_1 = null;
        Connection conn_2 = null;
        Connection conn_3 = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {           
            OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
            ocpds.setURL("jdbc:oracle:thin:@localhost:1521:XE");
            ocpds.setUser("lakshman01");
            ocpds.setPassword("passwd");
           
            pc_1 = ocpds.getPooledConnection();
            conn_1 = pc_1.getConnection();
            stmt = conn_1.createStatement();
           
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
            String msg = "Data = ";
            System.out.println(msg + "pc-1 - conn_1: " + rset.getString(QUERY_FIELD1));
           
            if (stmt != null) {
                stmt.close();
            }
            if (rset != null) {
                rset.close();
            }
           
           
            conn_2 = pc_1.getConnection();
            stmt = conn_2.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
           
            System.out.println(msg + "pc-1 - conn_2: " + rset.getString(QUERY_FIELD1));
            if (stmt != null) {
                stmt.close();
            }
            if (rset != null) {
                rset.close();
            }

            pc_2 = ocpds.getPooledConnection();
            conn_3 = pc_2.getConnection();
            stmt = conn_3.createStatement();
           
            rset = stmt.executeQuery(SQL_QUERY1);
            rset.next();
            System.out.println(msg + "pc_2 - conn_3: " + rset.getString(QUERY_FIELD1));           
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            }
            catch (Exception ignore) {}
            try {
                if (rset != null) {
                    rset.close();
                }               
            }
            catch (Exception ignore) {}
            try {
                if (conn_1 != null)
                    conn_1.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn_2 != null)
                    conn_2.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn_3 != null)
                    conn_3.close();
            }
            catch (Exception ignore) {}
            try {
                if (pc_1 != null)
                    pc_1.close();
            }
            catch (Exception ignore) {}
            try {
                if (pc_2 != null)
                    pc_2.close();
            }
            catch (Exception ignore) {}           
        }
    }
}


Sample Code to use OracleDataSource instead of old DriverManager.
(Remember this is not pooled.)
************


package com.struts1.tutorials.datasource;

import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.*;

import oracle.jdbc.pool.OracleDataSource;

public class DataSourceTest {

    private static final String SQL_QUERY1 = "SELECT * fROM customers";
   
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {
            OracleDataSource ods = new OracleDataSource();
            ods.setDriverType("thin");           
            ods.setDatabaseName("XE");
            ods.setServerName("localhost");
            ods.setPortNumber(1521);
            ods.setUser("lakshman01");
            ods.setPassword("passwd");
           
/*           
 *             Not an EJB - App. So don't bother about context
            Context ctx = new InitialContext();
            ctx.bind("jdbc/tutorialdb", ods);           
            OracleDataSource odsconn = (OracleDataSource)ctx.lookup("jdbc/sampledb");
*/

            conn = ods.getConnection();
           
            stmt = conn.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            while (rset.next()) {
                System.out.println ("Customer ID = " + rset.getInt("CUSTOMER_ID"));
                System.out.println ("Customer Name = " + rset.getString("CUSTOMER_NAME"));
            }                       

        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            try {
                if (rset != null)
                    rset.close();
            }
            catch (Exception ignore) {}
            try {
                if (stmt != null)
                    stmt.close();
            }
            catch (Exception ignore) {}
            try {
                if (conn != null)
                    conn.close();
            }
            catch (Exception ignore) {}           
        }       
    }
}

How to get Oracle DB name using SQL

select ora_database_name from dual

Oracle XE Connection - Sample

package com.struts1.tutorials.ejb.orders;

import java.sql.*;

public class TestDB {
   
    private static final String SQL_QUERY1 = "SELECT * FROM customers";
   
    private static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";
    private static final String DB_URL = "jdbc:oracle:thin:@//localhost:1521/xe";   
    private static final String DB_USR = "lakshman01";
    private static final String DB_PWD = "passwd";
   
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
       
        try {
            Class.forName (JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DB_USR, DB_PWD);
           
            stmt = conn.createStatement();
            rset = stmt.executeQuery(SQL_QUERY1);
            while (rset.next()) {
                System.out.println ("Customer ID = " + rset.getInt("CUSTOMER_ID"));
                System.out.println ("Customer Name = " + rset.getString("CUSTOMER_NAME"));
            }                       
        }
        catch (Exception ex) {
            ex.printStackTrace();           
        }
        finally {
            try {
                if (rset != null)
                    rset.close();
            } catch (Exception ignore) {}
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception ignore) {}
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception ignore) {}           
        }       
    }
}

SQL Debugging

1. Problem

When using a GROUP BY clause every field in the SELECT list must be either:

• One of the GROUP BY terms - in this example customer_city.
• An aggregate function - for example SUM or COUNT
• An expression based on the above

In the example the field name may not be used on the SELECT line.
In a group by line each region shows up only once - however in a typical region such as Africa there are several different name values. WHich one should SQL pick?

Solutions

• Remove the offending field from the SELECT line
• Add the field to the GROUP BY clause
• Aggregate the offending field

Example

• select count(customer_id),customer_city from customers group by customer_city;

REFERENCE : http://sqlzoo.net/howto/source/u.cgi/err979/oracle

2. Problem 

Objective: insert value for date column in a table say ('12/03/2006');
Here is what you need to do:
Method 1:
insert into db (target_column_name) values (to_date('12/03/2006','mm/dd/yyyy'));

Method 2:
insert into db values (to_date('12/03/2006','mm/dd/yyyy'));

Method 1 is the preferred way because the target column is named. Target columns should always be named. Inserting into a table without naming the target columns is a bug waiting to happen. 

REFERENCE : http://forums.digitalpoint.com/showthread.php?t=109851

Temporary Tablespace

TEMPORARY Tablespaces and TEMPFILES

REFERENCE :  http://www.orafaq.com/node/2


What are Temporary Tablespaces:
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.

What are TEMPFILES?
Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.
TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.
One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:
SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;
If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace:
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;
Except for adding a tempfile, as illustrated in the above example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).

How does one create Temporary Tablespaces?
Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). One should use the most recent method available:
- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;
Oracle 8i and 9i example:

SQL> CREATE TEMPORARY TABLESPACE temp 
      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.
Oracle 9i example using OMF (Oracle Managed Files):
SQL> CREATE TEMPORARY TABLESPACE temp;

Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:
- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
To see the default temporary tablespace for a database, execute the following query:
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.
Other Considerations:
Some performance considerations for temporary tablespaces:
- Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
- Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (Use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up sorting operations (user processes can read/write to it directly).

 Monitoring Temporary Tablespaces and Sorting:
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                             52428800   52428800

TEST DB for Online Orders

1. TABLESPACE / USER CREATION :

/*Create a tablespace first*/
CREATE TABLESPACE tutorials
DATAFILE 'C:\oraclexe\app\oracle\oradata\xe\tutorials01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;


/*Create new user*/
CREATE USER lakshman01
IDENTIFIED BY passwd
DEFAULT TABLESPACE tutorials
TEMPORARY TABLESPACE temp;


/*Grant permissions to the newly created user*/
GRANT CREATE SESSION TO lakshman01;
GRANT CREATE TABLE TO lakshman01;
GRANT CREATE VIEW TO lakshman01;
GRANT CREATE SEQUENCE TO lakshman01;

2. TABLE CREATION

CREATE TABLE customers
(
customer_id INT PRIMARY KEY NOT NULL,
customer_name varchar2(255),
customer_address varchar2(255),
customer_city varchar2(255),
customer_state varchar2(255)
);


CREATE TABLE orders
(
order_id INT PRIMARY KEY NOT NULL,
order_date DATE NOT NULL,
customer_id INT REFERENCES customers(customer_id)
);


CREATE TABLE items
(
item_id INT PRIMARY KEY NOT NULL,
item_description varchar2(255),
item_price varchar2(255)
);


/*Junction table between ORDERS and ITEMS*/
CREATE TABLE order_items
(
order_id INT REFERENCES orders(order_id),
item_id INT REFERENCES items(item_id),
item_qty varchar2(255),
CONSTRAINT pk_order PRIMARY KEY (order_id, item_id)
);


ALTER TABLE orders
ADD order_comments varchar2(255) DEFAULT NULL;


3. DATA INSERTION :

INSERT INTO customers VALUES
(1, 'Robin Smith', '100 Oracle Plaza', 'Sunnyvale', 'CA');

INSERT ALL
INTO customers VALUES (2, 'John Lincoln', '200 Oracle Plaza', 'Sunnyvale', 'CA')
select * from dual;

INSERT ALL
INTO customers VALUES (3, 'Peter Doje', '300 Oracle Plaza', 'Sunnyvale', 'CA')
INTO customers VALUES (4, 'David Crow', '400 Oracle Plaza', 'Sunnyvale', 'CA')
select * from dual;

INSERT ALL
INTO customers VALUES (5, 'Test User1', '500 Oracle Plaza', 'Santa Clara', 'CA')
INTO customers VALUES (6, 'Test User2', '600 Oracle Plaza', 'Santa Clara', 'CA')
INTO customers VALUES (7, 'Test User3', '700 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (8, 'Test User4', '800 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (9, 'Test User5', '900 Oracle Plaza', 'Palo Alto', 'CA')
INTO customers VALUES (10, 'Test User6', '1000 Oracle Plaza', 'Menlo Park', 'CA')
select * from dual;

INSERT ALL
INTO items VALUES (1, 'Pen', 10)
INTO items VALUES (2, 'Pencil', 10)
INTO items VALUES (3, 'Eraser', 10)
INTO items VALUES (4, 'Notebook', 20)
INTO items VALUES (5, 'Stapler', 30)
INTO items VALUES (6, 'Backpack', 40)
select * from DUAL;

INSERT ALL
INTO ORDERS VALUES (1, to_date('01/23/2012','mm/dd/yyyy'), 3)
INTO ORDERS VALUES (2, to_date('01/23/2012','mm/dd/yyyy'), 6)
INTO ORDERS VALUES (3, to_date('01/23/2012','mm/dd/yyyy'), 9)
INTO ORDERS VALUES (4, to_date('01/24/2012','mm/dd/yyyy'), 10)
select * from DUAL;

INSERT ALL
INTO ORDER_ITEMS VALUES (1, 1, 5)
INTO ORDER_ITEMS VALUES (1, 2, 5)
INTO ORDER_ITEMS VALUES (1, 3, 5)
INTO ORDER_ITEMS VALUES (1, 4, 10)
INTO ORDER_ITEMS VALUES (2, 1, 10)
INTO ORDER_ITEMS VALUES (2, 4, 5)
INTO ORDER_ITEMS VALUES (2, 5, 1)
INTO ORDER_ITEMS VALUES (3, 1, 2)
INTO ORDER_ITEMS VALUES (3, 2, 4)
INTO ORDER_ITEMS VALUES (3, 3, 2)
INTO ORDER_ITEMS VALUES (3, 4, 4)
INTO ORDER_ITEMS VALUES (3, 6, 1)
INTO ORDER_ITEMS VALUES (4, 5, 2)
INTO ORDER_ITEMS VALUES (4, 6, 2)
select * from DUAL; 

Any update or delete?
-----------------------
UPDATE customers SET customer_name='Peter Dove' where customer_id = 3;
DELETE FROM customers WHERE customer_id = 3 OR customer_id = 4;
 

REFERENCE ER : http://phlonx.com/resources/nf3/

FIRST Operations after installing Oracle DB

Starting on a newly installed system

When you start of on a newly installed system you will need to create a user that will be accessing the database. For security reasons you should not be using the system user nor the system tablespace. In the next sections you will learn how to create users and tablespaces for them to use.

Creation of tablespace

What is a tablespace and why do I need one? A tablespace is where Oracle allocates space for your database. Without this you cannot create tables or store data.
To see the Oracle documentation for this operation type 'help create tablespace' on the sqlplus prompt 'SQL>'.
What you basically need to know to get going is how to create a simple tablespace.

CREATE TABLESPACE tablespace
DATAFILE datafile
SIZE size
  [ AUTOEXTEND ON NEXT size [M] 
    [MAXSIZE size [M]]
  ]

With this simplified structure of the command we can build a small statment for creating a small test-tablespace.

SQL> CREATE TABLESPACE test 
       DATAFILE '/path/to/oracle/oradata/SID/test.dbf' 
       SIZE 10M;

Our first tablespace will be able to hold roughly 10 MB of data. You might ask yourself what will happen if you try to store more data than the tablespace will hold? Then Oracle will give you an error and not be able to store the data you're trying insert. You can either add more datafiles or alter the tablespace if you have created the tablespace already. But it's better to be prepared and make the tablespace more extensible. The next example will show just how to do that.

SQL> CREATE TABLESPACE test 
       DATAFILE '/path/to/oracle/oradata/SID/test01.dbf' 
       SIZE 10M 
       AUTOEXTEND ON NEXT 10M
       MAXSIZE 100M;

The datafile I used in this example is different from the one in the earlier example in the way that it names the datafile as tablespace followed by a number followed by the fileextension. This practice makes it a lot easier figuring out how you created the tablespace and a better scheme for adding new datafiles. The example set a maximum size of 100 MB since we don't want the database being able to consume all available space on the disksystem.

Creation of a user

To be able to connect to an Oracle database you need to create a user. A user can have different rights depending on what the user should be privilegded to do. It's generally a very good idea to create a user to not screw anything up on the database. To see the Oracle documentation for creating a user type 'help create user' on the sqlplus prompt.
From the Oracle documentation we can derive the next example.

SQL> CREATE USER test 
       IDENTIFIED BY passwd 
       DEFAULT TABLESPACE test 
       TEMPORARY TABLESPAC temp;

This will create a user test which has the password passwd and with test as the default tablespace, temp is an Oracle temporary tablespace.

Granting rights

Without any rights, the newly created user won't even be able to log on to the Oracle database. Among others creating tables is also a very important privilegde to have. Typing 'help grant' will only refer you to the Oracle server reference so I'll give a quick rundown of the important privilegies.

SQL> GRANT CREATE SESSION TO test;

The session priviledge will allow the user to connect to the database.

SQL> GRANT CREATE TABLE TO test;

The table priviledge will allow the user to create tables in the database.

SQL> GRANT CREATE VIEW TO test;

The view priviledge will allow the user to create views of tables in the database.

SQL> GRANT CREATE SEQUENCE TO test;

The sequence priviledge will alow the user to create sequences for making unique ids for his tables.

Altering quota on tablespaces

This must be all then, right? No, for the user to be able to create tables you should set the quota for the user on the default tablespace. This could have been done in the process of creating the user, but to avoid being to complicated I put this off until now.

SQL> ALTER USER test QUOTA unlimited ON test;

In this example the user would be able to fill up the whole tablespace, which sometimes isn't what you want. Instead you can put a quota in bytes, kilobytes, or megabytes on the user.

Deleting a user

Now that you have messed around with your new user you will probably want to start over fresh and you're asking yourself how to delete what you just created. Again the built-in help of sqlplus is very usefull. So if you try a 'help drop' on the sqlplus prompt you will get a listing of the different options you have.

SQL> DROP USER test CASCADE;

Cascade is the keyword to effectively wipe out everything belonging to the user including, but not limited to, tables, views, and sequences.

Reference : http://thomas.eibner.dk/oracle/basicdba/

DUAL Table in Oracle

DUAL Details : http://en.wikipedia.org/wiki/DUAL_table

SELECT 1+1
FROM DUAL;
 
SELECT SYSDATE 
FROM   DUAL;
 
SELECT USER 
FROM   DUAL;

INSERT ALL 
INTO customers VALUES (2, 'John Lincoln', '200 Oracle Plaza', 'Sunnyvale', 'CA')
INTO customers VALUES (3, 'Peter Doe', '300 Oracle Plaza', 'Sunnyvale', 'CA')
select * from dual;

SQL interview questions with answers

Geek Interview website : http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

Website 2 : http://www.sql-server-business-intelligence.com/sql-server/interview-questions-and-answers/sql-interview-questions-and-answers-pdf-download

Some famous questions:
*******************
What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

• SET SHOWPLAN_ALL ON,
• SET SHOWPLAN_TEXT ON,
• SET STATISTICS IO ON,
• SQL Server Profiler,
• Windows NT /2000 Performance monitor,
• Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from Microsoft web site.

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

Explain different isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

CREATE INDEX myIndex ON myTable (myColumn)

What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

What is the maximum size of a row?

8060 bytes. Do not be surprised with questions like 'What is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you do not, at least be familiar with the way clustering works and the two clustering configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.

Explain the architecture of SQL Server

This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.

What is Lock Escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it will not log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more information.

What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?

This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.

What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"


Reference : http://www.learn.geekinterview.com/resources/interview-articles/sql-interview-questions-with-answers.html

A transaction is a sequence of sql Operations(commands), 
work as single atomic unit of work. To be qualify 
as "Transaction" , this sequence of operations must satisfy 
4 properties , which is knwon as ACID test.

A(Atomicity):-The sequence of operations must be atomic, 
either all or no operations are performed.

C(Consistency):- When completed, the sequence of operations 
must leave data in consistent mode. All the defined 
relations/constraints must me Maintained.

I(Isolation): A Transaction must be isolated from all other 
transactions. A transaction sees the data defore the 
operations are performed , or after all the operations has 
performed, it can't see the data in between.

D(Durability): All oprtaions must be permanently placed on 
the system. Even in the event of system failure , all the 
operations must be exhibit.