High Water Mark

It is a marker that shows you the point when you last inserted the data in the table. Let us simplify it with an example. Suppose you created a new table initially with no value. The high water mark will be at initial position say at 0. You made an insertion. The HWM will rise by one and will reach at position 1. Similarly as you will make more and more insertions the HWM will also rise; suppose you reached at a position where the value of HWM was 100. Now you started deleting values from the table. The value of HWM will not fall with the deletion of values from the table. So you can easily say that HWM shows the highest level of insertion you have ever done in that particular table.

Every time you give the command of select to fetch values from a table the oracle searches the table to HWM. It does not matter whether your last value is stored on position 10 or 90; if your HWM is 100 then oracle will search up to 100 positions. Normally for deleting values from the table you use the command of delete. This does not affect your HWM even if you have deleted all the values from the table. To bring the HWM to the initial position you can use the command:

SQL>truncate table table_name;

The high water mark is only meaningful in the table segment.

Row Structure

The way Oracle stores a row of a table is known as row structure. A row consists of two parts; one is header and the second is data. In the header part all the control information of that particular row is stored. Control information of a row consists of three parts. First is the links of row migration and row chaining. Secondly is the lock information. Locks are used so that no two transactions should update the row simultaneously. And the last thing is how many columns are there in that row. Every row in a table has same number of columns; so why there is a need for storing this information separately in each row. This is one of the features that only oracle provides. We will see the advantage of this in just a moment.

Next is the data portion. Data portion contains the actual data stored. Each entry of data has two parts. One is known as column length (CL) and the other is column value (CV). Oracle does not store the null values. This is the feature I talked about earlier. Suppose for a particular row there are five columns in which the last three has null values. Oracle does not store the null values altogether. Then how it knows that there is a null value? As I mentioned that oracle stores the number of columns for each row. So the value stored in the header will be 5 for that row but the actual values that are present are two. Oracle will realize here that the last three values are null. Real world databases do have a lot of null values. So oracle wastes a little space for storing the number of columns information in the header of every row but on the other hand it saves a lot of space by not storing the null values.

LOB Introduction

Char and Varchar2 data types are used to store text columns in an Oracle database. While for the normal requirements they are OK, but they are not as suitable when it comes to store large objects. By large objects I mean storing of entire PDF documents for example or storing of an image. Traditionally Oracle provided two data types for these situations namely as RAW and Long RAW. But the use of these data types was inherent to some limitations. The first one was that you cannot have more one column of RAW or Long RAW data types in one table. The storage capacity was limited to 2GB. And last you cannot perform SQL operations on the subset of data stored as RAW or Long RAW.

In Oracle 10g Release 1 and above (not sure of 9i) Oracle introduced Large Objects or LOB for short. The introduction of LOB data types culminates all the restrictions that were present in RAW and Long RAW data types. LOB itself is not a data type. Rather it is a category of four data types which are divided into two distinct categories. The categorization of LOB data types is based on their storage. The two categories are:

1. Internal

2. External

Internally stored LOB objects are those which are stored inside database. They are treated just like any other object inside database. And all SQL operations are valid for them that are valid for other data types. There are three data types in this category:

1. CLOB: It is used to store large documents and stands for Character Large Objects.
2. NCLOB: It is also used to store large documents which are in character format. But it stores these documents using a different format namely as National Character Set.
3. BLOB: This data type is used to store large objects which are in binary format. For example an image.

Externally stored large objects are those objects which are stored outside database but whose reference is maintained inside database. The storage of these objects is inherent to the limitations of Operating System files. Normal SQL operations performed on other internally stored objects will not be applicable to these externally stored objects. The only data type in this category is BFILE. The column of BFILE data type will be used only to store the location of large object file.

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.

Oracle Data Block (Continued)

We are down to the most granular unit of storage in an Oracle system i.e Oracle block. If we continue to break up then it is evident that an extent consists of many Oracle blocks. Further more an Oracle block maps to the one or more physical Operating System blocks. And also it is a good practice to have the size of your Oracle block equal to the OS block size.

There are three main parts of an oracle block. These are header, free space and the data part. The header contains the control information. This control information includes the full address of the oracle block itself, information of the addresses of the rows stored in the data block known as row directory, and information of inittrans and maxtrans. The inittrans parameter defines that how many transactions can initially access the data block. And maxtrans defines that how many transactions are allowed to access the data block. Suppose that your inittrans is 3 and your maxtrans is 5. It means that 3 transactions are currently registered in the header and are accessing the data block and total of 5 are allowed to access the Oracle block. To allow two more transactions to be registered and access the data block the header will take the space from free space and allow them to access the data block.

The free space is reserved for the future growth. The data portion grows upwards while the header portion grows downwards. One point worth mentioning here is that free space is used for update command not for the insert command. It means that when an existing row grows in size then the free space is used; otherwise if you want to insert a new row and there is no free space left then a new data block will be used.

Data portion is used for the actual storage of the data. Suppose you have an extent in which data block 1,2,4,7 are free while all other are full. When new data needs to inserted oracle checks for the free data blocks. One way is look up the entire extent and find out which data blocks are used and which are free. But this is a very cumbersome method. What oracle does is it keeps a list of free data blocks in the form of link list. This list is known as free list.

Two thresholds are used to control the data in the data block named as PCT free and PCT used. When the data in the data block reaches the PCT used limit it means that data block has no more space to hold the data. Oracle looks in the free list and picks another data block. Now suppose a user has started deleting data from the data block. In this process if it gets to the PCT free limit then that block is included in the free list. Normally this limit is 40% of the data block. It means that if a data block is full but not up to 40% then it is kept in the free list.

Let us end it with two more very important concepts. Suppose you have a row with a null value for some column. Now you have updated the row with some value; that value is so big that space in the data block is not enough for it. As you are updating the row space will be available for you from free space. But suppose it is not enough also; in this case a new data block will be created for you and the all of row’s data is stored in it. This is known as Row Migration. In other situation suppose a user is inserting data for a particular row and it is so big that space in the data block is not enough for it. In this case you are inserting data not updating therefore no space will be available for you from free space. Instead a new data block is created for you and remaining data is stored in it. It is called Row Chaining. And the individual pieces of data in different blocks for a single row are known as Row Pieces.

If you want some information about the data blocks and free space then you can use the following views: dba_extents, dba_free_space. And also the following command will give you useful information about data blocks. It will tell you the sum of all data blocks in each tablespace of your database.

SQL>select tablespace_name, count (*), max (blocks), sum (blocks)

from dba_free_space group by tablespace_name;

Extents (Continued)

A segment has more than one extent in it. Extents are the basic allocation units. Suppose you created a tablespace with the following command:

SQL>create tablespace test datafile ‘c: \oracle\oradata\dbase\test01.dbf’ size 10M extent management local uniform size 512K.

Here the second size mentioned 512K is the size of extents created within each segment. By uniform we mean that the size will be same for all extents of all segments. You can override this behavior for a segment by using the storage clause e.g.

SQL>create table t1 (t_no number)

storage

(initial 1M

next 1M

);

Now all the extents created for the table T1 will be of size 1M instead of default 512K. By initial we mean the size of first extent and next are used for the every next extent.

As we know that the extents are the basic allocation units. It means that the size is allocated to tablespace according to the size of extent. When you turn the autoextend on then for example a segment needs more space. In the above example the size of more space given at once to the segment will be 1M i.e. the size of the extent.

Segments (Continued)

We all know that a database will always have users of it. Further more a user also has his or her objects e.g. tables, indexes, views etc. In database a user and his/her objects are collectively known as Schema. For each user in the database there must be a tablespace (although a user can have more than one tablespaces but we will consider only one because tablespace is not the topic here). Within each tablespace there are segments. Each segment corresponds to an object that a user owns. For example there may be a table segment for the tables, an index segment for the index etc. Almost all the user objects are stored inside segments except the one thing and that is programs. Stored procedures, triggers and all other type of programs are stored inside the system tablespace.
Oracle database provides different types of segments for storing different types of objects. They are:

1) Table Segment: It is used to store the tables created by the user.

2) Index Segment: It is used to store the indexes created by the user.

3) Cluster Segment: Sometimes for performance reasons two or more tables are stored together on the disc. It is known as clustering. Cluster segment holds the clustered tables.

4) Table partition Segment: When your table is too large then it is partitioned into one or more components. Table partition segment holds the partitioned table.

5) Index Partition Segment: Similarly when your index is too large it is also partitioned and index partition segment is used to store it. It is only present in 8i and above versions.

6) LOB Partition Segment: LOB stands for large objects. By large objects we mean video data, audio data, images etc. There are three data types for storing large objects. BLOB (Binary Large Objects), CLOB (Character Large Objects), NCLOB (Natural Character Large Objects). When any column in a table has a data type from any of above mentioned data types; then that column is not stored in the segment in which the actual table is being stored. There will be separate segment for that particular column and that segment is called LOB Partition Segment.

7) Index Organized Table Segment: Sometimes the table and the index of it are stored in the same segment. That segment is known as index organized table segment.

8) Undo/Rollback Segment: Suppose you have made a change in a table and you gave the command of rollback. The previous value will be restored. Oracle stores all of its old values in the rollback segment. Before 9i it was known as rollback segment and in and after 9i it is known as undo segment.

9) Temporary Segment: Temporary segment is used to hold the temporary objects that the user needs. For example when you are sorting you need an object to temporarily hold a value.

10) Nested Table Segment: When you store a table inside a table nested table segment is used to store a table inside a table.

Tablespace Management (Continued)

Managing Tablespace means to tell that what its extent management is. We know that inside a Tablespace there are many segments which in turn consist of many extents. There are two ways to manage a Tablespace.

1. Dictionary Managed
2. Locally Managed

Suppose there is a tablespace named as T1, inside T1 there is a segment named S1, and inside S1 there are four extents named as E1, E2, E3, and E4. Here we need to remember is that which extents are full and which are empty. For example for extent E1 entry will be:

T1-S1-E1: empty/full.

And similar for all other extents in that tablespace and in other tablespaces in the database. If you are managing your tablespaces using dictionary managed than all this information will be stored in the data dictionary. And data dictionary is consulted every time a tablespace is used. For storing this information in the data dictionary there are two base tables. One is UET$ and the other is FET$. The first one stands for used extent table and stores the information of all the extents that are used or full. The second one stands for free extent table and stores the information of all extents that are free. When an extent is free it will be in FET$ table and after it is filled its entry is removed from FET$ and is stored in the UET$ table.

The other way is to manage the tablespace locally without using the data dictionary. The extent information is held in the headers of data files in the form of bit maps. The bit map will contain all the information about the extents that whether they are free or full. For example a bit map will show 1 for the extent that is full and 0 for the extent that is free. This is a more preferred approach because it removes the overhead of consulting data dictionary every time you need to use the tablespace.

Both approaches are used. But as mentioned that locally managed is much better option and oracle highly recommends it. Locally managed was introduced in 8i. You can also manage the system tablespace. But for 8i release 2 and early versions you can’t manage it using locally managed. In later versions you can do this. But remember that if have managed the system tablespace using locally managed method than you will not be able to manage other tablespaces using dictionary managed method. You had to use locally managed through out the system. This is not the case with dictionary managed.