Primary key validation for new rows only ......

Anuj:orcl>create table test ( a number );

Table created.

Anuj:orcl>insert into test ( a ) values ( 1 ) ;

1 row created.

Anuj:orcl>/

1 row created.

Anuj:orcl>/

1 row created.

Anuj:orcl>insert into test ( a ) values (2 ) ;

1 row created.

Anuj:orcl>alter table test add constraint test_pk primary key ( a ) novalidate ;
alter table test add constraint test_pk primary key ( a ) novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.test_PK) - primary key violated


Anuj:orcl>create index test_i on test ( a );

Index created.

Anuj:orcl>alter table test add constraint test_pk primary key ( a ) novalidate ;

Table altered.

Anuj:orcl>select * from test ;

A
----------
1
1
1
2

Anuj:orcl>insert into test ( a ) values (2 ) ;
insert into test ( a ) values (2 )
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.test_PK) violated

Oracle 10g when transactions will finish rolling back

select sum(distinct(ktuxesiz)) from x$ktuxe; value should be very less ....




@rollback_active.sql

set serveroutput on
set feedback off
prompt
prompt transactions that are rolling back ...
prompt

declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v_$transaction t,
sys.v_$session s
where
x.inst_id = userenv('Instance') and
x.ktuxesta = 'ACTIVE' and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv('Instance') and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn || '.' ||
xid_slot || '.' ||
xid_sqn ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;
/

Size of oracle Control File

Size of control files

SYS@orcl> select sum((cffsz+1)*cfbsz)/1024/1024 "Control File Size(MB)" from x$kcccf;

Control File Size(MB)
---------------------
20.203125

How to stop oracle Trace ?

Stop trace dump


1. Get the process number from trace file.

Example :
-rw-rw—- 1 dba 184034 Jan 29 17:02 test_diag_17701.trc

2. 18701 is the process number from trace file.

SQL> oradebug setospid 17701
Oracle pid: 3, Unix process pid: 17701,

SQL> oradebug close_trace;
Statement processed.

Data pump status .... is job running ?

col JOB_NAME format a20
col OPERATION format a10
col JOB_MODE format a10
col STATE format a10
Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


SQL> r
1* Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


JOB_NAME OPERATION JOB_MODE STATE

-------------------- ---------- ---------- ----------
SYS_IMPORT_TABLE_08 IMPORT TABLE EXECUTING


set lines 200 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
col date format a20
col job_name format a20
col status format a10
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;


DATE PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL# PID

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

2010-07-13 09:44:21 udi@ln-ora-01tst (TNS V1-V3) 143 INACTIVE SYS SYS_IMPORT_TABLE_13 14130 2 23

2010-07-13 09:44:21 oracle@ln-ora-01tst (DM00) 146 ACTIVE SYS SYS_IMPORT_TABLE_13 14132 60 24

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW01) 140 ACTIVE SYS SYS_IMPORT_TABLE_13 14134 3 25

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW02) 137 ACTIVE SYS SYS_IMPORT_TABLE_13 14136 2 26






-bash-3.00$ impdp "'/ as sysdba'" attach=SYS_IMPORT_TABLE_08

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 02 July, 2010 14:58:09

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

Job: SYS_IMPORT_TABLE_08
Owner: SYS
Operation: IMPORT
Creator Privs: TRUE
GUID: 8A5433675F594D74E0440003BA0AE183
Start Time: Thursday, 01 July, 2010 14:10:58
Mode: TABLE
Instance: ccdb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND '/******** AS SYSDBA' directory=datapump1 DUMPFILE=pumpuat_1405.dmp SCHEMAS=(PROD1,UAT2,ESEARCH)
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND '/******** AS SYSDBA' DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=prod1:test TABLES=user_sites LOGFILE=user_sites01july.log TABLE_EXISTS_ACTION=APPEND
TABLE_EXISTS_ACTION APPEND
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/backup/pumpuat_1405.dmp

Worker 1 Status:
State: EXECUTING
Object Schema: TEST
Object Name: USER_SITES
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 239,889,948
Completed Bytes: 10,016,441,128
Percent Done: 100
Worker Parallelism: 1

Oracle 10g Datapump Session Wait Information

set lines 150 pages 100 numwidth 7
col program for a30
col username for a10
col spid for a7
col date for a20
col job_name for a20
col STATUS for a10

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.serial#,

s.status, s.username, d.job_name, p.spid, p.pid

from v$session s, v$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr; 2 3 4



DATE PROGRAM SID SERIAL# STATUS USERNAME JOB_NAME SPID PID

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

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DM00) 119 921 ACTIVE SYS SYS_IMPORT_TABLE_08 19832 40

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DW01) 124 4 ACTIVE SYS SYS_IMPORT_TABLE_08 19834 44




-- In SQL*Plus, activate SQL tracing with DBMS_SYSTEM and SID/SERIAL#
-- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')

-- Example to SQL_TRACE Worker process with level 4 (Bind values):
execute sys.dbms_system.set_ev(119,921,10046,4,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');


-- Example to SQL_TRACE Master Control process with level 8 (Waits):
execute sys.dbms_system.set_ev(119,921,10046,8,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');

SQL> execute sys.dbms_system.set_ev(119,921,10046,12,'');
PL/SQL procedure successfully completed.
SQL> execute sys.dbms_system.set_ev(124,4,10046,12,'');
PL/SQL procedure successfully completed.


-- Activate SQL tracing database wide,
-- Be careful: all processes will be traced !


CONNECT / as sysdba
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';


- Start the Import/export Data Pump or Import Data Pump job, e.g.:

impdp system/XXXX DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=scott:test TABLES=user_site LOGFILE=user_site.log


-- Unset event immediately after Data Pump job ends:

ALTER SYSTEM SET EVENTS '10046 trace name context off';


To Check session trace is on ?

SQL> select sid,serial#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session;

SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------- ------- ------------------------ --------------- ---------------
119 921 ENABLED TRUE TRUE <<<<<<<<---------
124 4 ENABLED TRUE TRUE <<<<<<<<---------
127 218 DISABLED FALSE FALSE
134 636 DISABLED FALSE FALSE
140 199 DISABLED FALSE FALSE
147 4 DISABLED FALSE FALSE

Data pump Shell Script on Oracle Solaris

#CREATE DIRECTORY datapump AS '/opt/oracle/backup/DataPump/Backup';


# !/bin/bash

TODAY=`date`

ORACLE_SID=orcl ; export ORACLE_SID

ORACLE_BASE=/opt/oracle ; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME

PATH=${ORACLE_HOME}/bin:$PATH ; export PATH

EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE

ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE

LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE

SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC



# echo $EXPFILE



find /opt/oracle/backup/DataPump/Backup -name "*.err" -mtime +2 -exec rm {} \;



ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp



if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then



[ -f $ERRFILE ]; rm $ERRFILE



expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=SCOTT,XYZ logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4



fi



find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +3 -exec rm {} \;