Oracle database 11g available now on Linux & Windows

Oracle 11g database software is downloadable from edelivery  as well from Oracle Technology Network for the following platforms:

Linux x86 (as of August 14th. 2007)
Linux x86-64bit ( October 30th. 2007)
Windows 32 bit ( October 25th. 2007)

How to crack Oracle passwords within seconds?

Yesterday The Hackers’ Choice organization published a paper that shows how to break into an Oracle 10g database very easily.

And there is not only the paper, but “…THC further releases practical tools to sniff and crack the password of an oracle database within seconds…” all you need to know is how to use them.

Is there a CHECKPOINT with SHUTDOWN IMMEDIATE?

Today I presented a little 2 hours presentation for some DBAs at Turkcell Academy.

I introduced them to the infrastructure of ASM and explained Oracle’s unique locking concept.

After this  Tonguç and his brothers invited me for a drink and dinner downtown and we had some intersting discussions.

One interesting questions we talked about was what happens exactly with a SHUTDOWN IMMEDIATE – is there a checkpoint before shutdown or is it just a flushing of the redolog buffer to the logfiles?

Here is the proof for the CHECKPOINT happening before shutdown:

SYS AS SYSDBA @ lutz1 SQL> create table t as select * from all_objects;

Table created.

SYS AS SYSDBA @ lutz1 SQL> select current_scn from v$database;

CURRENT_SCN
———–
     202799

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             197655
         2             197655
         3             197655
         4             197655

SYS AS SYSDBA @ lutz1 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA @ lutz1 SQL> startup mount
ORACLE instance started.

Total System Global Area  507510784 bytes
Fixed Size                  1220264 bytes
Variable Size             155189592 bytes
Database Buffers          348127232 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS AS SYSDBA @ lutz1 SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203071
         2             203071
         3             203071
         4             203071

SYS AS SYSDBA @ lutz1 SQL> alter database open;

Database altered.

SYS AS SYSDBA @ lutz1 SQL>  select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
———- ——————
         1             203072
         2             203072
         3             203072
         4             203072

SYS AS SYSDBA @ lutz1 SQL>

#######here is the content of the alertlog for the startup
####### as you can see there is no instance recovery!!!
####### there was a checkpoint before shutdown!!

Completed: ALTER DATABASE   MOUNT
Fri May 18 01:13:36 2007
alter database open
Fri May 18 01:13:36 2007
Thread 1 opened at log sequence 12
  Current log# 3 seq# 12 mem# 0: +DATA/lutz1/onlinelog/group_3.286.622819249
  Current log# 3 seq# 12 mem# 1: +FRA/lutz1/onlinelog/group_3.259.622819253
Successful open of redo thread 1
Fri May 18 01:13:36 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 18 01:13:36 2007
SMON: enabling cache recovery
Fri May 18 01:13:38 2007
Successfully onlined Undo Tablespace 1.
Fri May 18 01:13:38 2007
SMON: enabling tx recovery
Fri May 18 01:13:38 2007
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=4284
Fri May 18 01:13:44 2007
Completed: alter database open

DBMS_SERVICE and net service names

Oracle 10gR1 was the first release to come with the package DBMS_SERVICE.
This package can be used to manage services for single instances.
There are procedures for creation , activation, deactivation, and removing of services.
We can measure workloads and limit resources through services and there are more and more concepts which are using services.
A RAC has the capability of managing services across instances.

The package contains a number of constants, exceptions and subprogramms.

Here is an example for the creation of a service on a single instance:

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=> ‘HALLO’, NETWORK_NAME=> ‘OTTO’)
PL/SQL procedure successfully completed.

SQL> exec dbms_service.START_SERVICE(‘HALLO’)
SQL> show parameter service
NAME TYPE VALUE
———————————— ———– ——————————
service_names string OTTO

The confusing thing here is that the parameter NETWORK_NAME is in fact the value returned in the initialization parameter SERVICE_NAMES! And the parameter SERVICE_NAME is the values used for the net service name in tnsnames.ora!!This is crazy I think! How confusing and misleading this is!Therefore you should use the same value for both parameters in the call of the procedure!

connect lutz hartmann as sysdba

Tablespace 101% full!

Today I had a strange effect in my DBA class:
I filled up my UNDO TABLESPACE which has only one non autoextensible datafile and waited for the alert to show up in DATABASE CONTROL console.
And it did.
See what it showed: => tablespace 101% full.
How can this be?
This is funny, eh!

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

How to check if a tablespace is transportable

In order to find out if a tablespace or a tablespace set is transportable you can use the package DBMS_TTS.

This is the interface of the package:
sys@10gr2 SQL> desc dbms_tts
PROCEDURE DOWNGRADE
FUNCTION ISSELFCONTAINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN
FULL_CHECK BOOLEAN IN
PROCEDURE KCP_CKCMP
FUNCTION TRANSPORT_CHAR_SET_CHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
ERR_MSG VARCHAR2 OUT
PROCEDURE TRANSPORT_CHAR_SET_CHECK_MSG
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
PROCEDURE TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT

This is how you use the package:
sys@10gr2 SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK(‘LUTZ_TS’, TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK(‘SYSTEM‘, TRUE)
BEGIN dbms_tts.TRANSPORT_SET_CHECK(‘SYSTEM’, TRUE); END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 899
ORA-06512: at “SYS.DBMS_SQL”, line 19
ORA-06512: at “SYS.DBMS_TTS”, line 838
ORA-29351: can not transport system, sysaux, or temporary tablespace ‘SYSTEM’
ORA-06512: at “SYS.DBMS_TTS”, line 867
ORA-06512: at line 1

After you have checked the tablespace with the procedure TRANSPORT_SET_CHECK you can use a table call transport_set_violations which is owned by SYS to look at error messages from the procedure.
This table will contain the messages from the last run of the procedure and it will retained only for the duration of the session.
In other words, it will be refreshed with every new session.

sys@10gr2 SQL> desc transport_set_violations
Name Null? Type
—————————————– ——– —————————- VIOLATIONS VARCHAR2(2000)

Now SYS creates a table in the tablespace I want to check …
sys@10gr2 SQL> create table test_sys tablespace lutz_ts as select * from hr.test;
Table created.

And I check if the tablspace is transportable…

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK(‘LUTZ_TS’, TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
——————————————————————————–Sys owned object TEST_SYS in tablespace LUTZ_TS not allowed in pluggable set

This would be the error message if the tablespace is not self containing…

sys@10gr2 SQL> sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
——————————————————————————–
Constraint TESXT_FK between table SYS.TEST_SYS in tablespace USERS and table HR.
TEST in tablespace LUTZ_TS

Starting with Oracle 10gR2 we can create transportable tablespaces from backup.

connect lutz hartmann as sysdba

deferred constraints part 2

What you have to be aware of when you create a deferrable primary key:

- the system will need an index for the primary key as you know
- if there already is an index for the column you want to add a primary key constraint to, then
- the server will check, if the index is usable state
- it will check if the index is a unique index (does not allow duplicate values)
- if the index is a unique index the server will check if the primary key you want ot add is deferrable
- if the existing index is unique and the primary key you want to add is not deferrable ( which is the default , remember),
then the existing index can be used for the primary key constraint
- if the existing is unique but the primary key you create is deferrable, the existing index cannot be used and needs to be dropped first, before a non unique index is created (an index which allows duplicate values).

Both, the dropping and the creating of the index might significantly consume time on the system!!
You should thing deep enough before you create a deferrable primary key. Maybe it is sufficiently enough for your application that the foreing key is deferrable and you do not need to create a deferrable primary key and so can use an already existing index.

connect lutz hartmann as sysdba

deferred constraints part 1

Some interesting facts about constraints:
If you must update the primary key and the foreign key
and they are located in different tables then you can get into troubles:
If yo disable the foreign key it can happen that you will not be able to enable tit anymore because somebody has messed around with the values while it was disabled. You could get a full table lock to prevent this but this might be difficult also.

Today we have had a closer look at deferrable constraints.
These can be a good alternative for disabeling constraints for a cascading UPDATE.

It is essential to create the constraint as DEFERRABLE right away when creating it, if you want to be able to deferre the constraint chaeck to the end of the transaction.
This is not modifyable afterwards.
If you do not specify DEFERRABLE INITIALLY DEFEERED as creation time but only specify DEFERRABLE ,
then DEFERRABLE INITIALLY IMMEDIATE is the default.

Let’s see how they work:
PREREQUISITS:
- first I create a Primary key and a foreign key relationship.

SQL> ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (department_id);
Table altered.

SQL> ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (department_id)
2 REFERENCES DEPT (department_id)
3 DEFERRABLE;
Table altered.

SQL> SELECT constraint_name, search_condition, constraint_type, deferrable, deferred
2 FROM user_constraints
3* WHERE table_name=’EMP’

CONSTRAINT_NAME SEARCH_CONDITION C DEFERRABLE DEFERRED
—————————— —————————— – ————– ———
SYS_C002768 “LAST_NAME” IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002769 “EMAIL” IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002770 “HIRE_DATE” IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002771 “JOB_ID” IS NOT NULL C NOT DEFERRABLE IMMEDIATE
EMP_FK R DEFERRABLE IMMEDIATE
EMP_PK P NOT DEFERRABLE IMMEDIATE
EMP_FK_MGR R NOT DEFERRABLE IMMEDIATE
CHECK_SAL “SAL”>500 C NOT DEFERRABLE IMMEDIATE

DEMO 1:
- then I try my updates on the key and a foreign key, does not work of course
- now I set the deferrable constraints for my session to deferred
- then I try my updates on the key and a foreign key again and it works,
because the constraint-checking is deferred to the end of the transaction
- now I make a critical update which will cause a constraint volation at commit time
this will cause a TARNSACTION LEVEL ROLLBACK at commit time!!!!

DEMO 2:
- I do the same steps as in DEMO 1, only that I change the state of my deferrable constraints to immediate checking before my critical update.
=> this causes a STATEMENT LEVEL ROLLBACK right after the statement is processed,
the other two updates if issued before this are not rolled back this time!!!!!!

This is DEMO 1:

SQL> UPDATE emp SET department_id=department_id+1000;
UPDATE emp SET department_id=department_id+1000
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_FK) violated – parent key not found

SQL> UPDATE dept SET department_id=department_id+1000;
UPDATE dept SET department_id=department_id+1000
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_FK) violated – child record found

SQL> alter session set constraints=deferred;

SQL> UPDATE emp SET department_id=department_id+1000;
107 rows updated.

SQL> update dept set department_id=department_id+1000;
27 rows updated.

SQL> update dept set department_id=8888 where department_id=1090; # this is the critical update
1 row updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (HR.EMP_FK) violated – child record found

This is DEMO 2:

SQL> update dept set department_id=department_id+1000;

27 rows updated.

SQL> update emp set department_id=department_id+1000;

107 rows updated.

SQL> alter session set constraints=immediate;

Session altered.

SQL> update dept set department_id=8888 where department_id=1090;
update dept set department_id=8888 where department_id=1090
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_FK) violated – child record found

Some very good examples for the usage of deferred constraints you can find at asktom.oracle.com

connect lutz hartmann as sysdba

Oracle Block Size Specials

Did you know that Linux x86 and Sun Solaris
are not capable of Blocks with sizes larger than 16k?

This is what happened to me yesterday on an Linux box with Red Hat Advanced Server 3:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining option s
JServer Release 9.2.0.4.0 – Production

SQL> alter system set db_32k_cache_size = 32M;

alter system set db_32k_cache_size = 32M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00382: 32768 not a valid block size, valid range [2048..16384]

And here is what Metalink says:

Doc ID:	Note:138242.1
Subject:	9i: ORA-382 Creating 32K Blocksize Tablespace and Related Cache Not Set
Type:	PROBLEM
Status:	PUBLISHED

This limitation is not an Oracle9i restriction, but is operating system -specific. Refer to the platform specifications for your operating system.

Oracle9i Administrator’s Reference     Release 1 (9.0.1) for UNIX Systems:

AIX-Based Systems,Compaq Tru64 UNIX,HP 9000 Series HP-UX,Linux Intel,Sun SPARC Solaris:

DB_BLOCK_SIZE:2048 to 16384 (Linux, Solaris)2048 to 32768 (AIX, HP, Tru64)

Oracle9i Database Administrator's Guide for Windows:

DB_BLOCK_SIZE: 2048 to 16384(Windows NT, 95, 98, 2000)

Have you ever created a tablespace
with a blocksize of -1 bytes on an Oracle database?
I had this recently with Oracle 9.2.0.4
(this was a patch with optimization potential I guess, hope it was fixed).
Is this a black hole, or is it some kind of anti-data you can store there?

connect lutz hartmann as sysdba