10’000 hits on this blog, thank you all !!

On last Friday my blog has reached 10’000 pageloads since I started blogging last December.
There are about 500 unique visitor and 100 returning visitors average every week coming from all over the world.

Thank you very much for your interest!
I’ll keep on blogging.

connect lutz hartmann as sysdba

Table Compression in Oracle 9iR2 and beyond

Starting with Oracle database 9iR2 (Enterprise Edition!)
it is possible to create compressed heap organized tables and compressed table partitions as well as compressed materialized views.

This feature is very interesting for data warehouses because it can save a lot of disk space.

Compression has already been availble for b-tree indexes and index organized tables
since Oracle 8.1.5.

Here is an example:

LUTZ @10gR2 SQL > CREATE TABLE compress1 (col1 NUMBER, col2 VARCHAR2(30)) COMPRESS;
Table created.

LUTZ @10gR2 SQL > CREATE TABLE not_compress1
(col1 NUMBER,
col2 VARCHAR2(30));
Table created.

LUTZ @10gR2 SQL > SELECT table_name, compression
FROM user_tables;

TABLE_NAME COMPRESS
—————————— ——–
NOT_COMPRESS1 DISABLED
COMPRESS1 ENABLED

LUTZ @10gR2 SQL > BEGIN FOR i IN 1..100 LOOP
INSERT INTO not_compress1 VALUES(i, ‘value’ || i);
END LOOP;
FOR i IN 1..14 LOOP
INSERT INTO not_compress1 SELECT * FROM not_compress1;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.

LUTZ @10gR2 SQL > SELECT COUNT(*) FROM not_compress1;
COUNT(*)
———-
819200

LUTZ @10gR2 SQL > SELECT COUNT(DISTINCT col2) FROM not_compress1;
COUNT(DISTINCTCOL2)
——————-
100

LUTZ @10gR2 SQL > INSERT /*+ APPEND*/
INTO compress1
SELECT *
FROM not_compress1;
819200 rows created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
(‘NOT_COMPRESS1′,
‘COMPRESS1′);

EXTENTS BYTES SEGMENT_NAME
———- ——- ——————-

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1

Serial INSERT with append hint, as well as Parallel INSERT (with parallel hint) and also
Direct path insert with SQL*Loader into segments with compression enabled cause compression.
Inserting into a compressed table by using an ORDER BY CLAUSE for the column with lowest cardinality (least distinct values) incerases the compression rate!

With the COMPRESS clause is also possible to
create a table with a subselect as a compressed segment:

SYSTEM @10gR2 SQL > CREATE TABLE compress2 COMPRESS
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > CREATE TABLE to_be_compressed AS
SELECT * FROM lutz.compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
(‘NOT_COMPRESS1′,
‘COMPRESS1′,
‘COMPRESS2′,
‘TO_BE_COMPRESSED’);

EXTENTS BYTES SEGMENT_NAME
———– ———– ——————————

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2

It is also possibel to create a tablespace with compression as default:

SYSTEM @10gR2 SQL > CREATE TABLESPACE lutz_compress
DATAFILE ‘/u01/app/oracle/oradata/orcl/lutz_compress.dbf’
SIZE 10m AUTOEXTEND ON
DEFAULT COMPRESS;
Tablespace created.

SYSTEM @10gR2 SQL > CREATE TABLE lutz.not_compress2
TABLESPACE lutz_compress
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
(‘NOT_COMPRESS1′,
‘COMPRESS1′,
‘COMPRESS2′,
‘TO_BE_COMPRESSED’,
‘NOT_COMPRESS2′);

EXTENTS BYTES SEGMENT_NAME
————- —————- —————————–
27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2
27 12582912 NOT_COMPRESS2

connect lutz hartmann as sysdba

This week in Baden Daettwil

This week I deliver in Baden Daettwil after a long periode of traveling.
I teach Oracle Database 10g: New Features for Oracle 8i DBAs.
This is a 5 day event and we walk through the entire process of
installation,
patching,
upgrading,
character set conversioning and
all the new features since Oracle 8.1.7.

This is a hard one to deliver because there are so many different topics to discuss.
We will have to really hurry up in order to cover all of it.

connect lutz hartmann as sysdba

Licensing information for Oracle 10gR2 Database and Oracle Secure Backup

Many of the Oracle 10g new features are only available in Enterprise Edition.
In order to find out which feature is included in which Oracle License you can go to the
Licensing Information Section in the
Online Documentation which is available at Oracle Technology Network (OTN).
Go here for licensing info for the database and
here for licensing information for Oracle Secure Backup.

connect lutz hartmann as sysdba

Heading back to Zurich

After three days of New Features
with a very nice group of very
highly skilled DBAs in London.

It was strange for me to switch to English English
after almost four weeks of American English.
Also I had to deal with people of a very
different mentality compared to the New Yorkers
and even more compared to the San Francisco People.

I will fly back to Zurich today.
The foto shows Juliet,. Phil,
David, Maulya,
Rajen, Rob and Pratab and me
at an Asian restaurant in London.

Ian Burgess

And again I got to know a very nice collegue
who I want to say thank you
to for being so very caring and helpful to me
when I felt so lost and locked out in a bazar location.
You are a real good fellow, Ian Burgess.

connect lutz hartmann as sysdba

DML ERROR LOGGING an ORACLE 10gR2 New Feature

If a constraint violation raises a runtime error during execution of a DML statement we encounter a statement level rollback.
Oracle server creates a so called implicit savepoint before it excutes a DML statement, to which it can automatically rollback.
This can be very time and resource consuming especially with bulk DML operations.

Starting with Oracle database 10g release 2 it is possible to let a DML statement continue without erroring out although constaint violations are created.
For this we can use the new PL/SQL-package DBMS_ERRLOG to create an error logging table which can be used to capture information about rows which have caused a constraint violation during DML-execution.
Additionally we can define a limit of violations which will cause the statement to fail.
If the rejected rows are less than the specified limit the statement will succeed despite the contraint violations.

Here is a little demo:
– create a test table
HR @10gR2 SQL > create table error_test
(
col1 number,
col2 number constraint error_check check (col2 > 100)
);
Table created.

– populate the test table
HR @10gR2 SQL > BEGIN
2 FOR i IN 100..200 loop
3 INSERT INTO error_test
4 VALUES (i, i+50);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.

– create the error log table
HR @10gR2 SQL > exec DBMS_ERRLOG.CREATE_ERROR_LOG(‘ERROR_TEST’,'error_log_tab’)
PL/SQL procedure successfully completed.

HR @10gR2 SQL > desc error_log_tab
Name Null? Type
—————————————– ——– —————–
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
COL1 VARCHAR2(4000)
COL2 VARCHAR2(4000)

– cause constraint violations less than reject limit
HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 80;
50 rows updated.
– look at the error loggin table
HR @10gR2 SQL > SELECT ora_err_number$,
ora_err_mesg$,
ora_err_rowid$,
col1,
col2
FROM error_log_tab;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ COL1 COL2
————— —————————————————- ———————- ———- ———-
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAA 100 50
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAB 101 51
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAC 102 52
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAD 103 53
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAE 104 54
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAF 105 55
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAG 106 56
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAH 107 57
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAI 108 58
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAJ 109 59
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAK 110 60
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAL 111 61
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAM 112 62
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAN 113 63
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAO 114 64
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAP 115 65
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAQ 116 66
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAR 117 67
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAS 118 68
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAT 119 69
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAU 120 70
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAV 121 71
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAW 122 72
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAX 123 73
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAY 124 74
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAZ 125 75
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAa 126 76
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAb 127 77
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAc 128 78
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAd 129 79
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAe 130 80
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAf 131 81
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAg 132 82
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAh 133 83
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAi 134 84
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAj 135 85
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAk 136 86
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAl 137 87
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAm 138 88
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAn 139 89
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAo 140 90
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAp 141 91
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAq 142 92
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAr 143 93
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAs 144 94
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAt 145 95
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAu 146 96
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAv 147 97
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAw 148 98
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAx 149 99
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAy 150 100

51 rows selected.
– rollback
HR @10gR2 SQL > rollback;

– cause constraint violations more than reject limit
HR @10gR2 SQL > HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 20;
UPDATE error_test
*
ERROR at line 1:
ORA-02290: check constraint (HR.ERROR_CHECK) violated

connect lutz hartmann as sysdba

Arrived in London

I have just arrived in London City.
This week I will deliver another customized
10gR2 New Features for Administrators
course as a private event.

I am just checking out the training center
and prepare my environment for tomorrow and then I’ll take a look around the city.

connect lutz hartmann as sysdba

DOAG sent my article after me

The DOAG News has sent a copy of the latest issue
with my articel after me to my hotel in
California. It is the Greman version of my article about the Shared Pool Size implementation in Oracle 10g.
Thank you very much for this first class service!
And greetings from Redwood Shores to Berlin.

connect lutz hartmann as sysdba

Heading back to Europe

Yesterday I had a last meal with the two project managers of the
Real Application Cluster course who have delivered the
RAC 10g pilot class for release 2, Jean-Francois Verrier and Jim Womack.
The foto shows J.F., Jim, my very dear collegue from Düsseldorf Michael Cebulla and me on Friday after we had gone to the movy theaters (Da Vinci Code) and had dinner together afterwards.

I am at SanFrancisco Airport right now,
waiting for my flight to Munich to depart.
Have just waved goddbye to Michael at the gate. He is on his way back to Düsseldorf already now.

I was so happy to find Michael in San Francisco also attending the same RAC pilot class with me. We had a fun time for five days.

connect lutz hartmann as sysdba

Demos @ Oracle By Example on OTN

There is new site on Oracle Technology Network (OTN) with a large number of very up to date demos for best practices and new features for ORACLE 10gR2 real application clusters .

At the moment you find these 10gR2 demos under the link for Database 10gR1 in the Oracle By Example Series.

Here is a list of the demos you can find there at the moment, there will be more comming up:

-Using SQL Baselines
-Using Metric Baselines
-Install ASM single instance in its own home
-Install ASM single instance in the same home
-Migrate a database to ASM
-Setup XML DB to access ASM
-Access ASM files using ASMCMD
-Transport a tablespace version to another database
-RAC scale example
-RAC speedup example
-Use Oracle Clusterware to protect the apache application
-Use Oracle Clusterware to protect the Xclock application
-Use Transparent Application Failover (TAF) with SELECT statements
-Runtime Connection Load Balancing example
-Install ASM in its own home in a RAC environment
-Convert a single-instance database to a RAC database using Grid Control
-Push Management Agent software using Grid Control
-Clone Oracle Clusterware to extend your cluster using Grid Control
-Clone ASM home to extend your cluster using Grid Control
-Clone database home to extend your cluster using Grid Control
-Add a database instance to your RAC database using Grid Control
-Basic use of services in your RAC environment

connect lutz hartmann as sysdba