Compressing Extents in Oracle

While Oracle has been moving in the direction of locally managed extents in tablespaces several of us are still working with dictionary managed tablespaces. Here’s a good trick for adjusting table storage clauses in dictionary managed tablespaces.

Note: “compressing” extents is not a way to make data take up less space, but rather a way to take a fragmented (for lack of a better word) table and re-ogranize it into a more contiguous area of storage.

In the older dictionary managed method a storage clause in the table creation command controls how much space was initially allocated to that table (the initial extent) and how much would additionally be allocated if it filled the initial space (the next extent). If no storage clause was specified an often absurd default would be used.

This leaves us with two problems: First, we may have a very low value for the next extent. This can leave us creating extents of, for instance, 32KB even though each row may be 50KB.

Second, we may have tables which already contain a ridiculous number of extents and possibly chained rows. (Chained rows are a topic for another day.)

For my example I will use a table owned by system called session_audit.

To view the storage parameters on a table we can query the dba_tables view:

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 65536

These numbers represent size in bytes, so dividing by 1024 we see that this table is configured to have an initial extent of 128KB and a next extent of 64KB.

We can also count the current number of extents whch make up this table with the following SQL command:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
77

It may seem the right method is to change the initial and next values with an ALTER TABLE command like this:

SQL> ALTER TABLE system.session_audit
STORAGE (INITIAL 10M NEXT 5M);
ALTER TABLE SYSTEM.SESSION_AUDIT
*
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

If we dropped the change to the initial extent we could use this command to change the next extent. If we want to update the initial extent we will need to get sneaky. We could create a new table with the desired storage settings, move the data, then rename the new table to the old name, but this would cause the table to be temporarily unavailable and have the unfortunate side-effect of invalidating any of this tables dependencies. Alternately we could export and import the table but this would lead to the same problem.

Instead, using the ‘ALTER TABLE MOVE’ command, we can tell Oracle to move this table into a different tablespace while at the same time sneaking a new storage clause in.

SQL> ALTER TABLE system.session_audit
MOVE TABLESPACE system
STORAGE (INITIAL 10M NEXT 5M);

Table altered.

We now have our table in the new tablespace with the desired storage clause. This also creates an initial extent of the specified size instead of the smaller extents we had previously. If we want to move the table back to it’s original tablespace we can just issue the same command with the original tablespace.

NOTE: I was also able to tell Oracle to “move” the table to the same tablespace it was originally in. This had the same affect of changing the storage clause and compressing the extents without needing a foster tablespace.

We can now re-check the number of extents and storage clause for the table:

SQL> SELECT count(*) FROM dba_extents
WHERE segment_name='SESSION_AUDIT' AND
OWNER='SYSTEM';

COUNT(*)
--------
1

SQL > SELECT initial_extent, next_extent
FROM dba_tables
WHERE table_name='SESSION_AUDIT' AND owner='SYSTEM';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 5242880

We see that the number of extents currently being used by the table has been reduced to 1 and our next extent will be 5MB in size.

oracle, database, database administrator, oracle database, dba, sql, database tuning

The Center for Internet Security – Oracle Security Benchmark

Wondering if you’re doing everything possible to secure your Oracle database? You probably aren’t, but the Center for Internet Security has compiled a practical checklist which will get you damned close!

The Center for Internet Security (CIS) is a non-profit organization which is committed to providing information on best-practices for security. The CIS does not report every vulnerability in a piece of software, but rather provides a set of best-practices for setup and configuration of systems and applications to minimize security risks.

The CIS Oracle Benchmarks provide a wealth of information on installing and configuring Oracle. Far from a step-by-step on how to install Oracle, it does fill in many of the gaps that are easily overlooked.

The CIS approach is very practical. As an example, item 2.01 from the Oracle benchmark reads as follows:

2.01
Installation
Try to ensure that no other users are connected while installing Oracle 10g

The Oracle 10g installer application could potentially
create files in a temporary directory with public
privileges. It would be possible for any local user to
delete, overwrite or corrupt these files during the
installation process. Try to ensure that no other users
are connected while installing Oracle 10g. Also set the
$TMP and $TMPDIR environment variables to a
protected directory with access given only to the Oracle
software owner and the ORA_INSTALL group.

In this compressed format the Oracle 10g Benchmarks still span 55 pages; however, these 55 pages represent the equivalent of several years of experience.

While the complete list of benchmarks offered by the CIS is relatively small it hits the high points on enterprise level software. I expect in the long run the list will grow to include more of the open-source solutions we are now finding commonplace.

So grab your favorite sys-admin and a big cup of coffee and run through this checklist. You’ll be surprised what you find. If you happen to be the DBA _and_ the sys-admin you’d better make it an extra large coffee.

oracle, oracle security, oracle 10g, oracle 9i, database administration, dba, database, database security

MasterWish.com – Getting Some Great Feedback

MasterWish LogoI am happy to say that MasterWish.com is getting some fantastic feedback from the user community!

While we are not about to stop improving the site it is fantastic to see feedback like this weblog entry from Kevin Scaldeferri’s Weblog. Glad you liked the site Kevin! In our hectic, fast paced, information-heavy world I’m glad to hear people appreciate our less-is-more attitude.

MasterWish was founded on the ideal of offering flexibility… a tool that a person uses the way they want to, not the way we think they should. This feedback helps guide us to continue to develop in this direction.

Thanks Kevin!

For more information on MasterWish, check out my previous article, or go straight to masterwish.com and check it out for yourself!

christmas, christmas list, cool, free, gift, gift lists, holiday, masterwish, registry, web 2.0, web20, wish list, wishlist, wish

RadioShack Screwdriver Sets – Great Tools for Small Work

National Weather Service LogoNeed a good $5-$10 gift for the tinkerer on your list? Buy them the last set of precision screwdrivers they’ll ever need!

RadioShack offers some fantastic precision tools, but only a few make it onto my must-have list. The Kronus 6-Piece Precision Phillips Screwdriver Set and corresponding slotted set are the exception. These are the type of tools you will wonder how you ever lived without.

The slotted set, RadioShack Model 64-2968 span from 1.0mm to 3.5mm in logical steps. Similarly, the Phillips, RadioShack Model 64-2969 ranges from 1.4mm to 3.8mm (the largest is roughly Phillips #1, but the others are too small for the normal Phillips measurement). Also, unlike some sets which come with a single handle and several interchangeable shafts, these are all fixed to the handle so there’s no more switching bits!

At $4.99 per set I highly recommend buying one of each, but if you take a look at the selection, RadioShack also has some combined sets with more or less tools.

Several years ago I purchased both these sets after being frustrated at not having the correct size of slotted driver while working on a turntable. Since then I have never been at a loss for the correct sized driver!

With free-spinning tops and great grips it is easy to apply the right amount of torque, and as for durability… well, I’m rough on tools and I don’t think I’ll be needing the limited lifetime warranty anytime soon.

While I’m sure I could get a comparable or possibly better set of precision drivers through one of the popular mail-order tool dealers but you just can’t beat simply walking into RadioShack and picking these up. You even save on shipping!

tool, tools, radioshack, building, electronics, repairs, diy, do it yourself, repair, modeling, models

Performing Math on Oracle Dates

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;

SYSDATE+7
---------
06-DEC-05

SQL> SELECT sysdate - 30 FROM dual;

SYSDATE-3
---------
30-OCT-05

SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(SYSDATE-14,
-------------------
11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;

SYSDATE+(
---------
13-DEC-05

Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

TO_CHAR(
--------
09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

TO_CHAR(
--------
11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

SQL> SELECT
to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

SQL> SELECT
to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

SQL> SELECT
to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
04/29/2006

SQL> SELECT
to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
11/29/2007

Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

SQL> SELECT
TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;

TO_DATE('12/25/2005','MM/DD/YYYY')-SYSDATE
------------------------------------------
26.5124421

The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

SQL> SELECT
months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('01/01/2006','MM/DD/YYYY'))
----------------------------------------------------------
-1.0829409

Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

SQL> SELECT
greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

GREATEST(
---------
25-DEC-05

SQL> SELECT
least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

LEAST(SYS
---------
11-SEP-05

What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-NOV-05

Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(
---------
04-DEC-05

Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development