ORACLE: DBA's day out

CTAS : Create Table as

noreply@blogger.com (Hasan Shaharear) — Thu, 12 Jan 2012 18:58:35 PST

We all are very familiar with CTAS or 'Create Table as' clause that used to construct new table and populated it with data extract by 'Select' statement. That means table creation and population are done by executing a single sql CTAS statement.

Fom example,

CREATE TABLE employee_info As
SELECT emp.id, emp.name, dp.name FROM employee emp, department dp WHERE emp.dp_id =dp.id;

The above CTAS statment create a new table named 'employee_info' and store the result of SELECT statement into that table.

Here are some common use of CTAS statement:

Store the result of select statement into a table.
create a duplicate table for backup purpose.
Reorganize existing table for better performance.

There are some limitations of CTAS statement:

Can't create indexes ( in case of table duplication or Reorganization).
Can't create primary, unique or foreign key constraints ( in case of table duplication or Reorganization). But it can create 'NOT NULL' constraint.

Though CTAS mirrors metadata structure, it can't mirror 'DEFAULT' value checker for columns.

I have learned the 3rd pitfall of CTAS recently. I used CTAS to construct a duplicate table ( a sub task of table partitioning). The partitioning job was successful but the problem arose when we inserted new rows into the partitioned table. One of the column got null value stored instead of zero (the column's default value was zero). Through an investigation we discover that CATS statement skipped default value check of that column.

SPM - SQL PLAN MANAGER

noreply@blogger.com (Hasan Shaharear) — Wed, 28 Dec 2011 03:53:22 PST

As a DBA some I feel that Oracle is living thing ! for example query performance. Out of no where a gentle looking SQL statement may pop up your top time consuming query list. A sql query which gives efficient performance in 3 months ago (Or even week ago), now giving horrible performance that threatening you cool database performance :)

Yes, we all DBA know that a gentle statement can screwed up due to
- Re-guthering optimizer statistics
- Change in optimizes parameters
- Change in Schema? Object structure (i.e, metadata definition)

Though Optimizer always try to choose list expensive plan for each SQL statement execution, Some time It (optimizer) choose expensive plans and raise annoying situations for peace loving DBAs :)

Well Oracle 11g come up with a new feature that can handle such weird situation. The simplest solution we can think is a mechanism that only allow trusted SQL plans to be executed and reject untrusted plans. O Yes Oracle 11g gives us a manager named SPM that do the same thing.

SQL PLAN MANAGER (SPM) has three major components

SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.
SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.
SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)

To enable this cool feature we just do the followings

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]
ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

You are done ! Keep an eye in DBA_SQL_PLAN_BASELINES view and enjoying SPM's magic

TABLE_EXISTS_ACTION

noreply@blogger.com (Hasan Shaharear) — Tue, 27 Dec 2011 01:44:13 PST

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is 'SKIP', so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

ORA-08102

noreply@blogger.com (Hasan Shaharear) — Wed, 19 Oct 2011 04:14:53 PDT

ORA-08102: index key not found, obj# 630, file 1, block 4353

Cause: Internal error: possible inconsistency in index.

Action: Send trace file to your customer support representative, along with information on reproducing the error

Well I don't have access to oracle support (if you need it, you need to buy that service). So first of all, I tried to identify which index cause that problem.

select * from dba_objects x where x.object_id=630;

Guess what ? it's an index own by 'SYS' user. So I issued index rebuild command and it didn't work. As the index lies in system tablespace, I didn't want to move it to other tablespace. Thus I droped and created the index again.

It works. Previously, I thought that 'REBUILD' command internaly drop an object and recreate it. But, now I need to explore 'How rebuild command works ?'.

ORA-01591: lock held by in-doubt distributed transaction string

noreply@blogger.com (Hasan Shaharear) — Mon, 17 Oct 2011 01:17:06 PDT

Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: DBA should query the DBA_PENDING_TRANSACTIONS and DBA_2PC_PENDING, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact end user for rollback/commit the transection.

sqlplus / as sysdba;

SQL> SELECT * FROM DBA_2PC_PENDING;

SQL> ROLLBACK FORCE LOCAL_TRAN_ID;

SQL> commit;

SQL> alter system enable distributed recovery;

SQL> execute dbms_transaction.purge_lost_db_entry('LOCAL_TRAN_ID');

SQL > commit;

For more details plese go through this document

Table Comparison and Synchronisation

noreply@blogger.com (Hasan Shaharear) — Mon, 25 Jul 2011 06:07:33 PDT

If you are managing more than one databases for same application, it is very common that you need to compare one table ( say scott.employee) in two different databases. I think, we had previous experiences to do such job :) . The simplest way is just minus two tables. Like

1. Two different tables In same DB - SELECT * FROM table1 minus SELECT * FROM table2;
2. Same table in two different DB - SELECT * FROM table1 minus SELECT * FROM table2@remote_db;

But this simple task can be time consuming if your tables are very big (say 1000000+ records). If targeted tables do not contain BLOB,CLOB or Long data type columns, you can easily use DBMS_COMPARISON, a new package introduce in Oracle 11g, to compare them. More over this package also provide Synchronization to remove miss match records in targeted tables. For details please go through Oracle Documentations.

Here we are going to compare and synchronise 'employee' table in two different DB.

Requirement: A database link between host (i.e, where these script will be run) and remote DB.

Step One: Create comparison Task

BEGIN
DBMS_COMPARISON.CREATE_COMPARISON(
comparison_name => 'compare_employee'
, schema_name => 'SCOTT'
, object_name => 'EMPLOYEE'
, dblink_name => 'db_link_to_remote'
, remote_schema_name=>'SCOTT'
, remote_object_name=>'EMPLOYEE'
);
END;
/

If you wish to compare two different table in same DB, set dblink_name => NULL, object_name => 'table1' and remote_object_name=>'table2'

Step Two: Execute comparison Task

DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE( comparison_name =>'compare_employee', scan_info=> scan_info, perform_row_dif => TRUE);
DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
IF consistent=TRUE THEN DBMS_OUTPUT.PUT_LINE('No differences found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/

Step Three: See how may difference you got

col COMPARISON_NAME format a15;
col SCHEMA_NAME format a15;
col OBJECT_NAME format a15;

SELECT s.scan_id ,c.COMPARISON_NAME,c.SCHEMA_NAME,c.OBJECT_NAME ,s.CURRENT_DIF_COUNT
FROM USER_COMPARISON c,USER_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND s.scan_id = 514; (this number is a out put of step two)

Step Four: Synchronise Remote table with host table.

DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE (comparison_name => 'compare_employee',
scan_id => 506,
scan_info => scan_info, --this number is a out put of step two
converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

You can also Synchronise Host table by using Remote table, please visit given link.

I hope you all will enjoy this exiting new package of Oracle 11g.

Traveling past with Log Miner

noreply@blogger.com (Hasan Shaharear) — Mon, 11 Jul 2011 02:49:34 PDT

Some times you may wish to see what happen in recent past. In a beautiful morning, you may discover that a misshape occure in your database :(. Unfortunately you did not enabled any auditing options earlier.

If your database is in archivelog mod and supplemental log is enabled then 'Log Miner' can save the day ! Oracle Log Miner is also capable of mining redo log, archive log of remote DB. Please see Oracle Utility Document for details.

In this post, we only consider a scenario in which Source DB and Mining DB is same. Lets describe how you can do this

STEP 1 : Create Log Miner User

CREATE USER logminer_adm identified by logminer_adm;

GRANT resource,connect to logminer_adm;
GRANT EXECUTE_CATALOG_ROLE,DBA to logminer_adm;

Step 2 : Enable Supplemental Login

Check whether supplemental login is enabled

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If not, then you can't mine all transections. So enable it for future and follow the remaining steps to mine a subset of all transections.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 3: Add Archieve Log Files to Log Miner

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_470_71m4jv90_.arc', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/u/archivelog/2011_07_10/o1_mf_1_471_71m4kn09_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

You can add as much files as you need

Step 4: Start Log Miner

EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME =>'10-JUL-2011 19:50:00',ENDTIME => '10-JUL-2011 19:55:00',OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+DBMS_LOGMNR.CONTINUOUS_MINE);

Step 5: View What Happen Last Night :)

SELECT SEG_OWNER ,OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SYS';

Step 6: Stop Log Miner Session

EXECUTE DBMS_LOGMNR.END_LOGMNR;

DATA PUMP: Network mode import

noreply@blogger.com (Hasan Shaharear) — Sat, 02 Jul 2011 00:58:47 PDT

Some time we need to copy one schema from one DB machine to another machine or copy one schema as schema (i.e. remap schema) in same DB. The simplest way is to

(a) export required schema, move dump to remote DB and import [copy in 2 DB]

(b) export required schema and import the dump with schema replace option [copy in same DB]

Scenario ONE

Suppose the source schema is too small (say 1-3 GB) the above process is time consuming and labours compare with task requirements.

Scenario TOW

Suppose the source schema is too big (say 100 GB+) and there is not enough disk space to store the 100G size dump.

In above cases you can use the option of network import. But you need to consider that network import is a slower process because all the data should be travel in connection cable if your target is coping a schema in two different DB machine.

Lets describe how we can achieve this. we move user_info schema from source db to remote db.

STEP 1: grant exp_full_database role to socre shcema [IN REMOTE DB]
conn system/pass;

GRANT EXP_FULL_DATABASE to user_info;

STEP 2: [IN DESTINATION DB] Create destination user and grant necessary roles

conn system/pass;
CREATE USER user_info
IDENTIFIED BY pass
DEFAULT TABLESPACE USER
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT , RESOURCE TO user_info;

STEP 3: [IN DESTINATION DB] grant read/write on dump directory

conn system/pass;

GRANT read,write on DIRECTORY dump_directory to user_info;

Network import does not requer any dump file. This directory is only requer to write the import log file.

STEP 4: [IN DESTINATION DB] create public DB Link

conn system/pass;
CREATE PUBLIC DATABASE LINK SATURN
connect to user_info identified by pass
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_db)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=orcl.oracle.com)
(server=DEDICATED)))';

STEP 5: [IN DESTINATION DB MACHINE] execute impdp

impdp user_info/pass directory=dump_dir network_link=SATURN logfile=net_import_proddev.log EXCLUDE=GRANT,STATISTICS,SYNONYM,DB_LINK REMAP_SCHEMA=USER_INFO:USER_INFO

Multiplex online Redo Logfile

noreply@blogger.com (Hasan Shaharear) — Tue, 03 May 2011 20:43:53 PDT

It is important to have multiple (at list two) copy of each online redo logfile. To avoid the disaster of disk failure, it is recommended to place each member of a specific online redo logfile group in different disk.

To view to current online redo logfile group members

SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILES;

Suppose we have disk01 (mount point /u01 ) that contains current online redolog files .To add member in redo logfile group 1,2 and 3 in disk02 ( mount point /u02 )

ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo01.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo03.log' TO GROUP 3;

Track Database Growth

noreply@blogger.com (Hasan Shaharear) — Sun, 20 Mar 2011 21:19:06 PDT

Some time it become very important to monitor your database growth specially in test database machines. Usually these machines have very limited disk space.
Thus, tracking the consumption of disk space is one of the frequent tasks in administrative checklist. Here is a SQL script that gives you the current size of database and average disk space consumed in each day.

SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
SELECT a.days,a.tsname
, used_size_mb
, used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
FROM (
SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
,ts.tsname
,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days
) a
) b GROUP BY b.tsname ORDER BY b.tsname;

ORA-14519 Conflicting tablespace blocksizes while importing partition tables

noreply@blogger.com (Hasan Shaharear) — Wed, 26 Jan 2011 03:02:35 PST

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace T2 block size 16384 [partition specification] conflicts with previously specified/implied tablespace T1 block size 8192 [object-level default]

Well :) the error massage clearly indicate that I am trying to create a partition table in more than one tablespaces where the blocksize of the tablespaces are different. We all know that table partitions can not be placed in different blocksize tablespaces. Though I know this and not did this mistake before, I get this error when I am trying to make a replica schema in local test database.

I took dump by using EXPDP without any error and get the above error when try to import it by using REMAP_SCHEMA option of IMPDP. It is really annoying to get such error that can not be explainable. All of my table partitions are placed in a single tablespace but the error message says that I am trying to place table partitions in two tablespaces where their blocksize is different ! It sounds simply horrible.

But at the end of a long research :) I have found the mistake.The user's default tablespace is T1 [8k blocksize] and I create the table in tablespace T2 [16K blocksize] just like this -

CREATE TABLE TEST (ID NUMBER (10), TEXT VARCHAR2(50)

) PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This script create 2 table partitions of TEST table in T2 tablespace but unfortunately the default tablespace attribute of TEST table set to T1 (As I did not mansion default table space of table TEST when I create it. So ORACLE set this attribute same as user's default tablespace). See! Sometime ORACLE does something smartly that can cause stupid scenario. IF you want to see this unbelievable thing, just generate the table's SQL. You will see this

CREATE TABLE TEST (ID NUMBER (10),TEXT VARCHAR2(50)

) TABLESPACE T1

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This sturdiness of oracle cause ORA-14519 when I tryed to import TEST table because oracle can't create a partition table in different blocksize tablespaces.

So The solution is simple; mention default tablespace when you create a partition table. Just like this

CREATE TABLE TEST (

ID NUMBER (10),

TEXT VARCHAR2(50)

) TABLESPACE T2

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

[ It is not mandatory that all tablespace should be same (t2 in this case). You can put 3 different tablespace but their blocksize should be same.]

But, what is the solution for already created partition tables ? Well, the solution is very simple, just change the default tablespace attribute of your partition tables that are already created. Here is a script to identify the problematic tables.

SELECT * FROM (

SELECT x.table_name, x.def_tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name=x.def_tablespace_name) df

, x.tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name = x.tablespace_name) tb

FROM (

SELECT distinct t.table_name, t.def_tablespace_name, p.tablespace_name

FROM user_part_tables t, user_tab_partitions p

WHERE t.table_name = p.table_name

) x ) k WHERE k.tb <> k.df;

Then reset the default tablespace attribute

ALTER TABLE TEST MODIFY default attributes TABLESPACE T2;

Now take dump and apply it without facing ORA-14519 !

PL/SQL - Operator Precedence

noreply@blogger.com (Hasan Shaharear) — Fri, 08 Oct 2010 02:15:58 PDT

Hi all, It has been almost an year since I posted a topic in this blog. After a 1 year study break, I back to my usual job and hopfully I am become regular in this blog again.

In SQL or PL/SQL we use several operators. Some are mathematical, logical and comparison operatiors. Oracle follow a order of precedence when execute an expression that contains more than one operators. If operatiors with same precidence are occured then it does not follow any order. Otherwise Oracle maintain the following order of precedence of operators.

Order |------------| Operator |------------| Operation
1 |------------| ** |------------| exponentiation
2 |------------| +, |------------| identity, negation
3 |------------| *, / |------------| multiplication, division
4 |------------| +, -, || |------------| addition, subtraction, concatenation
5 |------------| =, <, >, <=, >=, |------------| comparison
<>, !=, ~=, ^=, IS NULL, LIKE,
BETWEEN, IN
6 |------------| NOT |------------| logical negation
7 |------------| AND |------------| conjunction
8 |------------| OR |------------| inclusion

For example, when NOT, AND and OR operators are used in the same statement NOT is evaluated first, then AND and finally OR.

Estimate Tablespace growth

noreply@blogger.com (Hasan Shaharear) — Tue, 15 Sep 2009 01:17:24 PDT

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Add Unique key in a table that contains duplicate row

noreply@blogger.com (Hasan Shaharear) — Wed, 26 Aug 2009 03:14:40 PDT

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.

SQL> create table t2 (id number(10), t varchar2(20));

Table created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found

SQL> select * from t2;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

So normal method does not work !

Case 1: New Table That means initially the table does not have any data

SQL> create table t3 (id number(10), t varchar2(20));

Table created.

SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;

Table altered.

SQL> alter table t3 disable constraint gpu;

Table altered.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from t3;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

SQL> alter table t3 enable novalidate constraint gpu;

Table altered.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated

SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated

Case 2: Existing Table that contains duplicate data

SQL> create table t2 (id number(1),a varchar2(10));

Table created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated

SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated

OR

SQL> alter table t2 add constraint uk_t2 unique(id) disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated

Who use Temporary Tablespace ?

noreply@blogger.com (Hasan Shaharear) — Tue, 26 May 2009 03:55:05 PDT

Temporary Tablespace is used for sorting purpose it also temporarily data like temporary tables.

Here is a query that give you, which sql statement use who much on Temporary Tablespace (I considered TEMP is a 8k block size tablespace)

select su.username
, dbms_lob.substr(sq.sql_fulltext, 4000, 1) sql_text
, su.blocks
, su.extents
, su.segtype
, (su.blocks*8)/1024 size_MB
, sq.last_active_time
, sq.last_load_time
from v$sort_usage su
join v$sqlarea sq on (su.sql_id = sq.sql_id)
left join v$session s on (s.sql_id=su.sql_id)

where su.tablespace='TEMP';

ORA-10631

noreply@blogger.com (Hasan Shaharear) — Mon, 04 May 2009 23:46:19 PDT

Message: ORA-10631: SHRINK clause should not be specified for this object

Cause: shrink clause use inappropriately.

Solution: Write the syntax correctly. If a table has function based index or Text index, it can not be shrinked

Moving Oracle Text Index

noreply@blogger.com (Hasan Shaharear) — Sun, 26 Apr 2009 21:46:26 PDT

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;

You can even do it online for most indexes:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow thw above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

* DR$[index_name]$I
* DR$[index_name]$K
* DR$[index_name]$N
* DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');

TEXT Index Size

noreply@blogger.com (Hasan Shaharear) — Wed, 22 Apr 2009 02:17:50 PDT

Oracle Text Index, an interMedia Index, is powerful for searching user specified text within a column or columns. It is widely used in web applications such as search engines, content management systems. As a DBA, accurately identify sizes of text indexes in the database and monitor their growth is important. But the problem is that text index is a domain index, and every text index internally consists of several tables with names prefixed with 'DR$'. Some of these tables have their own indexes and some of them are Index Organized Tables (IOTs). If you search in dba_segments, there is no segment for text indexes. SO when you try to calculate the size of an text index, you should consider all of these DR$ tables and their indexes. The script provided here gives a fast report of the sizes of all text indexes in the schema:

COL table_name format A30;
COL ind_nm format A30;
COL KB 9999999999;

select table_name, x.index_name, sum(KB) KB from
(select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB
from user_tables t, user_segments s
where t.table_name = s.segment_name and t.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
union
select substr(table_name, 4, instr(table_name, '$', -1)-4) index_name,
sum(bytes)/1024 KB from user_indexes i, user_segments s
where i.index_name = s.segment_name and i.table_name like 'DR$%$%'
group by substr(table_name, 4, instr(table_name, '$', -1)-4)
) x, user_indexes ind
where x.index_name = ind.index_name
group by table_Name, x.index_name
order by table_name, x.index_name;

Related Topics:

Features of Oracle Flashback

noreply@blogger.com (Hasan Shaharear) — Fri, 17 Apr 2009 23:24:27 PDT

1. Flashback query

Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select id from hasan.test where id in (100,101);
SQL>delete from hasan.test where id in (100,101);
SQL>commit;

SQL>select id from hasan.test as of timestamp systimestamp - interval '20' minute where id not in (select id from hasan.test);

SQL>insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '10' minute
where id not in (select id from hasan.test);

SQL>commit;

2. Flashback Table

When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints

SQL> select dbms_flashback.get_system_change_number from dual;

SQL>select count(*) from hasan.test;
SQL>delete from hasan.test;
SQL>commit;

SQL>alter table hasan.test enable row movement;
SQL>Flashback table hasan.test to SCN ;
SQL>Flashback table hasan.test to timestamp systimestamp - interval '5' minute ;

SQL>select count(*) from hasan.test;

3. Flashback Version Query

Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management.

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> select password from hasan.test where id in (100,101);

SQL> update hasan.test set password='Bang'where id in (101);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> update hasan.test set password='ladesh'where id in (100);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> delete from hasan.test where id in (100,101);
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> insert into hasan.test
select * from hasan.test as of timestamp systimestamp - interval '20' minute
where id not in (select id from hasan.test);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;

SQL> SELECT versions_startscn start_scn
, versions_endscn end_scn
, versions_xid transection_id
, DECODE(versions_operation,'I','Insert','U','Update','D','Delete',NULL) oper
, id, password
FROM hasan.test versions between scn 85174629 AND 85186130
WHERE id in (100,101) Order by start_scn;

4. Flashback Transaction Query

You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes.

SQL> sqlplus / as sysdba

SQL> SELECT * FROM flashback_transaction_query x
where x.table_name='LOGIN' AND x.table_owner='HASAN'
AND x.start_timestamp >(systimestamp - interval '10' minute);

5. Flashback Drop

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

SQL> create table table1 (a number(1));
SQL> INSERT INTO table1 values(1);
SQL> INSERT INTO table1 values(2);
SQL> INSERT INTO table1 values(3);
SQL> INSERT INTO table1 values(4);
SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

SQL> DROP TABLE hasan.table1;

SQL> SELECT ur.base_object, ur.object_name,ur.original_name FROM user_recyclebin ur;

SQL> FASHBACK TABLE table1 TO BEFORE DROP;

6.Flashback Database

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast

Related Topics:

Uninstall Oracle 10gR2 On Solaris 5.10

noreply@blogger.com (Hasan Shaharear) — Thu, 09 Apr 2009 00:08:13 PDT

To uninstall Oracle 10gR2 from Solaris 5.10, please do the followings

Remove all database, by running dbca.
Stop aall oracle running process : A. Database Control : $ORACLE_HOME/bin/emctl stop dbconsole B.Oracle Net listener : $ORACLE_HOME/bin/lsnrctl stop C. iSQL*Plus : $ORACLE_HOME/bin/isqlplusctl stop D.Ultra Search : $ORACLE_HOME/bin/searchctl stop
Start Oracle Universal installer locating at $ORACLE_HOME/oui/bin/runInstaller.
In the Welcome window, click Deinstall Products.
In the Inventory screen, select the Oracle home and the products that you want to remove, then click Remove.

Multiplexing Control File

noreply@blogger.com (Hasan Shaharear) — Wed, 08 Apr 2009 23:15:10 PDT

Oracle consist of three major physical files, they are :

Controlfiles
Datafiles
Online Redo log files

Among them control files are the most impotent one. Controlfile contains Database name, database creation date, Tablespace names, Physical location of datafiles and Recovery information.

With default installation, Oracle has 3 control files placed in same physical location. According to database availability, It is safe to place the 3 controlfiles in disk.

To see the current physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua1/control03.ctl

Suppose we have two HD and those two are mount as /ua1 and /ua2. So we need to move at list one controlfile in /ua1 and we move the 3rd contronlife. there are several way to do the Control File Multiplexing:

1. Using SPFILE:

The steps to multiplex control files using an SPFILE are describe bellow:

Login as SYSDBA

1. Alter the SPFILE: Using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used.

SQL> ALTER SYSTEM SET control_files='/ua1/control01.ctl'
,'/ua1/control01.ctl', '/ua1/control01.ctl' scope=spfile;

2. Shut down the database: Shut down the database in order to create the additional/ relocate control files on the operating system.

SQL> SHUTDOWN IMMEDIATE;

3. Create additional control files: Using the operating system copy command, create/move the additional control files as required and verify that the files have been created in the appropriate directories.

mv /ua1/control01.ct /ua2

4. Start the database: When the database is started the SPFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL>STARTUP;

To see the changed physical location of control file

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/ua1/control01.ctl
/ua1/control02.ctl
/ua2/control03.ctl

Find Top N resource absorbing SQL Queries

noreply@blogger.com (Hasan Shaharear) — Mon, 23 Feb 2009 01:18:19 PST

At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page. Suppose user reported slow performance and this occurred in night or in holiday. Now You need to see which sql queries were run at that specific time along with their resources usages details.

With default settings, in every hour a snapshots taken by Automatic Workload Repository (AWR) and those snapshots are available for next 7 days. Here is a SQL query that give the desired top N resource sensitive queries :

SELECT * FROM
(SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('18-feb-2009 18:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('18-feb-2009 18:40', 'dd-mon-yyyy hh24:mi'))
AND PARSING_SCHEMA_NAME='SCOTT'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
) WHERE rownum < =200;

DRIVING_SITE SQL Hint

noreply@blogger.com (Hasan Shaharear) — Wed, 11 Feb 2009 00:30:40 PST

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.

create Foreign key On two diffrent schema table

noreply@blogger.com (Hasan Shaharear) — Wed, 11 Feb 2009 00:33:17 PST

Some times you may face easy questions which can put into difficulties. Today I got that kind of a question and i am happy so that I get the answer though it take some time find it.

Here is the question which i got from a forum member :

" how to link a table from scott user to the hr user using a foreign key. For an example hr.emp to scott.dept using a foreign key "

In my answer, I assume hr.emp table has a colunm name scott_dept_id . So I create a foreign key on hr.emp (scott_dept_id) by referencing scott.dept (id).

sql> connect scott / pass;
sql> GRANT select,update, delete, references on dept to hr;

Though only references privilege required to create the foreign key

sql> connect hr/password;
sql> alter table emp add constraint fk_test foreign key (scott_dept_id) references scott.dept (id);

So simple ! but hard enough to bother you, if you not know exactly . Thanks to that forum user who lead me to learn this thing .

Reverse Key Index

noreply@blogger.com (Hasan Shaharear) — Tue, 10 Feb 2009 22:43:52 PST

I think we are familiar with Oracle Index, specially Bitmap Index. Indexes are used to help oracle retrieve data faster. But there is a drawback of Index, if an index create on a large table which under go massive insert/ update, the index may raise contention issue. To get ride from this Oracle provides couple of solutions and Reverse key Index is one of them.

Real world case scenario

Case 1: Suppose table 'track_user' contains login related information of a system. The primary key column 'ID' of the table populated by an increasing sequence. So every new entries (for the index) come to the same blocks when a row inserted into the table. This is the way contention may increase!

As all we know that the primary key constraint is impose on column by creating an unique index on that column. So if we use reverse key indexes in this case then the index entries will go to different blocks and contention will be reduced.

Case 2: If you have a table with column which is populated by an increasing sequence and some times it go under some delete operation for old records. A index was created on That column and this index is on face range scan when you issue a select on that table. But this index has contention issues on index blocks.

To avoide the contention issue you can use Reverse key index as a soluation.

What is Reverse key index ?

Reverse key index was first introduce in Oracle 8. A reverse key index reverses the bytes of each column indexed (except for the ROWID) while keeping the column order same as normal index.

Uses

Reverse key index can help avoid performance degradation in indexes in an Oracle Parallel server environment where modifications to the index are concentrated on a small set of leaf blocks. For example, if you insert rows with keys 101, 102 and 103 into a table with a regular index, the rows are likely to be inserted into the same leaf block. When users connected to different instances of the same database insert into the same block a pingoccurs.(When a block is written to disk by one instance so that another instance can read it, it is called a ping.) Excessive pinging will severely degrade performance, so you want to reduce it. In a Reverse Key Index the keys in our example become 101, 201 and 301, and the rows are inserted into disparate parts of the index segment. This type of index is therefore useful in a parallel server environment because it reduces pinging.
Reverse Key Indexes can also be useful in situations where users insert ascending values and delete lower values from a table. A regular index would become skewed, but a Reverse Key Index would not.

Disadvantages

With a Reverse Key Index you cannot run an index range scanning query. This is because lexically adjacent keys are not stored next to each other in a Reverse Key Index. You can only perform fetch-by-key value or full-index scans . Of course, you can avoid the index and perform full table scans or use the parallel query option.

Create And Manage Reverse Key Index

You create a Reverse Key Index with the key word REVERSE:

Create Index index_name on table_name (a,b,c) Reverse;

You can rebuild a Reverse Key Index into a regular index with the keyword NOREVERSE

Alter Index index_name Rebuild Noreverse;

If you rebuild a Reverse Key Index without the keyword NOREVERSE, it will rebuilt the Reverse Key Index.

Alter Index index_name Rebuild;

You cannot rebuild a normal index into a reverse key index. You must drop the normal index and create the reverse index.