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.

Logical Structure of Database (Continue)

Don’t confuse it with the logical structure of oracle server i.e. instance. It is the logical structure of database which is the physical part of oracle server. In other words it is the logical structure of the physical part of oracle server. Oracle database can be divided into two parts. One is physical and the other is logical. Physical part of database consists of Data Files and Operating System Blocks. In logical part a database consists of many tablespaces; which in turn consist of many segments. A segment is consisting of many extents and an extent is consisting of many data blocks or they are also known as oracle blocks. I am going to discuss each of them in detail here and in subsequent posts. Logical part can be mapped to the physical part and mapping is already what we know i.e. a tablespace consists of many data files and an Oracle Data Block consists of many Operating System Blocks.

Managing Tablespaces:

In very simple words a tablespace is the logical container of the data stored in the database. Usually a large database is divided into many tablespaces. Remember that tablespace is a logical construct and on physical level only data files exist. Usually a tablespace consists of many data files. Broadly speaking a tablespace will come in one of the following two types.

1. Permanent Tablespace: A permanent tablespace is a tablespace that stores the permanent data. It has two further types.
• System: It stores the data for the system i.e. oracle server. And as we know that oracle server stores all its data in the data dictionary. So you can say that system tablespace stores data dictionary.
• Non System: It stores all the user data that is of permanent nature.
2. Temporary Tablespace: It stores the data that is of temporary nature. For example when you are sorting you need a temporary storage to hold values for some time.

As mentioned system Tablespace stores the data dictionary. It also stores one more thing and that is programs. By programs I mean the triggers, stored procedures etc. In oracle you can create tablespaces of your own. If you do not create oracle does not generates any errors. But it is highly recommended that you create because it helps in managing the database. How? Suppose you have an ERP system in which there is department of sales, another department of production and yet another department of marketing. If you have stored all the data in one tablespace it will be a headache for you to manage it. But if you hade created a separate tablespace for every department; than it will be easy to mange the data of each department. This is called a modular approach. Let us clear it with an example. Suppose at any instant of time you are required to make the data of sales department unavailable for the users. If you have stored all of your data in one tablespace than you have to shutdown your entire database. But if you are following a modular approach i.e. you have created a separate tablespace for every department. Than to make the data of sales department unavailable all you need is to make that tablespace offline. There will be no effect of this on other tablespaces and on database.

Changing Database Modes

By default Oracle database runs in no archive log mode. This means that Oracle will not make any off line copies of online redo logs. Therefore it is highly recommended that you must change it to archive log mode; so that there may be no loss of log information. Now here is a point that archiving is done in two ways i.e. Manual and Automatic. Manual archiving can be a burden on a DBA and it takes too much time. And in some cases it also brings database to its knees. When you turn the database to archive log mode than ARCH process does the automatic archiving. Now the only question remains to be answered is how to change the database mode. Here are the simple steps.

1. First of all shut down the Instance.
2. Than go into the init.ora file and change the following parameters.

Log_archive_start=true

Log_archive_dest=’c: \oracle\oradata\dbase\archive’

3. Mount the Instance.
4. Than give the command;

SQL>alter database archivelog;

SQL>archive log list;

5. The second command will tell you the information about the database that whether it is in archive or noarchive log mode.
6. Finally open the database with the command.

SQL>alter database open;

After completing all these steps you will see that the mode of your database is changed. If you want to change it back to noarchive log mode then first change the parameter Log_archive_start to false and give the command:

SQL>alter database noarchivelog;

Remember that your database should be in mount state when applying these commands. Also you can check the status again by giving the command:

SQL>archive log list.

Now if you are using Oracle 10g then it is a lot easier. All you got to do is to shutdown the database first and then bring it to mount state and give the command:

SQL>alter database archivelog;

As for as the two parameters are concerned the first one is deprecated and second defaults to the flash recovery area.

Instance Recovery

I mentioned in an earlier post that LSN and SCN are greatly used in the instance recovery. As the matter of fact the whole instance recovery is done through these two. Suppose you were working in the database and before you can update the changes the power supply failed. But you have committed the transactions. When you give the command of commit the LGWR writes in the redo log files. As you know that when a check point occurs the DBWR writes and information of LSN and SCN is stored in the control files and in data file’s header. Next time when instance starts up the SMON process checks the LSN number and SCN number in the redo logs and in the control files. If they are the same then the data is consistent and there is no need of recovery. In case of a system failure the SCN number in the redo logs will be greater then the SCN number in the control files. The SMON process will see the difference in the entries and will restore the data for those entries. Suppose when you last saved the data your LSN was 23 and your SCN was 60. At LSN of 23 and SCN of 100 you committed the data but did not saved the data. The LSN and SCN in the control files will be 23 and 60 respectively because this was when you last saved the data. But the LSN and SCN number in the redo logs will be 23 and 100 respectively because this was when you last committed the data. SMON will see that there is difference of 40 entries; means that data of last 40 entries needs to be saved. So it recovers the data and stores it on data files. The whole process is going on background. Users will be allowed to continue their work. In this way oracle ensures that up till every commit or check point your data will be not lost whether you have saved it or not.

Oracle Forms:Client Server-Web

Oracle forms are proprietary platform for building database applications that make use of Oracle database. The reason for their popularity is their tight integration with Oracle database. Although you can make use of other more strong development environments while still using Oracle like .Net and J2EE etc, Oracle forms are by far the most conventional and easy to use environment for database applications that use Oracle database at the back end. Oracle provides a complete development environment rather then just forms. The suite includes Oracle reports, Oracle Designer, Oracle Schema Builder, and Oracle Query Builder among others. From 9i onwards this suite has taken a huge turn in terms of its runtime environment.

Although development environment is not much changed since the release of 6i but runtime environment is changed from client/server to web. Oracle developer suite 10g now uses Oracle Application Server Forms Services for the deployment of Forms applications. Now instead of client/server it uses three tier architecture. Three tiers are as follows:

1. Client Tier

2. Middle Tier

3. Database Tier

Client tier consists of low end machines with some browser installed so that they can view the forms. Oracle provides a utility named as JInitiator for running forms in browser. JInitiator gets installed on the client machine when you run your first form. Client requests the application through middle tier which is typically Application Server. Application Server uses some Java Servlet for processing requests. This middle tier has dual role to play in the entire Forms Services Architecture. It acts as a server to the client tier and as a client to the database tier. And finally the database tier holds all the company’s data.

This new architecture completely changes the way user log in to use the applications. In the client server environment all the user’s credentials were normally stored in the database; so the authentication process was done through database. This has changed. Although you can still use this technique but Oracle recommends you to use the application server’s security implementations. Oracle AS uses the Oracle Internet Directory (OID) as the central repository for all users’ information. The connection string of every user is maintained through Connect Access Descriptors (CAD) in OID. If you use this technique for authentication then you can make your applications to leverage the Oracle AS’s Single Sign On features.

Redo Mechanism (Continued)

Now what happens if LGWR has written on all the redo log groups one by one? It’s a solid question. The answer is that when LGWR has written on all the redo log groups it comes back to the first one. Now here are two considerations. First is that if your database is running in No Archive Log mode than LGWR will start over writing the groups starting from the first one. But if your database is running in Archived Log mode than LGWR will not over write the log group until ARCH (an optional background process) has made a copy of that redo log group. This offline copy of online redo log is called Archived Redo Log. The LSN number of the redo group of which the copy is created is also stored in the copy. By default oracle database runs in No Archived Log mode but it is highly recommended that you must change it to Archived Log mode.

Here are some commands that you can use in dealing with redo log files. First of all if you want to see the information about redo log groups then you can check V$log view. You will see the number of groups, and members in each group and also some other information. You can’t see this information on operating system level; because this is hidden from operating system. If you want to see the exact location of the members of the redo log groups then open the V$logfile view.

If you want to add a redo log group than use the command:

SQL>alter database add logfile group 4 (‘c: \oracle\oradata\dbase\log41.log’) size 1M;

The second part is the path of the member of the group you created. Name of the member can be anything.

If you want to add a member files in the existing group than use the command:

SQL>alter database add logfile member ‘C: \oracle\oradata\dbase\log42.log’ to group 4;

Remember here you will not mention the size of the file because it is same as of other group members; in this case it will be 1M.

If you want to drop a member files from a group than use the command:

SQL>alter database drop logfile member ‘C: \oracle\oradata\dbase\log42.log’;

If you want to drop a group than use the command:

SQL>alter database drop logfile group 4;

If you want to explicitly switch log at any instant of time than use the following command. Remember that when a log switch occurs than event of check point occurs. And when check point occurs the CKPT process is activated and what it does is already been discussed. This command is useful for synchronization of data and is recommended before and after hot backups.

SQL>alter system switch logfile;

If you want check point to occur than use the following command. The same thing will also happen here except there will be no log switch.

SQL>alter system checkpoint;

Be careful when you are performing all these operations your database should be in mount state not in open.

Redo Mechanism (Continue)

Redo logs are the most important component of oracle database. The way oracle manages its redo logs is known as redo mechanism and is the most salient feature of oracle database. It is this mechanism that differentiates oracle from other database vendors. The basic purpose of redo mechanism is to facilitate recovery. Redo Mechanism involves log buffer, log writer, online redo log, and archived redo log.

Redo log files are logically arranged in the form of groups. Normally each group has two members i.e. two redo log files. The members (files) in a group are of same size. Or you can say that a set of similar copies of redo log files is known as group. This grouping of redo log files is logical. It is hidden even from the operating system. Oracle manages them all by itself. Each group has a number assigned to it which is called Log Sequence Number. Members inside a group have same LSN and it is assigned each time log writer starts writing on redo log files. Within each member of the group there are entries. These are redo log entries; one per transaction. These redo log entries also have a unique number assigned to it which is called System Change Number. These two I mean Log Sequence Number (LSN) and System Change Number (SCN) have a great role to play in the Instance Recovery.

When LGWR writes on these groups it writes on both members of the group simultaneously. If you increase the number of members in the group that extra member will not be an exception to this mechanism. In simple words it uses the concept of multiplexing. When the first group is full it switches to the next and so forth. When LGWR switches from one log group to the other it is known as log switch.

When a log switch occurs the event of check point occurs. Here the CKPT performs both of its functions. First it asks the DBWR to write. Note here the synchronization again that before DBWR, LGWR will write. And second it writes the check point information to control files and to the header of data files. What is the check point information? It includes the LSN and SCN numbers. One point worth mentioning here is that LSN is stored normally of the next redo group (the group to which you are switching); and SCN is stored of the previous group (the group from which you are switching). It is clear now that LSN and SCN stored in the control files and in the data file’s header indicate that until them the data is stored back on disc and is safe.

First the use of multiplexing and then oracle stores the same information in control files and in data file’s header. This shows the concerns that Oracle have about an organization's data.

Oracle V/S ANSI Joins

If you talk about Database Management System now a day it is assumed that you are talking about Relational Database Management System because they are the norm. They have become the standard for information storage and retrieval. It is their power that has made these two business functions more easy and elegant like they were never before. For storing and retrieving data in Relational Database Management System the language used is Structured Query Language. It is shortly termed as SQL. SQL is a standard for successful information storage and retrieval in databases.

The standard of SQL is maintained by American National Standard Institute (ANSI). ANSI keeps track of the requirements and makes the necessary modifications and finally updates the standard. Despite of the standard each database vendor maintains its own version of SQL; so does the Oracle. However the basic functions remain the same. One of the areas in which Oracle SQL is different from ANSI SQL is the way it handles and manipulates the joins. The difference is mainly in the syntax rather in functionality. ANSI SQL puts all the conditions necessary for joins in the “From” clause of “Select” statement and “Where” clause is reserved for any optional conditions. On the other hand Oracle SQL uses “From” clause only to list the tables and aliases and all the conditions (Join Conditions + Optional Conditions) take place in the “Where” clause.

To clarify let us take the all familiar example of Employee-Department relationship. Suppose you want to list department name and all the corresponding employee’s information and for that purpose you intend to use INNER JOIN so that only matching records come in the result set. The syntax in ANSI SQL will be like this;

SQL>select a.dname,b.* from dept a inner join emp b on a.deptno=b.deptno;

The same result can be obtained in Oracle SQL by writing the statement as:

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno;

Now if you want to see all the departments no matter if they have any employees or not then you will write following ANSI SQL statement;

SQL> select a.dname,b.* from dept a left outer join emp b on a.deptno=b.deptno;

To achieve the same result in Oracle SQL you will need the following statement;

SQL> select a.dname,b.* from dept a,emp b where a.deptno=b.deptno(+);

For RIGHT OUTER JOINS you will use the keyword RIGHT instead of LEFT in the above statement for ANSI SQL and place (+) on the other side for Oracle SQL. ANSI SQL also provides the FULL OUTER JOIN which returns the records from both tables that have not the matching records in the corresponding tables. Oracle does not support the FULL OUTER JOIN.

If you are new to Oracle then don’t panic that you need to learn the syntax of joins again. You can avoid the Oracle syntax joins fully while still working in Oracle. This is possible because Oracle has got full support for ANSI JOINS. You can write all ANSI style queries and they will work for you in Oracle. What if you are stuck to Oracle JOINS and you want to get the results of FULL OUTER JOIN. Here is the simple trick to do this. Just UNION the results of LEFT OUTER JOIN and RIGHT OUTER JOIN and that’s it. As UNION operator eliminates the duplications automatically so the result will be an exact replacement of ANSI SQL’s FULL OUTER JOIN.