Wednesday 12 December 2007

Oracle 10g SQL Mass rebuild and move the indexes of a specified user

Here is a function that rebuilds or coalesces the indexes of a specified user.If asked to rebuild the indexes then you can supply a table space to move the rebuild indexes into.

The function takes three parameters :

  • user_name : The name of the user whose indexes are to be updated
  • rebuild_type : Ether C or R to indicate Coalesce or Rebuild operation
  • tablespace_name : Optional parameter to indicate where to move the indexes in case of rebuild

The function should be executed by a user with DBA privileges and this code is as follows

create or replace PROCEDURE rebuild_user_indexes( user_name IN VARCHAR2, 
                                rebuild_type IN VARCHAR2, 
                                tablespace_name IN VARCHAR2 DEFAULT NULL) 
AS
  full_index_name VARCHAR2(100);
  index_command VARCHAR2(10);
  sql_str VARCHAR2(100);
  
  total_indexes Integer;
  updated_indexes Integer;
  
  CURSOR get_index_cursor IS
    SELECT index_name, index_type 
    FROM dba_indexes
    WHERE owner = user_name AND
          index_type in ( 
              'BITMAP', 
              'NORMAL', 
              'FUNCTION-BASED NORMAL', 
              'FUNCTION-BASED DOMAIN');
      
BEGIN
  /* check initial parameters */
  IF rebuild_type <> 'R' AND rebuild_type <> 'C' THEN
    raise_application_error( -20999, 'rebuild type should be "C" or "R"');
  ELSIF rebuild_type = 'R' THEN
    index_command := ' Rebuild';
    /* if a rebuild tablespace is also defined */
    IF tablespace_name <> '' THEN
      index_command := index_command || ' tablespace ' || tablespace_name;
    END IF;
  ELSE
    index_command := ' Coalesce';
  END IF;
  
  total_indexes := 0;
  updated_indexes := 0;
  /* get all indexes that belong to the specified user */
  FOR r IN get_index_cursor LOOP
    total_indexes := total_indexes + 1; 
    dbms_output.put_line( total_indexes || ' ' || 
                          r.index_name || ' ' || 
                          r.index_type);

    /* Coalescing works only on normal B-Tree indexes, 
     * while rebuilding works with everything.
     */    
    IF (rebuild_type = 'R') OR 
       (rebuild_type = 'C' AND instr( r.index_type, 'NORMAL') > 0) THEN
      
      full_index_name := user_name || '.' || r.index_name;
      sql_str := 'alter index ' || full_index_name || index_command;
      
      BEGIN
        /* attempt to modify the index */
        EXECUTE IMMEDIATE sql_str;
        updated_indexes := updated_indexes + 1;
      EXCEPTION
        WHEN OTHERS THEN
          /* display the command and the error that occured during index 
           * update 
           */
          dbms_output.put_line( sql_str);
          dbms_output.put_line( sqlcode || ' ' || sqlerrm);
          /* continue working */
          NULL;
      END;
    END IF;
  END LOOP;
    
  /* report results */
  dbms_output.put_line( 'Indexes examined :' || total_indexes);
  dbms_output.put_line( 'Indexes Updated  :' || updated_indexes);
END rebuild_user_indexes; 

Thanks to SQLDeveloper running the function can is as easy as copying and pasting the following in SQLPlus.

DECLARE
  USER_NAME VARCHAR2(200);
  REBUILD_TYPE VARCHAR2(200);
  TABLESPACE_NAME VARCHAR2(200);
BEGIN
  USER_NAME := NULL;       -- add your user here, for example 'HR'
  REBUILD_TYPE := NULL;    -- either 'C' or 'R'
  TABLESPACE_NAME := NULL; -- optional new tablespace name if desired

  REBUILD_USER_INDEXES(
    USER_NAME => USER_NAME,
    REBUILD_TYPE => REBUILD_TYPE,
    TABLESPACE_NAME => TABLESPACE_NAME
  );
END;

Note

The above code provides a very simplistic approach to index rebuilding as it rebuilds all indexes of a user. In order to determine the indexes in your database that would really benefit from rebuilding you need to run the script available from Oracle Metalink Note:122008.1.

Tuesday 11 December 2007

JDeveloper OC4J Changing the language that eror messages are displayed

I have had this problem many times. During development when something went wrong with my application, all I could get after the Internal server error would be JBO:XXXX error code with numerous question marks instead of the actual error message.

Some people advised me to change the preferred language/locale of my WEB browser and make it English, so as to force the display of error messages in this language, but that didn't work either.

Today I found a remedy. Open the project properties Go to Run/Debug click edit and add -Duser.language=en -Duser.country=US in the java command line options, like I show you in the following pciture

... and that does it.

Thursday 6 December 2007

ADF BC Canceling edits in the current record of a ViewObject

The number of things that one man misses becomes frightening especially when something is as crucial as a cancel edits button in a record display form.

Up until now I have been using a Rollback action binding to cancel any edits. Of course that was also casing current record information to be lost, so I had to rely on Steve Muench's Restore Current Row After Rollback from his Not Yet Documented ADF Sample Applications in order to keep my data in sync..

Revelation came as I as was browsing for the 9999th time the 10.1.3.1 version of the Tutorial for Forms/4GL Developers. The magic function that makes a view record refresh and forget any changes is as simple as that.

public void cancelEditsToCurrentServiceRequest() {
    getServiceRequestMain().getCurrentRow().refresh( 
                            Row.REFRESH_WITH_DB_FORGET_CHANGES);
}

Shame on me!

On second thought Muench's approach is more centric and may come in handy especially if a program has many view objects through which users enter data. Of course writing cancelEdits functions is clearer but has to be done for every view object that we require to use. The choice is still ours.