Oracle Data Types

Data types in oracle can be divided in two general groups. First are the user defined data types. Oracle database allows you to create your own data types and use them if you find it necessary. Furthermore Oracle is an object relational database management system; that allows you to create objects and store them in the database. Any thing that has data and some functions in it is known as object.

The second group of data types is known as built in data types. These are also known as Oracle data types. These data types can be divided into three more groups. First is scalar data type. Scalar data types are those types that can store only one value in their variables. This group includes many data types that are used to store different types of data. For example char and varchar2 are used to store the characters or string values. However there is a difference and that is: suppose you have declared a variable as char (10) and have stored only one character in it. The char data type will reserve space for 10 characters despite the fact that you have stored only one. Other 9 characters space will be wasted. However if you have declared the same variable with varchar2 (10), the space will not be reserved for the number of characters that you have specified (10) rather only for the characters that you actually stored i.e. 1. The number data type is used to store numbers. It stores whole numbers as well as numbers with decimal points. Date is used for storing dates. Well you can also store dates in varchar2 but that will not allow you to use Oracle’s built in functions for dates. Timestamp is used to store date with time. BLOB stands for Binary Large Objects and it is used to store large objects e.g. images, audio data, video data etc. CLOB stands for Character Large Objects and it is used for storing large objects which are in the form of text e.g. a huge document.

The second group in the built in data types is collection. Collection data types are those data types that can store more then one values in their variables. Suppose if you want to store an array in any field of a table you can use the varray data type. And if you want to store a two dimensional array or table in any field of the table then you can use the table data type. This is used when you want to store a table inside another table.

The third and last group in the built in data types of oracle is relationship. There is only one data type in this group i.e. ref. If you want to store a value inside a field which is not in the oracle database; and resides in some other form of application then you can use the ref data type to give the address of that value. In very simple words this data type works solely as a pointer.

Rollback Segments

The primary purpose of rollback segments is to hold old values. Means that when you update a value the updated value is stored in the table and the old value is stored in the rollback segment. So whenever that old value is needed again it is restored from rollback segment. There are three main purposes of storing old values and thus of rollback segments:

1. Transaction Rollback: Suppose you are working in a table. And you updated the name column value of a particular row from Scott to James. And then suddenly you realized that change you made was not right so you give the command of rollback. The original value i.e. Scott will be restored again. How did database come to know that the old value was Scott? The answer is through rollback segment because it’s the rollback segment that stores the old values.
2. Transaction Recovery: The second purpose is transaction recovery. Suppose you have made a change and have not committed it. But another user working on the same database gave the command of commit. LGWR will write because the commit command is given. And when LGWR writes it writes all the changes from redo buffer cache to on-line redo logs. But remember along other things redo logs also store the SCN of transaction and also whether it is committed by its user or not. Some how your DBWR also fires and writes the uncommitted change to data files. At this point in time your power supply goes off. Next time when instance will startup the SMON process will see that which transactions are stored correctly and also which are committed by their own users and which are not. If any transaction is not committed by its own user just like one above it will restore its old value; and it will take its old value from rollback segment.
3. Read Consistency: Read consistency is that something that every good database must posses. It is a sort of yard stick through which the maturity of a RDBMS is measured. In oracle this feature is implemented by making use of rollback segments. Suppose a user has given the query to fetch the first five rows from a table. The query has only fetched first three rows that another query came and updated the last two rows. Now the first query should get the old values because it was given first then the query who updated the rows. Surely it will get the old values but not from the table but from rollback segment. However any other query given after the update command will get the new updated values. In this example two features of oracle came in front of us. They are consistency and concurrency. By the way consistency must always be given the higher priority.

Rollback segments are maintained in a circular fashion. Usually more then one transaction can write in one rollback segment. Up till 8i it was not possible for multiple transactions to write in one extent. But in later versions of 8i and in above all versions it is possible. But still it is not possible for multiple transactions to write in one data block. Once one extent is full it is switched to the other. This is known as Wrap. When all the extents are full then instead of over writing the first one a new extent is created at run time. It is called Extend.

Rollback segments are automatically freed up when the data in them is no longer required. Suppose your rollback segment was full up to 100M and then automatically freed up to 4M. In this case what will happen of extra space? Is it wasted? No it is not wasted instead oracle shrinks the rollback segment up to a value stored in the optimal parameter. Normally the value of optimal parameter is 4M.

With these considerations we have a legacy problem. Consider again the example given in the Read Consistency. First command wants to fetch the first five rows of a table; it has only fetched the first three that the second command updated the last two rows. At this point in time suppose oracle has shrunk the rollback segment; where the first command will get the old values? The answer is from no where. Till 8i it was a huge problem and it is famous too. It is known as “snapshot too old” and the oracle message number for this error is ora-15500. In 9i and above versions it is automatically controlled by oracle itself.

To get information about the rollback segments you can use v$rollname, v$rollstat data dictionary views. To get information about your current active transactions you can use the view named as v$transaction.

Temporary Segments

As its name suggest that this segment is used to hold temporary data. The prime example of this is sorting. So you can say that temporary segments are used when the database does any kind of sorting. When a database does sorting? There are a few events when a database needs to sort values.

1. Order by: Every time you use the command which includes the order by clause the database does sorting and intimately uses temporary segment. The example of an order by command is:

SQL>select * from emp order by ename;

This command will order the results by Employee Name in ascending order. If you want it to order it descending then you will give the command:

SQL>select * from emp order by ename desc;

2. Group by: When you use the clause of group by the database also performs the sorting. The group by clause is used to take aggregate values on a column. The example of group by clause is:

SQL>select dept no, count(*) from emp group by dept no;

3. Distinct: Distinct keyword is used to avoid the multiple show of a single value in a result. If you use command which includes this keyword the database does the sorting. The example of this is:

SQL>select distinct dept no from emp;

4. Creation of an Index: Every time you create an index the database does the sorting. Indexes are created for easy retrieval of data.
5. Union: Union operator is used when you need to merge the outputs of two commands. The use of union operator also involves the sorting process. The example of this is:

SQL>select * from emp union select * from employee;

These are the five situations when the database does the process of sorting. And when it does it stores some values for some time and it stores those values in the temporary segment.

Temporary segments are created inside the temporary tablespace. If you check the V$tablespace data dictionary view you will see the temporary tablespace named as temp. Usually these segments are created one per transaction. And also they are created at run time. Oracle is very clever it creates the temporary segments in advance for the maximum load. In this manner it avoids the creation of these segments at run time and intimately improves performance. The corresponding data file for the temporary tablespace is temp file and it can be seen on the operating system level. If you want more information about the temporary segments on your database then check the data dictionary view named as v$sort_segment.