Oracle – Page 35 – Gokhan Atil’s Blog

Installing and Configuring StatsPack

Statspack is a performance tuning package provided by Oracle. It’s free to use and a reliable tuning tool since Oracle 8i. You can install it on Oracle 10g and 11g if you don’t have license for tuning pack. Oracle recommend installing the statspack package into a different tablespace than system tablespaces. “TOOLS” tablespace of Oracle 9i is because I’ll install it to . If you use Oracle 10g+, you need to create this tablespace.

During the installation, the user PERFSTAT will be created. This user will own all objects of the STATSPACK package. The installation SQL script will prompt for the PERFSTAT user’s password and default and temporary tablespaces.

CONNECT / AS SYSDBA
@?/rdbms/admin/spcreate

1 2	CONNECT / AS SYSDBA @?/rdbms/admin/spcreate

You need to check spcpkg.lis for any errors. You can test your statspack install by issuing:

EXEC statspack.snap;

1	EXEC statspack.snap;

You can create spauto.sql script to automatically schedule an hourly data collection for statspack:

@?/rdbms/admin/spauto

1	@?/rdbms/admin/spauto

If you want to remove statspack, run spdrop:

CONNECT / AS SYSDBA
@?/rdbms/admin/spdrop

1 2	CONNECT / AS SYSDBA @?/rdbms/admin/spdrop

December 7, 2010Gokhan AtilOracleperformance, statspack

How to Make a Table Read-only in Oracle

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:

ALTER TABLE our_table READ ONLY;

1	ALTER TABLE our_table READ ONLY;

To make it writable again, issue the following command:

ALTER TABLE our_table READ WRITE;

1	ALTER TABLE our_table READ WRITE;

In previous versions of Oracle, there was no command to make a table read-only, but we can use a little trick to do it. All we need is to create a constraint and make it disable:

ALTER TABLE our_table ADD CONSTRAINT chck1 CHECK (1=1) DISABLE VALIDATE;

1	ALTER TABLE our_table ADD CONSTRAINT chck1 CHECK (1=1) DISABLE VALIDATE;

Because the constraint is disabled, it is not enforced but it will not let you change the data to ensure that data still conforms to the constraint’s requirements. Unfortunately, direct path loads will bypass this, so we also need to issue the following command:

ALTER TABLE our_table DISABLE TABLE LOCK;

1	ALTER TABLE our_table DISABLE TABLE LOCK;

This will also prevent user to drop this table. Another option is to mark the tablespace which contains the table as read-only, but it’s not practical because it wll make all the objects read-only residing in the tablespace.

September 17, 2010Gokhan Atil3 CommentsOracleread only, table

How to Order Siblings in Hierarchical Queries

In a hierarchical query, if you specify either ORDER BY or GROUP BY, the hierarchical order will be destroyed. If we need to sort the rows of siblings of the the same level (while keeping the hierarchical order) we can to use ORDER SIBLINGS BY clause. Let’s create a sample table:

CREATE TABLE emp ( emp_id NUMBER, name VARCHAR2(50),  manager_id NUMBER );
INSERT INTO emp VALUES ( 1, 'JACK', null ); 
INSERT INTO emp VALUES ( 2, 'MILLS', 1 );
INSERT INTO emp VALUES ( 3, 'ATIL', 1 ); 
INSERT INTO emp VALUES ( 4, 'BROWN', 3 ); 
INSERT INTO emp VALUES ( 5, 'ROGERS', 2 ); 
INSERT INTO emp VALUES ( 6, 'DAVIDS', 2 ); 
INSERT INTO emp VALUES ( 7, 'LEE', 3 ); 
INSERT INTO emp VALUES ( 8, 'WHITE', 2 ); 
COMMIT;

CREATE TABLE emp ( emp_id NUMBER, name VARCHAR2(50), manager_id NUMBER );

INSERT INTO emp VALUES ( 1, 'JACK', null );

INSERT INTO emp VALUES ( 2, 'MILLS', 1 );

INSERT INTO emp VALUES ( 3, 'ATIL', 1 );

INSERT INTO emp VALUES ( 4, 'BROWN', 3 );

INSERT INTO emp VALUES ( 5, 'ROGERS', 2 );

INSERT INTO emp VALUES ( 6, 'DAVIDS', 2 );

INSERT INTO emp VALUES ( 7, 'LEE', 3 );

INSERT INTO emp VALUES ( 8, 'WHITE', 2 );

COMMIT;

Let’s list these rows in a hierarchical order:

SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 3) || NAME report
FROM emp
START WITH manager_id IS NULL
CONNECT BY manager_id = PRIOR emp_id;

     LEVEL REPORT
---------- ------------------------------
         1 JACK
         2    MILLS
         3       ROGERS
         3       DAVIDS
         3       WHITE
         2    ATIL
         3       BROWN
         3       LEE

8 rows selected.

SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 3) || NAME report

FROM emp

START WITH manager_id IS NULL

CONNECT BY manager_id = PRIOR emp_id;

LEVEL REPORT

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

1 JACK

2 MILLS

3 ROGERS

3 DAVIDS

3 WHITE

2 ATIL

3 BROWN

3 LEE

8 rows selected.

August 28, 2010Gokhan Atil5 CommentsOraclelevel, siblings, sql

Update Joins in Oracle

We have an application running on Ms SQL Server, and we want to move it to Oracle. While I examine the custom queries, I see that “update joins”.

UPDATE table_name
SET colname = value
FROM source_table JOIN source_table2
WHERE condition

This syntax helps user to join tables and update together. Oracle does not support this syntax, so I searched to find an alternative way.

It’s suggested to use subqueries:

CREATE TABLE emp ( ID NUMBER, NAME VARCHAR2(100), sal NUMBER, dep_id NUMBER );
CREATE TABLE dep ( ID NUMBER, NAME VARCHAR2(50 ) );
INSERT INTO emp VALUES (1, 'GOKHAN', 10000, 1);
INSERT INTO emp VALUES (2, 'OSMAN', 10000, 1);
INSERT INTO emp VALUES (3, 'HAKAN', 10000, 1);
INSERT INTO emp VALUES (4, 'ACAR', 5000, 2);
INSERT INTO emp VALUES (5, 'CEMIL', 5000, 2);
INSERT INTO dep VALUES (1, 'IT' );
INSERT INTO dep VALUES (2, 'SALES' );
COMMIT;

CREATE TABLE emp ( ID NUMBER, NAME VARCHAR2(100), sal NUMBER, dep_id NUMBER );

CREATE TABLE dep ( ID NUMBER, NAME VARCHAR2(50 ) );

INSERT INTO emp VALUES (1, 'GOKHAN', 10000, 1);

INSERT INTO emp VALUES (2, 'OSMAN', 10000, 1);

INSERT INTO emp VALUES (3, 'HAKAN', 10000, 1);

INSERT INTO emp VALUES (4, 'ACAR', 5000, 2);

INSERT INTO emp VALUES (5, 'CEMIL', 5000, 2);

INSERT INTO dep VALUES (1, 'IT' );

INSERT INTO dep VALUES (2, 'SALES' );

COMMIT;

Let’s say we want to increase salaries of sales stuff:

UPDATE (SELECT emp.sal, dep.name FROM emp
JOIN dep ON emp.dep_id = dep.ID )
SET sal = sal * 1.2
WHERE name = 'SALES';

UPDATE (SELECT emp.sal, dep.name FROM emp

JOIN dep ON emp.dep_id = dep.ID )

SET sal = sal * 1.2

WHERE name = 'SALES';

May 4, 2010Gokhan AtilOraclemerge, update join

DB_ULTRA_SAFE Parameter

DB_ULTRA_SAFE is a new parameter introduced in 11g. It provides an integrated mechanism to offer protection from various possible data corruptions. and provides critical high availability benefits for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.

DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks.

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk.

DB_LOST_WRITE_PROTECT enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

April 29, 2010Gokhan AtilOracle11g, db_block_checking, db_block_checksum