Ural URAL's Oracle Blog

How to configure Oracle Streams for table replication

2011-09-27T03:33:00.000-07:00

Below post is a sample case which explains how to configure Oracle Streams for table replication in an Oracle 10gR2 database. It will use asynchronous downstream capture method when replicating a table from one database to another database. This means archive log files generated in the source database will be implicitly copied to the target database asynchronously and they will be processed in the target database to capture the changes related to the replicated table and they will be applied there.

First you need to create a new user who is the Streams administrator in both source and target databases. You also create a separate tablespace for this new user's objects.

Below command should be executed in both the source and the target databases.

To create a new tablespace
If you are using ASM with OMF(Oracle Managed Files) and ASSM (Automatic Segment Space Management), use the below statement.

create tablespace streams_tbs;

if not, use the below statement.

CREATE TABLESPACE streams_tbs DATAFILE '/usr/oracle/dbs/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Ceate the Streams admin user

CREATE USER strmadmin
IDENTIFIED BY "strmadminpwd"
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
GRANT DBA TO strmadmin;

The source database should be in archivelog mode to generate files containing the change records happened in this database.

You can check with the following sql whether the source database is in archivelog mode or not.

--@db1
select log_mode from v$database;
--ARCHIVELOG

Next you should create an ANYDATA queue in the target database to associate with the capture process.
If the specified queue table does not exist, then it is created. If the specified queue table exists, then the existing queue table is used for the new queue. If you do not specify any queue table when you create the queue, then, by default, streams_queue_table is specified.
You can use a single procedure, the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, to create an ANYDATA queue and the queue table used by the queue. For SET_UP_QUEUE to create a new queue table, the specified queue table must
not exist.

--strmadmin@db2
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue',
queue_user => 'd_dba');
END;
/

"queue_user" parameter is the user who is privileged to use this queue, in our case it is "d_dba" database user which we will replicate a table of this user.

You can use the following sqls to check the newly created queue.

select * from dba_queue_tables where owner='STRMADMIN'; 
/*
OWNER,QUEUE_TABLE,TYPE,OBJECT_TYPE,SORT_ORDER,RECIPIENTS,MESSAGE_GROUPING,COMPATIBLE,PRIMARY_INSTANCE,SECONDARY_INSTANCE,OWNER_INSTANCE,USER_COMMENT,SECURE
STRMADMIN,STREAMS_QUEUE_TABLE,OBJECT,SYS.ANYDATA,COMMIT_TIME,MULTIPLE,TRANSACTIONAL,10.0.0,0,0,1,,YES
*/
select * from dba_queues where owner='STRMADMIN'; 
/*
OWNER,NAME,QUEUE_TABLE,QID,QUEUE_TYPE,MAX_RETRIES,RETRY_DELAY,ENQUEUE_ENABLED,DEQUEUE_ENABLED,RETENTION,USER_COMMENT,NETWORK_NAME
STRMADMIN,STREAMS_QUEUE,STREAMS_QUEUE_TABLE,133367,NORMAL_QUEUE,5,0, YES , YES ,0,,
STRMADMIN,AQ$_STREAMS_QUEUE_TABLE_E,STREAMS_QUEUE_TABLE,133366,EXCEPTION_QUEUE,0,0, NO , NO ,0,exception queue,
*/
select * from dba_rulesets where owner='STRMADMIN'; 
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/

We should add the tns alias of the target database to the "tnsnames.ora" file in source database. So that we will use this name for implicit archivelog file transfer to the target database.

--@db1srv
vi $TNS_ADMIN/tnsnames.ora

--Add these lines
### STREAMS CONFIG ###
DB2 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB2SRV)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=DB2)
)
)
### END STREAMS CONFIG ###

In the target database's ASM create the folder structure where the newly created archivelogs will be copied.

--@db2srv
asmcmp -p
ASMCMD [+DG_DB_ASM_DB2/DB2] > mkdir ARCHIVELOG
ASMCMD [+DG_DB_ASM_DB2/DB2] > cd ARCHIVELOG
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG] > mkdir FROM_DB1
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG] > cd FROM_DB1
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] > ls -l
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] >

In the source database configure the log_archive_dest_2 parameter and enable it so that it will point to and send the generated archivelogs to the destination database.

alter system set LOG_ARCHIVE_DEST_2='SERVICE=DB2 ASYNC NOREGISTER 
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
TEMPLATE=+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1/%t_%s_%r.dbf 
DB_UNIQUE_NAME=DB2'
scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';

Try to generate a new archive in source database and check if it is copied to the destination database ASM or not.

--@db1
alter system switch logfile;

--@db2srv
asmcmp -p
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] > ls -l
Type Redund Striped Time Sys Name
N 1_200291_699577217.dbf => +DG_DB_ASM_DB2/DB2/ARCHIVELOG/2011_09_22thread_1_seq_200291.296.762521353

We should add the tns alias of the source database to the "tnsnames.ora" file in target database. So that we will use this name for implicit archivelog file transfer to the target database.

--@db2srv

vi $TNS_ADMIN/tnsnames.ora

--Add these lines
### STREAMS CONFIG ###
DB1 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB1SRV)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=DB1)
)
)
### END STREAMS CONFIG ###

--test this tns alias if it is reachable or not
--@db2srv
tnsping DB1
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 22-SEP-2011 15:48:03
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/oracle/asmhome1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=DB1SRV) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=DB1)))
OK (0 msec)

Create a database link in strmadmin schema in target database, we will use this later.

--strmadmin@db2
CREATE DATABASE LINK db1
CONNECT TO strmadmin
IDENTIFIED BY "strmadminpwd"
USING 'DB1';

--Test it if it works or not
select sysdate from dual@db1;
--OK

While connected to the downstream database as the Streams administrator, run
the CREATE_CAPTURE procedure to create the capture process:

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'strmadmin.streams_queue',
capture_name => 'strm04_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'db1',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/
SELECT CAPTURE_NAME, FIRST_SCN, MAX_CHECKPOINT_SCN FROM DBA_CAPTURE;
/*
CAPTURE_NAME,FIRST_SCN,MAX_CHECKPOINT_SCN
STRM04_CAPTURE,74313112953,0
*/

We created a capture process called "strm04_capture".

This step does not associate the capture process strm04_capture with any rule
set. A rule set will be created and associated with the capture process in the next
step.
First we need to create the table in source database which will be replicated to the destination database.

--sys@db1
create table d_dba.streams_test_uural(col1 number, col2 varchar2(100), a_date date);
insert into d_dba.streams_test_uural(col1, col2, a_date) values (1,'1',sysdate);
commit;

While connected to the downstream database as the Streams administrator, create
the positive rule set for the capture process and add a rule to it:

--strmadmin@db2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'd_dba.streams_test_uural',
streams_type => 'capture',
streams_name => 'strm04_capture',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true);
END;
/
select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
*/

You should set minimum supplemental log data in the source database (SUPPLEMENTAL_LOG_DATA_MIN), so that you can enable the table level supplemental logging in the source database.

--sys@db1
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;
/*
SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI
NO,NO,NO
*/
alter database add supplemental log data;
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;
/*
SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI
YES,NO,NO
*/

Now you should instantiate the source table

--strmadmin@db1
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name => 'd_dba.streams_test_uural',
supplemental_logging => 'keys');
END;
/
/*
DROP TABLE D_DBA.STREAMS_TEST_UURAL CASCADE CONSTRAINTS;
CREATE TABLE D_DBA.STREAMS_TEST_UURAL
(
COL1 NUMBER,
COL2 VARCHAR2(100 BYTE),
A_DATE DATE,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
)
TABLESPACE TS_DBA
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
*/

By doing this you enabled the primary key, unique key and foreign key supplemental logging on the source table, so that if any of these key exists on this table the supplemental log data can be captured by using one one these key columns in the destination database.
If your table does not have any key columns like here then you should enable the "all" columns supplemental log data for this table to be place in to the newly generated archivelog files.

Check the current supplemental log data activated for this table and then add the all columns supplemental log data to this table.

Check activated supplemental logging

select * from dba_log_groups order by owner, table_name, log_group_type;
/*
OWNER,LOG_GROUP_NAME,TABLE_NAME,LOG_GROUP_TYPE,ALWAYS,GENERATED
D_DBA,SYS_C0043369,STREAMS_TEST_UURAL,FOREIGN KEY LOGGING,CONDITIONAL,GENERATED NAME
D_DBA,SYS_C0043367,STREAMS_TEST_UURAL,PRIMARY KEY LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043368,STREAMS_TEST_UURAL,UNIQUE KEY LOGGING,CONDITIONAL,GENERATED NAME
*/

alter table d_dba.streams_test_uural add supplemental log data (ALL) columns;

select * from dba_log_groups order by owner, table_name, log_group_type;
/*
OWNER,LOG_GROUP_NAME,TABLE_NAME,LOG_GROUP_TYPE,ALWAYS,GENERATED
D_DBA,SYS_C0043423,STREAMS_TEST_UURAL,ALL COLUMN LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043369,STREAMS_TEST_UURAL,FOREIGN KEY LOGGING,CONDITIONAL,GENERATED NAME
D_DBA,SYS_C0043367,STREAMS_TEST_UURAL,PRIMARY KEY LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043368,STREAMS_TEST_UURAL,UNIQUE KEY LOGGING,CONDITIONAL,GENERATED NAME
*/

Now we should create the replicated table in the target database as an empty table.
Instantiate the table in DB2

create table d_dba.streams_test_uural nologging
as select /*+ parallel(t1, 4) */ * from d_dba.streams_test_uural@db1 t1;

Create a database link in source database pointing to the target database

--strmadmin@db1 
CREATE DATABASE LINK db2
CONNECT TO strmadmin
IDENTIFIED BY "strmadminpwd"
USING 'DB2';
select sysdate from dual@db2;
--OK

Set the instantiation SCN for the "d_dba.streams_test_uural" table at db2 by running the following procedure at the source database db1:

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@db2(
source_object_name => 'd_dba.streams_test_uural',
source_database_name => 'db1',
instantiation_scn => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the "d_dba.streams_test_uural" table from the streams_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object. When all of the necessary propagations and apply processes are configured, start the capture process using the START_CAPTURE procedure in DBMS_CAPTURE_ADM.

--strmadmin@db2
select * from dba_apply_instantiated_objects;
/*
SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,SOURCE_OBJECT_TYPE,INSTANTIATION_SCN,IGNORE_SCN,APPLY_DATABASE_LINK
DB1,D_DBA,STREAMS_TEST_UURAL,TABLE,74313116531,0,
*/

select * from dba_capture;
/*
CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,RULE_SET_NAME,RULE_SET_OWNER,CAPTURE_USER,START_SCN,STATUS,CAPTURED_SCN,APPLIED_SCN,USE_DATABASE_LINK,FIRST_SCN,SOURCE_DATABASE,SOURCE_DBID,SOURCE_RESETLOGS_SCN,SOURCE_RESETLOGS_TIME,LOGMINER_ID,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,MAX_CHECKPOINT_SCN,REQUIRED_CHECKPOINT_SCN,LOGFILE_ASSIGNMENT,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE,VERSION,CAPTURE_TYPE,LAST_ENQUEUED_SCN,CHECKPOINT_RETENTION_TIME
STRM04_CAPTURE,STREAMS_QUEUE,STRMADMIN,RULESET$_2,STRMADMIN,STRMADMIN,74313112953,DISABLED,,,YES,74313112953,DB1,0,0,0,1,,,0,0,IMPLICIT,22/09/2011 17:52:51,,,,DOWNSTREAM,,60
*/
--STATUS=DISABLED

Creating an Apply Process for Captured Messages with DBMS_APPLY_ADM

BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'strmadmin.streams_queue',
apply_name => 'strm04_apply',
rule_set_name => 'strmadmin.ruleset$_2',
message_handler => NULL,
ddl_handler => NULL,
apply_user => 'd_dba',
apply_database_link => NULL,
apply_tag => NULL,
apply_captured => true,
precommit_handler => NULL,
negative_rule_set_name => NULL,
source_database => 'db1');
END;
/
select * from dba_apply_error;
--Nrr
select * from dba_apply_progress;
/*
APPLY_NAME,SOURCE_DATABASE,APPLIED_MESSAGE_NUMBER,OLDEST_MESSAGE_NUMBER,APPLY_TIME,APPLIED_MESSAGE_CREATE_TIME,OLDEST_TRANSACTION_ID
STRM04_APPLY,DB1,0,0,01/01/1988,01/01/1988,
*/
select * from sys.streams$_apply_progress; --holds historical data of the apply process

Starting an Apply Process

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'strm04_apply');
END;
/

Starting a Capture Process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'strm04_capture');
END;
/

Check the views

select * from dba_capture;
/*
CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,RULE_SET_NAME,RULE_SET_OWNER,CAPTURE_USER,START_SCN,STATUS,CAPTURED_SCN,APPLIED_SCN,USE_DATABASE_LINK,FIRST_SCN,SOURCE_DATABASE,SOURCE_DBID,SOURCE_RESETLOGS_SCN,SOURCE_RESETLOGS_TIME,LOGMINER_ID,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,MAX_CHECKPOINT_SCN,REQUIRED_CHECKPOINT_SCN,LOGFILE_ASSIGNMENT,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE,VERSION,CAPTURE_TYPE,LAST_ENQUEUED_SCN,CHECKPOINT_RETENTION_TIME
STRM04_CAPTURE,STREAMS_QUEUE,STRMADMIN,RULESET$_2,STRMADMIN,STRMADMIN,74313112953,ENABLED,74313112953,74313112953,YES,74313112953,DB1,2977989118,538113,699577217,1,,,74318832255,0,IMPLICIT,23/09/2011 11:23:51,,,10.2.0.4.0,DOWNSTREAM,0,60
*/

--STATUS=ENABLED

select * from dba_apply;
/*
APPLY_NAME,QUEUE_NAME,QUEUE_OWNER,APPLY_CAPTURED,RULE_SET_NAME,RULE_SET_OWNER,APPLY_USER,APPLY_DATABASE_LINK,APPLY_TAG,DDL_HANDLER,PRECOMMIT_HANDLER,MESSAGE_HANDLER,STATUS,MAX_APPLIED_MESSAGE_NUMBER,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE
STRM04_APPLY,STREAMS_QUEUE,STRMADMIN,YES,RULESET$_2,STRMADMIN,D_DBA,,,,,,ENABLED,,,,23/09/2011 11:22:19,,
*/
select * from d_dba.streams_test_uural;
/*
COL1,COL2,A_DATE
1,1,22/09/2011 17:49:55
*/

Now try to add more rows to the source table in the source database and check whether they will be replicated to the target database or not.

--sys@db1
insert into d_dba.streams_test_uural(col1, col2, a_date) values (2,'2',sysdate);
commit;
alter system switch logfile;
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
--74318845118
select max(sequence#) from v$archived_log where next_change# <= 74318845118; 
--200551

--strmadmin@db2
select * from v$streams_capture;
/*
SID,SERIAL#,CAPTURE#,CAPTURE_NAME,LOGMINER_ID,STARTUP_TIME,STATE,TOTAL_PREFILTER_DISCARDED,TOTAL_PREFILTER_KEPT,TOTAL_PREFILTER_EVALUATIONS,TOTAL_MESSAGES_CAPTURED,CAPTURE_TIME,CAPTURE_MESSAGE_NUMBER,CAPTURE_MESSAGE_CREATE_TIME,TOTAL_MESSAGES_CREATED,TOTAL_FULL_EVALUATIONS,TOTAL_MESSAGES_ENQUEUED,ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBER,ENQUEUE_MESSAGE_CREATE_TIME,AVAILABLE_MESSAGE_NUMBER,AVAILABLE_MESSAGE_CREATE_TIME,ELAPSED_CAPTURE_TIME,ELAPSED_RULE_TIME,ELAPSED_ENQUEUE_TIME,ELAPSED_LCR_TIME,ELAPSED_REDO_WAIT_TIME,ELAPSED_PAUSE_TIME,STATE_CHANGED_TIME
506,230,1,STRM04_CAPTURE,1,23/09/2011 11:23:51,CAPTURING CHANGES,6360288,0,6360289,157710,23/09/2011 11:25:49,74314650119,23/09/2011 06:55:23,157797,43,44,23/09/2011 11:24:35,74313260014,23/09/2011 00:24:42,74318845076,23/09/2011 10:46:45,11105,0,22,95,0,0,23/09/2011 11:25:49
*/
select * from d_dba.streams_test_uural;
/*
COL1,COL2,A_DATE
1,1,22/09/2011 17:49:55
2,2,23/09/2011 10:46:36
*/

As a result we see that newly inserted row in source table is replicated to the target database.We can check the apply errors, if there are any, by using the below sql in the target database.

select * from dba_apply_error;
--No rows returned

Above example only replicates the data changes in the source table to the destination table, we can also capture and apply the DDL changes happening on the source table to the destination table. You can use the following commands to achive this.Add DDL capture rule to existing table ruleset :

--strmadmin@db2
select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
*/

Now we add a new DDL rule to the existing table rule by using the parameter "include_ddl => true"

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'd_dba.streams_test_uural',
streams_type => 'capture',
streams_name => 'strm04_capture',
queue_name => 'strmadmin.streams_queue',
include_dml => false,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true);
END;
/

Running this procedure performs the following actions:¦ Creates two rules. One rule evaluates to TRUE for DML changes to thed_dba.streams_test_uural table, and the other rule evaluates to TRUE for DDL changes to the d_dba.streams_test_uural table. The rule names are system generated.¦ Adds the two rules to the positive rule set associated with the capture processbecause the inclusion_rule parameter is set to true.¦ Prepares the d_dba.streams_test_uural table for instantiation by running the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package.¦ Enables supplemental logging for any primary key, unique key, bitmap index,and foreign key columns in the d_dba.streams_test_uural table. When the PREPARE_TABLE_INSTANTIATION procedure is run, the default value (keys) is specifiedfor the supplemental_logging parameter.If the capture process is performing downstream capture, then the table is preparedfor instantiation and supplemental logging is enabled for key columns only if thedownstream capture process uses a database link to the source database. If adownstream capture process does not use a database link to the source database, thenthe table must be prepared for instantiation manually and supplemental logging mustbe enabled manually.

select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
STRMADMIN,STREAMS_TEST_UURAL3,(((:ddl.get_object_owner() = 'D_DBA' and :ddl.get_object_name() = 'STREAMS_TEST_UURAL')or (:ddl.get_base_table_owner() = 'D_DBA' and :ddl.get_base_table_name() = 'STREAMS_TEST_UURAL')) and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL3,ENABLED,,,
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL3,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
*/

Lets test the DDL capture :

--sys@db1
alter table d_dba.streams_test_uural drop column col2;
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
--74318863000
alter system switch logfile;
select max(sequence#) from v$archived_log where next_change# <= 74318863000; 
--200556

--strmadmin@db2
select * from v$streams_capture;
/*
SID,SERIAL#,CAPTURE#,CAPTURE_NAME,LOGMINER_ID,STARTUP_TIME,STATE,TOTAL_PREFILTER_DISCARDED,TOTAL_PREFILTER_KEPT,TOTAL_PREFILTER_EVALUATIONS,TOTAL_MESSAGES_CAPTURED,CAPTURE_TIME,CAPTURE_MESSAGE_NUMBER,CAPTURE_MESSAGE_CREATE_TIME,TOTAL_MESSAGES_CREATED,TOTAL_FULL_EVALUATIONS,TOTAL_MESSAGES_ENQUEUED,ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBER,ENQUEUE_MESSAGE_CREATE_TIME,AVAILABLE_MESSAGE_NUMBER,AVAILABLE_MESSAGE_CREATE_TIME,ELAPSED_CAPTURE_TIME,ELAPSED_RULE_TIME,ELAPSED_ENQUEUE_TIME,ELAPSED_LCR_TIME,ELAPSED_REDO_WAIT_TIME,ELAPSED_PAUSE_TIME,STATE_CHANGED_TIME
506,230,1,STRM04_CAPTURE,1,23/09/2011 11:23:51,WAITING FOR REDO: LAST SCN MINED 74318863003,18339223,0,18339230,224424,23/09/2011 17:15:01,74318863003,23/09/2011 17:14:50,224545,45,82,23/09/2011 17:15:33,74318863003,,74318863003,23/09/2011 17:14:58,25043,0,23,107,2087402,0,23/09/2011 17:16:03
*/
select * from d_dba.streams_test_uural;
/*
COL1,A_DATE
1,22/09/2011 17:49:55
3,23/09/2011 11:30:20
*/

OK , col2 column is dropped in destination db2 database alsoOther streams commands that you might need :

--Removing a Rule from a Rule Set for a Capture Process
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'departments3',
streams_type => 'capture',
streams_name => 'strm01_capture',
drop_unused_rule => true,
inclusion_rule => true);
END;
/

--Removing a Rule Set for a Capture Process
BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
capture_name => 'strm01_capture',
remove_rule_set => true,
remove_negative_rule_set => true);
END;
/

--Setting a Capture Process Parameter
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'strm01_capture',
parameter => 'parallelism',
value => '3');
END;
/

--Removing a Streams Configuration
You run the REMOVE_STREAMS_CONFIGURATION procedure in the DBMS_STREAMS_ADM package to remove a Streams configuration at the local database.
To remove the Streams configuration at the local database, run the following
procedure:
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
After running this procedure, drop the Streams administrator at the database, if
possible.
Attention: Running this procedure is dangerous. You should run
this procedure only if you are sure you want to remove the entire
Streams configuration at a database.

Why we should put index creation statements at the end of the table transfer scripts

2011-07-22T07:15:00.000-07:00

Try to put the index creation statements at the end of the table transfer scritps, after the table data transfer part. It does not only increase the performance of the script in data insert part but also results in a better utilized index structure. Creating the index first and inserting table data causes different index structure to be created than inserting the table data and then creating the index, if your index key is not a unique value and inserted into the table in sequential order. Second option will be most probably faster and will cause the index blocks better utilized.

Below is a demonstration showing that case.

Lets start with creating a sample schema. You can find the contents of the "cre_user-U0001.sql" in my previous blog posts.

[oracle@localhost uural]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jul 21 01:02:47 2011
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> @cre_user-U0001
 
User dropped.
 

User created.
 

Grant succeeded.
 

User altered.
 

Grant succeeded.
 

Grant succeeded.
 
Connected.
USER is "U0001"
SQL> set lines 120
SQL> !vi check_index_stats.sql
 
set lines 120
select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, num_rows, clustering_factor
from user_indexes
where index_name = '&&index_name';
validate index &&index_name;
select name, height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key, b
tree_space, used_space, pct_used, rows_per_key, blks_gets_per_access
from index_stats
where name = '&&index_name';
undefine index_name
--set lines 80
 
~
~

Now create an empty table and an index on its id column.

SQL> create table t1 tablespace ts_no_assm
  2  as
  3  select rownum id
  4  from dual
  5  where null is not null;
 
Table created.
 
SQL> create index t1_01 on t1(id) tablespace ts_no_assm;
 
Index created.

Then insert some 100,000 rows into that table, with id column values are not unique and sequential. You can think of that operation like you are transferring the data from another database table to "t1" table.

SQL> insert into t1
  2  select mod(rownum,1000) id
  3  from dual
  4  connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent=>null, cascade=>true, method_opt=>'for all columns size 1');
 
PL/SQL procedure successfully completed.

Check the index stats

SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   1         252          1000                       1                     100
    100000            100000
 

old   1: validate index &&index_name
new   1: validate index T1_01
 
Index analyzed.
 
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'
 
NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   2        256     100000        252     1489000       7996        251          1
       3314       8028           0               0          1000               100     2023020    1492314         74
         100                 52.5

As you can see in the index stats above, index has grown to 252 leaf blocks which are %74 full on average and index size is around 2 MB.

Now try the second case by first inserting the rows than creating the index.

SQL> drop table t1;
 
Table dropped.

SQL> create table t1 tablespace ts_no_assm
  2  as
  3  select rownum id
  4  from dual
  5  where null is not null;
 
Table created.
 
SQL> insert into t1
  2  select mod(rownum,1000) id
  3  from dual
  4  connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index t1_01 on t1(id) tablespace ts_no_assm;
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent=>null, cascade=>true, method_opt=>'for all columns size 1');
 
PL/SQL procedure successfully completed.
 
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   1         208          1000                       1                     100
    100000            100000
 

old   1: validate index &&index_name
new   1: validate index T1_01
 
Index analyzed.
 
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'
 
NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   2        256     100000        208     1489000       7996        207          1
       3066       8028           0               0          1000               100     1671196    1492066         90
         100                 52.5

As you can see now, index has grown to 208 leaf blocks which are %90 full on average and index size is around 1.7 MB.

As a result, putting the index creation statements at the end of a table transfer script, does not only makes the data insert operation faster, since the database will not have to deal with creating the index tree, but also makes the index well organized by utilizing the leaf blocks better with %90 average usage because of 90-10 index block splits.

The reason for this result is, when you first create the index and then insert the rows, the indexed column values are inserted into the table not in sequential order because of the "mod(rownum, 1000)" function because the same value was inserted again for every 1,000 rows causing 50-50 index leaf block splits. This caused the index allocate more blocks for its leaf blocks making the index larger. Although in the second scenario, since all the rows were already inserted to the table, when we created the index on id column, Oracle selected the id column values from table and sort them before inserting into the index tree causing only 90-10 index leaf block splits.

If the id column were unique and inserted into table in sequential order, it would not make much difference creating the index first from the index structure point of view, but anyway insert operation will be faster if you create the index as a last step.

At least, if you can not drop the existing index on an existing table and then create it after the insert operation, you can use the method of making the existing index "UNUSABLE" then "REBUILD" it after the insert operation which will be faster and more effective related the index structure.

In an OLTP system, which the insert, update, delete operations will start to happen after you transferred the table, index structure will soon do some 50-50 index leaf block splits and return to a structure with less utilized leaf blocks, but in a datawarehouse-like database where mostly insert and select statements happen, you can use this method more effectively.

Will index compression help us or not ?

2011-07-19T06:59:00.000-07:00

Below are two demonstrations related to the index compression in Oracle. In the first test, index compression will make situation worse because the indexed column values are unique, in the second test we will see how much we will get by using index compression in a specific case.

Lets begin the first test by creating a sample schema and a sample table.

[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 19 01:45:22 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !vi cre_user-U0001.sql

drop user u0001 cascade;
create user u0001 identified by u0001;
grant create session to u0001;
alter user u0001 quota 5G on users;
grant create table to u0001;
grant dba to u0001;

conn u0001/u0001
sho user
~
~
~
"cre_user-U0001.sql" 9L, 207C written

SQL> @cre_user-U0001

User dropped.


User created.


Grant succeeded.


User altered.


Grant succeeded.


Grant succeeded.

Connected.
USER is "U0001"

I created a script to query the index related information form "user_indexes", validate the index and then query the "index_stats" view.

SQL> !vi check_index_stats.sql

set lines 120
select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, num_rows, clustering_factor
from user_indexes
where index_name = '&&index_name';
validate index &&index_name;
select name, height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access
from index_stats
where name = '&&index_name';
undefine index_name
set lines 80
~
~
~
"check_index_stats.sql" 11L, 558C written


SQL> create table t1 as
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8  dual
  9  connect by level <= 1000000
 10  ;
/

SQL> create index t1_01 on t1(id);

Index created.

Elapsed: 00:00:02.02
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.47
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        2226       1000000                       1                       1
   1000000             17858


Elapsed: 00:00:00.03
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.59
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       2304    1000000       2226    15979802       7996       2225          5
      26657       8028           0               0       1000000                 1    17839236   16006459         90
           1                    4


Elapsed: 00:00:00.19

As we can see from the above output, the index we created has 2226 leaf blocks and since the values in "id" column are unique it has 1,000,000 distinct keys, it took nearly 18 MB storage to keep this index.

Lets now try to query this table by using this index.

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.15
SQL> set lines 120
SQL> set autot trace
SQL> select * from t1 where id between 100000 and 200000;

100001 rows selected.

Elapsed: 00:00:03.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100K|    11M|  2013   (1)| 00:00:25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100K|    11M|  2013   (1)| 00:00:25 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100K|       |   226   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=100000 AND "ID"<=200000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      15213  consistent gets
       2013  physical reads
          0  redo size
   13766300  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100001  rows processed

SQL> set autot off

As a result it took 03.25 seconds, 15213 consistent gets and 2013 physical reads to get 100001 rows from the table.

Now drop and create this index as compressed

SQL> drop index t1_01;

Index dropped.

SQL> create index t1_01 on t1(id) compress;

Index created.

SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        3067       1000000                       1                       1
   1000000             17858


old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       3200    1000000       3067    11000000       7992       3066          6
      36702       8028           0               0       1000000                 1    24559632   22016504         90
           1                    4

As we can see from the above output, the compressed index we created has 3067 leaf blocks and it took nearly 25 MB storage to keep this index. These values are greater than the values that we got when we used non-compressed index.

Lets now try to query this table by using this compressed index.

SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> set autot trace
SQL> select * from t1 where id between 100000 and 200000;

100001 rows selected.

Elapsed: 00:00:03.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100K|    11M|  2097   (1)| 00:00:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100K|    11M|  2097   (1)| 00:00:26 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100K|       |   310   (1)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=100000 AND "ID"<=200000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      15292  consistent gets
       2097  physical reads
          0  redo size
   13766300  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100001  rows processed

SQL> set autot off
SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.11

As a result, it took 03.31 seconds, 15292 consistent gets and 2097 physical reads to run the query by using this compressed index. These values are greater than the previous test which we used a non-compressed index.

In that case using a compressed index made situation worse. The reason for this is that, Oracle makes the compression by using an algorithm to tore the index key values only once in every index block in a special structure and put only the reference to this index key in index entries instead of using the index key itself.

Compressing an index on a column with unique values, causes the index to consume more storage space and it will make the performance worse. Because index will have to write all the key values in the block and keep an extra structure to manage the compressed index.

We should use the index compression on indexes with less selective columns, so that the number of repeating values for a index key in a block will increase making the index compression more efficient in terms of storage space and performance due to reading and fetching less number of index blocks to the database server memory.

Indeed, Oracle protects this kind of case to happen, if we had given it some information about this index column. If we try to create the index having only one column as unique and compressed, it will not allow us to do that, since the index compression in that case will not be efficient. It tries to protect us from making mistakes shortly.

Lets try this

SQL> create unique index t1_01 on t1(id) compress;
create unique index t1_01 on t1(id) compress
                                    *
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key

-------------------

Lets demonstrate another case where index compression will help to decrease index size and improve the performance.
In this test, we will create a sample table which has a non-selective column with repeating values on which we will create our index.

SQL> set autot off
SQL> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          *
  6      from all_objects
  7  )
  8  select
  9      v1.*
 10  from
 11      generator   v1,
 12      generator   v2
 13  where
 14      rownum <= 1000000
 15  ;

Table created.

Elapsed: 00:00:23.85
SQL> create index t1_01 on t1(object_type);

Index created.

Elapsed: 00:00:02.80
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.38
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        2391             3                     797                    3800
   1000000             11402


Elapsed: 00:00:00.02
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.54
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       2560    1000000       2391    17147394       7996       2390          7
      45746       8028           0               0             3            484121    19174632   17193140         90
  333333.333           166670.167


Elapsed: 00:00:00.19

This index has 2391 leaf blocks, takes nearly 19 MB storage space.

Lets try to query the table using this index.

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.18
SQL> set autot trace
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |  3168   (2)| 00:00:39 |
|   1 |  SORT GROUP BY     |      |     3 |    21 |  3168   (2)| 00:00:39 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|  6835K|  3139   (1)| 00:00:38 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11397  consistent gets
      11395  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

Oracle chose Full Table Scan (FTS) because the index column "object_type" is not defined as a "NOT NULL" column and Oracle thinks that there maybe null values which are not in the index but in the table and do not use index. To force Oracle use index, we will make "object_type" column "NOT NULL".

SQL> alter table t1 modify object_type not null;

Table altered.

Elapsed: 00:00:00.29
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 2450666175

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     3 |    21 |   682   (5)| 00:00:09 |
|   1 |  SORT GROUP BY        |       |     3 |    21 |   682   (5)| 00:00:09 |
|   2 |   INDEX FAST FULL SCAN| T1_01 |  1000K|  6835K|   653   (1)| 00:00:08 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         74  recursive calls
          0  db block gets
       2473  consistent gets
       2415  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          3  rows processed

Now Oracle uses this index. It takes 00.35 seconds, 2473 consistent gets and 2415 physical reads to get the final 3 rows.

Now try to recreate the index as compressed.

SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.13
SQL> create index t1_01 on t1(object_type) compress;

Index created.

Elapsed: 00:00:02.18
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.07
SQL> set autot off
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        1537             3                     512                    3800
   1000000             11402


Elapsed: 00:00:00.01
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.56
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       1664    1000000       1537    11000000       7992       1536          5
      29374       8028           0               0             3            484121    12323844   11048070         90
  333333.333           166670.167


Elapsed: 00:00:00.05

The new compressed index has 1537 leaf blocks, taking nearly 12 MB storage space and this is nearly half of the uncompressed index size. Also pay attention, how AVG_LEAF_BLOCKS_PER_KEY values got down from 797 to 512, which shows average index leaf blocks needed to store each index key.

Lets go on running the SQL by using this compressed index.

SQL> set autot trace
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 2450666175

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     3 |    21 |   451   (8)| 00:00:06 |
|   1 |  SORT GROUP BY        |       |     3 |    21 |   451   (8)| 00:00:06 |
|   2 |   INDEX FAST FULL SCAN| T1_01 |  1000K|  6835K|   422   (1)| 00:00:06 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1550  consistent gets
       1543  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

This time query took 00.28 seconds which is not too much faster than using a non-compressed index but still faster. Physical reads which are very expensive went down from 2415 to 1543. By using compressed index we saved some index storage and performance of some SQLs.

Indexing only the needed column values to save index space and also to improve the performance

2011-07-17T11:06:00.000-07:00

You can reduce the index space and also improve the performance of specific SQL statements by indexing only a specific value of a column by using function-based indexes in Oracle.

Below is a demonstration related to this.

First create a sample schema.

[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Jul 17 05:01:51 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @cre_user-U0001


User dropped.


User created.


Grant succeeded.


User altered.


Grant succeeded.


Grant succeeded.

SQL> !more cre_user-U0001.sql
drop user u0001 cascade;
create user u0001 identified by u0001;
grant create session to u0001;
alter user u0001 quota 5G on users;
grant create table to u0001;
grant dba to u0001;

Create a sample table with 1,000,000 rows with a status column which has the value of 'CLOSED'.

SQL> conn u0001/u0001
Connected.
SQL> create table t1 as
  2  select
  3    rownum id,
  dbms_random.string('U',6) v1,
  4    5    lpad(rownum,10) v2,
  6    rpad('x',100) padding,
  7    'CLOSED' status
  8  from
  dual
  9   10  connect by level <= 1000000;


Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 V1                                                 VARCHAR2(4000)
 V2                                                 VARCHAR2(40)
 PADDING                                            VARCHAR2(100)
 STATUS                                             CHAR(6)

Now insert some 100 rows which have the status column as 'OPEN'.

SQL> insert into t1
  2  select
  3    rownum id,
  4    dbms_random.string('U',6) v1,
  5    lpad(rownum,10) v2,
  6    rpad('x',100) padding,
  7    'OPEN' status
  8  from
  9    dual
 10  connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> set autot trace
SQL> sho autot
autotrace TRACEONLY EXPLAIN STATISTICS

Create an index on status column.

SQL> create index t1_01 on t1(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> alter system flush buffer_cache;

System altered.

Query the the rows which has the status column with the value 'OPEN'.

SQL> set timing on
SQL> select * from t1 where status = 'OPEN';

100 rows selected.

Elapsed: 00:00:01.94

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|    62M|  5220   (1)| 00:01:03 |
|*  1 |  TABLE ACCESS FULL| T1   |   500K|    62M|  5220   (1)| 00:01:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"='OPEN')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      18897  consistent gets
      18887  physical reads
          0  redo size
       4229  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

As you can see the CBO did not use the index on status column most probably because of the low distinct values and low selectivity of the "status" column. It did 18887 "physical reads" and 18897 "consistent gets" and it took "01.94" seconds to get the result.

Now try to give a hint to make the CBO use the index on "status" column.

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.25
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02

SQL> select /*+ index(t, t1_01)*/ * from t1 t where status = 'OPEN';

100 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   500K|    62M| 10703   (1)| 00:02:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   500K|    62M| 10703   (1)| 00:02:09 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   500K|       |  1262   (1)| 00:00:16 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"='OPEN')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         19  consistent gets
          5  physical reads
          0  redo size
      14839  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

As you can see above, CBO used the index and did 5 "physical reads" and 19 "consistent gets" and query took "00.04" seconds although the CBO computed the cost higher then Full Table Scan (FTS).

Lets look at the index structure

SQL> set autot off

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from user_indexes where index_name = 'T1_01';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
---------- ----------- ------------- -----------------------
AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
----------------------- ----------
         2        2513             2                    1256
                   9435    1000100


Elapsed: 00:00:00.06
SQL> validate index t1_01;

Index analyzed.

Elapsed: 00:00:01.45

SQL> select HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS,DEL_LF_ROWS,DISTINCT_KEYS,USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
DISTINCT_KEYS USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------- ---------- ---------- ------------ --------------------
         3       2688    1000100       2513       2512          8           0
            2   18051946         90       500050             250028.5


Elapsed: 00:00:00.73

You can see from the index stats that, this index ha 2513 leaf blocks which takes about 18 MB storage space.

Lets now drop this index and create a function-based index on "status" column but for only the value that we are interested in.

SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.60

We use the CASE statement, to tell Oracle that we only want the rows whose status column is "OPEN" to be indexed. Since the CASE will return NULL value for other values these rows' rowids will not be kept in the index structure.

SQL> create index t1_01 on t1(case when status = 'OPEN' then status else null end);

Index created.

Elapsed: 00:00:01.87
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.51
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11
SQL> set autot trace

SQL> SQL> sho autot
autotrace TRACEONLY EXPLAIN STATISTICS

Now we need to use the same statement that we used while creating the function-based index, in the where condition of our query to make it use the new index.

SQL> select * from t1 where (case when status = 'OPEN' then status else null end) = 'OPEN';

100 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 | 13200 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 | 13200 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE  WHEN "STATUS"='OPEN' THEN "STATUS" ELSE NULL END ='OPEN')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         19  consistent gets
          4  physical reads
          0  redo size
       4229  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

From the trace we see that this statement did 4 "physical reads", 19 "consistent gets" and completed in "00.03" seconds. These values are very close to the values we got from the test using the non function-based index before.
But what about the storage space that this new index uses, lets check.

SQL> set autot off
SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from user_indexes where index_name = 'T1_01';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
---------- ----------- ------------- -----------------------
AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
----------------------- ----------
         0           1             1                       1
                      2        100


Elapsed: 00:00:00.01
SQL> validate index t1_01;

Index analyzed.

Elapsed: 00:00:00.04
SQL> select HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS,DEL_LF_ROWS,DISTINCT_KEYS,USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
DISTINCT_KEYS USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------- ---------- ---------- ------------ --------------------
         1          8        100          1          0          0           0
            1       1800         23          100                 51.5

Elapsed: 00:00:00.04

As we can see, this new function-based index has only 1 leaf blocks and takes only 1.8 KB storage space which is far less compared to the values of non function-based index which took around 18 MB.

Now we can go one step further, suppose instead of having a status column, we have two date columns named "date_to_be_processed" and "date_processed". Every row is inserted by a batch process with their date_to_be_processed column set to a specific date normally one day later and when tomorrow comes another job selects only the rows with their "date_to_be_processed" column set to this date and "date_processed" column is set to "NULL". In that case we are not interested in the column which their "date_processed" values set already. Below is the demonstration of that case.

SQL> alter table t1 drop column status;


Table altered.

Elapsed: 00:02:00.87

SQL> alter table t1 add (date_processed date);

Table altered.

Elapsed: 00:00:00.38
SQL> alter table t1 add (date_to_be_processed date);

Table altered.

Elapsed: 00:00:00.05
SQL> truncate table t1;

Table truncated.

Elapsed: 00:00:02.35

We insert 1,000,000 rows which their "date_processed" columns are set. Every day has 100 rows processed.

SQL> insert into t1
  2      select
  3        rownum id,
  4        dbms_random.string('U',6) v1,
  5        lpad(rownum,10) v2,
  6        rpad('x',100) padding,
  7        trunc(sysdate) - (10000 - trunc((rownum-1)/100)) date_processed,
      trunc(sysdate) - (10000 - trunc((rownum-1)/100)) date_to_be_processed
  8    9      from
 10       dual
 11     connect by level <= 1000000;


1000000 rows created.

Elapsed: 00:01:04.80
SQL> set pause on
SQL> select date_processed, count(*) from t1 group by date_processed order by date_processed;


DATE_PROC   COUNT(*)
--------- ----------
29-FEB-84        100
01-MAR-84        100
02-MAR-84        100
03-MAR-84        100
04-MAR-84        100
05-MAR-84        100
06-MAR-84        100
07-MAR-84        100
08-MAR-84        100
09-MAR-84        100
10-MAR-84        100
11-MAR-84        100
...
Ctrl+C
13 rows selected.

Elapsed: 00:00:05.70

SQL> select date_processed, count(*) from t1 group by date_processed order by date_processed desc;


DATE_PROC   COUNT(*)
--------- ----------
16-JUL-11        100
15-JUL-11        100
14-JUL-11        100
13-JUL-11        100
12-JUL-11        100
11-JUL-11        100
10-JUL-11        100
09-JUL-11        100
08-JUL-11        100
07-JUL-11        100
06-JUL-11        100
05-JUL-11        100
...
Ctrl+C
13 rows selected.

Elapsed: 00:00:04.60

Now insert some 100 rows which should be processed today with their "date_processed" columnsset to "NULL".

SQL>  insert into t1
  2      select
  3        rownum id,
  4        dbms_random.string('U',6) v1,
  5        lpad(rownum,10) v2,
  6        rpad('x',100) padding,
  7        null date_processed,
  8        trunc(sysdate) date_to_be_processed
  9      from
 10       dual
 11     connect by level <= 100;

100 rows created.

Elapsed: 00:00:00.04
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select date_processed, count(*) from t1 group by date_processed order by date_processed desc;


DATE_PROC   COUNT(*)
--------- ----------
                 100
16-JUL-11        100
15-JUL-11        100
14-JUL-11        100
13-JUL-11        100
12-JUL-11        100
11-JUL-11        100
10-JUL-11        100
09-JUL-11        100
08-JUL-11        100
07-JUL-11        100
06-JUL-11        100
...
Ctrl+C
13 rows selected.

Elapsed: 00:00:07.02

Create a regular composite index on these columns as we could normally do.

SQL> create index t1_01 on t1(date_processed, date_to_be_processed);

Index created.

Elapsed: 00:00:04.99
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.62
SQL> set autot trace
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07

Now try to query the rows that we are interested.

SQL> select * from t1 where date_processed is null and date_to_be_processed = trunc(sysdate);

100 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   140 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   140 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATE_PROCESSED" IS NULL AND
              "DATE_TO_BE_PROCESSED"=TRUNC(SYSDATE@!))
       filter("DATE_TO_BE_PROCESSED"=TRUNC(SYSDATE@!))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          5  physical reads
        188  redo size
      15117  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from user_indexes where index_name = 'T1_01'
  2

SQL> set autot off
SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from user_indexes where index_name = 'T1_01';


    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
---------- ----------- ------------- -----------------------
AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
----------------------- ----------
         2        3774         10001                       1
                      2    1000100


Elapsed: 00:00:00.60
SQL> validate index t1_01;

Index analyzed.

Elapsed: 00:00:00.97
SQL> select HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS,DEL_LF_ROWS,DISTINCT_KEYS,USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS from index_stats;


    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
DISTINCT_KEYS USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------- ---------- ---------- ------------ --------------------
         3       3968    1000100       3774       3773         15           0
        10001   27107816         90          100                 53.5


Elapsed: 00:00:04.46

It did 21 "consistent gets", 5 "physical reads" and it took "00.08" seconds to complete. The index we created has 3773 leaf blocks and it took about 27 MB storage space.

Now try a function-based index on only specific values we interested in. We tell Oracle that put into index "date_to_be_processed" values of only the rows which has "date_processed" columns are null.

SQL> set lines 120
SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.34
SQL> create index t1_01 on t1(case when date_processed is null then date_to_be_processed else null end);

Index created.

Elapsed: 00:00:02.22
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.58
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL> set autot trace

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL> select * from t1 where (case when date_processed is null then date_to_be_processed else null end) = trunc(sysdate);

100 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 | 14100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 | 14100 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE  WHEN "DATE_PROCESSED" IS NULL THEN
              "DATE_TO_BE_PROCESSED" ELSE NULL END =TRUNC(SYSDATE@!))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          3  physical reads
          0  redo size
       4311  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> set autot off
SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from user_indexes where index_name = 'T1_01';


    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS
---------- ----------- ------------- ----------------------- ----------------------- ----------
         0           1             1                       1                       2        100

Elapsed: 00:00:00.98
SQL> validate index t1_01;

Index analyzed.

Elapsed: 00:00:00.18
SQL> select HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS,DEL_LF_ROWS,DISTINCT_KEYS,USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS from index_stats;


    HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS DISTINCT_KEYS USED_SPACE   PCT_USED
---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
         1          8        100          1          0          0           0             1       1900         24
         100                 51.5


Elapsed: 00:00:00.78
SQL>

Now we see that it did 17 "consistent gets, 3 "physical reads" and it took just "00.02" seconds to get the results which is also a better value compared to "00.08" of previous normal index test, but when you compare the index structure, new index has only 1 leaf block which took 1.9 KB storage space which is far less compared to the 27 MB which was used in a normal index test.

These examples are suitable for very selective cases, I mean, in these tests you are not interested in the old historical values, you only need to select the new values or values which we are interested in. But especially, from the storage point of view, if the table will grow very huge to billion of rows and you will not query the old historical values very often then this method is very well suits your needs.

Effects of indexes on Oracle Cost Based Optimizer (CBO) execution plan selection

2011-07-14T17:22:00.000-07:00

Followings are some tests that I performed on an Oracle 11gR2 database and my comments about the effects of the indexes on Oracle Cost Based Optimizer (CBO) execution plan selection. The results can change on different database versions or on the databases configured with different database parameters. Below are my output that I got at the time of my tests.

[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jul 14 14:15:11 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

First I create a test user

SQL> !more cre_user-U0001.sql
drop user u0001 cascade;
create user u0001 identified by u0001;
grant create session to u0001;
alter user u0001 quota 5G on users;
grant create table to u0001;
grant dba to u0001;
SQL> @cre_user-U0001

User dropped.


User created.


Grant succeeded.


User altered.


Grant succeeded.


Grant succeeded.

Then I connect with that test user

SQL> conn u0001/u0001
Connected.

Create a big sample table

SQL> create table t1 as
  2  select
  3    rownum id,
  4    dbms_random.string('U',6) v1,
  5    lpad(rownum,10) v2,
  6    rpad('x',100) padding
  7  from
  8    dual
connect by level <= 1000000;  9

Table created.

Create an index on id column

SQL> create index t1_01 on t1(id);

Index created.

gather table statistics with cascade option, which will gather index statistics also

SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> set lines 160

SQL> desc t1
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 ID                                                                                                 NUMBER
 V1                                                                                                 VARCHAR2(4000)
 V2                                                                                                 VARCHAR2(40)
 PADDING                                                                                            VARCHAR2(100)

set auto trace to explain only, we are only interested in the execution plans right now

SQL> set autot trace exp
SQL> sho autot
autotrace TRACEONLY EXPLAIN

When we try to select all columns with a condition using an indexed column, CBO decides to do an "INDEX RANGE SCAN" and after finding the related rowids of the rows matching the index key in where condition, then select the rows from table by using these rowids.

SQL> select * from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   124 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   124 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=100000)

When we select only the indexed column from the table CBO goes directly to the table and does a full table scan(TABLE ACCESS FULL). Although this column is indexed, it did not use the index for this sql, because there is no "NOT NULL" constraint on indexed column "ID" and there can be NULL values for this column in the table which they did not exist in the corresponding index, since the index keys consisting of null values are not kept in an index.

SQL> select id from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|  4882K|  4895   (1)| 00:00:59 |
|   1 |  TABLE ACCESS FULL| T1   |  1000K|  4882K|  4895   (1)| 00:00:59 |
--------------------------------------------------------------------------

This time we query again the indexed column by giving a not null condition, and CBO uses associated index. It does not go to table because indexed column value also exists in the index as index key so there is no need to go to the table.Although, actually we know that there is only one row matching the condition, CBO does not know it and it does a "INDEX RANGE SCAN" instead of "INDEX UNIQUE SCAN" which could only be an option if we created a unique index on "ID" column.

SQL> select id from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2311469903

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T1_01 |     1 |     5 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=100000)

Again for the count operation since there is a not null condition in the where clause CBO computes this count only using the index without going to the table.

SQL> select count(*) from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1142046246

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_01 |     1 |     5 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=100000)

Lets now set the indexed column to "NOT NULL" and see what will change.

SQL> alter table t1 modify id not null;

Table altered.

Now CBO knows that there is no NULL value for the indexed column in the table and since all the values of indexed column is also recorded in the corresponding index there is no need to go to table. CBO chooses a "INDEX FAST FULL SCAN" (index ffs) which is done by using multi block read (mbr) technique, and because of that, returning rows from this operation will not be ordered by indexed column value.

SQL> select id from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 93400700

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000K|  4882K|   609   (1)| 00:00:08 |
|   1 |  INDEX FAST FULL SCAN| T1_01 |  1000K|  4882K|   609   (1)| 00:00:08 |
------------------------------------------------------------------------------

This time we want the result in order and CBO chooses "INDEX FULL SCAN" which will read all the index keys from the beginning leaf block to the end leaf block in the order of indexed key and return them again without going to the table.

SQL> select id from t1 order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2357002434

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  1000K|  4882K|  2235   (1)| 00:00:27 |
|   1 |  INDEX FULL SCAN | T1_01 |  1000K|  4882K|  2235   (1)| 00:00:27 |
--------------------------------------------------------------------------

This is very common execution plan CBO uses the index to get the rowids matching the criteria and then goes to table to fetch the matching rows by using the rowids got from the index.

SQL> select * from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   124 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   124 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=100000)

This time CBO does the ordering by using the index although there is no where condition containing the indexed column. Since the order by column is an indexes column, and all the values of that column exist in the index tree because of the "NOT NULL" constraint on that column, CBO finds the rowids of all rows in order from the index and then gets the associated rows from the table by using these rowids.

SQL> select * from t1 order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 331831787

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000K|   118M| 20103   (1)| 00:04:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   118M| 20103   (1)| 00:04:02 |
|   2 |   INDEX FULL SCAN           | T1_01 |  1000K|       |  2235   (1)| 00:00:27 |
-------------------------------------------------------------------------------------

Because of the "NOT NULL" constraint CBO knows that all values of the indexed column are index tree and the count of them equals to the count of rows in the table. It does a "INDEX FAST FULL SCAN" by using multi block read operation.

SQL> select count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 442041690

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   609   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_01 |  1000K|   609   (1)| 00:00:08 |
-----------------------------------------------------------------------

Now make the indexed column unique

SQL> alter table t1 add constraint t1_c_01 unique (id);

Table altered.

As we can see CBO still chooses "INDEX RANGE SCAN", because although we made the indexed column UNIQUE, the index is still non-unique

SQL> select * from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   124 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   124 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=100000)

Lets drop and recreate the index as a unique index

SQL> drop index t1_01;
drop index t1_01
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


SQL> alter table t1 drop constraint t1_c_01;

Table altered.

SQL> drop index t1_01;

Index dropped.

SQL> create unique index t1_01 on t1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent => null,cascade => true,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Now we see that CBO decides to do a "INDEX UNIQUE SCAN" which will be cheaper than a "INDEX RANGE SCAN" in means of cost.

SQL> select * from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2407846814

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   124 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   124 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_01 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=100000)

Again CBO chooses "INDEX UNIQUE SCAN" without going to the table since all the indexed column values exists in the index because of the "NOT NULL" constrained on indexed column.

SQL> select id from t1 where id = 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2394460924

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     5 |     2   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T1_01 |     1 |     5 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=100000)

Nothings changed in the execution plan after changing the index to a unique one.

SQL> select * from t1 order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 331831787

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000K|   118M| 19965   (1)| 00:04:00 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   118M| 19965   (1)| 00:04:00 |
|   2 |   INDEX FULL SCAN           | T1_01 |  1000K|       |  2097   (1)| 00:00:26 |
-------------------------------------------------------------------------------------

Lets give an example how CBO chooses to scan the index leaf blocks from the end to the beginning when we give an order by in descending order by using "INDEX FULL SCAN DESCENDING".

SQL> select * from t1 order by id desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 2369582229

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000K|   118M| 19965   (1)| 00:04:00 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   118M| 19965   (1)| 00:04:00 |
|   2 |   INDEX FULL SCAN DESCENDING| T1_01 |  1000K|       |  2097   (1)| 00:00:26 |
-------------------------------------------------------------------------------------

Again if we only select only the indexed column, column values comes from only the index without going to the table in descending order.

SQL> select id from t1 order by id desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 1356827296

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |  1000K|  4882K|  2097   (1)| 00:00:26 |
|   1 |  INDEX FULL SCAN DESCENDING| T1_01 |  1000K|  4882K|  2097   (1)| 00:00:26 |
------------------------------------------------------------------------------------

SQL>

That's all folks.
I hope my demonstrations and comments can give you any little idea about how the Oracle CBO works and decides the execution plans in the presence of indexes.

Oracle Heterogeneous Services (HS) problem with tables having lots of columns

2011-07-14T12:55:00.000-07:00

Today, our Oracle data warehouse team got an error while querying a table having around 650 columns in an AS/400 database by using Oracle Heterogeneous Services or HS for short(It is also called HSODBC).

HSODBC is a bundled component in Oracle database installation package. It enables yo to query any non-Oracle database tables from within an Oracle database as if the remote non-Oracle database is an Oracle database. I am thinking to publish another blog post in the future about how to setup HSODBC which I will not cover today.

In this post, I only want to talk about an error in HSODBC and its solution since I could not find so much information about the solution of this problem today. I want to help anybody who will have that same problem in their HSODBC configuration. The HSODBC version we use is Oracle 10gR2 HSODBC. HSODBC parameter in different Oracle versions are different so the following solution is especially for Oracle 10gR2 HSODBC configuration. In our case, Oracle HSODBC server is a separate Windows machine which runs only Oracle HSODBC on it.

The sql statements giving error were like below.
select col1,col2,...,col650 from owner.table_name@database_link_using_hsodbc_to_as400_db;
or the following sql statement got also the same error
select * from owner.table_name@database_link_using_hsodbc_to_as400_db;

Error :
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][A048] The execution tree of this sql text is too big. Try to increase the variable 'firstTreeExtensions' in your environment file.
ORA-02063: preceding line from database_link_using_hsodbc_to_as400_db

The above error indicates that Oracle HSODBC has an internal limitation about the length of the selected data(consisting of column values in select statement) of a table coming from the non-Oracle database. We tried to select around 520 column from that table without getting this error but after adding some more columns to this list select, we started to get this error because we already reached that internal limitation of selected row size. In another document, we found that this internal default max size limit of selected row size, you can select from the non-Oracle database, is 150 Kilobytes.

So we started to find how to increase that parameter and after trying different configurations we were able to increase the value of this parameter in the configuration and got rid of the above error.

Solution :
Just add the following line to the "init<sid_name_of_as400_db_in_listener.ora_file>.ora" file in the "$ORACLE_HOME\hs\admin" folder. With this setting we increase the limit to 1000 KB.

HS_FDS_CONNECT_STRING="<navobj><binding><environment><queryprocessor firstTreeExtensions='1000'></queryProcessor></environment></binding></navobj>"

Then we need to reload the listener in HSODBC machine, like below so that our change in ".ora" configuration will take effect.

In a windows Command Prompt window in HSODBC machine do the following to reload the listener.

lsnrctl
LSNRCTL> reload

Now you need to create a new session to the Oracle database, since the connections connected before these changes made to the listener in HSODBC server, will not be effected by this change, because of that, it is very important to create a new session to Oracle database where we will execute our sql statement querying the big table in the non-Oracle database.
Then try to query all 650 columns of this table in non-Oracle database which is As/400 in our case.

select col1,col2,...,col650 from owner.table_name@database_link_using_hsodbc_to_as400_db;

Finally, you should successfully query all the 650 columns of this table.
I hope the same solution will help to your case also.

Oracle automatically keeps the old, historical table statistics

2011-07-04T05:10:00.000-07:00

After Oracle version 10g, Oracle started to keep the old, historical table statistics in the database automatically so that you can use them, in case you need them later.
If a specific SQL starts to perform badly in the database and you realized that the reason is the statistics of the table changed a lot since the last statistics collected on that table, you can use the previous old statistics of this table by restoring them in a very short time, without any need to collect them from scratch which could take a long time.

Below is how you can accomplish to query and restore those old table statistics.

Firs connect to a sample schema in database.

[oracle@localhost uural]$ sqlplus u0001/u0001

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 4 04:03:56 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Then create a sample table and collect statistics on this table by using "dbms_stats.gather_table_stats" procedure.

SQL> create table t1 as
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8          all_objects
  9  where
 10          rownum <= 1000
 11  ;

Table created.

You can query the old statistics info in a view in the database called "user_tab_stats_history". Since there is no statistics collected on this table we will not see any rows returning from this view.

SQL> select * from user_tab_stats_history;

no rows selected

Now collect the statistics on the new table.

SQL> begin
  2          dbms_stats.gather_table_stats(
  3                  ownname          => user,
  4                  tabname          => 'T1',
  5                  cascade          => true,
  6                  estimate_percent => null,
  7                  granularity      => 'default',
  8                  method_opt       => 'for all columns size 1'
  9          );
 10  end;
 11  /

PL/SQL procedure successfully completed.

Now check the "num_rows" info of table in "user_tables" view.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000

Check the "user_tab_stats_history" view again.

SQL> set lines 250
SQL> r
  1* select * from user_tab_stats_history

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00

Now truncate the table and collect the statistics again, and check the "user_tab_stats_history" view.

SQL> truncate table t1;

Table truncated.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000

SQL> begin
        dbms_stats.gather_table_stats(
  2    3                  ownname          => user,
  4                  tabname          => 'T1',
  5                  cascade          => true,
  6                  estimate_percent => null,
  7                  granularity      => 'default',
  8                  method_opt       => 'for all columns size 1'
  9          );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

SQL> select * from user_tab_stats_history;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00
T1                                                                                           04-JUL-11 04.16.39.947784 AM -07:00

This sample table has only 1000 rows being relatively small, but suppose you have a big table with 100 million rows in it, and it takes quiet a long time to collect the statistics. If this table is truncated and statistics refreshed, then 100 million rows are inserted back to this table and new table statistics are not refreshed yet and an SQL in database is performing bad because the statistics of this table is not up-to-date. In that case if you have the old statistics from the time that this table had 100 million rows you can try to restore that statistics and try to tune the SQL.Below is how you can restore the old statistics.First insert 1000 rows back to that table, check the "num_rows" column in "user_tables" view.

SQL> insert into t1
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8          all_objects
  9  where
 10          rownum <= 1000
 11  ;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

Restore the old statistics back.

SQL> begin
  dbms_stats.restore_table_stats (ownname         => 'U0001',
  2    3                                    tabname         => 'T1',
  4                                    as_of_timestamp =>'04-JUL-11 04.10.24.567434 AM -07:00');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name = 'T1';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                   1000

SQL> select * from user_tab_stats_history;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T1                                                                                           04-JUL-11 04.10.24.567434 AM -07:00
T1                                                                                           04-JUL-11 04.16.39.947784 AM -07:00
T1                                                                                           04-JUL-11 04.31.24.129217 AM -07:00

As you can see, we restored the statistics of first row in the above "user_tab_stats_history" output, and Oracle created a new row stating that the statistics of the table has changed after the restore operation.Oracle keeps those old statistics for 31 days and after that automatically removes them which are older than 31 days.You can query that parameter "the history retention value" with the following SQL.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

Below function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
03-JUN-11 12.46.58.682468000 AM -07:00

Automatic purging is enabled when STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, the old versions of statistics need to be purged manually using the PURGE_STATS procedure.You can query that parameter like below.

[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 4 04:45:47 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL>

You can change the history retention value like below. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.retention parameter : The retention time in days. The stats history will be retained for at least these many number of days.The valid range is [1,365000]. Also the following values can be used for special purposes. 0 - old stats are never saved. The automatic purge will delete all stats history -1 - stats history is never purged by automatic purge. null - change stats history retention to default value

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> exec dbms_stats.alter_stats_history_retention(retention => 45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         45

Set it back to default value.

SQL> exec dbms_stats.alter_stats_history_retention(retention => null);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL>

Oracle hidden parameters

2011-06-23T09:05:00.000-07:00

Oracle database has some parameters related to the database configuration, some of them which are hidden and some of them are unhidden.

To list the unhidden parameters you can connect to the database "as sysdba" and directly query the "v$parameter" view as below.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 23 08:41:55 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column name format a30
SQL> column value format a50 
SQL> column description format a50
SQL> set lines 132
SQL> set pause on
SQL> select name, value, description from v$parameter order by name;

NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY    FALSE                                              Version 7 Dictionary Accessibility Support
active_instance_count                                                             number of active instances in the cluster database
aq_tm_processes                0                                                  number of AQ Time Managers to start
archive_lag_target             0                                                  Maximum number of seconds of redos the standby cou
                                                                                  ld lose

asm_diskgroups                                                                    disk groups to mount automatically
asm_diskstring                                                                    disk set locations for discovery
asm_power_limit                1                                                  number of parallel relocations for disk rebalancin
                                                                                  g



asm_preferred_read_failure_gro                                                    preferred read failure groups

............
SQL>

To list the hidden parameters you need to use an SQL like below, by switching commented where conditions, you can query different set of parameters hidden or unhidden. The following SQL only brings the hidden parameter called "_disable_logging" as an example.

SQL> SELECT x.ksppinm name,
       y.ksppstvl VALUE,
       x.ksppdesc description
  FROM x$ksppi x,
       x$ksppcv y
 WHERE x.inst_id = userenv('Instance')
   AND y.inst_id = userenv('Instance')
   AND x.indx = y.indx
--   AND x.ksppinm LIKE '%pga%'; --list hidden and unhidden all parameters like "pga"
   AND x.ksppinm LIKE '\_disable\_logging%' escape '\' --list hidden parameters like "_disable_logging"
--   AND x.ksppinm NOT LIKE '\_%' escape '\' --list all unhidden parameters
--   AND x.ksppinm LIKE '\_%' escape '\' --list all hidden parameters
    order by 1;  2    3    4    5    6    7    8    9   10   11   12   13  


NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
_disable_logging               FALSE                                              Disable logging

All the Oracle hidden parameters start with an underscore (_). They are not documented parameters by Oracle. Oracle does not advise customers to change them without the knowledge of Oracle Support.

For example, if you bump into an Oracle database bug and there is no published One-off patch to solve this bug yet, Oracle support can advise you to change an hidden Oracle parameter as a workaround until the patch will be released for this bug. Otherwise do not try to play with them since that can result to unexpected results in your databases.

How to change the command prompt on Unix

2011-06-23T08:15:00.000-07:00

Below is how I change the command prompt on IBM AIX Unix type.

It will set the command prompt on AIX to
"connected username"@"hostname of the server":"current folder full path"

[oracle@srvdb01]:/oracle > more .profile | grep PS
export PS1="[`whoami`@`hostname`]:\$PWD \> "
[oracle@srvdb01]:/oracle >

How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases

2011-06-23T07:27:00.000-07:00

First try it in an Oracle 10g database on an AIX server.

First create the sample export folder which export files will be written.
This folder should be created by unix user which is the owner of Oracle database (here "oracle" Unix OS user) or if created by another OS user, the directory permissions should be arranged so that Oracle owner OS user "oracle" can write to this folder.

[oracle@srvdb01]:/oracle > mkdir -p /transfer/uural/datapumpdemo
[oracle@srvdb01]:/oracle > cd /transfer/uural
[oracle@srvdb01]:/transfer/uural > ls -ld datapumpdemo
drwxr-xr-x    2 oracle   dba            4096 Jun 22 15:06 datapumpdemo
[oracle@srvdb01]:/transfer/uural > cd datapumpdemo
[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 0
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now create a new directory in the database pointing to the above export folder.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 22 15:28:09 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL AS SYSDBA> create or replace directory uural_datapumpdemo as '/transfer/uural/datapumpdemo';

Directory created.

SYS@ORCL AS SYSDBA> desc dba_directories
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SYS@ORCL AS SYSDBA> column directory_path format a50
SYS@ORCL AS SYSDBA> set lines 200
SYS@ORCL AS SYSDBA> r
  1* select * from dba_directories where directory_name = 'UURAL_DATAPUMPDEMO'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            UURAL_DATAPUMPDEMO             /transfer/uural/datapumpdemo

Lets create a sample schemas with some of empty tables in them.

SYS@ORCL AS SYSDBA> create user u0001 identified by "u0001_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0001 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> GRANT READ, WRITE ON DIRECTORY SYS.UURAL_DATAPUMPDEMO TO U0001;

Grant succeeded.

SYS@ORCL AS SYSDBA> create user u0002 identified by "u0002_pass";

User created.

SYS@ORCL AS SYSDBA> grant create session to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> grant create table to u0002;

Grant succeeded.

SYS@ORCL AS SYSDBA> alter user u0002 quota 5G on users;

User altered.

SYS@ORCL AS SYSDBA> conn u0001/u0001_pass
Connected.
U0001@ORCL > create table t0001 (col1 number);

Table created.

U0001@ORCL > conn u0002/u0002_pass
Connected.
U0002@ORCL > create table t0001 (col1 number);

Table created.

Lets try to export some tables from only a single schema in the database.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp u0001/u0001_pass directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001_tables logfile=u0001_tables tables=T0001

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:29:05

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "U0001"."SYS_EXPORT_TABLE_01":  u0001/******** directory=UURAL_DATAPUMPDEMO dumpfile=u0001_tables logfile=u0001_tables tables=T0001
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "U0001"."T0001"                                 0 KB       0 rows
Master table "U0001"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U0001.SYS_EXPORT_TABLE_01 is:
  /transfer/uural/datapumpdemo/u0001_tables.dmp
Job "U0001"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:39

[oracle@srvdb01]:/transfer/uural/datapumpdemo > ls -l
total 136
-rw-r-----    1 oracle   dba           61440 Jun 23 15:32 u0001_tables.dmp
-rw-r--r--    1 oracle   dba            1043 Jun 23 15:32 u0001_tables.log
[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Now try to export tables from two different schemas, since we are going to export tables from two schemas we will use "SYS" as the userid parameter in expdp command.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001                                                     

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:35:01

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema

[oracle@srvdb01]:/transfer/uural/datapumpdemo >

As you can see you can not export tables from different schemas by using "tables" parameter in expdp in Oracle 10g.
What you should do is create a table and insert the names of the tables to be exported to this table and use the "schemas" and "include" parameter in expdp command.

U0001@ORCL > create table expdp_tables (table_name varchar2(30));

Table created.

U0001@ORCL > insert into expdp_tables values ('T0001');

1 row created.

U0001@ORCL > commit;

Commit complete.

You only need to insert the distinct names of all the tables into "expdp_tables" schema. Put the names of the all the distinct schemas whose tables will be exported in the "schemas" parameter.
In that method you export all the tables in "expdp_tables" table from both schemas, this means if a specific table name exist in both schemas and you only want to export one of them, you can not do that.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                                             

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 15:50:35

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=UURAL_DATAPUMPDEMO dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 INCLUDE=TABLE:"IN (SELECT table_name FROM u0001.expdp_tables)"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /transfer/uural/datapumpdemo/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:58:01

[oracle@srvdb01]:/transfer/uural/datapumpdemo >

Lets now try to export tables from different schemas in Oracle 11g database on a Linux server.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 23 07:07:24 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column directory_path format a50
SQL> select * from dba_directories where directory_name = 'DATA_PUMP_DIR'

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            DATA_PUMP_DIR                  /home/oracle/app/oracle/admin/orcl/dpdump/

SQL>

Check the number of files in the destination folder before the export

[oracle@localhost ~]$ ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 4
-rw-r----- 1 oracle oracle 116 Oct  2  2010 dp.log
[oracle@localhost ~]$

Prepare the sample schema and objects

SQL> create user u0001 identified by "u0001_pass";

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by "u0002_pass";

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> grant create table to u0002;

Grant succeeded.

SQL> conn u0001/u0001_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.

SQL> conn u0002/u0002_pass
Connected.
SQL> create table t0001 (col1 number);

Table created.

Try the export in 11g

[oracle@localhost ~]$ expdp '"/ as sysdba"' directory=DATA_PUMP_DIR dumpfile=u0001-u0002_tables 
logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001

Export: Release 11.2.0.2.0 - Production on Thu Jun 23 07:17:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables tables=U0001.T0001,U0002.T0001 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "U0001"."T0001"                                 0 KB       0 rows
. . exported "U0002"."T0001"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/app/oracle/admin/orcl/dpdump/u0001-u0002_tables.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 07:18:48

[oracle@localhost ~]$  ls -l /home/oracle/app/oracle/admin/orcl/dpdump/
total 96
-rw-r----- 1 oracle oracle   116 Oct  2  2010 dp.log
-rw-rw---- 1 oracle oracle 86016 Jun 23 07:18 u0001-u0002_tables.dmp
-rw-rw-r-- 1 oracle oracle  1167 Jun 23 07:18 u0001-u0002_tables.log
[oracle@localhost ~]$

As you can see, in Oracle 11g, you can export tables from different schemas by using "tables" parameter in Export Data Pump Utility and this is not possible in Oracle 10g.

How to enable and disable archivelog mode in an Oracle database

2011-06-21T07:17:00.000-07:00

Although this is a very basic info, I just wanted to express it again and give some tips about it.

1-) Enable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:15:49 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Current log sequence           429

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             373295800 bytes
Database Buffers           75497472 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Enable archivelog in the database
SQL> alter database archivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Next log sequence to archive   429
Current log sequence           429

Do you know where the newly generated archivelogs are going to from now on, if there is any "log_archive_dest_n" parameter set and its "log_archive_des_n_state" is "enable" then all the newly generated archivelogs goes to that destination.

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL>

As you can see above there is no explicitly set "log_archive_dest_n", then archivelogs will go to the destination set in Oracle database parameter "db_recovery_file_dest" because "log_archive_dest_10" parameter implicitly set to "db_recovery_file_dest" parameter value, if there is no explicitly set "log_archive_dest_n" parameter. This is also the reason why we see in the above output "Archive destination USE_DB_RECOVERY_FILE_DEST", means the destination set in the "db_recovery_file_dest" parameter value.

Lets check that "db_recovery_file_dest" parameter value and see that it points to flash recovery area.

SQL> sho parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M

Check that destination

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL
[oracle@localhost ORCL]$ ls -l
total 4
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$

There is a folder named "ORCL"(value of $ORACLE_SID) below that folder.There is no "archivelog" folder yet, since there is no archivelogs generated yet.

Lets force generation of some archivelogs

SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL>

Check the archivelog destination again

[oracle@localhost ORCL]$ ls -l
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 21 06:37 archivelog
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$ cd archivelog/
[oracle@localhost archivelog]$ ls -l
total 4
drwxrwx--- 2 oracle oracle 4096 Jun 21 06:37 2011_06_21
[oracle@localhost archivelog]$ cd 2011_06_21/
[oracle@localhost 2011_06_21]$ ls -l
total 2756
-rw-rw---- 1 oracle oracle 2808320 Jun 21 06:37 o1_mf_1_429_7017nnrw_.arc
-rw-rw---- 1 oracle oracle    1024 Jun 21 06:37 o1_mf_1_430_7017nq5w_.arc
-rw-rw---- 1 oracle oracle    2560 Jun 21 06:37 o1_mf_1_431_7017nrxo_.arc
[oracle@localhost 2011_06_21]$

As you can see Oracle created a folder named "archivelog" and a subfolder with today's date "2011_06_21" and put the newly generated archivelogs there.

If you want, you can explicitly set a new archivelog destination instead of implicitly set flash recovery area like below.

First create the new destination on the server

[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle
[oracle@localhost oracle]$ mkdir arc2
[oracle@localhost oracle]$ cd arc2
[oracle@localhost arc2]$ mkdir ORCL
[oracle@localhost arc2]$ cd ORCL
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$

Now set "log_archive_dest_1" parameter to this new destination, you can do this while the database is in open mode.

SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/arc2/ORCL';

System altered.

--Check the "log_archive_dest_1" parameter
SQL> sho parameter log_archive_dest_1 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
                                                 le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string

--Now check the archivelog mode state, as you can see we see the new destination as "Archive destination            /home/oracle/app/oracle/arc2/ORCL"
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     430
Next log sequence to archive   432
Current log sequence           432
SQL>

From now on, all the newly generated archivelogs will go to that new destination.

There is no archivelog in the new destination for now

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 0
[oracle@localhost ORCL]$

Generate some new archivelogs

SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL>

Check the new destination and see the newly generated archivelogs

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf
[oracle@localhost ORCL]$

2-) Disable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:56:38 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Next log sequence to archive   435
Current log sequence           435

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Disable archivelog in the database
SQL> alter database noarchivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Current log sequence           435

As you can see we disabled the archivelog mode in the database, there will not be any newly generated archivelogs in the archivelog destination although it is still set in the database.

Switch some logfiles

SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL>

As you can see there is no newly generated archivelogs in the archivelog destination.

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf

3-) Additional info :

Do not close the database in a way that it will require instance recovery(e.g. shutdown abort) before you will mount it for archivelog mode change otherwise you will get the below error "ORA-00265", always try to shutdown the database gracefully by using "shutdown immediate".

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:07:41 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL>

If there is a little chance that you can close the database with "shutdown immediate" because of the heavy activity on the database, you can use the below method to bring the database to the mount mode without causing any instance recovery.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:11:07 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     438
Next log sequence to archive   440
Current log sequence           440
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

When you open the database with "startup restrict", only the users with "Restricted Session" system privilege (SYS user is one of them) can connect to database, since all the other client sessions will not be able to connect to the database, you will be able to shutdown the database faster with "shutdown immediate".

How to set Oracle SQL*Plus sqlprompt on Unix

2011-06-20T03:27:00.000-07:00

Sometimes it is very confusing to realize in which database sqlprompt we are, writing and executing the SQL commands. If we write an SQL command in a production database instead of development or test database, the results can be catastrophic. In the below example, I will try to demonstrate my way of setting the sqlprompt in Oracle SQl*Plus. My example Unix version is IBM AIX.

Oracle SQL*Plus, always, first runs an sql script called "glogin.sql" when you try to connect to an Oracle database.
First take the backup of this script, if you need it later you can use the original version.

[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > ls -l
total 40
-rw-r-----    1 oracle   dba            1525 Aug 30 2004  glogin.sql
drwxr-x---    2 oracle   dba             256 May 23 2008  help
drwxr-x---    3 oracle   dba             256 May 23 2008  iplus
-rw-r-----    1 oracle   dba            5363 Mar 02 2008  libsqlplus.exp
-rw-r-----    1 oracle   dba             813 Mar 26 2006  plustrce.sql
-rw-r-----    1 oracle   dba            2118 Feb 16 2003  pupbld.sql
[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > cp glogin.sql glogin.sql-201106141153

Add the following line to the end of "glogin.sql" script.

set sqlprompt "&_USER'@'&_CONNECT_IDENTIFIER &_PRIVILEGE> "

Now try to login to database by using SQL*Plus in a telnet session.

[oracle@srvdb01]:/oracle/orahome1/sqlplus/admin > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 14 12:53:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL AS SYSDBA>

As you can see, we set the Oracle SQL*Plus sqlprompt to the username@instance_name the privilege like " as sysdba".

If you apply this in your Unix Oracle database servers, when you have multiple databases in the same server or when you have multiple telnet sessions connected to different database servers and you are always switching between them, you realize in which database you are connected to and it will help a lot not to write the wrong SQL in especially in production databases instead of development or test databases.

How to set the userid parameter in Oracle 10g impdp command line on IBM AIX

2011-06-20T03:05:00.000-07:00

You can set the userid parameter in Oracle 10g impdp command line on IBM AIX OS like in the below example. It can sometimes be confusing to remember the placement of ' and " characters around / as sysdba, in below example it is ' "/ as sysdba" ' with no spaces between ' and ".

[oracle@srvdb01]:/oracle > impdp '"/ as sysdba"' 
directory=DPUMP_DIR  dumpfile=SRVDB01_PROD_PROD_201105101132_tables 
logfile=SRVDB01_PROD_PROD_201105101132_tables_log tables=T0001 remap_schema=U0001:U0002

Oracle 11g New Feature "SQL Result Cache"

2011-06-19T13:56:00.000-07:00

I want to demonstrate one of the new features of Oracle 11g called "SQL Result Cache" which will directly effect the database performance .

I am going to use the same sample schemas that I created in my previous blog post.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:13:02 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user u0001 quota 2G on users;

User altered.

SQL> conn u0001/u0001
Connected.
SQL> create table t0002 as select * from all_objects;

Table created.

SQL> select count(*) from t0002;

COUNT(*)
----------
56100

Populate the records in table t0002

SQL> begin
for c in (select rownum from t0002 where rownum <= 5) loop
    insert into t0002 select * from t0002;
    commit;
  end loop;
end;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL> select count(*) from t0002;

COUNT(*)
----------
1795200

SQL>

Run a GROUP BY query on this table

SQL> set timing on
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:03.11
SQL>

Query took 03.11 seconds
Now run the query so that results of the query will be cached in "SQL Result Cache", we accomplish this by using the "/*+ RESULT_CACHE */" hint in the query.

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:02.87
SQL>

First run of the query took 02.87 seconds

SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP              64
DESTINATION                 64
EDITION                     64
EVALUATION CONTEXT          32
FUNCTION                  6688
INDEXTYPE                  288
JAVA CLASS              730496
JAVA RESOURCE            26816
JOB CLASS                   64
OPERATOR                  1760
PACKAGE                  14080

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                 1216
PROGRAM                    352
SCHEDULE                    96
SCHEDULER GROUP            128
SEQUENCE                   416
SYNONYM                 893216
TABLE                     5152
TRIGGER                     32
TYPE                     49024
VIEW                     63040
WINDOW                     288

OBJECT_TYPE           COUNT(*)
------------------- ----------
XML SCHEMA                1824

23 rows selected.

Elapsed: 00:00:00.05
SQL>

Second run of the query took only 00.05 seconds because the query results has been fetched from "SQL Result Cache" in database server memory without going to disk to execute the SQL.

We can also see from the explain plan that the results of the query is read from SQL Result Cache.

SQL> set autotrace traceonly explain
SQL> r
1* select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4234960643

--------------------------------------------------------------------------------
------------------

| Id  | Operation           | Name                       | Rows  | Bytes | Cost
(%CPU)| Time     |

--------------------------------------------------------------------------------
------------------

|   0 | SELECT STATEMENT    |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   1 |  RESULT CACHE       | 44uuyvrukrh7w7w2m48aqb12tk |       |       |
|          |

|   2 |   SORT GROUP BY     |                            |    57M|   603M|  9677
(26)| 00:01:57 |

|   3 |    TABLE ACCESS FULL| T0002                      |    57M|   603M|  7612
(6)| 00:01:32 |

--------------------------------------------------------------------------------
------------------


Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(U0001.T0002); parameters=(nls); name="selec
t /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type orde
r by object_type"


Note
-----
- dynamic sampling used for this statement (level=2)

SQL>

We can also compare the SQL statisticts and see that SQL with RESULT_CAHCHE hint has no "consistent gets" or "physical reads".

SQL> set autotrace traceonly statistics;
SQL> select object_type, count(*) from t0002 group by object_type order by object_type;

23 rows selected.

Elapsed: 00:00:01.83

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
37542  consistent gets
25909  physical reads
0  redo size
1041  bytes sent via SQL*Net to client
430  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
23  rows processed

SQL> select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type 
order by object_type;

23 rows selected.

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
1041  bytes sent via SQL*Net to client
430  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
23  rows processed

SQL>

Lets now monitor the SQL Result Cache by using related dynamic views.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:41:23 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT name
,      type
,      cache_id
,      row_count
FROM   v$result_cache_objects
ORDER  BY
creation_timestamp;    

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
U0001.T0002
Dependency
U0001.T0002
0


NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------
select /*+ RESULT_CACHE */ object_type, count(*) from t0002 group by object_type
order by object_type
Result
44uuyvrukrh7w7w2m48aqb12tk
23

NAME
--------------------------------------------------------------------------------
TYPE
----------
CACHE_ID
--------------------------------------------------------------------------------
ROW_COUNT
----------

When there will be any DML operation on the dependent object u0001.t0002, the result cache is invalidated and generated again with the next run of SQL.

Monitor the SQL Result Cache statistics

SQL> column name format a30
SQL> column value format a30
SQL> select name, value from v$result_cache_statistics order by id;

NAME                           VALUE
------------------------------ ------------------------------
Block Size (Bytes)             1024
Block Count Maximum            1120
Block Count Current            32
Result Size Maximum (Blocks)   56
Create Count Success           1
Create Count Failure           0
Find Count                     2
Invalidation Count             0
Delete Count Invalid           0
Delete Count Valid             0
Hash Chain Length              1

NAME                           VALUE
------------------------------ ------------------------------
Find Copy Count                2

12 rows selected.

SQL>

"WITH GRANT OPTION" and Cascading Revoke

2011-06-19T12:44:00.000-07:00

Most of you probably already know, but I want to demonstrate again one of the important features of Oracle database called "Cascading Revoke". I will use Oracle 11g as an example database for this demonstration but this feature is also available in the older versions of Oracle databases.

First connect to the database as SYS user.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 12:05:59 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Create sample schemas

SQL> create user u0001 identified by u0001;

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by u0002;

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> create user u0003 identified by u0003;

User created.

SQL> grant create session to u0003;

Grant succeeded.

Create a sample table in u0001 schema and give select privilege on this table to u0002 user with grant option.

SQL> conn u0001/u0001
Connected.
SQL> create table t0001 (col1 number, col2 varchar2(100));

Table created.

SQL> grant select on t0001 to u0002 with grant option;

Grant succeeded.

SQL> column grantee format a7
SQL> column owner format a5
SQL> column table_name format a10
SQL> column grantor format a7
SQL> column privilege format a9
SQL> column grantable format a9
SQL> select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0002   U0001 T0001      U0001   SELECT    YES       NO

Connect with u0002 and grant select privilege on u0001.t0001 table to user u0003

SQL> conn u0002/u0002
Connected.
SQL> grant select on u0001.t0001 to u0003;

Grant succeeded.

Check grants on table u0001.t0001

SQL> conn u0001/u0001
Connected.
SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0003   U0001 T0001      U0002   SELECT    NO        NO
U0002   U0001 T0001      U0001   SELECT    YES       NO

Now revoke select privilege from only user u0002

SQL> revoke select on t0001 from u0002;

Revoke succeeded.

List privileges on table u0001.t0001

SQL> select * from user_tab_privs;

no rows selected

As you can see, although we revoked the select privilege only from user u0002, select privilege given to user u0003 by u0002 user was also revoked, because a "Cascading Revoke" occured.

In summary, any privilege given to any user(u0003) by another grantor user(u0002) having this privilege "WITH GRANT OPTION" will automatically be revoked when this privilege revoked by object owner(u0001) from this grantor user(u0002).

Problem starting the second node of RAC after OS upgrade from IBM AIX v5.3 to v6.1

2011-06-19T08:56:00.000-07:00

We decided to test the IBM AIX OS upgrade operation in a test Oracle RAC environment. We wanted to do this test in a rolling fashion not to cause any downtime in production environment which is two node Oracle 10gR2 RAC database.

What happened was that, after Unix team upgraded the OS on second node of RAC from IBM AIX v5.3 to v6.1, we restarted the second node and we saw that Oracle crs processes did not start successfully on the second node.

We checked the CRS services

[root@srvdb01]:/home/root > crsstat
HA Resource                       Target  State
-----------                       ------  -----
ora.ORCL.ORCL1.inst               ONLINE  ONLINE on srvdb01
ora.ORCL.db                       ONLINE  ONLINE on srvdb01
ora.srvdb01.ASM1.asm              ONLINE  ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr ONLINE  ONLINE on srvdb01
ora.srvdb01.gsd                   ONLINE  ONLINE on srvdb01
ora.srvdb01.ons                   ONLINE  ONLINE on srvdb01
ora.srvdb01.vip                   ONLINE  ONLINE on srvdb01
ora.ORCL.ORCL2.inst               OFFLINE OFFLINE
ora.srvdb02.ASM2.asm              OFFLINE OFFLINE
ora.srvdb02.LISTENER_SRVDB02.lsnr OFFLINE OFFLINE
ora.srvdb02.gsd                   OFFLINE OFFLINE
ora.srvdb02.ons                   OFFLINE OFFLINE
ora.srvdb02.vip                   ONLINE  ONLINE on srvdb02

As you can see from the above output, "ora.srvdb02.vip" did not automatically moved to node1, so first we relocated that vip service to node1. Because client sessions trying to connect to the database by using this vip address was getting connection errors.

[root@srvdb01]:/home/root > $CRS_HOME/bin/crs_relocate ora.srvdb02.vip

Status after relocate,

[root@srvdb01]:/home/root > crsstat
HA Resource                       Target State
-----------                       ------  -----
ora.ORCL.ORCL1.inst               ONLINE  ONLINE on srvdb01
ora.ORCL.db                       ONLINE  ONLINE on srvdb01
ora.srvdb01.ASM1.asm              ONLINE  ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr ONLINE  ONLINE on srvdb01
ora.srvdb01.gsd                   ONLINE  ONLINE on srvdb01
ora.srvdb01.ons                   ONLINE  ONLINE on srvdb01
ora.srvdb01.vip                   ONLINE  ONLINE on srvdb01
ora.ORCL.ORCL2.inst               OFFLINE OFFLINE
ora.srvdb02.ASM2.asm              OFFLINE OFFLINE
ora.srvdb02.LISTENER_SRVDB02.lsnr OFFLINE OFFLINE
ora.srvdb02.gsd                   OFFLINE OFFLINE
ora.srvdb02.ons                   OFFLINE OFFLINE
ora.srvdb02.vip                   ONLINE  ONLINE on srvdb01

By the way if you do not have "crsstat" command on your server, you can use "crs_stat -t" command instead or you can put the following script in a folder and add its path to the "PATH" environment variable in "oracle" user profile on your server. "crs_stat -t" command does not display the full name of "HA Resource" but "crsstat" command below will display full names of "HA Resource" column.

[oracle@srvdb01]:/oracle/uural > more crsstat
#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
#AWK=/usr/xpg4/bin/awk    # if not available use /usr/bin/awk
AWK=/usr/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

[oracle@srvdb01]:/oracle/uural >

Anyway we checked the reason why CRS services was down on the second node after OS upgrade. We checked the CRS related logfiles on node2.
We saw the following error.

[root@srvdb02]:/oracle/crshome1/log/srvdb02/client > vi ocrcheck_327920.log
Oracle Database 10g CRS Release 10.2.0.4.0 Production Copyright 1996, 2008 Oracle.  All rights reserved.
2011-06-14 16:13:48.455: [OCRCHECK][1]ocrcheck starts...
2011-06-14 16:13:48.457: [  OCROSD][1]utopen:7:failed to open OCR file/disk /dev/ocr_disk1 /dev/ocr_disk2, errno=19, os err string=No such device
2011-06-14 16:13:48.457: [  OCRRAW][1]proprinit: Could not open raw device
2011-06-14 16:13:48.457: [ default][1]a_init:7!: Backend init unsuccessful : [26]
2011-06-14 16:13:48.457: [OCRCHECK][1]Failed to access OCR repository: [PROC-26: Error while accessing the physical storage Operating System error [No such device] [19]]
2011-06-14 16:13:48.457: [OCRCHECK][1]Failed to initialize ocrchek2
2011-06-14 16:13:48.457: [OCRCHECK][1]Exiting [status=failed]...

We thought that node2 could not access the OCR disk anyway. We checked the OCR disk configuration on node2.

[root@srvdb02]:/home/root > ls -l /dev/ocr*
crw-r-----    1 root     dba          23, 27 Jun 08 15:14 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jun 08 15:16 /dev/ocr_disk2

When we first installed Oracle RAC, we created the OCR disks as below

mknod /dev/ocr_disk1 c 23 27
mknod /dev/ocr_disk2 c 23 5

Querying the corresponding hdisks by using the disk access path "major, minor" numbers gave no results

[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23, 27"
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23,  5"

We realized that major numbers of hdisks have changed after OS upgrade.

[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "21, 27"
brw-------    1 root     system       21, 27 Jun 14 15:34 /dev/hdisk25
[root@srvdb02]:/home/root > ls -l /dev/hdisk* | grep "23,  5"
brw-------    1 root     system       21,  5 Jun 14 15:34 /dev/hdisk3

But how could we be sure that the minor numbers did not change after OS upgrade. To check this we need to get one level deeper and compare the LUN ids of those hdisks with the ones on the node1 which has stil IBM AIX v5.3 OS.

We checked the ocr disk configuration on node1

[root@srvdb01]:/home/root > ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  4 Jun 19 15:52 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jun 19 15:52 /dev/ocr_disk2
[root@srvdb01]:/home/root > ls -l /dev/hdisk* | grep "23,  4"
brw-------    1 root     system       23,  4 May 16 2008  /dev/hdisk2
[root@srvdb01]:/home/root > ls -l /dev/hdisk* | grep "23,  5"
brw-------    1 root     system       23,  5 May 16 2008  /dev/hdisk3

Find the LUN ids of those hdisks on node1

[root@srvdb01]:/home/root > for i in 2 3
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk2 -> lun_id = 0x0001000000000000 / reserve_policy = no_reserve / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = no_reserve / pvid = none

Find the LUN ids of hdisks on node2 which were configured as ocr disks

[root@srvdb02]:/home/root > for i in 25 3
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk25 -> lun_id = 0x0018000000000000 / reserve_policy = single_path / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = single_path / pvid = none

As we compare the LUN ids of those disks on both nodes, we saw that they did not match, which means not only the major numbers but also the minor numbers of hdisks had been changed on node2 after the OS upgrade on this node.
Since we could not find so much information about this problem on the Internet, we discussed with the Unix guys about the problem and we decided that the problem of changing disk access path major minor number change could be the result of IBM AIX v6.1 started to use MPIO to manage storage although IBM AIX v5.3 was using RDAC. Although the Unix guys said they tried to changed the MPIO to RDAC after the upgrade on node2, they said this corrected only the major number change and set the major numbers to the old original values but still the minor numbers were scrambled causing the Oracle RAC node2 to fail to start CRS services.

In any case like that if the Unix administrators are not able to bring back the original major,minor numbers of the hdisks, what you can do is find the corresponding hdisks numbers by matching the LUN ids on node2, remove ocr disks and create them againg with the correct hdisk major, minor numbers and try to start the OCR on node2.

I will demonstrate how to find the matching hdisks on node2 by using LUN ids on node1.
List the LUN ids on node2

[root@srvdb02]:/home/root > for i in 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
> do
> lsattr -El hdisk$i | grep reserve_policy | awk '{print $1,$2 }'| read rp1 rp2
> lsattr -El hdisk$i | grep pvid | awk '{print $1,$2 }'| read pv1 pv2
> lsattr -El hdisk$i | grep lun_id | awk '{print $1,$2 }'| read li1 li2
> if [ "$li1" != "" ]
> then
> echo hdisk$i' -> '$li1' = '$li2' / '$rp1' = '$rp2' / '$pv1' = '$pv2
> fi
> done
hdisk1 -> lun_id = 0x0000000000000000 / reserve_policy = single_path / pvid = none
hdisk2 -> lun_id = 0x0001000000000000 / reserve_policy = single_path / pvid = none
hdisk3 -> lun_id = 0x0002000000000000 / reserve_policy = single_path / pvid = none
hdisk4 -> lun_id = 0x0003000000000000 / reserve_policy = single_path / pvid = none
hdisk5 -> lun_id = 0x0004000000000000 / reserve_policy = single_path / pvid = none
hdisk6 -> lun_id = 0x0005000000000000 / reserve_policy = single_path / pvid = none
hdisk7 -> lun_id = 0x0006000000000000 / reserve_policy = single_path / pvid = none
hdisk8 -> lun_id = 0x0007000000000000 / reserve_policy = single_path / pvid = none
hdisk9 -> lun_id = 0x0008000000000000 / reserve_policy = single_path / pvid = none
hdisk10 -> lun_id = 0x0009000000000000 / reserve_policy = single_path / pvid = none
hdisk11 -> lun_id = 0x000a000000000000 / reserve_policy = single_path / pvid = none
hdisk12 -> lun_id = 0x000b000000000000 / reserve_policy = single_path / pvid = none
hdisk13 -> lun_id = 0x000c000000000000 / reserve_policy = single_path / pvid = none
hdisk14 -> lun_id = 0x000d000000000000 / reserve_policy = single_path / pvid = none
hdisk15 -> lun_id = 0x000e000000000000 / reserve_policy = single_path / pvid = none
hdisk16 -> lun_id = 0x000f000000000000 / reserve_policy = single_path / pvid = none
hdisk17 -> lun_id = 0x0010000000000000 / reserve_policy = single_path / pvid = none
hdisk18 -> lun_id = 0x0011000000000000 / reserve_policy = single_path / pvid = none
hdisk19 -> lun_id = 0x0012000000000000 / reserve_policy = single_path / pvid = none
hdisk20 -> lun_id = 0x0013000000000000 / reserve_policy = single_path / pvid = none
hdisk21 -> lun_id = 0x0014000000000000 / reserve_policy = single_path / pvid = none
hdisk22 -> lun_id = 0x0015000000000000 / reserve_policy = single_path / pvid = none
hdisk23 -> lun_id = 0x0016000000000000 / reserve_policy = single_path / pvid = none
hdisk24 -> lun_id = 0x0017000000000000 / reserve_policy = single_path / pvid = none
hdisk25 -> lun_id = 0x0018000000000000 / reserve_policy = single_path / pvid = none
hdisk26 -> lun_id = 0x0019000000000000 / reserve_policy = single_path / pvid = none
hdisk27 -> lun_id = 0x001a000000000000 / reserve_policy = single_path / pvid = none
hdisk28 -> lun_id = 0x001b000000000000 / reserve_policy = single_path / pvid = none
hdisk29 -> lun_id = 0x001c000000000000 / reserve_policy = single_path / pvid = none
hdisk30 -> lun_id = 0x001d000000000000 / reserve_policy = single_path / pvid = none
hdisk31 -> lun_id = 0x001e000000000000 / reserve_policy = single_path / pvid = none
hdisk32 -> lun_id = 0x001f000000000000 / reserve_policy = single_path / pvid = none
hdisk33 -> lun_id = 0x0028000000000000 / reserve_policy = single_path / pvid = 000c84c103c1d4480000000000000000
hdisk34 -> lun_id = 0x0020000000000000 / reserve_policy = single_path / pvid = none
hdisk35 -> lun_id = 0x0021000000000000 / reserve_policy = single_path / pvid = none
hdisk36 -> lun_id = 0x0022000000000000 / reserve_policy = single_path / pvid = none
hdisk37 -> lun_id = 0x0023000000000000 / reserve_policy = single_path / pvid = none
hdisk38 -> lun_id = 0x0024000000000000 / reserve_policy = single_path / pvid = none
hdisk39 -> lun_id = 0x0025000000000000 / reserve_policy = single_path / pvid = none
hdisk40 -> lun_id = 0x0026000000000000 / reserve_policy = single_path / pvid = none
hdisk41 -> lun_id = 0x0027000000000000 / reserve_policy = single_path / pvid = none

As you can see from the following output, if we match hdisk2 and hdisk3 LUN ids on node1 to LUN ids on node2 we found that they correspond again to the hdisk2 and hdisk3 on node2. This time LUN ids and hdisk names matches on both nodes but this is not always like that, hdisk names does not necessarily have to match on both servers everytime, the important thing is LUN ids should match, hdisk2 on node1 could match to hdisk18 on node2 in any other occasion.

In our case, hdisk2 and hdisk3 on node1 are the same disks as hdisk2 and hdisk3 on node2.
We need to find major, minor numbers of those disks on node2.

[root@srvdb02]:/home/root > ls -l /dev/hdisk[2,3]
brw-------    1 root     system       21,  4 Jun 14 15:34 /dev/hdisk2
brw-------    1 root     system       21,  5 Jun 14 15:34 /dev/hdisk3

Delete old ocr disk device definitions

[root@srvdb02]:/home/root > rm /dev/ocr_disk1
[root@srvdb02]:/home/root > rm /dev/ocr_disk2

Recreate ocr disks with the correct disk major,minor numbers

[root@srvdb02]:/home/root > mknod /dev/ocr_disk1 c 21 4
[root@srvdb02]:/home/root > mknod /dev/ocr_disk2 c 21 5

After completing this configuration you can first check the status and then try to restart CRS services

[root@srvdb02]:/home/root > crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
[root@srvdb02]:/home/root > crsctl start crs

How to delete and then add the same Oracle RAC node to an Oracle 10gR2 cluster

2011-06-18T18:47:00.000-07:00

If you will need this kind of operation any time, you can use the following steps to remove and then add the same Oracle RAC node to an Oracle 10gR2 Cluster.

1-) Remove node2 from cluster :

First we backed up the OCR configuration (in case we need it)

[root@srvdb01]:/home/root > ocrconfig -export beforeupgrade -s online

Then we got RAC network configuration on first node.

[root@srvdb01]:/home/root > oifcfg getif
en8  10.1.11.0  global  public
en9  192.168.1.0  global  cluster_interconnect

List nodeapps

[root@srvdb01]:/home/root > crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.ORCL.ORCL1.inst                           ONLINE     ONLINE on srvdb01
ora.ORCL.db                                   ONLINE     ONLINE on srvdb01
ora.srvdb01.ASM1.asm                          ONLINE     ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr             ONLINE     ONLINE on srvdb01
ora.srvdb01.gsd                               ONLINE     ONLINE on srvdb01
ora.srvdb01.ons                               ONLINE     ONLINE on srvdb01
ora.srvdb01.vip                               ONLINE     ONLINE on srvdb01
ora.ORCL.ORCL2.inst                           ONLINE     ONLINE on srvdb02
ora.srvdb02.ASM2.asm                          ONLINE     ONLINE on srvdb02
ora.srvdb02.LISTENER_SRVDB02.lsnr             ONLINE     ONLINE on srvdb02
ora.srvdb02.gsd                               ONLINE     ONLINE on srvdb02
ora.srvdb02.ons                               ONLINE     ONLINE on srvdb02
ora.srvdb02.vip                               ONLINE     ONLINE on srvdb02

On node1 we removed the instance ORCL2 running on node2 by using $ORACLE_HOME/bin/dbca.

[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/dbca
Choose RAC database
Choose Instance Management
Choose Delete Instance

On node1 we stopped and removed the ASM2 instance running on node2.

[root@srvdb01]:/home/root > srvctl stop asm -n srvdb02
[root@srvdb01]:/home/root > srvctl remove asm -n srvdb02

On node2 we removed the instance LISTENER_SRVDB02 running on node2 by using $ORACLE_HOME/bin/netca.

[oracle@srvdb02]:/oracle > $ORACLE_HOME/bin/netca
Choose Cluster management
Choose Listener
Choose Remove
Confirm deletion of LISTENER_SRVDB02

On node2 update node list in $ORACLE_HOME

[oracle@srvdb02]:/oracle > $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="srvdb02" -local

On node1 update node list in $ORACLE_HOME

[oracle@srvdb01]:/oracle > $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="srvdb01"

Remove node2 from cluster

[oracle@srvdb01]:/oracle > $CRS_HOME/bin/racgons remove_config srvdb02:6201

( Replace port 6201 in the above command with the number that you see in the remote port section of the ons.config file in the CRS_HOME/opmn/conf diretory )

On node2

[root@srvdb02]:/home/root > $CRS_HOME/install/rootdelete.sh

List cluster nodes.

[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1
srvdb02       2

On node1

[root@srvdb01]:/home/root > $CRS_HOME/install/rootdeletenode.sh srvdb02,2

On node2 update node list in $CRS_HOME

[oracle@srvdb02]:/oracle > $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$CRS_HOME CLUSTER_NODES="srvdb02" CRS=TRUE -local

On node1 update node list in $CRS_HOME

[oracle@srvdb01]:/oracle > $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=$CRS_HOME CLUSTER_NODES="srvdb01" CRS=TRUE

Check the nodeapps have been removed on node2

[root@srvdb01]:/home/root > srvctl status nodeapps -n srvdb02

List cluster nodes.

[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1

2-) Add node2 back to cluster :

Add Cluster Home

[oracle@srvdb01]:/oracle > $CRS_HOME/oui/bin/addNode.sh -nocopy

Add node2 to cluster

[oracle@srvdb01]:/oracle > $CRS_HOME/bin/racgons add_config srvdb02:6201

( Replace port 6201 in the above command with the number that you see in the remote port section of the ons.config file in the CRS_HOME/opmn/conf diretory )

Add Oracle Home

[oracle@srvdb01]:/oracle > $ORACLE_HOME/oui/bin/addNode.sh -nocopy

Configure VIP of node2

[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/vipca -nodelist srvdb02

On node2 we added the instance LISTENER_SRVDB02 running on node2 by using $ORACLE_HOME/bin/netca.

[oracle@srvdb02]:/oracle > $ORACLE_HOME/bin/netca
Choose Cluster management
Choose Listener
Choose Add
Choose the name as LISTENER
This will add a listener on node2 with the name LISTENER_SRVDB02

On node1 we added the instance ORCL2 running on node2 by using $ORACLE_HOME/bin/dbca with this step we also created the ASM2 instance.

[oracle@srvdb01]:/oracle > $ORACLE_HOME/bin/dbca
Choose RAC
Choose Instance Management
Choose Add Instance

List cluster nodes.

[root@srvdb01]:/home/root > olsnodes -n
srvdb01       1
srvdb02       2

List nodeapps

[root@srvdb01]:/home/root > crsstat
HA Resource                                   Target     State
-----------                                   ------     -----
ora.ORCL.ORCL1.inst                           ONLINE     ONLINE on srvdb01
ora.ORCL.db                                   ONLINE     ONLINE on srvdb01
ora.srvdb01.ASM1.asm                          ONLINE     ONLINE on srvdb01
ora.srvdb01.LISTENER_SRVDB01.lsnr             ONLINE     ONLINE on srvdb01
ora.srvdb01.gsd                               ONLINE     ONLINE on srvdb01
ora.srvdb01.ons                               ONLINE     ONLINE on srvdb01
ora.srvdb01.vip                               ONLINE     ONLINE on srvdb01
ora.ORCL.ORCL2.inst                           ONLINE     ONLINE on srvdb02
ora.srvdb02.ASM2.asm                          ONLINE     ONLINE on srvdb02
ora.srvdb02.LISTENER_SRVDB02.lsnr             ONLINE     ONLINE on srvdb02
ora.srvdb02.gsd                               ONLINE     ONLINE on srvdb02
ora.srvdb02.ons                               ONLINE     ONLINE on srvdb02
ora.srvdb02.vip                               ONLINE     ONLINE on srvdb02

The difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup

2011-06-18T12:40:00.000-07:00

Most probably most of you know but I wanted to demonstrate the difference between "DELETE INPUT" and "DELETE ALL INPUT" after RMAN archivelog backup.

Lets first check the current archivelog destinations in the target database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 18 12:16:24 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      LOCATION=USE_DB_RECOVERY_FILE_
DEST
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL>

As you can see there are two archivelog destinations defined "log_archive_dest_1" and "log_archive_dest_2".

Lets list the archivelogs in the target database by using RMAN tool.

[oracle@localhost ORCL]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 18 12:24:32 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

24      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

26      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc


RMAN>

Lets now try to backup and delete backed up archivelogs by using "DELETE INPUT".

RMAN> backup archivelog all delete input;

Starting backup at 18-JUN-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=404 RECID=24 STAMP=754143819
input archived log thread=1 sequence=405 RECID=26 STAMP=754143861
input archived log thread=1 sequence=406 RECID=28 STAMP=754143995
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T122636_6zsyzf26_.bkp tag=TAG20110618T122636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_404_6zsysvtq_.arc RECID=24 STAMP=754143819
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_405_6zsyv5hp_.arc RECID=26 STAMP=754143861
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_406_6zsyzcjd_.arc RECID=28 STAMP=754143995
Finished backup at 18-JUN-11

Now list the remaining archivelog files, since only one copy of archivelog files are deleted, we still see the first copies of archivelogs remaining in the first archivelog destination.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
23      1    404     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_404_701609923.dbf

25      1    405     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_405_701609923.dbf

27      1    406     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_406_701609923.dbf


RMAN>

Now lets try to backup and delete the all the archivelogs backed up by using "DELETE ALL INPUT". We will start the demonstration with newly generated archivelogs.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
29      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf

30      1    407     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc

31      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf

32      1    408     A 18-JUN-11
Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc

Backup and delete all of them.

RMAN> backup archivelog all delete all input;

Starting backup at 18-JUN-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=407 RECID=30 STAMP=754144245
input archived log thread=1 sequence=408 RECID=32 STAMP=754144248
input archived log thread=1 sequence=409 RECID=34 STAMP=754144369
channel ORA_DISK_1: starting piece 1 at 18-JUN-11
channel ORA_DISK_1: finished piece 1 at 18-JUN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_06_18/o1_mf_annnn_TAG20110618T123249_6zszc1s6_.bkp tag=TAG20110618T123249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_407_6zsz7586_.arc RECID=30 STAMP=754144245
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_407_701609923.dbf RECID=29 STAMP=754144245
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_408_6zsz78g4_.arc RECID=32 STAMP=754144248
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_408_701609923.dbf RECID=31 STAMP=754144248
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_18/o1_mf_1_409_6zszc108_.arc RECID=34 STAMP=754144369
archived log file name=/home/oracle/app/oracle/arc2/ORCL/1_409_701609923.dbf RECID=33 STAMP=754144369
Finished backup at 18-JUN-11

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN>

As a result, by using "DELETE INPUT" we made RMAN, backup only one copy of all the archivelogs in one archivelog destination and then delete only the backed up copies of all the archivelogs in only one archivelog destination, but by using "DELETE ALL INPUT", we made RMAN backup only one copy of all the archivelogs in one archivelog destination and then delete all the copies of all the archivelogs in all the archivelog destinations.

Monitoring last RMAN backup log in target Oracle database in Oracle 10g

2011-06-18T06:11:00.000-07:00

You can use two simple SQL commands to monitor the last RMAN backup log in target Oracle database without using RMAN catalog database in Oracle 10g.

The information in "v$rman_status" comes from the target database controlfile, showing one record for every RMAN command executed in RMAN backup script.

select * 
from v$rman_status 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;

The information in "v$rman_output" comes from the target database memory and is not stored in target database controlfile, showing all the output as seperate records for every RMAN command executed in RMAN backup script.

In a single server database you should use "v$rman_output", if you have a RAC database you should use "gv$rman_output" dynamic view instead.

select * 
from v$rman_output 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid ;

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

2011-06-17T06:45:00.000-07:00

Today, we faced an error in some of our daily Oracle database export jobs as below.

;;;
Export: Release 10.2.0.3.0 - 64bit Production on Friday, 17 June, 2011 0:30:00

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/export/SRVDB01_DEV_DEV_201106170030_01.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 4

Working together with our Unix team, we tried to solve the problem.
First we checked the "/export" NFS file system mount options on AIX server.

[oracle@srvdb01]:/oracle > mount -p
node       mounted        mounted over    vfs       date        options
-------- ---------------  ---------------  ------ ------------ ---------------
srvnfs01 /export          /export          nfs3   Jun 17 14:02 bg,hard,intr,rsize=32768,wsize=32768,proto=udp,sec=sys,rw

We saw that the mount options are correct on the database server where the "/export" filesystem is mounted.

Then, one of our Unix team members realized that file access permissions on "/etc/filesystems" file does not have a read permission for the Unix user "oracle" and he gave this permission so that "oracle" Unix user can read this file.

--Before
[oracle@srvdb01]:/oracle > ls -ld /etc/filesystems
-rw-rw----    1 root     system         3107 Jun 17 14:38 /etc/filesystems

--After
[oracle@srvdb01]:/oracle > ls -ld /etc/filesystems
-rw-r--r--    1 root     system         3107 Jun 17 14:38 /etc/filesystems

And the Oracle export can not write to "/export" NFS filesystem problem is solved.

What I think Oracle Export Datapump Utility does is, it reads "/etc/filesystems" file when exporting to an NFS file system. This file contains an entry about the mounted "/export" NFS filesystem in it.

[oracle@srvdb01]:/oracle > more /etc/filesystems
...
/export:
dev             = "/export"
vfs             = nfs
nodename        = srvnfs01
mount           = true
options         = bg,hard,intr,rsize=32768,wsize=32768,proto=udp,sec=sys,rw
account         = false
...

Parallel index rebuild

2010-02-19T09:44:00.001-08:00

You can use parallel index rebuild option whenever you have to rebuild an index since, most of the time, parallel rebuild option will be much faster than a noparallel operation.

I will try to demonstrate this situation below.

First create a tablespace and a database user

C:\uural\work_cmd\20081014>sqlplus sys@db01 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:16:33 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@db01> create tablespace tbs01;

Tablespace created.

SYS@db01> create user dbuser01 identified by dbuser01 default tablespace tbs01;

User created.

SYS@db01> grant connect, resource to dbuser01;

Grant succeeded.

SYS@db01> grant alter system to dbuser01;

Grant succeeded.

Connect to the database with the new user

C:\uural\work_cmd\20100219>sqlplus dbuser01/dbuser01@db01

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:32:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

DBUSER01@db01>

Create a test table

DBUSER01@db01> @cre_table01
DBUSER01@db01> create table table01 nologging pctfree 90
2  as
3  select dbms_random.value(1,1000000) id
4  , rpad('*',4000,'*') data
5    from dual
6  connect by level <= 1000000;

Table created.

Create an index on this test table

DBUSER01@db01> select blocks,bytes from user_segments where segment_name = 'TABLE01';

BLOCKS BYTES
---------- ----------
1007616 8254390272

1 row selected.

DBUSER01@db01> create index table01_01 on table01(id) nologging;
Index created.

Make this index unusable

DBUSER01@db01> alter index table01_01 unusable;

Clear the buffer cache of the database

SYS@db01> alter system flush buffer_cache;

System altered.

Set timing on

DBUSER01@db01> show timing
timing OFF
DBUSER01@db01> set timing on

Enable session trace

DBUSER01@db01> alter system set sql_trace=TRUE;

System altered.

Elapsed: 00:00:00.00

Run NOPARALLEL index rebuild

DBUSER01@db01> alter index table01_01 rebuild nologging;

Index altered.

Elapsed: 00:01:01.20

It took 1 minutes 1 secondsCheck the trace file

********************************************************************************

alter index table01_01 rebuild nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          1          1          0           0
Execute      1     20.11      59.45    1000005    1000174       6897           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     20.11      59.49    1000006    1000175       6897           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378
********************************************************************************

Before trying the PARALLEL option clear the buffer cache again

SYS@db01> alter system flush buffer_cache;

System altered.

Rebuild the index with PARALLEL option this time

DBUSER01@db01> alter index table01_01 unusable;

Index altered.

Elapsed: 00:00:00.07
DBUSER01@db01> alter index table01_01 rebuild parallel nologging;

Index altered.

Elapsed: 00:00:30.62
DBUSER01@db01>

It took 30 secondsCheck the trace file again

********************************************************************************

alter index table01_01 rebuild parallel nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          1          1          0           0
Execute      1      0.06      29.72    1005359    1003636       9010           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06      29.73    1005360    1003637       9010           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378



********************************************************************************

As you can see parallel index rebuild is much faster than noparallel index rebuild.
Since, most of the time, parallel operations use the server CPU more efficiently, especially in data warehouse databases
we better use the parallel option in database operations whenever possible.

Regards,
Ural

How to kill a session which is connected to another RAC node in an Oracle 10g RAC database

2010-02-19T03:18:00.001-08:00

If you want to kill a session which is connected to another node of an Oracle 10g RAC database, there is no one direct single command provided by Oracle which you can use, this became possible with the version 11g of Oracle RAC database.

For example, assume that you have a 2-node Oracle 10g RAC database and you are currently connected to the first node with SYS user. You want to kill a session that is connected to the second node of RAC database. This is not possible with one single command in 10g. You can use the database package that I developed below which uses database job method to kill a session that is connected on another node of Oracle 10g RAC database. I also tried to explain the situation below.

First connect to the first node of 2-node Oracle 10g RAC database.

C:\Documents and Settings\user01>sqlplus sys@server01_inst1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 09:59:03 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Bring some information about this RAC database

SQL> set lines 200
SQL> col host_name format a30
SQL> r
1  select inst_id, instance_number, instance_name, host_name, version, status
2* from gv$instance

INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
---------- --------------- ---------------- ------------------------------ ----------------- ------------
1               1 INST1            server01                       10.2.0.4.0        OPEN
2               2 INST2            server02                       10.2.0.4.0        OPEN

I am connected to the fist node

SQL> select instance_number, instance_name, host_name, version, status
2  from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
--------------- ---------------- ------------------------------ ----------------- ------------
1 INST1            server01                       10.2.0.4.0        OPEN

Create a schema in which I will create my package

SQL> create user dbuser01 identified by dbuser01;

User created.

SQL> grant create session to dbuser01;

Grant succeeded.

SQL> grant select on gv_$session to dbuser01;

Grant succeeded.

SQL> grant alter system to dbuser01;

Grant succeeded.

SQL> grant create job to dbuser01;

Grant succeeded.

Create the package

SQL> CREATE OR REPLACE PACKAGE dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2);
4  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
5                             p_serial  NUMBER,
6                             p_inst_id NUMBER);
7  PROCEDURE Kill_Session(p_sid     NUMBER,
8                         p_serial  NUMBER);
9  END DBA_OPERATION;
10  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2) IS
4    d_stmt VARCHAR2(4000);
5  BEGIN
6    FOR c_stmt IN (
7      SELECT t1.sid, t1.serial#, t1.inst_id
8        FROM gv$session t1
9       WHERE t1.username = p_username
10     ) LOOP
11      Kill_Session_Job(p_sid     => c_stmt.sid,
12                       p_serial  => c_stmt.serial#,
13                       p_inst_id => c_stmt.inst_id);
14    END LOOP;
15    DBMS_OUTPUT.Put_Line('===== All the database sessions of user "'||p_username||'" killed successfully ! =====');
16
17  END Kill_Session_User;
18
19  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
20                             p_serial  NUMBER,
21                             p_inst_id NUMBER) IS
22    JOB BINARY_INTEGER;
23    d_stmt VARCHAR2(4000);
24  BEGIN
25    d_stmt := 'begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => '||p_sid||', p_serial => '||p_serial||'); end;';
26    SYS.DBMS_JOB.Submit(job => job, what => d_stmt, instance => p_inst_id);
27    COMMIT;
28    DBMS_OUTPUT.Put_Line(d_stmt);
29  END Kill_Session_Job;
30
31  PROCEDURE Kill_Session(p_sid     NUMBER,
32                         p_serial  NUMBER) IS
33    d_stmt VARCHAR2(4000);
34  BEGIN
35    d_stmt := 'ALTER SYSTEM KILL SESSION '''||p_sid||', '||p_serial||''' IMMEDIATE';
36    DBMS_OUTPUT.Put_Line(d_stmt);
37    EXECUTE IMMEDIATE d_stmt;
38  END Kill_Session;
39
40  END DBA_OPERATION;
41  /

Package body created.

SQL>

Create a second user which we will use in kill session test

SQL> create user dbuser02 identified by dbuser02;

User created.

SQL> grant create session to dbuser02;

Grant succeeded.

Connect to the second node of RAC with this user

C:\Documents and Settings\user01>sqlplus dbuser02/dbuser02@server02_inst2

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 10:22:58 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

Return to the first session on node1 and try to kill the session of dbuser02 which is on node2.
Query the session info of dbuser02

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

SID    SERIAL#    INST_ID
---------- ---------- ----------
260      28071          2

If we are on the same node as the session that we want to kill, we would use command below, but since the session to be killed is on another node you can not use the below command

SQL> alter system kill session '260, 28071';
alter system kill session '260, 28071'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

If the database version would have been 11g then you can could use the below command, but in 10g not

SQL> alter system kill session '260, 28071, @2';

So in that case we can use the package procedure which uses database job method and kills a specific session or all of the sessions of a specific user, no mather which node they are connected

To kill only one specific session

SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_Job(p_sid => 260, p_serial => 28071, p_inst_id => 2);
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.sid = 260
4     AND t1.serial# = 28071
5     AND t1.inst_id = 2;

no rows selected

To kill all of the sessions of a specific user no mather which node they are connected

SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_User(p_username => 'DBUSER02');
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;
===== All the database sessions of user "DBUSER02" killed successfully ! =====

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

no rows selected

Regards,
Ural

Oracle background processes still use and lock the dropped ASM disks and these disks can not be removed and assigned to another server without restart

2010-02-11T12:26:00.001-08:00

Oracle background processes still use and lock the dropped ASM disks and these disks can not be removed and assigned to another server without restarting the databases which are locking this ASM disks.

Hi All,

Today, our Unix Admins group wanted to take back a storage LUN (/dev/hdisk19) which had been given temporarly to be used for a project and assigned to the the ASM of one of our Oracle databases.

We use Oracle Database Enterprise Edition and ASM version 10.2.0.4.

As a normal procedure I dropped the ASM disk which is corresponding to this LUN.
First I connected to the ASM instance and checked the ASM disk name of the related ASM device.

SQL> select name, substr(path, 1, 20) from v$asm_disk order by name;

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0000            /dev/ASM_Disk1
DG_DB_ASM_DB01_0001            /dev/ASM_Disk2
DG_DB_ASM_DB01_0002            /dev/ASM_Disk3
DG_DB_ASM_DB01_0003            /dev/ASM_Disk4
DG_DB_ASM_DB01_0004            /dev/ASM_Disk5

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0005            /dev/ASM_Disk6
DG_DB_ASM_DB01_0006            /dev/ASM_Disk13
DG_DB_ASM_DB01_0007            /dev/ASM_Disk14
DG_DB_ASM_DB01_0008            /dev/ASM_Disk15
DG_DB_ASM_DB01_0009            /dev/ASM_Disk17

10 rows selected.

I used the following command to drop the ASM disk.

ALTER DISKGROUP DG_DB_ASM_DB01 DROP DISK DG_DB_ASM_DB01_0009;

After this command completes, Unix Admin group tried to remove the hdisk (/dev/hdisk19) corresponding to this device and they said that there were some processes in the Unix server (IBM AIX) which were using this hdisk and because of that they can not remove it.

I checked again to find out which processes use this ASM disk corresponding this ASM device.

[osuser01@os01]:/oracle > fuser /dev/ASM_Disk17
/dev/ASM_Disk17:   200796  430276  675882  712952  745630  749608  757766  782390  892942 1220622 1515754 1613864 1765430 1921252 1945674
[osuser01@os01]:/oracle > ps -ef|grep  200796
oracle  200796       1   0 16:52:57      -  0:00 oracle+ASM (LOCAL=NO)
oracle 1146924 1589428   0 17:56:00  pts/2  0:00 grep 200796
[osuser01@os01]:/oracle > ps -ef|grep  430276
oracle  430276       1   0   Oct 06      - 20:01 asm_gmon_+ASM
oracle 1589430 1146926   0 17:56:05  pts/2  0:00 grep 430276
[osuser01@os01]:/oracle > ps -ef|grep  675882
oracle  675882       1   0   Oct 06      - 26:20 ora_cjq0_DB01
oracle 1400942 1065140   0 17:56:09  pts/2  0:00 grep 675882
[osuser01@os01]:/oracle > ps -ef|grep  712952
oracle  712952       1   0   Oct 06      - 10:15 ora_smon_DB01
oracle 1065142 1400944   0 17:56:14  pts/2  0:00 grep 712952
[osuser01@os01]:/oracle > ps -ef|grep  745630
oracle  745630       1   0   Oct 06      - 207:01 ora_ckpt_DB01
oracle 1380554 1400950   0 17:56:19  pts/2  0:00 grep 745630
[osuser01@os01]:/oracle > ps -ef|grep  749608
oracle  749608       1   0   Oct 06      - 58:08 ora_lgwr_DB01
oracle 1065156 1380564   0 17:56:25  pts/2  0:00 grep 749608
[osuser01@os01]:/oracle > ps -ef|grep  757766
oracle  757766       1   0 09:27:29      -  0:11 ora_j000_DB01
oracle 1876066 1589452   0 17:56:31  pts/2  0:00 grep 757766
[osuser01@os01]:/oracle > ps -ef|grep  782390
oracle  782390       1   0   Oct 06      - 3793:04 ora_dbw0_DB01
oracle 1589454 1876068   0 17:56:42  pts/2  0:00 grep 782390
[osuser01@os01]:/oracle > ps -ef|grep  892942
oracle  892942       1   0   Oct 06      -  1:59 ora_rbal_DB01
oracle 1589456 1065172   0 17:56:48  pts/2  0:00 grep 892942
[osuser01@os01]:/oracle > ps -ef|grep  1220622
oracle 1065174 1589460   0 17:56:54  pts/2  0:00 grep 1220622
oracle 1220622       1   0   Oct 06      - 15:45 ora_mmon_DB01
[osuser01@os01]:/oracle > ps -ef|grep  1515754
oracle 1400968 1876080   0 17:57:02  pts/2  0:00 grep 1515754
oracle 1515754       1   0   Oct 06      -  2:55 asm_rbal_+ASM
[osuser01@os01]:/oracle > ps -ef|grep  1613864
oracle 1065184 1876088   0 17:57:13  pts/2  0:00 grep 1613864
oracle 1613864       1   0   Oct 06      -  4:20 asm_lgwr_+ASM
[osuser01@os01]:/oracle > ps -ef|grep  1765430
oracle 1765430       1   0   Oct 06      -  3:19 asm_dbw0_+ASM
oracle 1876090 1065186   0 17:57:18  pts/2  0:00 grep 1765430
[osuser01@os01]:/oracle > ps -ef|grep  1921252
oracle 1065188 1876092   0 17:57:25  pts/2  0:00 grep 1921252
oracle 1921252       1   0 15:34:14      -  0:00 oracle+ASM (LOCAL=NO)
[osuser01@os01]:/oracle > ps -ef|grep  1945674
oracle 1065192 1876098   0 17:57:30  pts/2  0:00 grep 1945674
oracle 1945674       1   0   Oct 06      - 49:47 ora_mmnl_DB01

As we can see, the locking processes belong to the Oracle database and ASM instance background processes.

I also checked the database side whether dropped disk is still there or not.

I connected to the ASM instance and run the following query.

SQL> select name, substr(path, 1, 20) from v$asm_disk order by name;

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0000            /dev/ASM_Disk1
DG_DB_ASM_DB01_0001            /dev/ASM_Disk2
DG_DB_ASM_DB01_0002            /dev/ASM_Disk3
DG_DB_ASM_DB01_0003            /dev/ASM_Disk4
DG_DB_ASM_DB01_0004            /dev/ASM_Disk5

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0005            /dev/ASM_Disk6
DG_DB_ASM_DB01_0006            /dev/ASM_Disk13
DG_DB_ASM_DB01_0007            /dev/ASM_Disk14
DG_DB_ASM_DB01_0008            /dev/ASM_Disk15
/dev/ASM_Disk17

10 rows selected.

ASM device name still appears there but without any ASM disk name, this looks a bit strange because we successfully dropped this ASM disk and there are still Oracle background processes which does not release this ASM device.

After searching a bit about this problem, I found a Metalink note in Oracle Metalink website explaining that this problem occurs because of an Oracle bug which will be solved in Oracle version 10.2.0.5 (we use Oracle version 10.2.0.4). (Oracle Metalink note : Asm Devices Are Still Held Open After Dismount or Drop [ID 402526.1])

As a quick solution, since this database is not a production database, I restarted ASM and Oracle database instance to get rid of these background processes locking the ASM device and problem was solved.

Regards,
Ural

How to add an Oracle related script to Unix crontab

2010-02-06T03:10:00.000-08:00

Dear All,

After a long period of time not publishing any blog posts, I'm back again to share my Oracle knowledge with you and get your reviews and feedbacks about the published subjects.

I want to share a point with you, which most of you would already have been aware of it.

When you want to prepare an Oracle related script which will connect to an Oracle database and do some work and schedule it in Unix crontab (in my case IBM AIX), you should pay attention to set the environment variables related to database which will be connected to and running on the same server.

These environment settings are normally set in the profile of the Unix user, running the script in Unix, most of the time this Unix user is also the owner of the database running on this server.

In case of scheduling a shell script in crontab, the scheduled script does not have any profile setting automatically and you should prepare a separate script including these Oracle related environment settings or you can directly embed these setting to your database related Unix shell script.

Below are the examples of these kinds of Unix shell scripts.

Regards,
Ural

[user01@server01]:/oracle > crontab -l
00 00 * * * /oracle/scripts/script.sh > /oracle/scripts/script.log 2>&1

[user01@server01]:/oracle > more /oracle/scripts/script.sh
. /oracle/scripts/set_env.sh
/oracle/scripts/script_02.sh

[user01@server01]:/oracle > more /oracle/scripts/set_env.sh
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/orahome1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
cd /oracle/scripts

[user01@server01]:/oracle > more /oracle/scripts/script_02.sh
d_date=`date +"%Y%m%d%H%M"`
expdp parfile=script_02.par dumpfile=script_02_${d_date}.dmp logfile=script_02_${d_date}.log

Oracle database keeps the old dropped tablespace info in database registry for a partitoned table

2007-10-03T10:30:00.000-07:00

Hi,

We have just upgraded our Oracle production database from 9i(9.2.0.4) to 10g(10.2.0.3).
I will prepare and publish a blog post about this subject in the following days.
While I struggle with the CBO subjects that are changed in 10g. I realised
something that I was aware of before but I am very shocked that it is not corrected in 10g.

The case is related to a partitioned table which is originally created together with its partitions in
"tablespace_1" but for some time later, as a reorganization need, its partitions are moved online to
"tablespace_2" and old "tablespace_1" is dropped from the database.

I know from my earlier experiences that in 9i, in a case like above, Oracle 9i keeps the old dropped
tablespace "tablespace_1" info in database registry as a part of the partitioned table's description.

Lets give an example...

conn dbuser01@prod10g

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA
*/

CREATE TABLESPACE USER_DATA_TEST DATAFILE 
'/data2/oracle3/oradata/oradb3/user_data_test.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

ALTER USER DBUSER01 DEFAULT TABLESPACE "USER_DATA_TEST";

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA_TEST
*/

--DROP TABLE DBUSER01.EGTEST1;

CREATE TABLE DBUSER01.EGTEST1
(
COL1           CHAR(21 BYTE),
COL2           CHAR(1 BYTE),
COL3           DATE
)
PARTITION BY RANGE (COL3) 
(  
PARTITION P1 VALUES LESS THAN (TO_DATE(' 2003-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE TB_DATA
,  
PARTITION P2 VALUES LESS THAN (MAXVALUE)
TABLESPACE TB_DATA
);

ALTER USER DBUSER01 DEFAULT TABLESPACE "USER_DATA";

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA
*/

drop tablespace USER_DATA_TEST INCLUDING CONTENTS AND DATAFILES;

try to export this table

user01@test3:/data1/oracle3>exp dbuser01 file=egtest1 log=egtest1 tables=egtest1 rows=n

Export: Release 10.2.0.3.0 - Production on Thu Sep 27 11:21:45 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                 EGTEST1
Export terminated successfully without warnings.

DROP TABLE DBUSER01.EGTEST1;

try to import this table

user01@test3:/data1/oracle3>imp dbuser01 file=egtest1 log=egtest1_imp fromuser=dbuser01 touser=dbuser01

Import: Release 10.2.0.3.0 - Production on Thu Sep 27 11:24:17 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
. importing DBUSER01's objects into DBUSER01
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "EGTEST1" ("COL1" CHAR(21), "COL2" CHAR(1), "COL3" DATE)"  
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USER_DATA_TEST" LOGGING P"
"ARTITION BY RANGE ("COL3" )  (PARTITION "P1" VALUES LESS THAN (TO_DATE('"
" 2003-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))"
"  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREEL"
"ISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TB_DATA" LOGGING "
"NOCOMPRESS, PARTITION "P2" VALUES LESS THAN (MAXVALUE)  PCTFREE 10 PCTUSED "
"40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "TB_DATA" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USER_DATA_TEST' does not exist
Import terminated successfully with warnings.

import can not find the already dropped tablespace and can not import the table

---------------------------
testing the normal behaviour with a non-partitioned table.

CREATE TABLESPACE USER_DATA_TEST DATAFILE 
'/data2/oracle3/oradata/oradb3/user_data_test.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table test_tbl (col1 number) tablespace USER_DATA_TEST;
insert into test_tbl values (1);
commit;

select table_name,tablespace_name from user_tables where table_name = 'TEST_TBL';
/*
table_name|tablespace_name
TEST_TBL|USER_DATA_TEST
*/
drop tablespace USER_DATA_TEST INCLUDING CONTENTS AND DATAFILES; 
select table_name,tablespace_name from user_tables where table_name = 'TEST_TBL';
--No rows returned.

---------------------------

In normal database behaviour when we drop the tablespace, the tables, that resides in the dropped tablespace,
dropped also. Why the database behaves differently in case of parititioned tables ?

You can see this oddity when you try to extract the DDL script of such a parititoned table with any database tool,
because the Oracle database continues to keep this already dropped tablespace info in the database registry.

Oracle Advanced Resolution Team of Oracle Worldwide Support considered the issue not as a bug and
they accept it only as an enhancement request which is done in expdp/impdp (Oracle Data Pump Export/Import)
on 10G. When you try to import (impdp) a partitioned table like in the above case, which is exported with expdp,
you can use "remap_tablespace=user_data_test:an_existing_tblspace" parameter to change the old dropped
tablespace name to a tablespace that exists in the database.

So, in our case, since the related partitioned tables are very big and the database operates in archivelog mode
we do not want to export them with expdp and import them again into database with impdp by using the
"remap_tablespace" parameter. Nevermind our case, but the strange thing is that , Oracle still have this
oddity even in 10g database. I mean if you do not export and then import them, then you have to live with it.
Any tool (including expdp) extracts the DDL of such a partitioned table with wrong old tablespace,
then you will have to manually edit this generated script (except impdp) to change this non-existing
tablespace from the DDL script.

One more point, since the impdb can import only the export dump files that are taken with expdp, if you
just upgraded your database from 9i to 10g, I thing you should convert your export scripts to use the expdp,
if any. Otherwise, if, from time-to-time, you are importing some tables into any of your test databases,
there will be some manual extra works waiting for you, like for these partitioned tables containing old dropped
tablespace info you will have to extract the script from export file, change the tablespace part to point to an
existing tablespace,pre-create the tables and then import them.

Regards
Ural Ural

Oracle CBO chooses the wrong index although the statistics are collected on the related table

2007-07-12T10:19:00.000-07:00

Hi,

Our system analysis and support team called us, the Oracle DBA team, for a problem. The problem was a report program was running
OK before but it runs very slowly now, but they said that nothing has changed about the program except the data that it selects.
So we examined the situation and these are the things we did.

We traced the situation and found the guilty SQL. It is a simple one as it looks. The table TABLE01 has about 65 million rows.

SELECT SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;

We looked at the explain plan of that SQL statement,

Plan
SELECT STATEMENT  CHOOSECost: 350  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 350  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_2X Cost: 347  Cardinality: 9

When we run this query like that, although this SQL statement should not return any rows, we waited for 1 hour but it did not finish, so we killed the session because this is too much time for a report and apperantly there was a problem.Then we looked at the indexes of this table and there was another candidate index for this index and we tried the SQLby giving a hint for this second index.

SELECT /*+ index(t1 TABLE01_4X)*/
SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;

OOOPPPS !!!, it just finished in 15 miliseconds. So we looked at the explain plan of this SQL statement.

Plan
SELECT STATEMENT  CHOOSECost: 542  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 542  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_4X Cost: 501  Cardinality: 1,452

Although the cost of the second query (hinted) is higher than than the first one, the second SQL runs much faster.So how can this be, is the Oracle CBO missing something in calculating the cost ? We will see...Lets look at the structures of related indexes.

CREATE INDEX TABLE01_2X ON TABLE01
(htno1, dkod1, skod1);

CREATE INDEX TABLE01_4X ON TABLE01
(TAR1, skod1);

We collect statistics for the tables in our Oracle database, the Oracle CBO (Cost Base Optimizer) calculates the cost of a SQL statement false and chooses the wrong index, I mean the one that is less efficient. As a result of this behaviour these SQL statements does not finish in a resonable time, causing loss of business.How we collect the statistics is like that, we collect statistics for tables by using the following command :

exec dbms_stats.gather_table_stats(ownname => 'DBUSER01', tabname => 'TABLE01', estimate_percent => 33, cascade => TRUE);

After collecting the statistics we will examine how the CBO calculates the cost.According to Jonathan Lewis, Oracle CBO calculates the cost like below :cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)Lets calculate the cost of the following SQL

SELECT SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;
---------------------------------------------------------
Plan
SELECT STATEMENT  CHOOSECost: 350  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 350  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_2X Cost: 347  Cardinality: 9   
---------------------------------------------------------

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_2X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_2X|3|331491|16303842

effective index selectivity = density of "HTNO1" columneffective table selectivity = (density of "DKOD1" column) * (density of "HTNO1" column) * (density of "SKOD1" column)

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
DKOD1|12|0.0833333333333333|1
HTNO1|965|0.00103626943005181|1
SKOD1|73|0.0136986301369863|1

Since the " AND 9000 + dkod1 = 9001" condition equals "function(col) = constant" is has the static selectivity of %1 (0.001)instead of 0.0833333333333333.cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)cost =3 +ceiling(331491 * 0.00103626943005181) +ceiling(16303842 * (0.01 * 0.00103626943005181 * 0.0136986301369863))= 350-----------------------Lets calculate the cost of the following SQL

SELECT /*+ index(t1 TABLE01_4X)*/
SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;
---------------------------------------------------------
Plan
SELECT STATEMENT  CHOOSECost: 542  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 542  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_4X Cost: 501  Cardinality: 1,452  
---------------------------------------------------------

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_4X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_4X|3|302597|1791039

effective index selectivity = density of "TAR1" columneffective table selectivity = (density of "TAR1" column) * (density of "SKOD1" column)

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('TAR1', 'SKOD1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
TAR1|608|0.00164473684210526|1
SKOD1|73|0.0136986301369863|1

cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)cost =3 +ceiling(302597 * 0.00164473684210526) +ceiling(1791039 * (0.00164473684210526 * 0.0136986301369863))= 542------------------------------It is obvious that CBO can not choose the optimal index for this SQL, that is "TABLE01_4X". Why ?The answer is that we did not give the CBO the enough info about the structure of the data in table and the indexes.So the solution is, we should collect more data for this table about the distribution of data in that table. What should we do ? We should collect histogram data for this table, lets see the result of collecting histograms for this table.Lets collect histograms for this table.

exec dbms_stats.gather_table_stats('DBUSER01','TABLE01', estimate_percent=>33, cascade => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 10');

Lets look at the explain plans :First select is just finished in 15 miliseconds, and the second one does not finishso the problem is solved, our SQL now finishes in 15 msecs.

SELECT SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;

Plan
SELECT STATEMENT  CHOOSECost: 621  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 621  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_4X Cost: 519  Cardinality: 3,648  

SELECT /*+ index(t1 TABLE01_2X)*/
SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;

Plan
SELECT STATEMENT  CHOOSECost: 11,248  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 11,248  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_2X Cost: 11,066  Cardinality: 716

------------------------------Lets examine the costs of the following SQLs

SELECT SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;
---------------------------------------------------------
Plan
SELECT STATEMENT  CHOOSECost: 621  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 621  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_4X Cost: 519  Cardinality: 3,648  
---------------------------------------------------------

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_4X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_4X|3|303888|1796569

effective index selectivity = density of "TAR1" columneffective table selectivity = (density of "TAR1" column) * (density of "SKOD1" column)

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('TAR1', 'SKOD1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
TAR1|608|0.00169779286926995|10
SKOD1|73|0.0333333333333333|8

cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)cost =3 +ceiling(303888 * 0.00169779286926995) +ceiling(1796569 * (0.00169779286926995 * 0.0333333333333333))= 621----------------------

SELECT /*+ index(t1 TABLE01_2X)*/
SUM (DECODE (b1, 'A', tut1, 0)), SUM (DECODE (b1, 'B', tut1, 0))
FROM table01 t1
WHERE tar1 = to_date('29.06.2007','dd.mm.yyyy')
AND skod1 = NVL(NULL, skod1)
AND htno1 = '22001901'
AND 9000 + dkod1 = 9001
AND NVL (dtkod1, '*') = NVL (NULL, '*')
AND eno1 < 9000;
---------------------------------------------------------
Plan
SELECT STATEMENT  CHOOSECost: 11,248  Bytes: 36  Cardinality: 1     
3 SORT AGGREGATE  Bytes: 36  Cardinality: 1    
2 TABLE ACCESS BY INDEX ROWID DBUSER01.TABLE01 Cost: 11,248  Bytes: 36  Cardinality: 1   
1 INDEX RANGE SCAN NON-UNIQUE DBUSER01.TABLE01_2X Cost: 11,066  Cardinality: 716  
---------------------------------------------------------

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_2X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_2X|3|331864|16304224

effective index selectivity = density of "HTNO1" columneffective table selectivity = (density of "DKOD1" column) * (density of "HTNO1" column) * (density of "SKOD1" column)

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
DKOD1|12|0.0384615384615385|2
HTNO1|968|0.0333333333333333|9
SKOD1|73|0.0333333333333333|8

Since the " AND 9000 + dkod1 = 9001" condition equals "function(col) = constant" is has the static selectivity of %1 (0.001)instead of 0.0833333333333333.cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)cost =3 +ceiling(331864 * 0.0333333333333333) +ceiling(16304224 * (0.01 * 0.0333333333333333 * 0.0333333333333333))= 11248--------------------------How the histograms caused the CBO to choose the righr index ?Lets look at the histogram data.

select * from dba_tab_histograms t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('TAR1','HTNO1', 'DKOD1', 'SKOD1')
order by t1.column_name, t1.endpoint_number ;
---------------------------------------------
OWNER|TABLE_NAME|COLUMN_NAME|ENDPOINT_NUMBER|ENDPOINT_VALUE|ENDPOINT_ACTUAL_VALUE
DBUSER01|TABLE01|DKOD1|8|1|
DBUSER01|TABLE01|DKOD1|9|2|
DBUSER01|TABLE01|DKOD1|10|21|
DBUSER01|TABLE01|HTNO1|0|2.50227984373754E35|
DBUSER01|TABLE01|HTNO1|1|2.60632781273513E35|
DBUSER01|TABLE01|HTNO1|2|2.60632860505268E35|
DBUSER01|TABLE01|HTNO1|3|2.60775139366763E35|
DBUSER01|TABLE01|HTNO1|5|2.65906207772846E35|
DBUSER01|TABLE01|HTNO1|6|2.76351807179888E35|
DBUSER01|TABLE01|HTNO1|7|2.76351807179912E35|
DBUSER01|TABLE01|HTNO1|8|2.81462815943683E35|
DBUSER01|TABLE01|HTNO1|9|2.81462815943707E35|
DBUSER01|TABLE01|HTNO1|10|2.97121551998981E35|
DBUSER01|TABLE01|TAR1|0|2453374|
DBUSER01|TABLE01|TAR1|1|2453502|
DBUSER01|TABLE01|TAR1|2|2453599|
DBUSER01|TABLE01|TAR1|3|2453689|
DBUSER01|TABLE01|TAR1|4|2453790|
DBUSER01|TABLE01|TAR1|5|2453889|
DBUSER01|TABLE01|TAR1|6|2453973|
DBUSER01|TABLE01|TAR1|7|2454060|
DBUSER01|TABLE01|TAR1|8|2454141|
DBUSER01|TABLE01|TAR1|9|2454218|
DBUSER01|TABLE01|TAR1|10|2454292|
DBUSER01|TABLE01|SKOD1|0|8345|
DBUSER01|TABLE01|SKOD1|3|9000|
DBUSER01|TABLE01|SKOD1|4|9050|
DBUSER01|TABLE01|SKOD1|5|9159|
DBUSER01|TABLE01|SKOD1|6|9170|
DBUSER01|TABLE01|SKOD1|7|9300|
DBUSER01|TABLE01|SKOD1|8|9412|
DBUSER01|TABLE01|SKOD1|9|9631|
DBUSER01|TABLE01|SKOD1|10|9892|

Lets look at the densities before and after the histograms.Before

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_2X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_2X|3|331491|16303842
DBUSER01|TABLE01_4X|3|302597|1791039

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1', 'TAR1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
DKOD1|12|0.0833333333333333|1
HTNO1|965|0.00103626943005181|1
TAR1|608|0.00164473684210526|1
SKOD1|73|0.0136986301369863|1

After

select owner, index_name, blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'TABLE01_2X';
---------------------------------------------------------
OWNER|INDEX_NAME|BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR
DBUSER01|TABLE01_2X|3|331864|16304224
DBUSER01|TABLE01_4X|3|303888|1796569

select column_name, num_distinct, density, num_buckets from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1', 'TAR1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|NUM_BUCKETS
DKOD1|12|0.0384615384615385|2
HTNO1|968|0.0333333333333333|9
TAR1|608|0.00169779286926995|10
SKOD1|73|0.0333333333333333|8

As we can see the leaf_blocks and clustering_factor values of the indexes are changed a bit,but these are not so important in cost calculation, but the densities of the indexed columns are much more effective in cost calculation.Lets examine them closer.Before the histograms the densities of all the colums are equal to 1/num_distinct of the respective columns.

select column_name, num_distinct, density, 1/numdistinct density_calculated from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1', 'TAR1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|DENSITY_CALCULATED
DKOD1|12|0.0833333333333333|0.0833333333333333
HTNO1|965|0.00103626943005181|0.00103626943005181
TAR1|608|0.00164473684210526|0.00164473684210526
SKOD1|73|0.0136986301369863|0.0136986301369863

After collecting the histograms the densities of all the colums are NOT equal to 1/num_distinct of the respective columns.

select column_name, num_distinct, density, 1/numdistinct density_calculated from dba_tab_columns t1 where t1.table_name = 'TABLE01' 
and t1.column_name in ('DKOD1', 'HTNO1', 'SKOD1', 'TAR1')
order by t1.column_name;
---------------------------------------------------------
COLUMN_NAME|NUM_DISTINCT|DENSITY|DENSITY_CALCULATED
DKOD1|12|0.0384615384615385|0.0833333333333333
HTNO1|968|0.0333333333333333|0.00103305785123967
TAR1|608|0.00169779286926995|0.00164473684210526
SKOD1|73|0.0333333333333333|0.0136986301369863

Since the "DKOD1" column has the static selectivity of 0.01, the selectivities of the other columns
are effective role in the calculation of cost. As we see after the histograms, the density of "HTNO1"
and "SKOD1" columns are increased, the density of "TAR1" is decreased a little bit. Since the "SKOD1"
column exists in both indexes we should examine the densities of the "HTNO1" and "TAR1" columns.

In normal case, I mean statistics without histograms, the CBO assumes a smooth distribution of data values of
a column between minimum and maximum values. But in reality there are fluctuation in the distribution of data
for some columns. I mean some values appears rarely but some data appears frequently in distribution. So the CBO
can not determine this distribution without having histogram data on these columns. The calculation of density of
these columns in case of histograms is a very complex algorithm so we should not bother about it.

This case show the importance of histograms where there is skewed data in indexed columns.

Regards
Ural Ural

PGA memory hungry plsql codes and inevitable "ORA-04030" error

2007-04-20T09:55:00.000-07:00

Hi,
Today, we found out memory eating, carnivore unix processes in our
production Oracle database server's operating system.
When we trace them, we realized
that they are Oracle client processes coming from Internet banking
connections. We analyzed the case and these sessions were growing in PGA
Memory usage. When they hit the Operating System memory limit they got
"ORA-04030" error but they did not stop , they continued to eat up the
memory (PGA). So we killed some of them but there were new sessions coming
from the same system behaving similarly.We examined the situation with the
people from development team and they found out that one day ago they
added a new dynamic parameter to one of the database packages generating
html output, they generating the html code by combining data from
different tables but one of these cursors that selects form these tables
gone into an infinite loop and caused that problem, so they corrected the
data that caused this infinite loop and they put extra controls to the
plsql code and user interface programs to prevent the error happening
again.

But after that we, as Oracle DBA team, decided to develop a database
procedure and database job that repeatedly checks for any sessions that
consumes PGA memory and have it email these sessions info to the DBA
Group for being able take early corrective action.

The following is the code that we developed and use for this purpose.

-----------------

select * from v$statname where statistic# = 20
/*
STATISTIC#|NAME|CLASS
20|session pga memory|1
*/

CREATE OR REPLACE PROCEDURE DBUSER01.Dba_Sess_Pga_Mem_Control IS
CURSOR c1 IS
SELECT t1.username, t1.SID, t1.serial#,
TRUNC (a_sess_pga_mem / 1024 / 1024) sess_pga_mem_mb
FROM v$session t1,
(SELECT *
FROM (SELECT   SID, VALUE a_sess_pga_mem
FROM v$sesstat
WHERE statistic# = 20 --session pga memory
AND TRUNC(VALUE / 1024 / 1024) >= 50
ORDER BY VALUE DESC)
WHERE ROWNUM <= 1) t2
WHERE t1.SID = t2.SID;

v_message   VARCHAR2(2000);
v_delimiter VARCHAR2(2);
r1          c1%ROWTYPE;
BEGIN
IF m_tatil_bul2(TRUNC(SYSDATE), 'B') = 0 THEN
-- If it is not on weekend

v_delimiter := '';
OPEN c1;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;

v_message := v_message || v_delimiter;
v_message := v_message || 'Username '        || CHR(9) || CHR(9) || ' : ' || r1.username                 || CHR(10)
|| 'Sid,Serial# '     || CHR(9) || CHR(9) || ' : ' || r1.sid || ',' || r1.serial# || CHR(10)
|| 'PGA Memory (MB) '           || CHR(9) || ' : ' || r1.sess_pga_mem_mb          || CHR(10)
|| '------------------------------------------------------';
v_delimiter := CHR(10);

END LOOP;
CLOSE c1;

IF v_delimiter = CHR(10) THEN
DBUSER01.smtp.mail('dba@company01.com.tr',
'dba@company01.com.tr',
'There are sessions consuming too much PGA MEMORY (PGA Memory >= 50 MB) Please check !...',
v_message);
END IF;
END IF;
END;
/

Create the job that will call the above procedure in the specified intervals (3 mins in our case)

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job       => X 
,what      => 'DBUSER01.Dba_Sess_Pga_Mem_Control;'
,next_date => SYSDATE
,interval  => 'SYSDATE + 3/(24*60)'
,no_parse  => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

I hope you do not have any PGA memory hungry sessions.

Getting "ORA-01034" and "ORA-27123" errors when connecting to Oracle 9i database with another unix user other than "oracle" user (database owner)

2007-04-19T11:11:00.000-07:00

The problem is when you try to connect to the oracle locally by using another user other than oracle (database owner) we get "ORA-01034" and "ORA-27123" errors. When you use "TWO_TASK" environment variable, it is OK, since it is going through network I mean over the listener.
But locally we are not able to connect to the database whithout using "TWO_TASK".

There may be others who have seen the same error, so I am writing the solution in my case here.

user1@host1:/data3/oracle/user1 > echo $TWO_TASK

user1@host1:/data3/oracle/user1 > sqlplus db_user1

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 18 23:37:00 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied


Enter user-name: ^C

ORACLE_SID and ORACLE_HOME are checked and they are what they should be for this database so what is the problem then ?

Solution :

After searching for the solution for some time in Internet,
I finally found the following a solution that solved my problem.

oracle@host1:/data3/oracle > cd $ORACLE_HOME/bin
oracle@host1:/data3/oracle/bin > ls -l oracle
-rwxr-xrwx   1 oracle   dba        74053740 Sep 04 2003  oracle

When we look at the file permissions of "oracle" executable it looks strange
because it normally should be like "-rwsr-s--x", so we are going to make it so

First of all we close the database
This is very important that you should close the database before doing that

oracle@host1:/data3/oracle > sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:17:08 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 -
64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Then we change the file permissions of "oracle" executable

oracle@host1:/data3/oracle/bin > chmod 6751 oracle
oracle@host1:/data3/oracle/bin > ls -l oracle
-rwsr-s--x   1 oracle   dba        74053740 Sep 04 2003  oracle

Then we open the database

oracle@host1:/data3/oracle/bin > sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:18:10 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622298928 bytes
Fixed Size                   743216 bytes
Variable Size             486539264 bytes
Database Buffers          134217728 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 -
64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

We switch to the user where the connection problem occurs

oracle@host1:/data3/oracle/bin > su - user1
user1's Password:

user1@host1:/data3/oracle/user1 > sqlplus db_user1

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:18:46 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>

Finally the problem is solved.

Resizing Undo Tablespaces

2007-04-16T12:52:00.000-07:00

How do you resize undo tablepaces ? This is how I do it in an Oracle 9i database.

Problem is the undo tablespace has grown up to the limit of its maxsize
and we need to compact it or just we want to make it smaller due to a space problem

Find undo info

SELECT NAME, VALUE
FROM v$parameter
WHERE LOWER (NAME) LIKE '%undo%';
/*
NAME|VALUE
undo_management|AUTO
undo_tablespace|UNDOTBS1
*/

Find undo tablespace datafile info

SELECT   file_name, BYTES / 1024 / 1024 mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
ORDER BY file_name;
/*
FILE_NAME|MB
/datac3/oradata/andlp2/undotbs1_01.dbf|1793
/datac6/oradata/andlp2/undotbs1_02.dbf|235
/datac7/oradata/andlp2/undotbs1_03.dbf|1679
*/

Execute the following query to determine who is using a particular UNDO or Rollback Segment:

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr 
AND r.usn = t.xidusn(+) 
AND x.NAME = 'db_block_size'
--No rows returned

Create a second temporary undo tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 
'/datac7/oradata/andlp2/undotbs2_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M
ONLINE
BLOCKSIZE 8K;

Set this new undo tablespace as undo tablespace of the database

ALTER SYSTEM SET undo_tablespace = UNDOTBS2;

You can monitor the undo extents of all the undo tablespaces by using the following query

SELECT tablespace_name, segment_name, SUM (blocks), SUM (BYTES) / 1024
FROM dba_undo_extents
GROUP BY tablespace_name, segment_name;

Since we are going to drop the old undo tablespace we need to know if any session is
using any undo extent of this undo tablespace

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1'  
/*
SID_SERIAL|ORAUSER|PROGRAM|UNDOSEG|Undo|TABLESPACE_NAME
615,998|USER1|runform30x@host1 (TNS interface)|_SYSSMU102$|8K|UNDOTBS1
*/

You can get more info about the undo extent that is online (used)

SELECT *
FROM dba_rollback_segs
WHERE status = 'ONLINE' 
AND tablespace_name = 'UNDOTBS1'
ORDER BY tablespace_name, segment_name
/*
SEGMENT_NAME|OWNER|TABLESPACE_NAME|SEGMENT_ID|FILE_ID|BLOCK_ID|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|STATUS|INSTANCE_NUM|RELATIVE_FNO
_SYSSMU102$|PUBLIC|UNDOTBS1|102|33|4473|131072||2|32765||ONLINE||33
*/

Monitor the status of online undo extents

SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS1' 
AND segment_id = usn;
/*
SEGMENT_NAME|XACTS|STATUS
_SYSSMU102$|1|PENDING OFFLINE
*/

See it is in "PENDING OFFLINE" status until the transaction that is using it ends.

See all the rollback info

SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU102$|PENDING OFFLINE|42|42065920|42065920|1
_SYSSMU262$|ONLINE|4|253952|253952|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|2|122880|122880|0
_SYSSMU271$|ONLINE|2|122880|122880|0
*/

Find the active transactions using undo extents

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
/*
USERNAME|XIDUSN|UBAFIL|UBABLK|USED_UBLK
MARDATA|102|30|153176|1
*/

After all active transactions are committed, the status of Undo segment
_SYSSMU102$ is automatically switched from PENDING OFFLINE mode to OFFLINE mode.
PENDING OFFLINE mode does not allow any deletion process of tablespace.
You can delete the tablespace in OFFLINE mode.

If no segment is selected after you execute the SQL statement below,
it means the segment is already switched to OFFLINE mode.

SQL> SELECT SEGMENT_NAME, XACTS, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS_02'
AND SEGMENT_ID = USN;

If the segment is selected and XACTS points to zero (0), rollback segment
does not contain any pending transactions, and therefore,
the segment is switched to OFFLINE mode immediately

Wait for the active transaction to finish then monitor again

SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU262$|ONLINE|5|319488|319488|0
_SYSSMU263$|ONLINE|2|122880|122880|0
_SYSSMU264$|ONLINE|3|188416|188416|0
_SYSSMU265$|ONLINE|3|188416|188416|0
_SYSSMU266$|ONLINE|4|253952|253952|0
_SYSSMU267$|ONLINE|2|122880|122880|0
_SYSSMU268$|ONLINE|2|122880|122880|0
_SYSSMU269$|ONLINE|2|122880|122880|0
_SYSSMU270$|ONLINE|3|188416|188416|0
_SYSSMU271$|ONLINE|2|122880|122880|0
*/

As you can see all undo extents of the old undo tablespace UNDOTBS1 are dropped already now we can drop the tablespace

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

Recreate the original configuration :
After that we can create again the original configuration, so recreate the old undo again
and switch to it

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 
'/datac3/oradata/andlp2/undotbs1_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M,
'/datac6/oradata/andlp2/undotbs1_02.dbf' SIZE 500M AUTOEXTEND ON NEXT 16M MAXSIZE 5000M,
'/datac7/oradata/andlp2/undotbs1_03.dbf' SIZE 100M AUTOEXTEND OFF
ONLINE
BLOCKSIZE 8K;

ALTER SYSTEM SET undo_tablespace = UNDOTBS1;

See if there are any active undo extents of UNDOTBS2

SELECT n.NAME, s.status, s.extents, s.rssize, s.hwmsize, s.xacts
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
/*
NAME|STATUS|EXTENTS|RSSIZE|HWMSIZE|XACTS
SYSTEM|ONLINE|6|385024|385024|0
_SYSSMU1$|ONLINE|2|122880|122880|0
_SYSSMU2$|ONLINE|2|122880|122880|0
_SYSSMU3$|ONLINE|2|122880|122880|0
_SYSSMU4$|ONLINE|2|122880|122880|0
_SYSSMU5$|ONLINE|2|122880|122880|0
_SYSSMU6$|ONLINE|2|122880|122880|0
_SYSSMU7$|ONLINE|2|122880|122880|0
_SYSSMU8$|ONLINE|2|122880|122880|0
_SYSSMU9$|ONLINE|2|122880|122880|0
_SYSSMU10$|ONLINE|2|122880|122880|0
*/

See if there is any rollback segments of UNDOTBS2

SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS2' 
AND segment_id = usn;
--No rows returned

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;

Gathering Table Statistics By Using Database Jobs

2007-04-16T11:33:00.000-07:00

In Oracle9i Database, sometimes you need to gather statistics for a big table, since it will take long time
you may not want to wait for it to finish or maybe you do not want to be disconnected by your company's firewall
in the middle of the stat gathering work, in a situation like that you can create a database job for that purpose
and let the job do the work at the background. You can use the some idea for your other long running processes too,
convert them to database jobs, ideally using packaged logged procedures or functions, by doing that you can monitor the
work from log records. I use the same idea in most of my work.
Connect as SYS to the database and run the example code below, put the relevant parameters for the literal parts.

DECLARE
x NUMBER;
BEGIN
DBMS_JOB.submit
(job => x,
what => 'DBMS_STATS.gather_table_stats(ownname => ''OWNER1'', tabname => ''BIGTABLE1'', estimate_percent => 10, cascade => TRUE);',
next_date => SYSDATE,
interval => NULL,
no_parse => TRUE
);
DBMS_OUTPUT.put_line ('Job Number is: '  TO_CHAR (x));
COMMIT;
END;
/

Resizing Temporary Tablespaces

2007-04-15T04:10:00.000-07:00

How do you resize temporary tablepaces ? This is how I do it in a 9.2.0.4 Oracle database.

Problem is the default temporary tablespace consists of only one tempfile
and that tempfile has grown up to the limit of its maxsize
and we need to compact it.

The following is our default temporary tablespace

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
/*
PROPERTY_VALUE
TEMP
*/

The creation script of the temporary tablespace TEMP

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/datac6/oradata/andlp2/temp_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

This script is just for your information, to know the structure of the temporary tablespace

Find the tempfile of this temporary tablespace

SELECT tablespace_name, file_name, maxbytes, BYTES
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
/*
TABLESPACE_NAMEFILE_NAMEMAXBYTESBYTES
TEMP/datac6/oradata/andlp2/temp_1.dbf83886080008388608000
*/

As you can see from the maxbytes and bytes columns this datafile has grown up to its maxsize of 8000M

First of all, we create another temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/datac7/oradata/andlp2/temp1_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Make the new temprary tablespace TEMP1 the default temporary tablespace of the database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Change the default temporary teblespaces of users to use he new TEMP1 temporary tablespace which are using the TEMP temporary tablespace before

SELECT 'ALTER USER '  username  ' TEMPORARY TABLESPACE temp1;'
FROM dba_users
WHERE temporary_tablespace = 'TEMP'
ORDER BY username;

Run the output of the above query as a script like below

ALTER USER USER1 TEMPORARY TABLESPACE temp1;
ALTER USER USER2 TEMPORARY TABLESPACE temp1;
ALTER USER USER3 TEMPORARY TABLESPACE temp1;
...

Check if any of the active sessions use the TEMP temporary tablespace
since there should not be any session using it, in order to change the size of it
You can examine the sessions using it and decide to kill them or wait for them to finish their jobs and disconnect

select *
from v$session
where saddr in (select session_addr
from v$tempseg_usage v
where v.tablespace = 'TEMP')

You can kill these sessions in your operating system with "kill -9" command (since the OS I am working on is IBM AIX)

select 'kill -9 'spid
from v$process
where addr in (select paddr
from v$session
where saddr in (select session_addr
from v$tempseg_usage v
where v.tablespace = 'TEMP'))

After killing the sessions you can drop the old TEMP temporary tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Then to make the structure the same as before, we need to recreate the TEMP temporary tablespace and drop the TEMP1 temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/datac6/oradata/andlp2/temp_1.dbf' SIZE 500M AUTOEXTEND ON NEXT 128M MAXSIZE 8000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

select 'ALTER USER 'username' TEMPORARY TABLESPACE TEMP;'
from dba_users
where temporary_tablespace = 'TEMP1'
order by username;
--run the output as script

DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

Sqlplus - pagesize

2007-04-12T09:48:00.000-07:00

Everyday we learn something new about Oracle databases that we missedbefore or may be we did not needed. Below is one of them for me, but Iused it recently in a Unix script to generate an Sqlplus report. Ipublish it here so that there may be others that missed the samepoint.

Maximum value of the Sqlplus pagesize parameter that you can set is 50000.

set pagesize 50000

2007-04-11T11:53:00.000-07:00

Hi to the blogging community !

My name is Ural Ural and I've been working with Oracle databases for about 12 years. I am a senior Oracle DBA. I am going to share all the information I learned about the Oracle databases and database administration here. I think, the more we share the more we learn new things about the Information Technology. So lets share our knowledge with the blogging community...

Regards
Ural