Oracle Database Administration

http://www.oracle-wiki.net/startsql#toc7 SQL Library Table LAST_ANALYZED set ver off set linesize 60 col table_name format a15 col last_analyzed format a40 select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where lower(TABLE_NAME)='&tname'; Displays Last Analyzed Details for a given Schema. (All schema owners if 'ALL' specified). -- SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 SELECT t.owner,        t.table_name AS "Table Name",         t.num_rows AS "Rows",         t.avg_row_len AS "Avg Row Len",         Trunc((t.blocks * p.value)/1024) AS "Size KB",         to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed" FROM   dba_tables t,        v$parameter p WHERE t.owner = Decode(Upper('&&Table_Owne...

Detail report user session COL orauser HEA " Oracle User " FOR a17 TRUNC COL osuser HEA " O/S User " FOR a10 TRUNC COL ssid HEA " Sid " FOR a4 COL sserial HEA " Serial# " FOR a7 COL ospid HEA " O/S Pid " FOR a7 COL slogon HEA " Logon Time " FOR a14 COL sstat HEA " Status " FOR a6 COL auth HEA " Auth " FOR a4 COL conn HEA " Con " FOR a3 SELECT ' ' || NVL ( s . username , ' ???? ' ) orauser , ' ' || s . osuser osuser , LPAD ( s . sid , 4 ) ssid , LPAD ( s . serial #, 6 ) sserial, LPAD ( p . spid , 6 ) ospid , INITCAP ( LOWER ( TO_CHAR ( logon_time , ' MONDD HH24:MI:SS ' ) ) ) slogon , DECODE ( s . status , ' ACTIVE ' , ' Busy ' , ' INACTIVE ' , ' Idle ' , ' KILLED ' , ' Kill ' , ' ?? ...

Track Logon time of DB user and OS user: Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND'; ‎ Track all Session User Details: select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; Track Active Session User Details: SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= 'ACTIVE' AND UserName IS NOT NULL; Track Active User Details: 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'; Report OS Process ID for each session: SELECT ses.username || '(' || ses.sid || ')' users, acc.owner owner, acc.OBJECT OBJECT, ses.lockwait, prc.spid os_process FROM v$process prc, v$access acc, v$session ses WHERE prc.addr = ses.paddr AND ses.sid = acc.sid; Sho...

Script – Find SQL being executed by a OS Process ID (PID) prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID; script used to find out cpu used by this session SELECT substr(a.sid,1,5) "Sid", substr(a.process,1,7) "Process", nvl(a.USERNAME,'ORACLE PROC') "user", v.value "CPU used by this session" FROM v$statname s, v$sesstat v, v$session a WHERE s.name = 'CPU used by this session' and v.statistic#=s.statistic# AND v.sid = a.sid and v.value > 0 ORDER BY v.value DESC; Top 10 cpu heavy SQLs set pagesize 999 set linesize 150 col cpu_time format 9999999999 heading "Cputime" col sql_text f...