set pages 999 select sess.username , sess.sid , sess.serial# , stat.value cursors from v$sesstat stat , v$statname sn , v$session sess where sess.username is not null and sess.sid = stat.sid and stat.statistic# = sn.statistic# and sn.name = 'opened cursors current' order by value /
Or alternatively…
set lines 100 pages 999
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
/
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username');
username should be case sensitive and capitalized
col "SID/SERIAL" format a10 col username format a15 col osuser format a15 col program format a40 select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program from v$session s , v$process p Where s.paddr = p.addr order by to_number(p.spid);
select s.username , s.sid , s.serial# , p.spid , last_call_et , status from V$SESSION s , V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid';
RMAN> run {
set command id to ‘rman’;
allocate channel c1 type disk format ‘D:\oracle\backup\inconsistent\%d_%s_%p.dbf’;
backup
incremental level 0
tag “Full hot backup”
(database);
backup current controlfile tag=’current’;
release channel c1;
allocate channel c1 type disk format ‘D:\oracle\backup\inconsistent\arch_%d_%u_%s_%p’;
change archivelog all validate;
backup archivelog all;
sql “alter database backup controlfile to ”D:\ORACLE\BACKUP\INCONSISTENT720_bkp.ctl””;
release channel c1;
}
…
SQL*PLUS session
—————-
SQL> SELECT sid, spid, client_info
2 FROM v$process p, v$session s
3 WHERE p.addr = s.paddr
4 AND client_info LIKE ‘%id=rman%’;
SID SPID CLIENT_INFO
———- ———— ——————–
143 3388 id=rman
SQL> SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) “% Complete”
FROM v$session_longops
WHERE opname LIKE ‘RMAN%’
AND opname NOT LIKE ‘%aggregate%’
AND totalwork != 0
AND sofar <> totalwork
/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
———- ———- ———- ———- ———- ———-
139 67 1 56316 95360 59.06
SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
———- ———- ———- ———- ———- ———-
139 67 1 81534 95360 85.5
SQL> /
no rows selected
SQL> SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait
WHERE wait_time = 0
ORDER BY sid;
Note: The V$SESSION_WAIT view shows only Oracle events, not media manager events.
RMAN> run {
allocate channel t1 type disk;
debug io;
backup database;
debug off;
}
You can change the option to “debug on” which is equivlent to “debug all” to
get even more detail about the sql and pl/sql being executed by RMAN.
SELECT a.tablespace_name, a.file_name, a.bytes/1024/1024 ALLOCATED_MB,
b.free_bytes/1024/1024 FREE_MB
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;
How can I check if I have the right indexes for the foreign key constraints on a child table ?
Since any index always add some space and resource overhead, it is probably unwise to blindly index every foreign key. But, if there are legitimate locking or query performance benefits to be made, the following script authored by Tom Kyte gives a list of foreign keys, their columns and a flag indicating whether the appropriate index exists.
column columns format a20 word_wrapped
column table_name format a30 word_wrapped
select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
/
http://www.jlcomp.demon.co.uk/faq/fk_ind.html
How can I identify which index represents which primary or unique key constraint ?
The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:
select --+ rule
o.owner as index_owner
, o.object_name as index_name
, n.name as constraint_name
from sys.cdef$ c
, dba_objects o
, sys.con$ n
where c.enabled = o.object_id
and c.con# = n.con#
and n.owner# = uid
/
SQL> select uid from dba_users where username like '%RXADMIN%';
If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.
Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.
Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.
http://www.jlcomp.demon.co.uk/faq/constraints_and_indexes.html
