Weblogic’s config of trust keystore and identity keystore to call https service

 

When access a https service in the web browser, the service provider should provide some certificate on its site which proves it’s the authorized service provider. Sure, in production environment, the certificate should be certificated/provided by well-known certification providers like Verisign. The browser has build-in verify/accept abilities if the certification is from big players like Verisign.

So actually, during https access, the service provider sends something to the browser. If it’s a mutal https access, the browser’s machine really also sends something to the server.

First Scenario: With single Java program

use the following statement to claim the certificates related:

//tells the JVM that I trusted the certificates stored in the trust store.

//usually in the JRE/lib/security/cacerts

System.setProperty("javax.net.ssl.trustStore", trustStorePath); 

//tells the JVM that when I connect the https service

//I will using certificate like below. This contains my identify information.

System.setProperty("javax.net.ssl.keyStore", keyStorePath);

//keystore password, used to extract my identity.

System.setProperty("javax.net.ssl.keyStorePassword", keyStorePassword);

Second Scenario: Program within Weblogic server

First attention: the accesing url should be in a DNS format, don’t use the ip address.

Second Attention: the DNS of the url should be the same as in the keystore’s entry’s CN value. otherwise, hostname verification should be ignored or customized in the server’s SSL configuration console. 

Beside the code above, following configuration are needed.

In the server’s keystore configuration console.

Configure the trust keystore and identify keystore.

Sometimes, both trust keystore and identity keystore are provided with keystore file extension.

Trust keystore tells that the weblogic trusted the one who provides certificate like in the keystore. 

While the identity keystore tells others should trust the weblogic server(as a client) as the one presented in the identity keystore.

In the SSL console, the identity keystore’s alias should be configured. Also, sometimes, it works if you configure ‘using the server’s certificate’ checkbox, and configure the mutual SSL options.

The keystore files provided by the service providers sometimes should be merged with the existing ones, like JRE/lib/security/cacerts.  The command is as below:

First export the certificate stored in the keystore by using the keytool:

keytool –export –alias testserver –file testserver.crt –keystore testtrust.keystore

password of the testtrust.keystore is needed.

Then import the exported certificate to the cacerts file:

keytool –import –alias testserver –file testserver.crt –keystore cacerts

password of the cacerts is needed.

Attention: the testtrust.keystore is generated by the service provider maybe by using the keytool command.

Log4j, isDebugEnabled check necessary when logging a debug message?

 

logger.debug() method in Log4j already checks isDebutEnabled, maybe it’s not necessary to check isDebugEnabled() before every debug() method.

But consider when there is very complex operations in the debug()’s message construction. When calling the debug() method, the construction logic of the message are always executed first. It’ time and space consuming.

So, practically speaking, in situations when the debug message’s construction logic is not complex, for example, a simple constant string, the isDebugEnabled() check before the logging is not necessary.  On the other hand, in situations when the debug message’s construction logic is complex, for example, involving string buffer and loops, the isDebugEnabled() check before the logging is definitely necessary.

In practice, I would prefer to make a consistency to alway add the check before the real message logging.

SWIFT Message Type

SWIFT provides a network to allow financial and non-financial institution(e.g. corporates) to transfer financial transactions through a ‘financial message’.

Currently SWIFT’s network can support the following message standards.

SWIFT MT

   SWIFT messages, developed by SWIFT Standards, consist of five blocks of data including headers, message content, and a trailer. Message types are crucial to identifying content. All SWIFT messages include the literal ‘MT’(Message Type). This is followed by a 3-digit number that denotes the message type, category, and group.

  Example MT304.

  The first digit(3) represents the category. A category denotes messages that relate to particular financial instruments or services such as precious metals(6), Treasury(3), or Travelers Cheques(8). The category denoted by 3 is Treasury Markets.

  The second digit(0) represents a group of related parts in a transaction life cycle. The group indicated by 0 is a financial institution Transfer.

  The third digit(4) is the type that denotes the specific message. There are several hundred message types across the categories. The type represented by 4 is a notification.

  Overview of SWIFT MT Categories:

      MT0xx: System Messages

      MT1xx: Customer Payments and Cheques

      MT2xx: Financial Institution Transfers

      MT3xx: Treasury Markets

      MT4xx: Collection and Cash Letters

      MT5xx: Securities Markets

      MT6xx: Treasury Markets – Metals and Syndications

      MT7xx: Documentary Credits and Guarantees

     MT8xx: Travellers Cheques

     MT9xx: Cash Management and Customer Status.

ISO 15022 MT

 

ISO 20022 MX

BIC code and IBAN code

Technorati 标签: banking

ISO 9362, also known as SWIFT-BIC, BIC code, SWIFT ID or SWIFT code, is a standard format of Business Identifier Codes approved by the ISO. It is a unqiue identification code for both financial and non-financial institutions. These codes are used when transferring money between banks, particularly for international wire transfers, and also for the exchange of other messages between banks.

IBAN: The international bank account number(IBAN) is an international standard for identifying bank accounts across national borders with a minimal risk of propagating transaction errors. It was originally adopted by the European Committee for Banking Standards, and was letter adopted as an international standard under ISO. The official IBAN registrar under ISO is SWIFT.

 

Please refer wike page:

http://en.wikipedia.org/wiki/ISO_9362

http://en.wikipedia.org/wiki/International_Bank_Account_Number

for detailed information.

Add Web Projects from Eclipse to Tomcat

I have a Java projects workspace, with several pure Java projects and several Web J2EE projects.

When import the existing projects into the workspace.  Eclipse can not identify it as a web project, so it can not be added into the tomcat runtime.

After some googling, the solution is as below:
  1. configure the web project's project facet in the properties menu.
     A. First choose 'Dynamic Web Module', please pay attention to the version of the Dynamic Web Module at the end of the check box line.
          The error message tells us that 'Dynamic Web Module 3.0 need Java 6.
     B. Check Java too and change the version of Java to 1.6.
2. Apply and OK.

Install erlang in CentOS 6

 In order to learn more about concurrent programming language, I decide to learn erlang programming in the Linux platform.

Here are the install procedures:
   1. Download the source for linux from :
  2.  Follow the INSTALL.md file in the source package, install available packages.
  3. for the wxWidgets, yum install wxGTK works.  even without wxWidgets, the erlang source can be build and installed.
  4. for openssl, both openSSL and openssl-devel should be installed
  5. some of the devel package should be installed.
  6. build the erlang by reference the INSTALL.md file. 

RPA---Repayment pending Appropriation

In case the customer pays a single or multiple installment amounts before it is due, then the system applies the amount as per the appropriation sequence and any excess over and above that is stored as RPA - Repayment pending appropriation.

Which means that the customer does not get any interest benefit for the same. On the next due date the system uses the RPA amount for satisfying the arrears.

Any shortfall is then recovered from the CASA account (if a CASA draw down is set up), If there are no outstanding arrears then the entire payment is stored in the RPA. Thus the customer only gets the advantage of his arrears getting knocked off in time avoiding any penalty. RPA does not also result in the term or the installment getting reduced as the outstanding does not come down nor does the interest base. In Advance payment the system reduces the interest base on the same day (as defined in the schedule Type) thereby bringing down the interest burden as in case of partial payoff and also stores the advance amount as RPA (principal and interest RPA). Thus like in case of RPA the system recovers the principle and interest arrears on the due date. Thus the salary draw down set on a date before the due date of payment gets the advantage of advance payment as the system recovers the arrears of the next cycle.

RPA maybe widely used in Advanced repayment, which is not widely used.

Oracle Tips 20110614- about data type conversion

This post explains how to use the Explain plan for Oracle's SQL execution to debug sqls.
First create two tables:

create table char_tables (party_id varchar2(30), party_name varchar2(30))

create table number_tables (party_id number, party_name varchar2(30));

insert into number_tables select rownum, table_name from user_tables

insert into char_tables select rownum, table_name from user_tables

insert into char_tables select table_name, table_name|| rownum from user_tables

When issue the following SQL, there is an invalid number exception:
   select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;

it maybe the data type conversion problem, but let's confirm it with explain plan.

explain plan
 for
select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;
 commit;
 select * from table(dbms_xplan.display);
  The plan is as below:
  
  We can see from the plan that the sql engine try to convert the VARCHAR2 column into the NUMBER column.

  Let's try another sql:
  explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

the plan is as below:
  
  The SQL engine try to convert the column into the NUMBER type.

 EXPLAIN PLAN
 FOR 
DELETE FROM char_tables WHERE party_id not in (1,2,3);
  COMMIT;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  The plan is as below:
 
  For above, the NOT clause is translated into the <> operator.  This SQL doesn't work because some values of the party_id column in the char_tables CAN NOT BE CONVERTED INTO NUMBER.

Another SQL:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

  The plan is as below:
 
  
   If the variable's type matches the column's type, no conversion is needed, otherwise, a conversion is applied.
Try to convert the VARCHAR2 into NUMBER.

  Let's considering the scenarios when indexes are used:
   create index char_index on char_tables(party_id) ;
   create index number_index on number_tables(party_id);

The SQL is as below:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

 

  it's a full-table access query. The index is not used because the SQL was translated into the TO_NUMBER()...
 A function index ON TO_NUMBER(party_id) may works.

 Let's try the other SQL:
 
  It's not a full table access, but an index range access.

  So, in Oracle, since data conversion will be automatically executed, the developer has to pay attention to those default tips.

 

 
 

Arrears Appropriation Sequence

 

The sequence which determines the order in which arrears are to be paid in a loan account.

At the product level, the bank can set the rules for treatment of payment when the amount paid is less than the arrears due. A loan installment may only be partly paid by the customer in which case the system will require to know the o rder in which the existing arrears need to be set off. This order is specified in the arrears appropriation sequence. A separate sequence can be specified for regular loans and for non-performing loans.

The sequence makes use of the following abbreviations:

• I – Interest Arrears
• F – Fee Arrears
• S – Legal Fees Arrears
• O – Outgoing Arrears
• D – Suspended Fees
• E – Suspended legal fees
• G – Suspended Premium
• M – Suspended outgoings
• P – Premium Arrears
• N – Non Accrual Interest Arrears (Suspended Interest)
• U – Post Maturity interest Arrears
• T – Post Maturity – Non Accrual Interest arrears (Suspended PMI)
• C – Principal Arrears

It may also be possible that within a particular type of arrear, there maybe more than one installment due. In t his case the longest overdue first flag will specify in which order each each arrear will need to be set off within a date. Ticking this flag will indicate that the system will pick up the earliest date for which arrears are due and then set off arrears based on the appropriate sequence.

Example:

Appropriation sequence specified as – I F A C S O D G E M P L N U T

(Interest, Fees, Arrears on interest(penalty interest), Capital etc.)

Total arrears due – INR 15,000

Total amount paid on 20th May 2004 – INR 10000

The arrears on account and their appropriation as follows for longest overdue first flag not ticked:

Order the arrears by the sequence specified at the product level.

 

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	5	3500	10000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	4	1000	6500
30-Apr-2004	C - Capital	4500	None	None	None (since there is no money for this arrear)
30-Apr-2004	I - Interest	3000	2	3000	5000
15-May-2004	F - Fees	500	3	500	500

 

The arrears on account and their appropriation as follows if longest overdue first flag is ticked:

Order the arrears based on the due date of the arrear as well as the sequence specified at the product level.

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	2	4000	6000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	5	500	10000
30-Apr-2004	C - Capital	4500	None	None	None (there is no money for this arrear)
30-Apr-2004	I - Interest	3000	3	3000	9000
15-May-2004	F - Fees	500	4	500	9500

Algorithm of Fibonacci numbers

 long fibonacci(int n){
  assert(n>=0);
  if(n == 0) return 1;
  if(n == 1) return 1;
  return fibonacci(n-1) + fibonacci(n-2);
}

long fibonacci(int n){
  if(n ==0 ) return 1;
  long[] fibs = new long[n+1];
  fibs[0] = 1;
  fibs[0] = 2;
  for(int i=2;i<=n;i++){
    fibs[i] = fibs[i-1] + fibs[i-2];
  }
  return fibs[n];
}

long fibonacci(int n){
  if(n==0) || (n==1) return 1;
  int sum = 0;
  int number0 = 1;
  int number1 = 1;
  for(int i=2;i<=n;i++){
    sum = number0 + number1;
    number0 = number1;
    number1 = sum;
  }
  return sum;
}

CASA: Current And Saving Account

RD in FCR is implemented as a variant of CASA.

For CASA. there are four things to do when configure a CASA account and make a transaction.

1. CASA definition

CASA definitions include the following sides: 

    A. Product Definitions

    B. Bank Parameters

    C. RD Product Rates

    D. RD Grace Days

2. Account Opening Process

     The Account opening process will use Branch code and Customer Information from the CIF module to open an account for the customer.

     A. Open Account

     B. A/C operating Instructions

     C. A/C Reporting Code/User Define Fields

     D. Account Memo

3.  Online Transactions

    Online transaction includes the following categories:

      A. Credit

           Cash/Single Cheque/Data Entry for Consolidated/Postdated(Deposit/ Cancel) / Purchase Cheque/ Liquidating BC/Liquidating DD/ Liquidating TC/ Purchase TC/ Redemption by Transfer/ Transferring Funds/ Disbursement by Transfer/ Making Loan Rescission/Loans Refund/Miscellaneous Credit/ IDC/ODC

      B. Debit

           Cash/Single Cheque/ Sale TC/Issue BC/Sale DD/Sale TT/Transferring Funds/Payin by Transfer/Paying Installment by Transfer/Making Partial Pay-off of Loan/Setting a Loan by Fund Transfer/Loan Advance Payment by Transfer/Miscellaneous Debit/Common Billing Transactions/Bill Payment

      C. Inquiry

           Account Balance/Account Statement/Inward Check status/ Account number checkdigit/Account status Audit Trail/SWIFT Statement Details

      D. Passbook:

           Issue New Passbook/Inquiring on passbook/passbook balance check digit.

      E. Fund Management / Account Instructions

           Hold Funds/Delete Earmark/Standing Instructions/Sweep-out/Sweep-in/Group Accounts/Inward Direct Debit/Account cheque purchase limit/Sweep out execution to CASA/TD/ SC for Sweep out/ Sweep-in processing –TD Redemption/Sweep-in Processing –Unmark lien/Standing Instruction Execution/Hold Funds Expiry

      F. Statement

            Special Frequency/Account Address Maintenance/Statement generation

      G. Cheque Book

             Check Box Request/Check Book Maintenance/Maintaining Stop cheque instruction

      H. Collateral & Limit

            Limit Collateral Inquiry/OD Limit Master Maintenance/Account Collateral/OD Limit Expiry Processing/Identifying Limits Expiring next month

      I. Account Closure

             Account close/Close out withdrawal by cash/close-out withdrawal by banker’s check/Close out withdrawal by GL

      J. Recurring Deposit (RD)

          RD Schedual Inquiry

      K. Other

          Maintaining the Account Master/Account Master Attribute/Account Status/SC Account NSF Preference/Interest Adjustment/Account SC waiver/Interest Freeze/JPA account cross reference

4. Batch Transactions

       A. NPA (Non-performing Asset, popularly known as Bad Loan).

               Reminder Processing/NPA classification processing/NPA Reserve Creation/NPA Appropriation

       B,. Interest and charges

               Interest Accrual/Reset ADB Statistics/ Interest Capitalization/ SC capitalization/ SC processing for transactions/SC/Interest – Internal Transfer processing.

      C. Others

                Transaction posting/Other House Keeping/RD Maturity Processing/GL handoff/Dormancy Processing/Combine Stream/Unclaimed Processing/TDS Processing/Dormancy Automatic re-activation.

Humble thoughts on data structure

 

Technorati Tags: data structure

Data structure actually contains two words. The first one is data, which is the abstract representation of reality in the computer programs.

For the other word, structure, which means the way to organize one or more than more things. The main purpose to organize something is to make the access to them available and easier.

Hereafter, something are referred as a set of data. The set here is not the same concept of set in pure mathematics, nor the class set  in java collection framework. Here element in the set can be duplicated or not, depends on the specific scenario.

Sometimes, the set of data are organized as an array because maybe random access and indexed access to the elements in the set are necessary and important. Sometimes, the set of data are organized as a linked list because maybe it’s very difficult to allocate a large space at the beginning (a necessity when using array ) or the above features are not important. 

Generally, array and linked list are two main low-level data structures that widely used in the compute program. Know how to manipulate elements and design algorithms on these two structures are very important to programmers.

High level data structures like List, Map, Set , Tree, graph are all represented by the two elementary structures.

In order to master the manipulation of the two, some practices are necessary. Let’s take some examples of practices to learn arrays and linked lists in programs.

Equilibrium array

Technorati Tags: Algorithm,Equibilbrium array,data structure

Equilibrium index of a sequence is an index such that the sum of elements at lower indexes is equal to the sum of elements at higher indexes. For example, in a sequence A:
A[0]=-7 A[1]=1 A[2]=5 A[3]=2 A[4]=-4 A[5]=3 A[6]=0

3 is an equilibrium index, because:
A[0]+A[1]+A[2]=A[4]+A[5]+A[6]

6 is also an equilibrium index, because:
A[0]+A[1]+A[2]+A[3]+A[4]+A[5]=0

(sum of zero elements is zero) 7 is not an equilibrium index, because it is not a valid index of sequence A.
Assume the sum of zero elements is equal zero. Write a function

int equi(int[] A);

that given a sequence, returns its equilibrium index (any) or -1 if no equilibrium indexes exist. Assume that the sequence may be very long.

 

Solution:

1. First get the sum of all the numbers in the array first. sum

2. adding the array from the left one by one. tempSum

3. if the sum – tempSum – A[i] == tempSum, then i is an equibibrium index.

int equip(int[] A){
  int sum = sum(A);
  int tempSum = 0;
  for(int i=0;i<A.length;i++){
   if(sum - tempSum - A[i] == tempSum){
    return i;
   }
   tempSum += A[i];
   }
   return -1;
}

long sum(int [] A){
int sum = 0;
for(int i=0;i<A.length;i++){
   sum += A[i];
  }
}

The best solution, which are very often available should be the O(N) complex one. Always try to find the solution with O(N) time complexity or less.

Given a positive integer number, how to find if it’s anagram.

Technorati Tags: Algorithm,Anagram of integer

public class Symmetric {

    // Solution A

    int[] digits = new int[] {};

    boolean symmetrical(int i) {

        int n = i;

        int index = 0;

        while (n != 0) {

            digits[index++] = n % 10;

            n = n / 10;

        }

        return symmetrical(digits, 0, digits.length - 1);

    }

    //recursive way to just if the array is symmetrical

    boolean symmetrical(int[] digits, int begin, int end) {

        if (begin >= end)

            return true;

        return (digits[begin] == digits[end])

                && symmetrical(digits, begin + 1, end - 1);

    }

    //traditional way to judge if the array is symmetrical

    boolean symmetrical(int[] digits, int begin,int end){

      int length = (end - begin)/2;

      for(int i=0;i< length;i++){

       if(digits[begin+i] != digits[end-i]){ return false;}

      }

      return true;

    }

    //end of Solution A

    // Solution B

    //just compute the new number from the end to first.  This method is recommended since there is no need to assign an new int[] array.

    boolean symmetrical_B(int i) {

        int n = i;

        int m = 0;

        int t = 0;

        while (n != 0) {

            t = n % 10;

            m = m * 10 + t;

            n = n / 10;

        }

        return m == i;

    }

}

When using the first algorithm, the algorithm tries to convert an integer into an array. That’s really a space consuming method. For every single problem, please consider just using the existing space and data structure, don’t try to convert the existing data structure into another structure.

Rownum in Oracle for filtering

 It's a traditional question in Oracle:

  How to select the 11th record to the 20 th record in a database table.
A. The simplest form:
  How to select the 1 to 10 records.
  Just the following sql:
  SELECT * FROM ta WHERE rownum < 11
  will select the first 10 records if there are 10 or more records in the database.
  SELECT 1 FROM ta WHERE rownum < 11
  will select 10 ones if there are 10 or more records in the database, if there is no record in the database, there will be no records returned by the SQL.

B. How to select the 11th to 20 th records.
  Please don't use :
  SELECT * FROM ta WHERE rownum BETWEEN 11 AND 21
    between means 11<=x<=21
  The statement will return no record. it's because the rownum of the statement begins with 0 and is a virtual column, it will add 1 unit a new row is caught. So, from beginning, the rownum will never meet the condition.
  Let's use another way:
 SELECT * FROM ( 
( SELECT T.*, ROWNUM ROWCOUNTER FROM ta T WHERE rownum <21 )
  ) WHERE ROWCOUNTER > 10

  Once, I thought the following statement may works:
   ( SELECT t.ctr_cont_no FROM ta WHERE rownum < 21 )
   MINUS
   (SELECT t.ctr_cont_no FROM ta WHERE rownum < 11)
  but it turns out the MINUS operator is an set operator, so the result may have less than 10 results. So use the following instead:
  (SELECT t.rowid, t.ctr_cont_no FROM ta WHERE ROWNUM <21 )
   MINUS
  (SELECT t.rowid,t.ctr_cont_no FROM ta WHERE ROWNUM < 11)
 it works but with an additional column.
  For
  (SELECT T.* FROM ta WHERE rownum < 21)
  MINUS
  (SELECT T.* FROM ta WHWER rownum < 10)
 I don't know the exact, then have the following test.
  create a single column table:
   create table sin_column_test (COLUMN1 NUMBER NOT NULL);
   insert into sin_column_test select 10 from all_objects where rownum <=100;

  issue the command:
    (select * from sin_column_test where rownum< 21 )
   minus
  (select * from sin_column_test where rownum < 11)

 There is no record returned. So T.* only means the columns defined in the DML SQL, not any virtual columns are included.

Automating Oracle 10g Database Startup and shutdown on Linux

  Steps to configure the automatically startup and shutdown on Linux

   Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
   iflex:/u01/app/oracle/product/10.1.0/db_1:Y

   Next, edit the dbstart batch
   vi $ORACLE_HOME/bin/dbstart
   edit the line
   ORACLE_HOME_LISTENER=$ORACLE_HOME
  
   Create a file called /etc/init.d/dbora as the root user, containing the following:
   #!/bin/sh
   # chkconfig: 345 99 10
   # description: Oracle auto start-stop script
  ORA_HOME=/u01/app/oracle/product/10.1.0/db_1
  ORA_OWNER=oracle

  if [ ! -f $ORA_HOME/bin/dbstart ]
  then
    echo "Oracle Startup: cannot start"
    exit
  fi

  case "$1" in
    'start')
    #start the oracle database
    #The following command assumes that the oracle
    #login will not prompt the user for any value
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
    touch /var/lock/subsys/dbora
    ;;
    'stop')
    #stop the oracle database
    #The following command assumes that the oracle
    #login will not prompt the user for any value
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
    rm -f /var/lock/subsys/dbora
    ;;
 esac
 
  Please pay attention two the second and the third comment line, it should begin with chkconfig and description to make it follow the chkconfig standard. Please refer the 'man chkconfig' to see the full version of this standard.
 
  Login as root, issue the following command:
  chmod 750 /etc/init.d/dbora
  Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:
  chkconfig --add dbora
  The relevant instances should now startup/shutdown automatically at the system startup and shutdown.

Tablespaces, Datafiles, and Control files

Tablespace---The primary logical database structures of any oracle database. datafiles correspond to each tablespace.

Oracle stores data logically in tablespace and physically in datafiles associated with the corresponding tablespace.  Objects store in tablespaces may span several datafiles but a datafile is a physical structure associated with only one tablespace.

    Database, tablespaces and datafiles:

         An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data;

         Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

         A database’s data is collectively stored in the datafiles that constitute each tablespace of the database.

Oracle-Managed Files

      Oracle-managed files eliminate the need for the DBA to directly manage the operating system files comprising an Oracle database.  The DBA can then specify operations in terms of database objects rather than filenames.  Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

          A. Tablespace

          B. Redo log files

          C. Control files

      The size of a tablespace is the size of the datafiles that consitute the tablespace. The size of the a database is the collective size of the tablespaces that consitute the database. The DBA can add a datafile to a tablespace; add a new tablespace; increase the size of a datafile. Tablespaces are divided into logical units of storage called segments, which are further divided into …

Bigfile tablespace

      Bigfile tablespace allows Oracle database to contain tablespaces made up of single large files rather than numerous smaller ones.

SYSTEM tablespace

     Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

     Create a locally managed SYSTEM tablespace or migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.  With a locally managed SYSTEM tablespace, dictionary managed tablespace can not be created. If a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

      The SYSTEM tablespace always contains the data dictionary tables for the entire database.  The data dictionary tables are stored in datafile 1.

      All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace.

SYSAUX tablespace

     The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. The SYSAUX tablespace is always created during database creation or database upgrade. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.  During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

Undo tablespace

      Undo tablespaces are special tablespaces used solely for storing undo information. Any other segment types (e.g tables or indexes) are not allowed to be created in the undo tablespace. 

      Each undo tablespace is composed of a set of undo files and is locally managed. Undo blocks are also grouped in extents and the status of each extent is represented in the bitmap.

Default Temporary Tablespace

      When the SYSTEM tablespace is locally managed, the DBA must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.  If SYSTEM is dictionary managed and if The DBA do not define default temporary tablespace when creating the database, the SYSTEM is still used for default temporary storage.

 

      Tablespace allocates space in extents. Tablespace can use two different methods to keep track of their free and used space:

          A. Locally managed tablespace: extent management by the tablespace

          B. Dictionary managed tablespace: extent management by the data dictionary.

       Locally Managed Tablespace: a tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. Oracle changes the bitmap values to show the new status of the blocks when an extent is allocated for freed for reuse.  These changes do not generate rollback information because they do not update tables in the data dictionary.

      Locally managed tablespaces’ advantages:

           1. automatically tracks adjacent free space, eliminating the need to coalesce free extents.

           2. avoids recursive space management operations. Such recursive operations can occur in dictionary managed tablespace if consuming or releasing space in an extent results in another operation that consumes or release space in a data dictionary table or rollback segment.

     The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

     Segment Space Management in Locally Management Tablespace can be both AUTO and MANUAL.

 

Dictionary Managed Tablespace

     For a tablespace that uses the data dictionary to manage its extents, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle Also stores rollback information about each update of the dictionary tables.

 

Oracle supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. The DBA specifies the standard block size by setting the initialization parameter DB_BLOCK_SIZE, values from 2K to 32K.

A DBA can bring any tablespace other than the SYSTEM tablespace online(accessible) or offline(not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle.

The primary purpose of read-only tablespace is to eliminate the need to perform backup and recovery of large, static portions of a database.  Oracle never updates the fiels of a read-only tablespace, and therefore the files can be reside on read-only media such as CD-ROMs or WORM drives.

Temporary tablespace for sort operations

    The DBA can manage space for sort operations more efficiently by designating one or more temporary tablespace exclusively for sorts.  Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.  A single SQL operation can use more than one temporary tablespace for sorting. All operations that use sorts, including joins, index builds, ordering, computing aggregates and collecting optimizer statistics benefit from temporary tablespace.

    One or more temporary tablespace can be used only for sort segments. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace.  Temporary tablespace provides performance improvements when you have multiple sorts that are too large to fit into memory.

Transport of Tablespace between database

     A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even cross different platforms.

     A tablespace repository is a collection of tablespace sets.  Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases.

    A tablespace in an Oracle database consists of one or more physical datafiles.  a datafile can be associated with only one tablespace and only one database.

 

Datafiles

     When a datafile is first created, the allocated disk space is formatted but does not contain any user data. Oracle reserves the space to hold the data for future segments of the associated tablespace—it’s used exclusively by Oracle.

     Data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tabelspace. Schema object does not correspond to a specific datafile; a datafile is a repository for the data of any schema object within a specific tablespace.  The DBA can alter the size of a datafile after its creation or can specify that a datafile should dynamically grow as schema objects in the tablespace grow.

Logical storage structures in Oracle database

Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.

At the finest level of granularity, Oracle stores data in data blocks( also called logical blocks, Oracle blocks or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space.  Each table’s data is stored in its own data segment, which each index’s data is stored in its own index segment.  If the table or index is partitioned, each partition is stored in its own segment. When the existing extents of a segment are full, Oracle allocates another extent for that segment.  Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.  A segment and all its extents are stored in one table-space. Within a tablespace, a segment can include extends from more than one file; The segment can span datafiles. Each extent can contain data from only one datafile.

If you allocate an extent to a specific instance(Manually storage management), the blocks are immediately allocated to the free list. However, if the extent is not allowed to a specific instance(Automatically storage management), then the blocks themselves are allocated only when the high water mark moves.  The high water mark is the boundary between used and unused space in a segment. 

Data blocks

Storage space in the datafiles of a database  is managed in units called data blocks.  At the OS level, all data is stored in bytes. Oracle block size is different from system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Data block format is similar regardless of whether the data block contains table, index, or clustered data.

   A. common and variable Header: the header contains general block information, such as the block address and the type of segment ( for example, data or index).

   B. Table directory: This portion of the data block contains information about the table having rows in this block.

   C. Row directory: contains information about the actual rows in the block(including addresses for each row piece in the row data area). The space for a row is not reclaimed when the row is deleted. Oracle reuses this space only when new rows are inserted in the block.

    E. Overhead: The data block header, table directory, and row directory are referred to collectively as overhead.

    F. Row Data: contains table or index data, rows can span blocks.

    G. Free space: for insertion of new rows and for updates to rows that requir additional space. In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE and SELECT … FOR UPDATE statement accessing one or more rows in the block. The transaction entry tells that the block is now updating by this transaction, so others have to wait?????

Free Space Management

    Free space can be managed automatically or manually.

    Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.

Availability and  Optimization of Free Space in a Data Block

Two types of statement can increase the free space of one or more data blocks: DELETE statement, and UPDATE statements that update existing values to smaller values.  The released space from these types of statements is available for subsequent INSERT statements under the following conditions.

     1. If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available

     2. If the INSERT statement is in a separate transaction from the statement that frees space, then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

   Based in pre-conditions, Oracle will compress free spaces in a block.

Row Chaining and Migrating

   In two circumstances, the data for a row in a table maybe too large to fit into a single data block.

     1. The rows is too large to fit into one data block when it is first inserted. Then Oracle stores the data for the row in a chain of data blocks reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data types LONG or LONG RAW. Row chaining in these cases is unavoidable.

      2. A row that originally fit into one data block is updatable so that the overall row length increases, and the block’s free space is already completely filled. Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

 

PCTFREE, PCTUSED and Row Chaining

For manually managed tablespaces, two space management parameters: PCTFREE and PCTUSED enable you to control the use of the free space of inserts and updatesd to the rows in all the data blocks of a particular segment.

   PCTFREE: sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. If set PCTFREE=20, this states 20% of each data block in this table’s data segment be kept free and available for possible updates to the existing rows already within each block.  New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area until the row data and overhead total 80% of the total block size.

   PCTUSED: set the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.  After a data block is filled to the limit determined by PCTFREE, oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block.

   Which means with PCTFREE and PCTUSED parameters, the free space is sometimes only available to update commands.

   For each data and index segment, oracle maintains one or more free lists---lists of data blocks that have been allocated for that segment’s extents and have free space greater than PCTFREE. This blocks are available for inserts.  If the free space in that block is not large enough and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

  After issue a DELETE or UPDATE statement, Oracle processes the statement and checkes to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction.

 

Overview of Extents

An Extent is made up a number of contiguous data blocks. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.  

When create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks. When the initial extent become full and more space is required, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

The header block of each segment contains a directory of the extents in that segment.

Storage parameters expressed in terms of extents define every segment.

Data dictionary managed table-space and locally managed table-space by bitmap.

The extents of a segment do not return to the table-space until you drop the schema object whose data is stored in the segment with some exceptions:

When extents are freed, Oracle modifies the bitmap in the data-file(for locally managed table-space) or updates the data dictionary ( for dictionary managed table-space) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

Extents in Non-clustered tables

As long as a non-clustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table.  Even if you delete all rows of a table, oracle does not reclaim the data blocks for use by other objects in the table-space. After the dropping of a non-clustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents and makes them available for other schema objects in the same tablespace.

In dictionary managed table-space,when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. this is called coalescing extents.

For clustered tables, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are de-allocated. You can also truncate clusters(except for hash clusters) to free extents. 

All extents allocated to an index segment remain allocated as long as the index exists.

Extents in Temporary Segments

When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated table-space.  Multiple sorts, can use sort segments in temporary tablespaces designated exclusively for sorts. The sort segments are allocated only once for the instance and they are not returned after the sort, but remain available for other multiple sorts.  A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tables.  So, there are two categories of temporary table-space, the one for temporary table and the one for multiple sorts.

Extents in Rollback Segments

Oracle periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is , it has too many extents), then Oracle automatically de-allocates one or more extents from the roll-back segment.

 

Overview of Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a table-space.

Data Segments

A single data segment in an Oracle database holds all of the data for one of the following:

    1. A table that is not partitioned or clustered

    2. A partition of a partitioned table

    3. A cluster of tables

The specified storage parameters when creating the table/cluster affect the efficiency of data retrieval and storage for the data segment associated with the object.

Index Segment

Every non-partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.  Also the storage parameters directly affects the efficiency of data retrieval and storage.

Temporary Segments

      A. When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment.  Typically, Oracle requires a temporary segments as a database area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes. Operations that requires temporary segments include: CREATE INDEX/SELECT …ORDER/ SELECT DISTINCT/SELECT ..GROUP/SELECT …UNION/ SELECT …INTERSECT/SELECT …MINUS

      B. Segments in Temporary tables and their indexes. Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

      Oracle allocates temporary segments differently for queries and temporary tables.

      A. Allocation of Temporary segments for queries: If no temporary table-space is defined for the user, then the default temporary table-space is the SYSTEM table-space.  Oracle drops temporary segments when the statement completes.

      B. Oracle allocates segments for a temporary table when the first INSERT into that table is issued. The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.  Segments for a temporary table are allocated in a temporary table-space for the user who created the temporary table. Oracle drops segments for a transaction/session-specific temporary table at the end of the transaction/session-specific temporary table at the end of the transaction/session.  If other transactions/sessions share the use of that temporary table, the segments containing their data remain in the table.  Means a temporary segment for every transaction/session.

Automatic Undo Management

    Oracle maintains information to nullify changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Oracle use undo to do the following:

    A. Rollback an active transaction

    B. Recover a terminated transaction

    C. Provide read consistency

    D. Recovery from logical corruptions

     Oracle recommends you use undo tablespaces to manage undo rather rollback segments.

     Undo mode provides a more flexible way to migrate from manual undo ma

Data Integrity and Triggers

An integrity constraint is a declarative way to define a business rule for a column of a table.  If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint can not be enforced.  After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.  Integrity constraint are defined with a table and are stored as part of the table’s definition in the data dictionary.

Supported Integrity constraint:

1. NOT NULL

2. UNIQUE KEY

3. CHECK.

4. PRIMARY KEY

5. FOREIGN KEY

Key is used in the definitions of several types of integrity constraints. Different types of keys include:

   1. Primary Key

   2. Unique Key

   3. Foreign Key

    4. Referenced Key

Triggers

Triggers are procedures written in PL/SQL, java or C that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.   

 

SQL Statement categories:

    Data definition Language (DDL) Statements

    Data Manipulation Language (DML) Statements

    Transaction Control Statements

    Session Control Statements

     System control Statements

      Embedded SQL statements

Structures used for recovery

Oracle uses several structures to provide complete recovery from an instance or disk failure: the redo log, undo records, a control file, and database backups.

The Redo Log: is a set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can consist of the online redo log and the archived redo log.  The online redo log is a set of two or more online redo log files that record all changes made to the database, including uncommitted and committed changes.  The background process LGWR writes the redo entries sequentially to an online redo log file. LGWR writes redo entries continually, and it also writes a commit record every time a user process commits a transaction.

Filled online redo files can be manually or automatically archived before being reused, creating archived redo logs. ARCHIVELOG and NOARCHIVELOG options.

Undo Records: undo records are stored in undo tablespaces.  Oracle uses the undo data for a variety of purposes, including accessing before-images of blocks changed in uncommitted transactions. During database recovery, Oracle applies all changes recorded in the redo log and then uses undo information to roll back any uncommitted transactions.

Control files: The control files include information about the file structure of the database and the current log sequence number being written by LGWR. During normal recovery procedures, the information in a control file guides the automatic progression of the recovery operation.