Is oracle database using a PFILE or SPFILE ?

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter
WHERE name = 'spfile';


Init File Type

------------------
SPFILE


or


SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ -------------- -----------
spfile string /opt/oracle/product/10.2/dbs/spfilecccdb.ora

Oracle connect sqlplus in comand line

sqlplus [user]/[password]@//[database_host]:[port]/[service_name]

sqlplus system/xxxxx@//192.168.2.151:1521/ora

Unix Pid to Sql

run a top command



PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
11149 oracle 1 0 0 3204M 3188M cpu/0 124:11 45.67% oracle ----<<<
11485 oracle 1 32 0 3205M 3188M cpu/0 0:49 18.47% oracle
11489 oracle 1 59 0 2352K 1864K cpu/2 0:00 0.16% top
11496 oracle 1 59 0 3204M 3187M sleep 0:00 0.09% oracle
647 oracle 15 59 0 3216M 3163M sleep 64:19 0.08% oracle
655 oracle 1 59 0 3208M 3166M sleep 60:06 0.08% oracle
847 noaccess 25 59 0 192M 54M sleep 172:23 0.07% java
4048 noaccess 25 59 0 222M 58M sleep 171:54 0.07% java
659 oracle 1 59 0 3203M 3165M sleep 54:39 0.06% oracle
639 oracle 1 59 0 3204M 3170M sleep 36:28 0.05% oracle
740 oracle 11 59 0 3207M 3176M sleep 34:36 0.05% oracle
137 root 15 59 0 8328K 1104K sleep 74:44 0.03% picld
649 oracle 21 59 0 3208M 3166M sleep 29:06 0.03% oracle
645 oracle 258 59 0 3224M 3177M sleep 35:50 0.02% oracle
744 oracle 1 59 0 3204M 3176M sleep 14:24 0.01% oracle

PID 11149 taking lot of cpu i.e. 45.67%

run this sql find out sql text, session sid and serial no


set linesize 200
col SID_serial format a15
select ses.sid||','||ses.SERIAL# SID_serial,sqa.SQL_TEXT SQL
from v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&pid_sql;

if you want to kill this process then run this sql



alter system kill session 'ses.sid,ses.SERIAL# ' ;

calculate size of Undo tablespace and Undo Retention !

Formula:

Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC


set linesize 200
col "OPTIMAL UNDO RETENTION [Sec]" format 99999999999
col "UNDO RETENTION [Sec]" format a20

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/


====

from Web



set serveroutput on
set feedback off
set heading off
set lines 132

declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';

begin

dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :');
dbms_output.put_line('====================================================' || chr(10));

for rec1 in get_undo_stat loop
dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'|| chr(10));

dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||TO_CHAR(rec1.c1,'999999') || ' MEGS');

dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');

dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');

dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS)',65,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');

end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/




select 'Number of "ORA-01555 --Snapshot too old " encountered since the last startup of the instance : ' || sum(ssolderrcnt) from v$undostat;




set linesize 200
col "ACTUAL UNDO SIZE [MByte]" format 99999999
col "UNDO RETENTION [Sec]" format a25


apt-rdbms-01.us.co.uk:PTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 "NEEDED UNDO SIZE [MByte]"
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = 'UNDO'
12 AND c.status = 'ONLINE'
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
19 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
20 undo_block_per_sec
21 FROM v$undostat
22 ) g
23 WHERE e.name = 'undo_retention'
24 AND f.name = 'db_block_size'
25 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
800 900 34.2421875

apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /


ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
800 900 21027

we can increase the size of UNDO RETENTION to >> OPTIMAL UNDO RETENTION [Sec]



apt-rdbms-01.us.co.uk:TDB\sys> apt-rdbms-01.us.co.uk:TDB\sys> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE MB"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE MB
------------
800



-- Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;


apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
USERS NOT APPLY
JTIME79 NOT APPLY
TEMP1 NOT APPLY
APTUS_OBJ NOT APPLY

7 rows selected.


DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_OBJECTS


==

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24




Select a.task_id, a.task_name, a.type, a.impact_type, a.impact,
b.benefit_type, c.message
from dba_advisor_findings a, dba_advisor_recommendations b,
dba_advisor_rationale c
where a.task_id=b.task_id
and a.finding_id=b.finding_id
and a.task_id=c.task_id
and a.task_name not like 'SYS_AUTO_SQL_TUNING%'
or a.task_name not like 'ADDM%'
or a.task_name not like '%ADDM%'
or a.task_name not like '%AWR_SNAPSHO%'
-- order by a.task_id, a.task_name, a.finding_id;

Declare
ret_val varchar2(2000);
PROCEDURE Show_Message(p_Msg_in IN CLOB)
IS
BEGIN
IF LENGTH(p_Msg_in) > 255
THEN
DBMS_OUTPUT.Put_Line(SUBSTR(p_Msg_in,1,255));
Show_Message(SUBSTR(p_Msg_in,256,LENGTH(p_Msg_in)));
ELSE
DBMS_OUTPUT.Put_Line(p_Msg_in);
END IF;
END;
begin
dbms_output.enable(1000000);
ret_val:=dbms_sqltune.report_tuning_task('TASK_00004');
show_message(ret_val);
end;
/




-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('937_1135_AWR_SNAPSHOT_UNDO1') AS report FROM dual;
SET PAGESIZE 24




DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 937);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 1135);
DBMS_ADVISOR.execute_task(tname);
end;





SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('937_1135_AWR_SNAPSHOT_UNDO1')
ORDER BY f.task_name, f.impact DESC;



SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name IN ('Undo Advisor')
ORDER BY f.task_name, f.impact DESC;

Oracle 10g Estimate Tablespace growth

set pagesize 200
col tsname format a20

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;

Oracle os process (unix ) to Sql text

run top command on unix prompt. find out oracle os pid from top command

and run following Sql .

set linesize 200
col SID_serial format a15
select ses.sid||','||ses.SERIAL# SID_serial,sqa.SQL_TEXT SQL
from v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&pid_sql;

prompt -- alter system kill session ' ' ;

Oracle top Session

from web

Modified and now it is working ....

SET VERIFY off;
SET LINESIZE 120;
SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAPPED;
--
SET HEAD off;
ACCEPT var_status DEFAULT '' PROMPT 'ENTER session status (e.g. ACTIVE, INACTIVE, leave blank for all) > ';
ACCEPT var_uid DEFAULT '' PROMPT 'ENTER user name (leave blank for all) > ';
PROMPT ;
SET HEAD on;
--
DECLARE
--
CURSOR cusr_cur ( cp_totcpu NUMBER, cp_logrds NUMBER, cp_phyrds NUMBER, cp_rdosz NUMBER, cp_srtdsk NUMBER,
cp_status VARCHAR, cp_uid VARCHAR ) IS
SELECT NVL(a.username,'UNKNOWN') uname,
a.sid sid,
a.serial# sernum,
b.spid spid,
a.osuser suser,
a.status status,
a.server server,
a.machine smach,
a.process process,
a.program sprog,
a.client_info cli,
c.value/100 tcpu,
d.value lrds,
e.value prds,
f.value/1024 rsz,
h.value sd
FROM V$SESSION a,
V$PROCESS b,
V$SESSTAT c,
V$SESSTAT d,
V$SESSTAT e,
V$SESSTAT f,
V$SESSTAT h
WHERE a.paddr = b.addr
AND a.sid = c.sid
AND a.sid = d.sid
AND a.sid = e.sid
AND a.sid = f.sid
AND a.sid = h.sid
AND c.statistic# = cp_totcpu
AND d.statistic# = cp_logrds
AND e.statistic# = cp_phyrds
AND f.statistic# = cp_rdosz
AND h.statistic# = cp_srtdsk
AND a.type != 'BACKGROUND'
AND a.status LIKE cp_status||'%'
AND a.username LIKE cp_uid||'%'
ORDER BY c.value DESC;
--
cusr_rec cusr_cur%ROWTYPE;
--
var_totcpu NUMBER;
var_logrds NUMBER;
var_phyrds NUMBER;
var_rdosz NUMBER;
var_srtdsk NUMBER;
--
var_sid NUMBER;
var_tcpu NUMBER;
var_lrds NUMBER;
var_prds NUMBER;
var_rsz NUMBER;
var_sd NUMBER;
--
var_stat_row INTEGER := 1;
var_row_max INTEGER := 20;
--
var_timestamp VARCHAR2(21);
var_cnt INTEGER := 1;
--
BEGIN
--
DBMS_OUTPUT.PUT_LINE('Top '||TO_CHAR(var_row_max)||' Sessions, Version 1.4, 01/30/2004');
--
SELECT statistic#
INTO var_totcpu
FROM V$STATNAME
WHERE name = 'CPU used by this session';
--
SELECT statistic#
INTO var_logrds
FROM V$STATNAME
WHERE name = 'session logical reads';
--
SELECT statistic#
INTO var_phyrds
FROM V$STATNAME
WHERE name = 'physical reads';
--
SELECT statistic#
INTO var_rdosz
FROM V$STATNAME
WHERE name = 'redo size';
--
SELECT statistic#
INTO var_srtdsk
FROM V$STATNAME
WHERE name = 'sorts (disk)';
--
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS')
INTO var_timestamp
FROM dual;
--
DBMS_OUTPUT.PUT_LINE( 'Timestamp: '||var_timestamp);
DBMS_OUTPUT.PUT_LINE(CHR(10));
--
DBMS_OUTPUT.PUT_LINE( ' User DB Serial Server Total CPU Logical Physical Redo Size Sorts' );
DBMS_OUTPUT.PUT_LINE( ' Name SID Num PID Status Time(Sec) Reads Reads (KBytes) (Disk)' );
DBMS_OUTPUT.PUT_LINE( '--------------- ------- ------- -------- -------- ---------- ----------- ----------- --------- ------' );
--
-- var_stat_row := 1;
OPEN cusr_cur (var_totcpu, var_logrds, var_phyrds, var_rdosz, var_srtdsk, UPPER('&var_status'), UPPER('&var_uid'));
-- <>
LOOP
FETCH cusr_cur INTO cusr_rec;
-- EXIT CUR1 WHEN cusr_cur%NOTFOUND;
EXIT WHEN cusr_cur%NOTFOUND;
--
var_cnt := var_cnt + 1;
--
IF var_stat_row <= var_row_max THEN
DBMS_OUTPUT.PUT_LINE( LPAD(cusr_rec.uname,15,' ')||' '
||LPAD(TO_CHAR(cusr_rec.sid,'999999'),8,' ')||' '
||LPAD(TO_CHAR(cusr_rec.sernum,'999999'),8,' ')||' '
||LPAD(cusr_rec.spid,9,' ')||' '
||LPAD(cusr_rec.status,9,' ')||' '
||LPAD(TO_CHAR(cusr_rec.tcpu,'9999990.99'),11,' ')||' '
||LPAD(TO_CHAR(cusr_rec.lrds,'999,999,990'),12,' ')||' '
||LPAD(TO_CHAR(cusr_rec.prds,'999,999,990'),12,' ')||' '
||LPAD(TO_CHAR(cusr_rec.rsz,'999,990'),10,' ')||' '
||LPAD(TO_CHAR(cusr_rec.sd,'99,990'),7,' ') );
--
var_stat_row := var_stat_row + 1;
END IF;
END LOOP;

-- CUR1;

CLOSE cusr_cur;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE( 'Total sessions that meet criteria: '||var_cnt);
END;
/

Oracle Table lock information

Oracle Lock Info
Oracle lock info



lock Mode

6 - Exclusive (X)
5 - Shared Row Exclusive (SRX)
4 - Share (S)
3 - Row Exclusive (RX)
2 - Row Share (RS)
1 - null (NULL)
0 - none



Row Level (TX)
Table Level (TM)

set linesize 100
set pagesize 100
select s.sid,s.serial#,s.username,l.sid,l.type "lock type",lmode "lock mode",s.SQL_ID
from v$lock L,v$session s
where l.sid = s.sid
-- and s.username='SCOTT'
and s.username is not null
order by l.sid



set linesize 121
SELECT r.name "ROLLBACK SEGMENT NAME ",l.sid "ORACLE PID",p.spid "SYSTEM PID ",s.username "ORACLE USERNAME"
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = p.pid(+)
AND s.sid=l.sid
AND TRUNC(l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name;


select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn
and b.usn=e.xidusn
and c.taddr=e.addr
and c.sql_address=d.address
and c.sql_hash_value=d.hash_value
order by a.name,c.sid,d.piece;




set termout off
set heading off
col dbname1 new_value dbname noprint
select name dbname1 from v$database;
set termout on
set heading on
set pagesize 9999
column osuser format a14 heading "-----O/S------|Username Pid"
column username format a17 heading "-----ORACLE-----|Username ID Ser"
column locktype format a10 heading "Type"
column held format a9 heading "Lock Held"
column object_name format a15 heading "Object Name" wrap
column request format a9 heading " Lock|Requested"
column id1 format 999999
column id2 format 9999
spool locks.txt
ttitle center 'Lock report for the ' &&dbname ' database' skip 2
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
' ' object_name,
decode(lmode,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',' ') held,
decode(request,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',' ') request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) held,
decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
'(Rollback='||rtrim(r.name)||')' object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) held,
decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6;
spool off



================================================


set lines 200
set pagesize 66
break on Kill on sid on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a30 heading "Table Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990

select
nvl(S.USERNAME,'Internal') username,
s.sid,
s.serial#,
nvl(S.TERMINAL,'None') terminal,
decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/



==========


set lines 200
set pages 200
col OsUser format a25
col OraUser format a10
col SID format 99999
col Owner format a10
col Ltype format a15
col Lmode format a10
col Lrequested format a10
col OBJECT_NAME format a20
select OS_USER_NAME osuser,
-- PROCESS os_pid,
ORACLE_USERNAME orauser,
l.SID Sid,
OWNER,
OBJECT_NAME,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) ltype,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lmode,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lrequested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status
from v$locked_object lo, dba_objects do, v$lock l
where lo.OBJECT_ID = do.OBJECT_ID(+)
AND l.SID = lo.SESSION_ID(+)
order by 1;

Oracle entire schema's scripts via dbms_metadata.get_ddl

dbms_metadata.get_ddl for entire schema's scripts



select distinct object_type from dba_objects where owner='ANUJ';

OBJECT_TYPE
---------------------------------------------------------
INDEX
PROCEDURE
TABLE
VIEW
FUNCTION
TRIGGER
PACKAGE BODY
PACKAGE
SEQUENCE
LOB


so there is space between PACKAGE BODY.


we can try this


Example .....

set long 3000
select dbms_metadata.get_ddl (REGEXP_replace(OBJECT_TYPE,'[[:space:]]','_'),object_name,owner)
from DBA_objects where owner='ANUJ' and OBJECT_TYPE like '%PACKAGE%';

Oracle swingbench installation

Load generator for Oracle

Swingbench installation on oracle 10g




download swingbench230422.zip

from http://dominicgiles.com/downloads.html

unzip swingbench230422.zip in oracle user home dir

in my case /home/oracle

pwd

/home/oracle


then

unzip swingbench230422.zip

it will create swingbench




download jre-6u22-linux-i586-rpm.bin

and install


apt-lnxtst-01:/tmp # sh jre-6u22-linux-i586-rpm.bin
Unpacking...
Checksumming...
Extracting...
UnZipSFX 5.50 of 17 February 2002, by Info-ZIP (Zip-Bugs@lists.wku.edu).
inflating: jre-6u22-linux-i586.rpm
Preparing... ########################################### [100%]
1:jre ########################################### [100%]
Unpacking JAR files...
rt.jar...
jsse.jar...
charsets.jar...
localedata.jar...
plugin.jar...
javaws.jar...
deploy.jar...

Done.

find the installation path

apt-lnxtst-01:/tmp # find /|grep rt.jar
/usr/java/jre1.6.0_22/lib/rt.jar
/usr/java/jre1.6.0_22/lib/alt-rt.jar


and change JAVAHOME accordingly ...

in my case /usr/java/jre1.6.0_22

oracle@apt-lnxtst-01:~/swingbench> cat swingbench.env

#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/10.2
export JAVAHOME=/usr/java/jre1.6.0_22
export SWINGHOME=/home/oracle/swingbench
export ANTHOME=$SWINGHOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/lib
export LOADGENHOSTS='localhost'
export LOADGENUSER=oracle
export CLASSPATH=$JAVAHOME/lib/rt.jar:$JAVAHOME/lib/tools.jar:$ORACLE_HOME/jdbc/lib/ojdbc14.jar:$SWINGHOME/lib/mytrations.jar:${SWINGHOME}/lib/swingbench.jar:$ANTHOME/ant.jar




anujs@apt-amd-02:~> ssh -Y oracle@192.168.2.232
Password:
Last login: Wed Nov 10 14:20:14 2010 from apt-amd-02.aptus.co.uk
oracle@apt-lnxtst-01:~> pwd
/home/oracle
oracle@apt-lnxtst-01:~> cd swingbench/
oracle@apt-lnxtst-01:~/swingbench> cd bin
oracle@apt-lnxtst-01:~/swingbench/bin> ./swingbench



and connect through

Oracle 10g Type II jdbc driver(oci)

Oracle high physical reads Sessions wise

SQL> r

1 select

2 OSUSER os_user,username,

3 PROCESS pid,

4 ses.SID sid,

5 SERIAL#,

6 PHYSICAL_READS,

7 BLOCK_CHANGES

8 from v$session ses,

9 v$sess_io sio

10 where ses.SID = sio.SID

11 and username is not null

12 and status='ACTIVE'

13* order by PHYSICAL_READS



OS_USER USERNAME PID SID SERIAL# PHYSICAL_READS BLOCK_CHANGES

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

oracle SYS 18038 112 1457 0 2

oracle SYS 18040 120 12352 0 2

oracle SYS 18032 128 24796 0 2

oracle SYS 18030 138 49309 0 2

oracle SYS 18034 135 63569 0 2

oracle SYS 18028 126 15159 0 2

oracle SYS 18036 146 22857 0 2

oracle SYS 18042 124 4489 7 10

oracle SYS 25381 131 54920 1516947 5

oracle SYS 17904 129 29110 15857587 47



10 rows selected.

Oracle Count Big table

How to count (*) big table in oracle ?

if 2 cpu then we can put no of parallel process 2 X 4  = 8 


select /*+ parallel (ttable, 8) */ count(*) from &table_name ttable ;



select /*+ parallel (ttable, 8) */ count(*) from anuj.user_site ttable ;



  COUNT(*)

----------

 280973975



Elapsed: 00:02:10.96




cpu info 



col STAT_NAME 	for a30
col VALUE 	    for a15
SELECT STAT_NAME, TO_CHAR (VALUE) AS VALUE, COMMENTS   FROM v$osstat
WHERE stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'NUM_CPU_SOCKETS')
UNION
SELECT STAT_NAME, trunc(VALUE / 1024 / 1024 / 1024) || ' GB', COMMENTS   FROM v$osstat
WHERE stat_name IN ('PHYSICAL_MEMORY_BYTES');

====

col param_name 			format a40 heading 	"Parameter Name"
col param_value 		format a38 heading 	"Parameter Value" word_wrap
col isdefault 			format a7 heading 	"Default|Value"

col isses_modifiable 	format a10 heading 	"Session|Modifiable"
col issys_modifiable 	format a10 heading 	"System|Modifiable"
col ismod                          heading 	"Is|Modified"


set linesize 500
col DISPLAY_VALUE for a20 
col DEFAULT_VALUE for a20 
col VALUE for a20
col UPDATE_COMMENT for a20
col DESCRIPTION for a20 
col NAME for a35
 select *
 from v$parameter
 where name in ('cpu_count', 'parallel_threads_per_cpu', 'parallel_degree_policy', 'parallel_degree_limit');

                                                                                                                    Default Session    System
       NUM NAME                                      TYPE VALUE                DISPLAY_VALUE        DEFAULT_VALUE        Value   Modifiable Modifiable ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION          UPDATE_COMMENT             HASH     CON_ID
---------- ----------------------------------- ---------- -------------------- -------------------- -------------------- ------- ---------- ---------- ----- ----- ---------- ----- ----- ----- -------------------- -------------------- ---------- ----------
       111 cpu_count                                    3 16                   16                   0                    TRUE    FALSE      IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE number of CPUs for t                      1095434542          3
                                                                                                                                                                                                his instance

      3392 parallel_degree_policy                       2 AUTO                 AUTO                 MANUAL               FALSE   TRUE       IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE policy used to compu                      1979359697          3
                                                                                                                                                                                                te the degree of par
                                                                                                                                                                                                allelism (MANUAL/LIM
                                                                                                                                                                                                ITED/AUTO/ADAPTIVE)

      3403 parallel_threads_per_cpu                     3 2                    2                    2                    TRUE    FALSE      IMMEDIATE  FALSE TRUE  FALSE      FALSE FALSE FALSE number of parallel e                      1020981983          3
                                                                                                                                                                                                xecution threads per
                                                                                                                                                                                                 CPU

      3776 parallel_degree_limit                        2 CPU                  CPU                  CPU                  TRUE    TRUE       IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE limit placed on degr                      3004872640          3
                                                                                                                                                                                                ee of parallelism

Oracle Temp segment usage per session

set linesize 300 pagesize 300
col kill for a15
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username,  c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space from  gv$session a,gv$sort_usage b, gv$process c, dba_tablespaces d
where    a.saddr  = b.session_addr
and      a.inst_id  = b.inst_id
and      a.paddr  = c.addr
and      a.inst_id  = c.inst_id
and      b.tablespace=d.tablespace_name
group by a.username, a.osuser, ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.sql_id,c.spid,b.tablespace, a.status;


set linesize 300 pagesize 300
col kill  for a15
col sql_text for a60 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, S.username,s.sql_id,T.blocks * TBS.block_size/1024/1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text from gv$sort_usage t, gv$session s, gv$sqlarea q, dba_tablespaces tbs
where 1=1
and t.session_addr = s.saddr
and t.inst_id = s.inst_id
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
order by s.sid;


set linesize 300 pagesize 300
col kill for a15
col username for a20
col program for a20
col tablespace for a12
select * from (select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.sql_id,s.PREV_SQL_ID,u.tablespace, s.username, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,i.inst_id,i.host_name from gv$session s, gv$sort_usage u ,gv$instance i
where s.saddr=u.session_addr
and u.inst_id=i.inst_id
and s.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by mb desc) a
where rownum <20;

Oracle TEMP tablespace usage

temp space info
temp usage
Temporary tablespace usage
all about temp tablespace
temp space usage



SYS AS SYSDBA>!cat temp.sql

col tablespace_name format a20

spool tempsp.txt
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;

col sid format a10
col username format a12
col tablespace format a15

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
a.sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2);

or

compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct,
s.sql_id
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128,
s.sql_id




-----

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment) s,
(select sum(blocks) total_blocks from dba_temp_files) f;

spool off


SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) /1024 /1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
TEMP 13240 1 13239




col SID_SERIAL format a12
col PROGRAM format a15
col OSUSER format a10
col MODULE format a10
col SPID format a10
col USERNAME format a10
col SQL_ID format a14
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements,s.sql_id
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,P.program, TBS.block_size, T.tablespace,s.sql_id
ORDER BY sid_serial;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS SQL_ID
------------ ---------- ---------- ---------- ---------- --------------- ---------- -------------------- ---------- --------------
138,3 PROD1 admin 28520 documentum oracle@co 1 TEMP 1
@dc ratst
tm-01tst (
TNS V1-V3)



SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------
TEMP 201 9 128 138 3 PROD1 admin INACTIVE




col USERNAME format a20
col TABLESPACE format a20

SELECT SE.USERNAME,
SE.SID,
se.serial#,
SU.EXTENTS,
(SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)))/1024/1024 AS SPACE,
TABLESPACE,
SEGTYPE
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
ORDER BY SE.USERNAME, SE.SID




set linesize 200

col user format a14

select USER,SESSION_NUM,sortu.TABLESPACE,sysdate,sql_text,sorts,sqla.sql_id

from v$sort_usage sortu,v$sqlarea sqla

where upper (tablespace) like 'TEMP%'

and sortu.SQLHASH=sqla.HASH_VALUE ;


USER SESSION_NUM TABLESPACE SYSDATE SQL_TEXT SORTS SQL_ID

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

SYS 3 TEMP 03-OCT-11 SELECT dm_dbalias_B.R_OBJECT_ID FROM prod1.DM_SYSOBJECT_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJ 0 5q7x0kvp3u19r

ECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)


============

from web


Temporary tablespace usage


In database :

CREATE TABLE "AVAIL"."MONITOR_SORTSEG"
( "TABLESPACE_NAME" VARCHAR2(31),
"USED_EXTENTS" NUMBER,
"FREE_EXTENTS" NUMBER,
"ISSUE_TIME" DATE);

In OS : (monitor_temp.sh)

#!/bin/ksh
SCRIPT=1; export SCRIPT
. ~orfnprd/.profile
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<\EOF
set serveroutput on
begin
insert into monitor_sortseg
select tablespace_name,USED_EXTENTS,FREE_EXTENTS,sysdate
from v\$sort_segment where upper(tablespace_name)='TEMP' and free_extents<=500;
if(SQL%ROWCOUNT<>0) then
dbms_output.put_line('Start -------- Start Date:'||sysdate);
dbms_output.put_line('TEMP free extent is lower than 50 .... Finding SQL statement');
insert into monitor_sortuse select USER,SESSION_NUM,sortu.TABLESPACE,
sysdate,sql_text,sorts from v\$sort_usage sortu,v\$sqlarea sqla where upper
(tablespace)='TEMP' and sortu.SQLHASH=sqla.HASH_VALUE ;
commit;
dbms_output.put_line('End ----------End Date:'||sysdate);
end if;
end;
/
exit;
EOF


0,5,10,15,20,25,30,40,45,50,55 * * * * sample_temp.sh > sample_temp.log 2>&1


====





temp tablespace usage


COLUMN username HEADING "UserName" FORMAT a30
COLUMN osuser HEADING "OS User" FORMAT A10
COLUMN qcsid HEADING "QC" FORMAT 9999
COLUMN sid HEADING "SID" FORMAT 9999
COLUMN segtype HEADING "Segment|Type" FORMAT A10
COLUMN sql_id HEADING "SQL ID" FORMAT A15

CLEAR BREAKS
COMPUTE SUM OF sum_blocks ON qcsid
COMPUTE SUM OF sum_blocks ON osuser
COMPUTE SUM OF sum_blocks ON report
COMPUTE SUM OF sum_mb ON qcsid
COMPUTE SUM OF sum_mb ON osuser
COMPUTE SUM OF sum_mb ON report
BREAK ON username ON osuser ON qcsid ON report

SELECT s.username
, s.osuser
, ps.qcsid
, s.sid
, su.segtype
, su.sql_id
, SUM(su.blocks) sum_blocks
, ROUND(SUM(su.blocks) * dt.block_size / (1024 * 1024) ) sum_mb
FROM v$sort_usage su
, v$session s
, v$px_session ps
, dba_tablespaces dt
WHERE s.sid = ps.sid(+)
AND s.saddr = su.session_addr
AND s.serial# = su.session_num
AND su.tablespace = dt.tablespace_name
GROUP BY s.username
, s.osuser
, s.sid
, ps.qcsid
, su.contents
, su.segtype
, su.sqladdr
, su.sqlhash
, su.sql_id
, dt.block_size
ORDER BY s.username
, s.osuser
, ps.qcsid
, su.sqladdr
, s.sid
/



SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes)/1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Active Sql Monitor

SYS AS SYSDBA>l
1 select sesion.sid,
2 sesion.serial#,
3 sesion.username,
4 sesion.sql_id,
5 sesion.sql_child_number,
6 optimizer_mode,
7 hash_value,
8 address,
9 sql_text
10 from v$sqlarea sqlarea, v$session sesion
11 where sesion.sql_hash_value = sqlarea.hash_value
12 and sesion.sql_address = sqlarea.address
13 and sesion.username is not null
14* and sql_text not like 'select sesion.sid,%'

Successful archiving of previously failed ORA-16014 ORA-00312

ORA-16038: log 1 sequence# 106 cannot be archived





Mon Nov 8 09:36:43 2010
ORA-16014: log 1 sequence# 106 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo01.log'
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo02.log'
Mon Nov 8 09:36:43 2010
Errors in file /opt/oracle/admin/cccdb/bdump/cccdb_arc0_668.trc:
ORA-16014: log 1 sequence# 106 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo01.log'
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo02.log'
Mon Nov 8 09:38:07 2010
PMON failed to delete process, see PMON trace file
Mon Nov 8 09:41:47 2010
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Nov 8 09:41:47 2010
ORACLE Instance cccdb - Archival Error
Mon Nov 8 09:41:47 2010
ORA-16038: log 1 sequence# 106 cannot be archived
ORA-00001: unique constraint (.) violated
Mon Nov 8 09:41:47 2010
Errors in file /opt/oracle/admin/cccdb/bdump/cccdb_arc0_668.trc:
ORA-16038: log 1 sequence# 106 cannot be archived
ORA-00001: unique constraint (.) violated
Mon Nov 8 09:41:53 2010
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Mon Nov 8 09:41:54 2010
Thread 1 advanced to log sequence 109 (LGWR switch)
Current log# 1 seq# 109 mem# 0: /opt/oracle/oradata/cccdb/logs/redo01.log
Current log# 1 seq# 109 mem# 1: /opt/oracle/oradata/cccdb/logs/redo02.log
Mon Nov 8 09:41:57 2010
PMON deletion of process succeeded
Mon Nov 8 09:42:42 2010


check the mount point ... is it full ?

if yes then

change the destination .....

alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;

or
delete the archive logfile

or
But not a very good idea ....


SQL> ALTER SYSTEM ARCHIVE LOG STOP;

System altered.



SQL> ALTER SYSTEM ARCHIVE LOG start;

System altered.

cron run hourly ...

cron run every hr

every Hr ....
59 min =hr


59 * * * * echo " ">/var/log/warn > /dev/null 2>&1
59 * * * * echo " ">/var/log/messages > /dev/null 2>&1

expdp with Query

expdp "'/ as sysdba'" directory=datapump_dir DUMPFILE=user_sites.dmp TABLES=cccprod1.user_sites QUERY=ANUJ.user_sites:\"order by USER_OBJECT_ID,SITE_NO\" EXCLUDE=STATISTICS

with parallel option

-bash-3.00$ expdp "'/ as sysdba'" directory=datapump_dir DUMPFILE=user_sites%U.dmp TABLES=cccprod1.user_sites QUERY=cccprod1.user_sites:\"order by USER_OBJECT_ID,SITE_NO\" EXCLUDE=STATISTICS PARALLEL=4 LOGFILE=user_sites_exp.log job_name=anuj1

Oracle OCR and voting disk

OCR : File that manages the cluster and RAC configuration

Voting Disk : Contains and manages information of all the node memberships and avoid
split-brain

Oracle Licensing Option usage

Oracle Licensing Option usage

set pagesize 200
select name,last_usage_date from dba_feature_usage_statistics where detected_usages>0 order by 1;

SET PAGES 200
COL "Component" FORMAT A55 TRUNC
SELECT name "Component", currently_used "Used",first_usage_date "Start", last_usage_date "Last Time"
FROM dba_feature_usage_statistics;

RAC database user lock script

SQL> connect sys@rac1/sys as sysdba
Connected.



SQL> SELECT o.name object_name, u.name owner, lid.*
FROM (SELECT
s.inst_id, s.SID, s.serial#, p.spid,NVL (s.sql_id, 0), s.sql_hash_value,
DECODE (l.TYPE,
'TM', l.id1,
'TX', DECODE (l.request,
0, NVL (lo.object_id, -1),
s.row_wait_obj#
),
-1
) AS object_id,
l.TYPE lock_type,
DECODE (l.lmode,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_held,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_requested,
l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,
s.row_wait_row#, s.row_wait_file#
FROM gv$lock l,
gv$session s,
gv$process p,
(SELECT object_id, session_id, xidsqn
FROM gv$locked_object
WHERE xidsqn > 0) lo
WHERE l.inst_id = s.inst_id
AND s.inst_id = p.inst_id
AND s.SID = l.SID
AND p.addr = s.paddr
AND l.SID = lo.session_id(+)
AND l.id2 = lo.xidsqn(+)) lid,
SYS.obj$ o,
SYS.user$ u
WHERE o.obj#(+) = lid.object_id
AND o.owner# = u.user#(+)
AND object_id <> -1 ;

oracle RAC raw device ownership and permissions

lost permission/ownership on ocr&voting device after reboot


from Metalink


Add the required raw device ownership and permissions, for example:
# vi /etc/udev/rules.d/99-raw.rules
KERNEL=="raw[1-2]*", OWNER="root", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="660"


or

[root@rac1 ~]# cat /etc/udev/rules.d/99-raw.rules
KERNEL=="raw[1-2]*", OWNER="root", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="660"


Restart the UDEV service
Restart the UDEV service as follows, after which devices should now reflect their intended ownership and permissions:

On SLES10:
# /etc/init.d/boot.udev stop
# /etc/init.d/boot.udev start

On RHEL5/OEL5:
# udevcontrol reload_rules
# start_udev

Failure at final check of Oracle CRS stack. 10

Failure at final check of Oracle CRS stack 10



as a root user ( on both the node )


To stop firewall

service iptables stop

service ip6tables stop ( if any )


To permanently disable the firewall, use:

chkconfig iptables off

chkconfig ip6tables off


clear crs and try again .



============

[root@apt-rdbms-01 ~]# iptables -L <------ to check iptable
Chain INPUT (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all -- anywhere anywhere

Chain FORWARD (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all -- anywhere anywhere

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Chain RH-Firewall-1-INPUT (2 references)
target prot opt source destination
ACCEPT all -- anywhere anywhere
ACCEPT icmp -- anywhere anywhere icmp any
ACCEPT esp -- anywhere anywhere
ACCEPT ah -- anywhere anywhere
ACCEPT udp -- anywhere 224.0.0.251 udp dpt:mdns
ACCEPT udp -- anywhere anywhere udp dpt:ipp
ACCEPT tcp -- anywhere anywhere tcp dpt:ipp
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh
REJECT all -- anywhere anywhere reject-with icmp-host-prohibited
[root@apt-rdbms-01 ~]# /etc/init.d/iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
[root@apt-rdbms-01 ~]# chkconfig iptables off

Oracle RAC file information on Sql prompt

set linesize 200

select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
/

NAME
--------------------------------------------------------------------------------
+DATA/rac/controlfile/current.256.733574031
+DATA/rac/datafile/sysaux.261.733574211
+DATA/rac/datafile/system.259.733574119
+DATA/rac/datafile/undotbs1.260.733574185
+DATA/rac/datafile/undotbs2.263.733574285
+DATA/rac/datafile/users.264.733574311
+DATA/rac/onlinelog/group_1.257.733574057
+DATA/rac/onlinelog/group_2.258.733574085
+DATA/rac/onlinelog/group_3.265.733578267
+DATA/rac/onlinelog/group_4.266.733578281
+DATA/rac/tempfile/temp.262.733574229

11 rows selected.



set linesize 200

select group_number, name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb
from v$asm_diskgroup

GROUP_NUMBER NAME ALLOC_UNIT_SIZE STATE TYPE TOTAL_MB USABLE_FILE_MB
------------ -------------------- --------------- ----------- ------ ---------- --------------
1 DATA 1048576 CONNECTED NORMAL 3057 -264



col file_name format a50
select file_name, bytes/1024/1024 from dba_data_files

FILE_NAME BYTES/1024/1024
-------------------------------------------------- ---------------
+DATA/rac/datafile/system.259.733574119 410
+DATA/rac/datafile/undotbs1.260.733574185 110
+DATA/rac/datafile/sysaux.261.733574211 230
+DATA/rac/datafile/undotbs2.263.733574285 100
+DATA/rac/datafile/users.264.733574311 5



col HOST_NAME format a20
select instance_name, host_name, archiver, thread#, status from gv$instance

INSTANCE_NAME HOST_NAME ARCHIVE THREAD# STATUS
---------------- -------------------- ------- ---------- ------------
rac1 rac1 STOPPED 1 OPEN
rac2 rac2 STOPPED 2 OPEN

col MEMBER format a50
select group#, type, member, is_recovery_dest_file from v$logfile
order by group#

GROUP# TYPE MEMBER IS_
---------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/rac/onlinelog/group_1.257.733574057 NO
2 ONLINE +DATA/rac/onlinelog/group_2.258.733574085 NO
3 ONLINE +DATA/rac/onlinelog/group_3.265.733578267 NO
4 ONLINE +DATA/rac/onlinelog/group_4.266.733578281 NO



select group_number, file_number, compound_index, incarnation, block_size, bytes/1024/1024/1024 GB, type, striped,
creation_date, modification_date
from v$asm_file
where TYPE != 'ARCHIVELOG'
/

no rows selected


SQL>
select group_number, file_number, bytes/1024/1024/1024 GB, type, striped, modification_date
from v$asm_file
where TYPE != 'ARCHIVELOG' ;


no rows selected

Oracle RAC listener and tnsnames file node wise

host file


[oracle@rac2 admin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.



127.0.0.1 localhost.localdomain localhost

# Public
192.168.2.50 rac1.localdomain rac1
192.168.2.51 rac2.localdomain rac2 rac2

#Private

192.168.7.50 rac1-priv.localdomain rac1-priv
192.168.7.51 rac2-priv.localdomain rac2-priv

#Virtual

192.168.2.53 rac1-vip.localdomain rac1-vip
192.168.2.54 rac2-vip.localdomain rac2-vip





on node one

# Generated by Oracle configuration tools.

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.50)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)


====

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


[oracle@rac1 bin]$ ps -ef|grep LIST
oracle 26404 781 0 09:08 pts/3 00:00:00 grep LIST
oracle 30665 1 0 Oct28 ? 00:00:11 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit


====
on node 2


[oracle@rac2 admin]$ cat listener.ora
# listener.ora.rac2 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.51)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_RAC2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

===

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora.rac2 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora.rac2
# Generated by Oracle configuration tools.

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



[oracle@rac2 admin]$ ps -ef|grep LIS
oracle 16119 5159 0 09:06 pts/3 00:00:00 grep LIS
oracle 20876 1 0 Oct28 ? 00:00:08 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC2 -inherit


to start Listener on RAC

[oracle@rac1 bin]$ ps -ef|grep LIST

oracle 26404 781 0 09:08 pts/3 00:00:00 grep LIST
oracle 30665 1 0 Oct28 ? 00:00:11 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit

[oracle@rac1 bin]$ srvctl stop listener -n rac1 -l LISTENER_RAC1

[oracle@rac1 bin]$ ps -ef|grep LIST
oracle 959 781 0 09:13 pts/3 00:00:00 grep LIST
[oracle@rac1 bin]$ srvctl start listener -n rac1 -l LISTENER_RAC1



lsnrctl status LISTENER_RAC1

Oracle ASM views info and command

v$asm_alias :Lists all aliases in all currently mounted diskgroups
v$asm_client :Lists all the databases currently accessing the diskgroups
v$asm_disk :Lists all the disks discovered by the ASM instance
v$asm_diskgroup :Lists all the diskgroups discovered by the ASM instance
v$asm_file :Lists all files that belong to diskgroups mounted by the ASM instance
v$asm_operation :Reports information about current active operations. Rebalance activity is reported in this view
v$asm_template :Lists all the templates currently mounted by the ASM instance
v$asm_diskgroup_stat :same as v$asm_diskgroup but does discover new diskgroups. Use this view instead of v$asm_diskgroup
v$asm_disk_stat :same as v$asm_disk but does not discover new disks. Use this view instead of v$asm_disk



break on inst_id skip 1

column inst_id format 9999999 heading "Instance ID" justify left
column name format a15 heading "Disk Group" justify left
column total_mb format 999,999,999 heading "Total (MB)" justify right
column free_mb format 999,999,999 heading "Free (MB)" justify right
column pct_free format 999.99 heading "% Free" justify right

select inst_id, name, total_mb, free_mb, round((free_mb/total_mb)*100,2) pct_free
from gv$asm_diskgroup
where total_mb != 0
order by inst_id, name;





/etc/init.d/oracleasm start
/etc/init.d/oracleasm stop
/etc/init.d/oracleasm restart
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm status
/etc/init.d/oracleasm enable
/etc/init.d/oracleasm disable
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm deletedisk
/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm querydisk /dev/sdd1
/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
/etc/init.d/oracleasm renamedisk /dev/sdb1 VOL1



ASM Specific Init.ora Parameters

.cluster_database= true
.asm_diskstring = ‘/dev/sd*1’
.instance_type=asm
.shared_pool_size=100M
.large_pool_size = 80M
.db_cache_size=60M
.asm_diskgroups = 'DATA','FRA'
.processes=128



$ ls -l /dev/asm
total 0
brwxrwx--- 1 root asmadmin 252, 153601 Nov 28 13:49 docsvol1-300
brwxrwx--- 1 root asmadmin 252, 153602 Nov 28 13:49 docsvol2-300
brwxrwx--- 1 root asmadmin 252, 153603 Nov 28 13:56 docsvol3-300

$ /sbin/mkfs -t acfs -b 4k /dev/asm/docsvol3-300 -n "DOCSVOL3"


/etc/init.d/oracleasm querydisk /dev/multipath/sdb2

srvctl status asm -n linux1


oracle@raclinux1 ~# srvctl status asm -a

Oracle Copy or move old archive log file

ctime last time the inode changed - ie., chmod, chown, etc.
atime last time the file was read (Example: cat, grep, sort, vim, file_get_contents..)
mtime last time the file was written, or open append and closed without writing.



-mtime -2 means files that are less than 2 days old, such as a file that is 0 or 1 days old.

-mtime +2 means files that are more than 2 days old... {3, 4, 5, ...}


find /opt/oracle/admin/anuj/arch -type f -mtime +2|xargs cp -p {} /mnt/backup/Archive_Before_16102010/ {} \;

find /opt/oracle/admin/anuj/arch/*.arc -mtime +2 -exec cp -p {} /mnt/backup/Archive_Before_16102010/ {} \;


find /opt/oracle/admin/anuj/arch -type f -mtime +2|xargs mv {} /mnt/backup/Archive_Before_16102010/ {} \;


shell script


days=10
find '/opt/oracle/admin/anuj/arch/' -type f -mtime +$days | \
# find '/tmp/' -type f -mtime +$days | \
while read file
do
cp $file /mnt/backup/Archive_Before_16102010/
# cp $file /tmp/XXX
done

dbms_metadata package to extract the schema ddl

Oracle schema / user ddl  via dbms_metadata

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);

SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';

dbms_metadata package to extract the schema ddl

Oracle dbms_metadata package to extract the schema ddl ....

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);


SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';



set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual ;


set autoprint on long 100000
variable Y clob
variable x clob

declare
            no_grant exception;
            pragma exception_init( no_grant, -31608 );
begin
   
  
begin 
            :Y := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '&&USER' );
         
exception
           when no_grant then :Y := '-- no system grants';
end ;
 end;
/




SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT','RESOURCE') from dual;

What is a Clustering Factor and how to improve?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,b.num_rows,b.blocks,a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
and a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;


SELECT constraint_name,table_name,r_constraint_name,status FROM dba_constraints 
WHERE constraint_type='P' and table_name='USER_SITES' 
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED



SQL> create table anuj.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "ANUJ"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ"; 

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
-- and index_name in ('USER_SITES_USPK1')
and a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') 
/


INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792 <<<<<--- 
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<<--new Clustering Factor 


now drop the old table. rename new table to old table name


to modify clustering factor >12c

TABLE_CACHED_BLOCKS statistics preference allows us to modify how the CF is 
calculated by not incrementing the CF value if an index rowid points to a block that was visited 
just TABLE_CACHED_BLOCKS




set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual 





PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             1




to set value !!!

set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
exec dbms_stats.set_table_prefs(ownname=>'&&owner', tabname=>'&&TABLE', pname=>'TABLE_CACHED_BLOCKS', pvalue=>10);
 



define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual

SQL> /
old   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual
new   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'EMP',ownname => 'ANUJ') prefs_for__EMP FROM dual

PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             10


EXEC dbms_stats.gather_table_stats(ownname=>&&owner, tabname=>'&&TABLE', estimate_percent=> null, cascade=> true,   method_opt=>'FOR ALL COLUMNS SIZE 1');


gather stats ... 

define tabname='EMP'
define owner='ANUJ'

set pages 100 lines 250
--set echo off feedback off heading on 
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather  from   dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;



exec dbms_stats.gather_table_stats (ownname => 'ANUJ', tabname => 'EMP' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);