Oracle detectar BLOQUEOS de Objetos

--funciona en oracle 10g y 11g
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
S_LOCKER.SID LOCK_SID,
S_LOCKER.SERIAL# LOCK_SERIAL,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): '||U.NAME||'.'||O.NAME||
' - Mode held: '||
decode(L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_LOCKER.LMODE))||
' / Mode requested: '||
decode(L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
sys.OBJ$ O,
sys.USER$ U
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TM')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L_WAITER.ID1 = O.OBJ#
and U.USER# = O.OWNER#
union
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.SID LOCK_SID,
S_LOCKER.SERIAL# LOCK_SERIAL,
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK L1_WAITER,
V$OPEN_CURSOR O
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TX')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L1_WAITER.LADDR = L_WAITER.ADDR
and L1_WAITER.KADDR = L_WAITER.KADDR
and L1_WAITER.SADDR = O.SADDR
and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE

;

detectar BLOQUEOS de FILAS en oracle

--funciona en oracle 10g y 11g
select oracle_username || ' (' || s.osuser || ')' username
, ' alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate; ' sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id;

ver ID del proceso del sistema operativo

--p.spid es el ID del sistema operativo Linux, Unix
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
and s.sid=41 and s.serial#=339;
--muestra el s.sid, s.serial# para saver el el usario dueño del proceso
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program
FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
and spid=16615; --id en sisitema operativo

ver consulta que se esta ejecutando

--SPID es el numero de proceso que se muestra a travez de comando TOP en un sistema operativo Linux, Unix

Select sql_text from V$SQLAREA Q, V$PROCESS P, V$SESSION S WHERE Q.ADDRESS=S.SQL_ADDRESS AND P.ADDR=S.PADDR

AND P.SPID=31598; --SPID=numero de proceso del sistema operativo

usuarios que se crean al instalar una base de datos oracle

'SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL',
'ORDPLUGINS','ORDDATA','MDSYS','ORDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','FLOWS_FILES','APEX_PUBLIC_USER','APEX_030200','OWBSYS','OWBSYS_AUDIT','SCOTT'
,'XDB','TRACESVR ','TSMSYS'

--para sacar los usuarios que no son de oracle
select username, account_status, lock_date, expiry_date, created from dba_users
where username not in ('SYSTEM','SYS','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS','CTXSYS','XDB','ANONYMOUS','XS$NULL',
'ORDPLUGINS','ORDDATA','MDSYS','ORDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','FLOWS_FILES','APEX_PUBLIC_USER','APEX_030200','OWBSYS','OWBSYS_AUDIT','SCOTT'
,'XDB','TRACESVR ','TSMSYS');

Nota original
http://www.orafaq.com/wiki/List_of_default_database_users

Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them:

User	Password	Purpose	Created by	Can change password?	Can be dropped
SYS	CHANGE_ON_INSTALL or INTERNAL	Oracle Data Dictionary/ Catalog	?/rdbms/admin/sql.bsq and various cat*.sql scripts	Yes	No
SYSTEM	MANAGER	The default DBA user name (please do not use SYS)	?/rdbms/admin/sql.bsq	Yes	No
OUTLN	OUTLN	Stored outlines for optimizer plan stability	?/rdbms/admin/sql.bsq	Yes	No
SCOTT ADAMS JONES CLARK BLAKE	TIGER WOOD STEEL CLOTH PAPER	Training/ demonstration users containing the popular EMP and DEPT tables	?/rdbms/admin/utlsampl.sql	Yes	Yes - Drop users cascade from all production environments
HR (Human Resources) OE (Order Entry) SH (Sales History)	HR OE SH	Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables	?/demo/schema/mksample.sql	Yes	Yes - Drop users cascade from all production environments
DEMO	DEMO	User for Oracle Data Browser Demonstration (last version 9.2)	?/rdbms/admin/demo.sql	Yes	Yes - drop user cascade
ANONYMOUS	invalid password	Used by the PL/SQL gateway that enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener.	?/rdbms/admin/catqm.sql		Yes - drop user if XDB is not used through a Web browser
AURORA$ORB$UNAUTHENTICATED	INVALID	Used for users who do not authenticate in Aurora/ORB	?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
AWR_STAGE	AWR_STAGE	Used to load data into the AWR from a dump file	?/rdbms/admin/awrload.sql	Yes	Yes - must be dropped in order to use awrload.sql script
CSMIG		User for Database Character Set Migration Utility	?/rdbms/admin/csminst.sql	Password is given at script call	Yes - drop user cascade (user is dropped and recreated by the script each time it is used)
CTXSYS	CTXSYS	Oracle interMedia (ConText Cartridge) administrator user	?/ctx/admin/dr0csys.sql
DBSNMP	DBSNMP	Oracle Intelligent agent	?/rdbms/admin/catsnmp.sql, called from catalog.sql	Yes - put the new password in snmp_rw.ora file	Yes - Only if you do not use the Intelligent Agents
DIP	DIP	Generic user account DIP for processing events propagated by DIP. This account would be used by all applications using the DIP provisioning service when connecting to the database	?/rdbms/admin/catdip.sql, called from catproc.sql	Yes - using "oidprovtool" tool	Yes - using "dbca" if DIP (Directory Integration and Provisioning) is no more used
DMSYS	DMSYS	Data Mining user	?/rdbms/admin/odmcrt.sql, called from dminst.sql		Yes - drop user cascade
DSSYS	DSSYS	Oracle Dynamic Services and Syndication Server	?/ds/sql/dssys_init.sql
EXFSYS		User to hold the dictionary, APIs for the Expression Filter	?/rdbms/admin/exfsys.sql, called from catexf.sql from catrul.sql from catproc.sql	Yes - password given at script execution	Yes - using using catnoexf.sql script
LBACSYS	LBACSYS	Label Based Access Control owner when Oracle Label Security (OLS) option is used	?/rdbms/admin/catlbacs.sql, called from catols.sql		Yes - using catnools.sql of OLS is no more used
MDSYS	MDSYS	Oracle Spatial administrator user	?/ord/admin/ordinst.sql
ORACLE_OCM	ORACLE_OCM	Owner of packages used by Oracle Configuration Manager	?/rdbms/admin/catocm.sql, called from dbmsocm.sql, called from catproc.sql	Yes - account is created locked and password expired
ORDPLUGINS	ORDPLUGINS	Object Relational Data (ORD) User used by Time Series, etc.	?/ord/admin/ordinst.sql
ORDSYS	ORDSYS	Object Relational Data (ORD) User used by Time Series, etc.	?/ord/admin/ordinst.sql
PERFSTAT	PERFSTAT	Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT	?/rdbms/admin/statscre.sql
TRACESVR	TRACE	Oracle Trace server	?/rdbms/admin/otrcsvr.sql
TSMSYS	TSMSYS	User for Transparent Session Migration (TSM) a Grid feature	?/rdbms/admin/cattsm.sql, called from catproc.sql		Yes - drop user cascade
XDB		Owner of objects for XDB system	?/rdbms/admin/catqm.sql		Yes - using catnoqm.sql script if XDB is no more used

Remember to change the passwords for the SYS and SYSTEM users immediately after installation!

Except for the user SYS and users having SYSAUX as default tablespace, there should be no problem altering these users to use a different default and temporary tablespace. For user with SYSAUX default tablespace, query v$sysaux_occupants to know the procedure to change the tablespace and move the objects in another tablespace.