Oracle RDBMS : Storage Terminology

Oracle database is made up of one or more tablespaces. You might be familiar with the few standard tablespaces like SYSTEM, SYSAUX ( from Oracle 10g), Users, Tools, Index, Temp, Undo. A tablespace is invisible in the file system. A tablespace is a logical storage and consists of one or more datafiles, which are physical structures that conform to the OS in which Oracle DB is running. Prior to Oracle 8i, all tablespaces were created as 'dictionary managed'. Dictionary Managed tablespaces rely on data dictionary tables for space utilization. 'Locally Managed' introduced later use bitmaps to track of used and free space. Locally Managed result in ease of management and better performance.

To create a locally managed tablespace, specify LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement.

CREATE [UNDO|TEMPORARY] TABLESPACE tablespace

DATAFILE|TEMPFILE '.dbf'

[MINIMUM EXTENT n [K|M]]

[AUTOEXTEND

OFF

ON NEXT n K|M MAXSIZE UNLIMITED|n [K|M]]

[LOGGING|NOLOGGING]

[ONLINE|OFFLINE]

[PERMANENT|TEMPORARY] (Not needed for TEMPORARY)

[EXTENT MANAGEMENT

DICTIONARY (Must be LOCAL for TEMPFILE)

LOCAL

AUTOALLOCATE

UNIFORM [SIZE n [K|M]]]

[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]

You can have Oracle manage extents for you automatically with the AUTOALLOCATE option (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE). If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Options are a) Manual - Oracle use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. You need need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. b) Auto - Oracle use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows.

A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.

With Oracle 10g, you can create Big Tablespaces. This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. The consequence of this is that Oracle Database can now scale up to 8 exabytes in size. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. An Oracle database can contain both bigfile and smallfile tablespaces.

A datafile can be associated with only one tablespace and only one database. Oracle Database creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header.

Segments are the database objects that consume storage. Data segments holds data of one of the following - a table, a partition of a partitioned table, a cluster of tables. Index segment holds every non-partitioned index, every partition for a partitioned index. Oracle requires a temporary segment for sorting.

Extents is a logically contiguous allocation of space in a file(not necessarily contiguous on disk). When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows. Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.

With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.

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 tablespace.

A block is the smallest unit of space allocation in Oracle. Blocks stores the rows of data, index. Oracle reads a block and writes block to disk. Recent versions of Oracle Database, allow to have multiple data block sizes. While creating an Oracle database we have to choose the db block as a multiple of the OS block size. Legitimate values are from 2KB, 4KB, 8KB or 16 KB. You need to configure subcaches within the buffer cache for each of the different block sizes used with the database. Multiple block sizes are useful primarily when transporting a tablespace.

Block Structure is:

a) Header - information about type of block (table or index), active and past transaction information, location of the block on the disk.

b) Table Directory - Date from more than one table can be stored in a block. This section contains info about tables that store rows in this block.

c) Row Directory - is an array of pointers to where the rows are to be found in the data portion of the block

d) Free space

e) Data

f) Tail

Now lets discuss about a common performance bottleneck - row migration and row chaining.

Apple must bite Sun

India, 04/14/2009: Now that another merger failed in the technology sector, there have been a lot of comments on SUN Microsystems, Inc. following YAHOO,Inc path. But was IBM the right one to take SUN?

SUN stands for Stanford University Network. The company is founded in the year 1982 and is headquartered at Santa Clara, CA. The company was initially into hardware business with SPARC processor based servers and work stations. Later on the company became famous for Unix systems -Solaris. Sun added to what we call the internet revolution with JAVA platform. JAVA has prominent role in the cloud computing architecture that the big techies are proceeding with. They also have products like StarOffice (to compete the Microsoft Office) and MYSQL database. Other technologies include the NFS, Sun ONE.

IBM who have vivid products in different areas - IBM AIX (Unix), DB2 (Database), Symphony (Office Suite) and hardware servers will have almost zero add-on to their billion dollars spend (if the deal had materialized). The Big Blue also have a bigger market share over SUN.

Apple Inc., is popular across all ages for their various hardware and software products. The innovations include Macintosh, OS X, and a list of "i" prefixes such as iTunes, iLife, iWork, iPod, iPhone and so on.

On the other hand, if Apple takes over the struggling tech firm, it would enhance its server market, get introduced to web-cloud, database technologies.

Other MNCs that could have been appropriate owners of SUN are Cisco Systems who recently entered into server business. According to the sources, they also have a cash reserve of about $30 billion. Everyone expected HP to tie up the IBM tender. I expect the acquisition guru Oracle to come into the picture. But again it wouldn't have been an effective deal for the database giant.

Apple who have been voted as the best innovative company for the year ahead of Google, Microsoft; would have been the right choice with the products that SUN own.

But expectations sometime come true. Larry Ellison led Oracle Inc. had proffered Sun with $7.4 billion. Larry is an expert with acquisition. The few advantages I believe on the deal between for Oracle-Sun is that they can concentrate their products more on Solaris environment, Oracle will have an open source step-brother MySQL, Oracle technologies which uses Java platform will get more efficient, Oracle can spread StarOffice usage in reporting.Both Oracle and Sun have their workforce spread across the globe specially in Asia-Pacific region.I am sure Larry and his team have more reasons to take over the shadowing Sun.

Is our Earth burning or weeping?

India: Another Christmas (2008) is over, I didn't buy any winter cloths this time. I am waiting for a bit more chilly day. Soon I saw that shops have put clearance sale of winter accessories. I then realized that already three month of 2009 are over. The temperature is rising, heat and humidity are gradually taking away the pleasure of life. I am sure in a circle of 10 you will at least see 5 sweating of their energy and talking about the heat.

For the last past years, when the temperature were a bit high than normal; I would tell to myself that its just this season. The following one would make cool. Every year the average temperature is rising, there is no rain when expected, unexpected snow, cyclones, earth quakes, forest fire. Things are changing and to be frank in the direction of the worst. I now understand the significant early efforts taken by different organization to protect the planet. Yes, friends climate is changing drastically and knowingly or unknowingly we are all part of it.

Check on Google News, Reuters, CNN, I am sure you would find multiple articles about ice melting at the two poles of earth (Arctic and Antarctica). Scientist and researchers predict not more than 30 years for the 80% of the arctic ice to be turned to liquid form. Folks, think. 30 years is nothing.. imagine your baby is 1 year old and by the time he or she is married and settled, the earth don't exist any more. We worry so much about the future of our kids. We send them to good school, save money for their future, buy land, build business for them to inherit. But what about fresh air, fresh water..

Few weeks back, we talked about the dream come true in the form of cheapest car - nano. Dreaming is good, to make it real is a big job done. But do we realize that the cheapest car producing firm also bottles water that costs INR 25 for 1 Litre. Just take the ratio about what is costing you more and why. I believe the automaker will make a limit to no# vehicles produced in a year, recycle the parts, maintain pollution and safety standards. Else this dream of a man will be a hell in disguise. Imagine you have the same road and another 30 more nanos to 1 Mile of your daily work. This is just an instance there are another millions to depict.

Government/courts must take sensible actions to this global concern. There is no difference in what an American or an European or an Indian breathe. There shouldnt be any difference in acting against this disturbing fact in terms of developed and developing countries.

Last week was Earth hour, Many did build or join communities/groups in social sites (orkut, facebook) with thousands of members. Its good and appreciable that you gave an hour to save the earth by less use of electricity. Did you recommend any of your friend who was not aware of it. Did you call your friend, colleague, next door to remind him/her about it. Its high time pals, we unite together to stand for our planet.

We have to heal the world. It is one best way you can thank for the various resources, minerals, wildlife, natural beauty that we have utilized and continued to be utilizing. We have to do whatever we can. Car pooling to work and for family occasions, walk or ride bike to go to the next drive to meet your classmate, switching off unwanted electrical equipments, planting trees, educating younger generation,reduce using plastics are some I think of.

Economic Recession: This is what I think?

India/US: The economic term can be described in the reduction of a country's gross domestic product (GDP) for at least two or more consecutive quarters.The term rhyme very similar to depression.

I read somewhere, a simple explanation to show the difference between both the words.A recession is when your colleague loses his job without any reason.A depression is when you are removed from the job.A recession eventually results in a depression.

I have been reading and constantly following up the economy news and how different developed and developing governments are responding.Why is everyone keen on US stimulus packages? US is one of the richest and strongest economy in the world. Is this the reason why every eye is on U.S. Absolutly not, US is the biggest consumer of the world. About 80% of the developed and developing countires do trade with U.S.Toyota and Sony are best example to name.

Its not the sudden fall of WaMu or the DSP Merrill Lynch or Lehman Brothers that caused this turmoil.The common man never realized that recession began in Dec 2007.The U.S mortgage market has just contributed to the collapse. Was the stock market only depending on these few behemoths? Absolutly not. Neither did foreign workers in a country resulted this mess.(You must admit that foreign workers also pay tax and add growth same as a citizen)

Inflation is one important factor for recession. Less spending by U.S. consumers and companies reduce demand.As corporate houses look for ways to trim costs, the GDP decline.A common man who gets smaller percentage of services with certain amount.Therefore he/she will now spend less, budget more, saving money.

Predictors of recession are significant stock market drop and unemployment rate increases. The effects of recession is well known;the absense of business expansion.

Where is the money that was with fortune 500 companies a year back? Did someone stole entire money from the banks? I believe just like SDLC, life cycle there is also a cycle of money.It the chain breaks, it affects everyone. No one stole money from anyone.It is just that he/she feels insecure with economy. It the same sentiment that in a relationship. People loose faith in investment and the fear reflects in their expendicture portfolio.

Faces turn away to loans, they don't buy new apartments, construction business gets affected. If construction is down, gradually the demand for steel, aluminium, machineries fall.Employee doesn't get bonus or pay hike. Percentage of folks who were planning a vacation or to replace his/her car changes mind. Car sale gets affected.Flight companies see a fall in revenue. People who want to upgrade television to HD plasma, buy macbook will think twice.A teenager would hesitate to buy a new ipod for his girl friend for Christmas. Demand and Supply ratio differs with a big gap (Manufactured goods gets slumped).The companies who use software to maintain their huge databases, support activities reduces. Now,folks in IT sector will be affected. New application developments halts. A mere support activities continue.Thus the companies with decrease in sales demand bail-outs,perform layoffs in order to sustain.

Its a big challenge to bring back the flow.Various approach can be taken stimulus packages (TARP) to generate, tax cuts to ignite growth again.Economists say that the only advantages with recession that it helps to curb inflation and bring the economy to be disciplined.

Apart from the normal methods, I strongly believe that the emerging eco-friendly products,advancement in technologies, new inventions/innovations would surely add and keep the interests of consumers.

To conclude, Its a myth to believe in the magic wand of new year or new president and remain idle. I guess each and every human being are equally responsible and should stand together to overcome this depression.