Oracle – Page 35 – Gokhan Atil’s Blog

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 Atil4 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

How to Generate XML from the Oracle Database

Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.

I’ll start with creating a simple table and fill it with sample data:

CREATE TABLE customers (
customer_name VARCHAR2 (30),
phone VARCHAR2(11));

INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' );
INSERT INTO customers VALUES ( 'JACK', '5426661010' );
INSERT INTO customers VALUES ( 'JHON', '5352004050' );
COMMIT;

CREATE TABLE customers (

customer_name VARCHAR2 (30),

phone VARCHAR2(11));

INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' );

INSERT INTO customers VALUES ( 'JACK', '5426661010' );

INSERT INTO customers VALUES ( 'JHON', '5352004050' );

COMMIT;

To be able to use SYS_XMLGEN, I’ll create an object to map our data:

CREATE OR REPLACE TYPE customers_map_type AS OBJECT (
customer_name VARCHAR2 (30),
phone VARCHAR2(11));

CREATE OR REPLACE TYPE customers_map_type AS OBJECT (

customer_name VARCHAR2 (30),

phone VARCHAR2(11));

March 16, 2010Gokhan Atil2 CommentsOraclesys_xmlagg, sys_xmlgen, xml