Machine Connections

1) MACHINE CONNECTIONS

SELECT USERNAME,COUNT(USERNAME)COUNT,SCHEMANAME,STATUS,OSUSER, MACHINE,PROGRAM ,to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') TIME
------------------LOGON_TIME
FROM V$SESSION
WHERE MODULE = 'JDBC Thin Client' and osuser='root'
----AND SCHEMANAME='GOLD_STAGING'  osuser='root' AND TO_DATE(LOGON_TIME)=TO_DATE(SYSDATE)
GROUP BY USERNAME,STATUS,SCHEMANAME,OSUSER, MACHINE,PROGRAM order by 1;


select parsing_schema_name,user_io_wait_time,EXECUTIONS,first_load_time,last_load_time,module,sql_fulltext
from v$sql where  MODULE = 'JDBC Thin Client' --and USER_IO_WAIT_TIME > 100
and TO_DATE(SUBSTR(first_load_time,1,10),'YYYY-MM-DD') in ('08-04-2009')
----            BETWEEN '07-31-2009' AND to_date(sysdate,'mm-dd-yyyy')
where first_load_time like '2009-07-15%' and MODULE = 'JDBC Thin Client' and USER_IO_WAIT_TIME > 100
ORDER BY FIRST_LOAD_TIME

2) CURRENT RUNNIG QUERY

SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;

select SUBSTR(nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')',1,12) username,SUBSTR(OSUSER,1,20) OSUSER,SUBSTR(MACHINE,1,20) MACHINE,MODULE,SQL_TEXT SQL_TEXT
from     v$open_cursor oc, v$session s
where     s.SQL_ADDRESS = oc.ADDRESS
and     s.SQL_HASH_VALUE = oc.HASH_VALUE AND MODULE = 'JDBC Thin Client'
order by SUBSTR(OSUSER,1,25);

SELECT USERNAME,COUNT(USERNAME)
FROM V$SESSION WHERE USERNAME IN ('GOLD_STAGING','CLEANSER','CONTRACTREPO')
GROUP BY USERNAME,SCHEMANAME

3) LIST ALL OBJECTS CORREPTED

select OWNER,SEGMENT_NAME,sum(BYTES)
from dba_segments group by OWNER,SEGMENT_NAME order by SEGMENT_NAME

select a.FILE#,a.block#,segment_name,owner,tablespace_name
from V$DATABASE_BLOCK_CORRUPTION a, dba_extents b where a.file#=b.file_id and a.block#=b.block_id;

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

select * from dba_tables where table_name like '%AUD%'

TRUNCATE TABLE FGA_LOG$;

TRUNCATE TABLE AUD$;

4) Execute the following query to see if your database was started with a PFILE or SPFILE

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

5) PARENT CHILD RELATION

SELECT PARENT.TABLE_NAME PARENT, CHILD.TABLE_NAME CHILD
FROM USER_CONSTRAINTS PARENT, USER_CONSTRAINTS CHILD
WHERE CHILD.CONSTRAINT_TYPE='R'
AND PARENT.CONSTRAINT_NAME=CHILD.R_CONSTRAINT_NAME
AND PARENT.OWNER = CHILD.OWNER
ORDER BY PARENT.TABLE_NAME, CHILD.TABLE_NAME;

6) PARENT CHILD RELATION FOR ALL SCHEMAS

SELECT DISTINCT P.CONSTRAINT_TYPE,P.CONSTRAINT_NAME PARENT_CONS,P.TABLE_NAME PARENT,C.CONSTRAINT_TYPE,C.CONSTRAINT_NAME CHILD_CONS,C.TABLE_NAME CHILD
FROM DBA_CONSTRAINTS P, DBA_CONSTRAINTS C
WHERE P.CONSTRAINT_NAME=C.R_CONSTRAINT_NAME AND C.CONSTRAINT_TYPE='R' AND C.TABLE_NAME ='OMS_CLIENTS'
ORDER BY 3

----select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

7) OPENED CONNECTIONS IN ORACLE

select type,substr(a.spid,1,9) pid,substr(b.sid,1,5) sid,substr(b.serial#,1,5) ser#,substr(b.machine,1,50) box,substr(b.username,1,50) username,--b.server,
       substr(b.osuser,1,8) os_user,substr(b.program,1,30) program
from v$session b, v$process a
where b.paddr = a.addr and type='USER'
order by spid;

8) SHOW ORACLE PROCESS IN DETAIL

select     substr(s.username,1,18) username,s.OSUSER,substr(s.program,1,15) program,
        decode(s.command,0,'No Command',1,'Create Table',2,'Insert',3,'Select',6,'Update',7,'Delete',9,'Create Index',15,'Alter Table',21,'Create View',
        23,'Validate Index',35,'Alter Database',39,'Create Tablespace',41,'Drop Tablespace',40,'Alter Tablespace',53,'Drop User',62,'Analyze Table',
       63,'Analyze Index',s.command||': Other') command
from v$session     s,v$process     p,v$transaction t,v$rollstat    r,v$rollname    n
where s.paddr = p.addr and   s.taddr = t.addr (+) and   t.xidusn = r.usn (+) and   r.usn = n.usn (+)
order by 1;

9) DB STARTUP TIME

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;

SElect * from v$session where STATUS='ACTIVE'