SPANISH - CREACION Y MANIPULACION DE TABLAS

"ORA-01555: Snapshot too old: rollback...too small". Why do I get it? How can I stop it?

despite we have a 6 GB tablespace for rollback segments and
the maximum size for a rollback segment output for hwmsize is only from v$rollstat,we are sometimes getting ora-1555 errors.so nobody says that our rollback segment tablespace size isnt enough! .then why we are getting this error?

some init parameters:
transactions=9011
transaction_per_rollback_segment=8
number of rollback segments=30
each rollback segment has the following storage parameters:

(INITIAL 10240K NEXT 10240K OPTIMAL 20480K   MINEXTENTS 2 MAXEXTENTS 505)

(note: we are using Baan ERP,and have an average of 150 concurrent users,each user open avarage of 5 sessions)

Thanks in advance.

1) My editorial comment: The error results from an architectural problem with the Oracle database itself that has existed since Oracle’s very beginning of rollback segments. The fact that it occurs at all is, in my opinion, an Oracle BUG, which Oracle refuses to fix, so you and I must implement our own workarounds to deal with this problem. To compound the problem, the “fix” that we infer from the error message (in your case, to “increase the size of rollback segment number 4 named R03”) is virtually useless in resolving the problem. To COMMIT “early and often” as we typically hear, may not be a viable option. So, I shall explain the PROBLEM and some SOLUTIONS in the following.

2) Background and a scenario to understanding the error:
Let’s assume the following:
Assumption 1: a database with seven rollback segments.

Assumption 2: One of rollback segments, R07, our DBA has made extremely large, and we do as you suggest, 'set transaction use rollback segment R07' before our long-running transaction.

Assumption 3: the other six rollback segments are of ‘normal’ size (whatever ‘normal’ is).

Scenario:
At 12:02:35, User A executes an update transaction that Oracle assigns to rollback segment R01, starting its writing at R01’s Block Number 126 of 128, since User Z is using blocks 1-125.

At 12:02:39, User Z commits her changes (freeing up blocks 1-125 of rollback segment R01).

At 12:02:40, User B begins our large, long-running transaction. As we designed, Oracle assigns this transaction to R07 (the way big rollback segment). This large transaction needs to see database data that was “true” (i.e., committed) at the beginning of this transaction. User A changed database blocks that User B will read later at, say 12:23:30; Oracle copied User A’s original data blocks (that were “true” at 12:02:40) to rollback segment R01. The only place on earth that User B will be able to find data that was “true”/committed at the start of User B transaction (at 12:02:40) is in rollback segment R01.

At 12:02:45, User A commits her changes. Her pre-change, consistent, PRE-COMMIT images occupied blocks 126, 127, 128, 1, 2, and 3 of rollback segment R01.

At 12:02:50 through 12:03:20, Users C, D, E, F, G, and H initiate transactions that Oracle automatically assigns to rollback segments R02, R03, R04, R05, R06, and R07, respectively.

At 12:03:30, User J initiates a transaction. Oracle’s “round-robin”-rollback-segment-assignment algorithm goes back to R01. To determine how to use rollback segment R01 (i.e., where to begin writing rollback data), Oracle asks the question, “Are there any un-committed transactions in rollback segment R01, whose rollback data we still need FOR THE TRANSACTION THAT WROTE THE DATA?” The answer is “No”, (since COMMIT happened at 12:02:45) therefore Oracle begins writing rollback data to R01’s first available location, Block Number 1. Notice, then, that User J’s rollback data overwrites User A’s rollback images, WHICH User B NEEDS TO READ IN 20 MINUTES FROM NOW !!!

At 12:23:30, User B reaches the point where she needs to read the data that was true when User B’s transaction began at 12:02:40. The only place that data resided was in blocks 126, 127, 128, 1, 2, and 3 of rollback segment R01. Those data a LONG GONE, having been overwritten by at least User J’s rollback entries. Oracle generates the error: “ORA-01555: snapshot too old…” Notice also, that the gigantic rollback segment R07 didn’t help us at all since the data we needed was in one of the ‘normal’ sized rollback segments.

Possible solutions:
Solution 1: Make ALL rollback segments GIGANTIC. How large is GIGANTIC? So large that during the life of your longest-running transaction, the OTHER concurrently running transactions could not possibly generate enough rollback entries to “rollover” on itself and overwrite rollback entries from other previously committed/rolledback transactions, which still-running, long-running transactions still need. The problem here is, who has a crystal ball to anticipate just how large each rollback segment must be to ensure no rollover. Even if we could anticipate how gigantic ALL rollback segments must be, think of all the space that the rollback segments would NOT be using 99% of the time. What an (expensive) waste of disk space.

Solution 2: Kick everyone else off your Oracle instance while your long-running transaction executes. Yea, right !!! I know what kind mutiny that would cause at our company.

Solution 3: (This is the solution our organization uses to inoculate against ORA-01555 and we have found it to never fail us.) Before running a known long-running transaction, we create a bogus transaction (such as “INSERT INTO DUMMY VALUES (‘X’);) for each on-line rollback segment in the database (except the SYSTEM rollback segment, which an application transaction would never use.) We do a “SET TRANSACTION USE ROLLBACK SEGMENT <rollback segment name>;” for each of the transactions. These bogus transactions, since they remain ‘active’ during the life of your long-running transaction, prevent Oracle from rolling over a rollback segment on itself; thus preventing the loss of vital rollback data to the long-running, referencing transaction. Instead of rolling over upon themselves, the rollback segments ‘grow’ by adding more extents rather than re-using still-needed extent blocks. For this to work, each of the bogus transactions needs to originate from a different session/window. Once your long-running transaction successfully runs to its natural end (including COMMIT), then you can rollback each of the bogus transactions.

If each of your rollback segments have a ‘…STORAGE (OPTIMAL xxMB)…”, then each rollback segment will shrink down to the OPTIMAL size, thus freeing up all of the “extra” space that the bogus transactions caused in order to preserve the vital rollback entries.

IMPORTANT NOTE: Following the completion of your long-running transaction, you MUST close off each of the bogus transactions with either an explicit or implicit COMMIT or ROLLBACK. If you do not stop the bogus transactions, then they cause the rollback segments to grow until you are out of disk space.

JAVA ORACLE EXAMPLE:

/*
   * @author  Reghu
   * @version 1.2
   *
   * Development Environment        :  JDeveloper 2.0
   * Name of the Application        :  DMLSample.java
   * Creation/Modification History  :
   *
   *    Reghu       27-Dec-1998      Created
   *    Sujatha     21-May-2002      Certified on Oracle9i JDeveloper
   *    Savitha     10-Jan-2003      Certified on Linux
   *                                 Included checkTables() method
   *
   */

package oracle.otnsamples.jdbc.dml;
  
  // Package for JDBC classes
  import java.sql.SQLException;
  import java.sql.PreparedStatement;
  import java.sql.Statement;
  import java.sql.Connection;
  import java.sql.ResultSet;
  
  // Package for AWT classes
  import java.awt.Dimension;
  import java.awt.Toolkit;
  
  // Java utility classes
  import java.util.Enumeration;
  import java.util.Properties;
  import java.util.ResourceBundle;
  import java.io.IOException;
  
  // oracle jdbc classes
  import oracle.jdbc.pool.OracleDataSource;
  
  /**
   * This application illustrates the SQL DML operations like SELECT, INSERT,
   * UPDATE and DELETE from a java program using JDBC. The user can insert,select,
   * update and delete records from the otn_airlines table using this sample.
   *
   * The GUI for this sample is handled in DMLFrame.java
   *
   *The sample uses the JDBC-Thin driver.
   *
   */

public class DMLSample {
  
    /** Database Connection Object */

  private Connection connection = null;
  
    /** GUI handler for this sample */

  private DMLFrame gui;
  
    /**
     *  Constructor , instantiates GUI.
     */

  public DMLSample() {
      gui = new DMLFrame(this); // Instantiate GUI
  
  

    // Display the frame in the center of screen
      Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
      Dimension frameSize  = gui.getSize();
      if( frameSize.height > screenSize.height ) {
        frameSize.height = screenSize.height;
      }
      if (frameSize.width > screenSize.width) {
        frameSize.width = screenSize.width;
      }
      gui.setLocation((screenSize.width - frameSize.width) / 2,
                        (screenSize.height - frameSize.height) / 2);
      gui.setVisible(true);
    }
  
    /**
     *  Main entry point for the class. Instantiates the DMLSample class
     *  and sets up the database connection.
     */

  public static void main( String args[] ) {
      DMLSample dml = new DMLSample();  // Instantiate DMLSample class
  
  

    //create connection object
      dml.connection = dml.dbConnection();
  
      // check if connection exists, else create it
      if( dml.connection != null ) {
        // Check if 'otn_airlines' table is present, else create it
        dml.checkTables();
      }
    }
  
    /**
     *  Dispatches the GUI events to the appropriate method, which performs
     *  the required JDBC operations. This method is invoked when event occurs
     *  in the GUI (like table Selection, Button clicks, etc.). This method
     *  is invoked from the setupListeners method of DMLFrame.java
     */

  public void dispatchEvent (String eventName) {
  
      // Get the user input values for code, name and partner
      String code = gui.codeTextfield.getText();
      String name = gui.nameTextfield.getText();
      String partner = gui.partnerTextfield.getText();
  
      // Dispatch Event
      if( eventName.equals("SELECT") )
        selectRecords( code, name, partner );
      else if( eventName.equals("UPDATE") )
        updateRecord( code, name, partner );
      else if( eventName.equals("INSERT") )
        insertRecord( code, name, partner );
      else if( eventName.equals("DELETE") )
        deleteRecord( code );
      else if( eventName.equals("EXIT") )
        exitApplication();
    }
  
    /**
     * This method reads Connection.properties file which is passed as
     * the parameter to it and load it into a java Properties
     * object and returns it.
     */

  private Properties loadParams(String file) throws IOException {
  
      // Loads a ResourceBundle and creates Properties from it
      Properties prop = new Properties();
      ResourceBundle bundle = ResourceBundle.getBundle(file);
      Enumeration enum = bundle.getKeys();
      String key = null;
      while(enum.hasMoreElements()) {
        key = (String)enum.nextElement();
        prop.put(key, bundle.getObject(key));
      }
      return prop;
    }
  
   /**
     * Creates a database connection object using DataSource object. Please
     * substitute the database connection parameters with appropriate values in
     * Connection.properties file
     */

  private Connection dbConnection() {
      try {
        gui.putStatus("Trying to connect to the Database");
  
        // Load the properties file to get the connection information
        Properties prop = loadParams("Connection");
  
        // Create a OracleDataSource instance
        OracleDataSource ods = new OracleDataSource();
  
        // Sets the driver type
        ods.setDriverType("thin");
  
        // Sets the database server name
        ods.setServerName((String)prop.get("HostName"));
  
        // Sets the database name
        ods.setDatabaseName((String)prop.get("SID"));
  
        // Sets the port number
        ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());
  
        // Sets the user name
        ods.setUser((String)prop.get("UserName"));
  
        // Sets the password
        ods.setPassword((String)prop.get("Password"));
        connection=ods.getConnection();
        gui.putStatus("Connected to " + prop.get("SID") +
                      " Database as " + prop.get("UserName")+". Please click on SELECT"+
                      " button to view records");
  
      } catch(SQLException ex) { // Trap SQL errors
          gui.putStatus("Error in Connecting to the Database "+'\n'+ex.toString());
      } catch(IOException ex) { // Trap IO errors
          gui.putStatus("Error in reading the properties file "+'\n'+ex.toString());
      }
      return connection;
    }
  
    /**
     * Checks if the table ('otn_airlines') is present, else creates it.
     * Look into PopulateTable.java for more details
     */

  private void checkTables()   {
      Statement stmt = null;
      ResultSet rset = null;
      try     {
        stmt = connection.createStatement();
  
        // check from User_tables data dictionary table if the table is existing.
        rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
                                 " WHERE Table_Name = 'OTN_AIRLINES' ");
  
        // if the resultset of the above query does not have any record, it means
        // table is not existing. So the table is created.
        if (!rset.next()) {
          // call the class to create the table
          PopulateTable pop = new PopulateTable(connection, gui);
          pop.createSchemaTables();
          gui.putStatus( "otn_airlines Table created." +". Please click on SELECT"+
                      " button to view records");
        }
      } catch (SQLException sqlEx) {
         gui.putStatus(" Could not create table otn_airlines : "+sqlEx.toString());
      } finally  {
          try {
            if( rset != null ) rset.close( );
            if( stmt != null ) stmt.close( );
          } catch(SQLException ex) { }
      }
    }
  
    /**
     *  Method to insert a new row into the database using JDBC.
     */

  private void insertRecord( String code,String name,String partner ) {
      try {
        // Prepare a SQL statement to insert a new record into the otn_airlines table
        PreparedStatement pst = connection.prepareStatement(
                                         "INSERT INTO otn_airlines VALUES (?,?,?)");
  
        // Bind the column values into the PreparedStatement
        pst.setString(1, code);
        pst.setString(2, name);
        pst.setString(3, partner);
  
        // Execute the PreparedStatement
        pst.execute();
  
        // Close the PreparedStatement object
        pst.close();
  
        // Update GUI to reflect new record
        gui.addToJTable( code, name, partner);
        gui.putStatus("The Record is inserted");
      } catch (SQLException ex) { //Trap SQL errors
          gui.putStatus("Error in inserting record to the database " +ex.toString());
      }
    }
  
    /**
     *  Method to select records from the otn_airlines table using JDBC, based on the
     *  query conditions entered by the user
     */

  private void selectRecords(String code,String name, String partner) {
      // The SQL query for selecting the rows from the otn_airlines table
      String query = "SELECT * FROM otn_airlines "

                  + "WHERE UPPER(code) LIKE UPPER(?) AND UPPER(name) LIKE UPPER(?) "+
                    " AND UPPER(partner) LIKE UPPER(?)";
      try {
        // Create a PreparedStatement based on the query
        PreparedStatement pst = connection.prepareStatement( query );
  
        // Use wild card % to query all records if no query condition was entered.
        code    = "%"+code+"%";
        name    = "%"+name+"%";
        partner = "%"+partner+"%";
  
        // Bind the PreparedStatement with corresponding values
        pst.setString(1, code);
        pst.setString(2, name);
        pst.setString(3, partner);
  
        // Execute the PreparedStatement
        ResultSet resultSet = pst.executeQuery();
  
        gui.tmodel.clearTable(); //Clear JTable in GUI
  
  

      // Populating the Result set, retrieve rows, and update GUI to
        // reflect each selected record
        while (resultSet.next()) { // Point result set to next row
          // Retrieve column values for this row
          String codeVal    = resultSet.getString(1);
          String nameVal    = resultSet.getString(2);
          String partnerVal = resultSet.getString(3);
          gui.addToJTable( codeVal, nameVal, partnerVal ); // Update Jtable
        }
  
         // Close PreparedStatement object
         pst.close();
  
         gui.putStatus(gui.tmodel.getRowCount() +" Records selected");
      } catch( SQLException ex ) { //Trap SQL errors
         gui.putStatus("Error in selecting from the database " +ex.toString());
      }
    }
  
    /**
     *  Method to update the selected row with new values using JDBC
     */

  private void updateRecord(String code, String name, String partner) {
      try {
        //Prepare a SQL statement to update the selected record
        PreparedStatement pst = connection.prepareStatement(
                    "UPDATE otn_airlines SET name = ?, partner = ? WHERE code = ?");
  
        // Bind the values for the update statement
        pst.setString(1, name);    // Sets the value of name
        pst.setString(2, partner); // Sets value of partner
        pst.setString(3, code);    // Sets the value of code
  
  

      // Execute the PreparedStatement and get the number of rows updated
        int numb = pst.executeUpdate();
  
        // Close the PreparedStatement object
        pst.close();
  
        // Update the GUI to reflect the changed record
        if( numb > 0 ) {
          gui.updateJTable( code, name, partner);
          gui.putStatus( numb + "  Record updated ");
        } else

      gui.putStatus("Record not updated. Code cannot be changed.");
      } catch( SQLException ex ) {  //Trap the SQL errors
          gui.putStatus("Error in updating to the database"+ '\n'+ex.toString());
      }
    }
  
    /**
     *  Method to delete the selected row from the JTable
     */

  private void deleteRecord(String code) {
      try {
        // Prepare a SQL statement to delete the selected row
        PreparedStatement pst = connection.prepareStatement(
                                          " DELETE FROM otn_airlines WHERE code=?");
  
        // Bind the airline code to the SQL statement
        pst.setString(1, code);//sets the value of code
  
  

      // Execute the PreparedStatement and obtain the number of rows deleted
        int numb =  pst.executeUpdate();
  
        // Close the PreparedStatement
        pst.close();
  
        // Delete the row from the JTable
        if( numb > 0 ) {
          gui.deleteFromJTable();
          gui.putStatus( numb + " Record deleted" );
        } else

        gui.putStatus( "Record not deleted" );
      } catch( Exception ex ) {  //Trap the SQL errors
          gui.putStatus("Error in deleting the row "+'\n'+ex.toString());
      }
    }
  
    /**
     *  Close the database Connection and exit the application
     */

  private void exitApplication() {
      try {
        gui.putStatus("Closing the connection....please wait.....");
        if( connection != null )
          connection.close(); //Close the connection object.
      } catch( SQLException ex ) { //Trap SQL Errors
        gui.putStatus(ex.toString());
      }
      System.exit(0); //Exit the application
    }
  }