/* 
Oracle Admin Statements; continually updated.  Last updated online 4/1/08 
Last updated in this file post DoState work 10/3/14 while at CB doing in-memory and 12c work.
*/


/*************************************************************/ 
/* Instance up/down */

select * from gv$instance;

SELECT count(*) FROM v$instance where status <> 'OPEN';

select instance_name,status from gv$instance;




/*************************************************************/ 
/* Example dml statements */ 

CREATE USER scott2 IDENTIFIED BY scott2 
    DEFAULT TABLESPACE system 
    QUOTA 10M ON scott 
    quota 0m on system 
    TEMPORARY TABLESPACE localtemp; 
GRANT connect, 

grant CREATE SESSION, 
connect, 
CREATE TABLE, 
CREATE VIEW, 
CREATE TRIGGER, 
CREATE PROCEDURE, 
CREATE SEQUENCE  to civfors; 

grant mmuser to tboss; /* mmuser being a role */ 

create tablespace boss 
datafile 'c:\oracle\oradata\boss8i\boss.dbf' 
size 20m 
default storage (initial 10k next 50k minextents 1 
maxextents 10) 
online; 

create tablespace ehri20curr 
datafile '/data1/oradata/EHRIUS/ehri20curr_01.dbf' 
size 500m online; 

create tablespace "BO2" 
datafile '/data1/oradata/EHRIUS/bo2_01.dbf' 
size 20m 
extent management local 
segment space management auto 
online; 

create index crnt_refrnc_data_ndx1 on 
crnt_rfrnc_data(dmn) 

alter tablespace BO2 add datafile 
'/data1/oradata/EHRIUS/bo2_02.dbf' size 30m; 

create public database link EHRIR1.EHRI.COM 
connect to ETL_MASTER 
identified by <pwd> 
using 'ehrir1' 

ALTER SYSTEM SET db_cache_size='1500M' SCOPE=MEMORY; 
ALTER SYSTEM SET db_keep_cache_size='150M' 
SCOPE=MEMORY; 




/*************************************************************/ 
/*  Analyze Plans */ 

DELETE FROM plan_table WHERE statement_id = 'tboss'; 

EXPLAIN PLAN SET STATEMENT_ID = 'tboss' FOR 

-----< Insert your SQL statement inbetween here <----- 

SELECT p.p_id, p.first_nm, p.middle_nm, p.last_nm, 
m.member_id 
FROM Person p, AbstractAuthor aa, Membership m 
WHERE aa.p_id = p.p_id 
AND m.p_id (+) = p.p_id 
AND aa.abstract_id = '477' 
AND aa.author_type_cde = 30 

-----< Insert your SQL statement inbetween here <----- 



-- column operation format a16 
-- column options format a15 
-- column object_name  format a20 
-- column id  format 99 

select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '|| 
       -- decode(partition_id, NULL, '', 
       -- 'PART:'||lower(partition_start||'-'||partition_stop))|| 
       decode(optimizer, NULL, '', ' ['||optimizer||']')|| 
       decode(id,0,' Cost=' ||nvl(position,   0)|| 
                   ' Rows=' ||nvl(cardinality,0)|| 
                   ' Bytes='||nvl(bytes,      0)) 
"Query Plan" 
from   plan_table 
where                                statement_id = 'tboss' 
start   with     id = 0          and statement_id = 'tboss' 
connect by prior id = parent_id  and statement_id = 'tboss' 
/ 


select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '|| 
       -- decode(partition_id, NULL, '', 
       -- 'PART:'||lower(partition_start||'-'||partition_stop))|| 
       decode(optimizer, NULL, '', ' ['||optimizer||']')|| 
       decode(id,0,' Cost=' ||nvl(position,   0)|| 
                   ' Rows=' ||nvl(cardinality,0)|| 
                   ' Bytes='||nvl(bytes,      0)) 
"Query Plan" 
from   plan_table 
start   with     id = 0 
connect by prior id = parent_id
/ 

select * from table (dbms_xplan.display);

set linesize 200
set pagesize 9999
select * from table (dbms_xplan.display('plan_table',null,'all'));


if you see "Note: PLAN_TABLE' is old version" then you need to run

/*************************************************************/ 
/* 10g version */

set linesize 132
col sql_text format a70
alter session set nls_date_format = 'YYMMDD HH24:MI:SS';

-- index monitoring.
select * from v$object_usage;


select 
--'select * from table(dbms_xplan.display_cursor(''' || s.sql_id || '''));' 
s.sql_id, s.plan_hash_value, u.username, s.executions,s.sql_text, s.last_active_time, 
s.elapsed_time, 
round(s.elapsed_time/(1000000*60),2) elapsed_mins, s.cpu_time, round(s.cpu_time/(1000000*60),2) cpu_mins
from v$sqlstats s, v$sqlarea v,dba_users u 
WHERE u.user_id = v.parsing_user_id 
AND u.username NOT IN ('SYSTEM','SYS','MDSYS','SYSMAN','EXFSYS','DBSNMP','ORAMON','SYSMON','CTXSYS','QUEST_SPOT') 
and u.username not in ('DBOPER','DM_BDGT','DM_BDGT2','BKREPOS')
--and u.username in ('ITSERVUS','DCPS_DASHBOARD','SERVUS_PORTAL_PRO')
--and u.username not in ('INFA_CITYDW','TBOSS')
--and u.username='DPM'
and s.sql_id = v.sql_id
--and s.elapsed_time > 1000000 -- this only gets jobs that lasted more than one second
-- and rownum < 30
and upper(s.sql_text) like '%FACT_BDGT%'
and upper(s.sql_text) not like 'INSERT INTO%'
and upper(s.sql_text) not like 'LOCK TABLE%'
--and upper(s.sql_text) not like '%AL_%'
--or upper(s.sql_text) like '%REMEDY%')
--and s.plan_hash_value=1366153129
-- 1 is a day, .04166 is last hour, 0.01041666 is last 15 mins
--and s.last_active_time > sysdate - 0.01041666 
--order by s.last_active_time desc;
--order by executions desc;
order by u.username asc,executions desc;



select * from v$sql;

select min(last_active_time), max(last_active_time) from v$sql 


select v.parsing_schema_name, v.executions, v.* from v$sql v
where parsing_schema_name='ITSERVUS'
--upper(t1.SQL_TEXT) like '%ITSERVUS%' 
order by v.parsing_schema_name asc,v.executions desc;




select * from v$sqlstats where plan_hash_value in ('1650321850','94380611','8vk3zb7mgm6mp');
select * from v$sqlarea where sql_id='8vk3zb7mgm6mp';

select min(last_active_time), max(last_active_time) from v$sqlstats;


-- this shows the actual plan used by the query above; pass in SQL_ID to this...
 
set linesize 300
set pagesize 9999
select * from table(dbms_xplan.display_cursor('9babjv8yq8ru3'));


/* Thanzeer's method; using gather_plan_statistics: the display_cursor line will show estimated and actual
rows, as well as the time at each step.  No cost though */

set serveroutput off
   SELECT /*+ gather_plan_statistics */  * FROM ( SELECT DISTINCT(PAY_END_DATE) FROM 
   EDW_PS.EMPLOYEE_PAY_FACT FACT, EDW_PS.PAY_PERIODS_DIM PAYPERIOD, EDW_PS.EMPLOYEES_DIM EMPLOYEE WHERE
   FACT.PAY_PERIOD_SID = PAYPERIOD.PAY_PERIOD_SID AND FACT.EMPLOYEE_SID = EMPLOYEE.EMPLOYEE_SID AND 
   FACT.EMPLOYEE_SID = 66521 ORDER BY 1 DESC) WHERE ROWNUM < 3;  

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));   











/*************************************************************/ 
/* Space Usage for Database in Meg .  Tablespace space/tablespace usage */


-- don't use this; too slow.  see below.
SELECT Total.name "Tablespace Name", 
       Free_space, (total_space-Free_space) 
Used_space, total_space 
FROM 
  (select tablespace_name, sum(bytes/1024/1024) 
Free_Space 
     from sys.dba_free_space 
    group by tablespace_name 
  ) Free, 
  (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE 
     from sys.v_$datafile a, sys.v_$tablespace B 
    where a.ts# = b.ts# 
    group by b.name 
  ) Total 
WHERE Free.Tablespace_name = Total.name 


/* this doesn't get tempts ... */ 
column tablespace_name format a30 
select a.tablespace_name,megs_allocated,megs_used, 
round(megs_used/b.megs_allocated, 2)*100 pct_used 
from 
  (select tablespace_name,sum(bytes)/(1024*1024) megs_used 
   from dba_extents group by tablespace_name) a, 
  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated 
   from dba_data_files group by tablespace_name) b 
where a.tablespace_name=b.tablespace_name 
and a.tablespace_name='HISTORY_STAGING'; 


/* this one does get tempts and is a hell of a lot faster */
set linesize 120
set pagesize 9999
col "TS Name" format a13
SELECT tablespace_name "TS Name",
ROUND(DECODE(MAXSIZE,0,CurrentSize,MAXSIZE)/1024/1024,1) "Max (Extendable)",
ROUND(CurrentSize/1024/1024,0) "Current Size",
ROUND(DECODE(free,NULL,0,free)/1024/1024,2) "Free Space",
ROUND((CurrentSize-DECODE(free,NULL,0,free))/1024/1024,2)"Used Space",
ROUND(100*(CurrentSize-DECODE(free,NULL,0,free))/
1024/1024/(DECODE(MAXSIZE,0, CurrentSize,MAXSIZE)/1024/1024),1) "Pct max Used",
round(ROUND(100*(CurrentSize-DECODE(free,NULL,0,free))/1024/1024,2)/
ROUND(CurrentSize/1024/1024,0),1) "pct curr used"
FROM ( SELECT tablespace_name, SUM(maxbytes) MAXSIZE,
SUM(bytes) CurrentSize , (SELECT SUM(bytes) FROM dba_free_space b
WHERE b.TABLESPACE_NAME=a.TABLESPACE_NAME) free FROM dba_data_files a
GROUP BY tablespace_name
UNION ALL
(SELECT d.tablespace_name, maxbytes,
(f.bytes_free + f.bytes_used)TotalK ,(f.Bytes_used)
FROM   SYS.V_$TEMP_SPACE_HEADER f , DBA_TEMP_FILES d
WHERE  f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id ) )
ORDER BY "TS Name"
/

/* TS % full by datafile, not whole TS */
col "Tablespace" format a10
col "Datafile name" format a40
Select t.tablespace_name  "Tablespace",
t.status "Status",
ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0,f.bytes))/1024/1024),2) "Used MB",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "Free MB"
, SUBSTR(d.file_name,1,80) "Datafile name"
FROM DBA_FREE_SPACE f , DBA_DATA_FILES d , DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id
GROUP BY t.tablespace_name ,
d.file_name ,
t.initial_extent ,
t.next_extent ,
t.min_extents ,
t.max_extents ,
t.pct_increase , t.status
ORDER BY 1,3 DESC
/


/* this charts datafile growth by month ... only really useful */ 
/* if you don't ever DROP any data files */ 
SELECT TO_CHAR(creation_time, 'RRRR Month') "Month", 
SUM(bytes)/1024/1024 "Growth in Meg" 
FROM sys.v_$datafile 
WHERE creation_time > SYSDATE-365 
GROUP BY TO_CHAR(creation_time, 'RRRR Month')   



/* Abhi's TS free/full command */


-- doesn't get tempts.
select     a.tablespace_name  name, 
    nvl(c.free_mb,0) free,
    to_char(nvl((b.total_mb - c.free_mb),0),'9999999990.99') used, 
    nvl(b.total_mb,0) bytes, 
    to_char(nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0),'990.99') Usedpct,
    nvl(d.max,0) maxsize,
    to_char(nvl(round((((b.total_mb - c.free_mb)*100)/d.max),2),0),'990.99') Maxpct
from    (   select  tablespace_name, 
                    to_char(nvl(round(((sum(bytes)/1024)/1024),2),0),'9999999990.99') total_mb 
            from    sys.dba_data_files
            group by tablespace_name) b, 
        (   select  tablespace_name,    
                    to_char(nvl(round(((sum(bytes)/1024)/1024),2),0),'9999999990.99') free_mb 
            from sys.dba_free_space
            group by tablespace_name) c, 
        (   select tablespace_name,
                    to_char(nvl(round(((sum(decode(autoextensible ,'YES', maxbytes, bytes))/1024)/1024),2),0),'9999999990.99')  max
            from sys.dba_data_files 
            --where autoextensible='YES'
            group by tablespace_name) d, 
sys.dba_tablespaces a 
where a.tablespace_name = b.tablespace_name (+) 
and a.tablespace_name = c.tablespace_name (+) 
and a.tablespace_name = d.tablespace_name 
--and a.tablespace_name like '%BUDGET%'
--and ((b.total_mb - c.free_mb)*100)/d.max >= 90
order by a.tablespace_name;


-- improved version just for temp (requires hard coded  temp ts name)
SELECT 'tablespace', nvl(A.tablespace_name,'TEMP1') name, 
D.mb_total total_in_mb, 
SUM (nvl(A.used_blocks,0)*D.block_size)/1024/1024 used_in_MB, 
(D.mb_total-SUM (nvl(A.used_blocks,0)*D.block_size)/1024/1024) free_in_mb, 
round((SUM(nvl(A.used_blocks,0)*D.block_size)/1024/1024*100/D.mb_total),0) percent_used 
FROM gv$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 D.name = A.tablespace_name (+)
and d.name='TEMP1'
GROUP by A.tablespace_name, D.mb_total;


/*************************************************************/ 
/* Space usage by USER, not tablespace */ 

select e.segment_name, sum(e.bytes) bytes 
from dba_extents e, dba_tables t 
where e.segment_name = t.table_name 
--and t.owner='BO2' 
group by e.segment_name 
order by bytes desc 




/*************************************************************/ 
/* Statistics cmds */ 

-- 8i: replaced by dbms_stats in 9i
analyze table Person compute statistics; 
analyze table AbstractAuthor compute statistics; 
analyze table Membership compute statistics; 

select index_name from user_indexes; 
select table_name from user_tables; 
select * from index_stats; 
analyze index abstractauthor_cst compute statistics; 

/* don't use this one; use one below */ 
/* execute dbms_stats.gather_schema_stats('ejp'); */ 

execute dbms_stats.gather_database_stats; 

-- this lets CBO determine if its necessary to 
actually get stats 
dbms_stats.gather_schema_stats(ownname=> 'boss', 
options=> 'GATHER AUTO'); 

-- this actually gets index stats too. 
exec dbms_stats.gather_schema_stats(ownname=>'LNDG_E2',cascade=>true); 

-- Table-level stats; this gets one table, all indexes w/ autosample size

exec dbms_stats.gather_table_stats ('owner','tablename',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL indexed COLUMNS',cascade=>TRUE);



/*************************************************************/ 
/* Active Transactions; doesn't give sql running, just transactions */ 

select username, terminal, osuser, 
       t.start_time, r.name, t.used_ublk "ROLLB BLKS", 
       decode(t.space, 'YES', 'SPACE TX', 
          decode(t.recursive, 'YES', 'RECURSIVE TX', 
             decode(t.noundo, 'YES', 'NO UNDO TX', 
t.status) 
       )) status 
from sys.v_$transaction t, sys.v_$rollname r, 
sys.v_$session s 
where t.xidusn = r.usn 
  and t.ses_addr = s.saddr; 

/* gives the number of concurrent transactions */ 

select count(*), sum(used_ublk) from v$transaction 
select * from v$resource_limit where 
resource_name='TRANSACTIONS'; 


/* -- to find out the parallel recovery processes during smon Tx recovery*/
select * from v$fast_start_servers; 


/* Monitor transaction recovery */
set linesize 100 
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", 
 decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
 "Estimated time to complete" 
from v$fast_start_transactions;



/*************************************************************/ 
/* all startup values */ 

select * from sys.props$; 
show parameter 

set pagesize 100 
set linesize 120 
column name format a35 
column value format a75 
SELECT name, value 
FROM   v$parameter; 
-- WHERE  name = 'background_dump_dest'; /* backup 
location */ 

/* 10g version: only non-default values listed */
select * from v$parameter where isdefault='FALSE' order by 2;



/*************************************************************/ 
/* Rollback Segment Hit Ratios: use? */ 
set column "Hit Ratio" format 999.99; 
select name "Rollback Segment" 
,writes,waits,gets,(100-(waits/gets)) "Hit Ratio" 
from v$rollstat a,v$rollname b 
where (a.usn = b.usn); 



/*************************************************************/ 
/* All users/schemas in database */ 
column temp_ts format a10 
set pagesize 100 
select 
username,default_tablespace,temporary_tablespace as 
temp_ts 
from   dba_users; 

select * from dba_users 
where account_status='OPEN' 
order by user_id 

-- 12c: now have common and oracle_maintained flags
select username,common,oracle_maintained from dba_users where oracle_maintained='Y'
order by username;

/* all NON-system users/schemas: updated 8/3/12 for 11g users and BOBJ users,  Updated 10/3/14 
for 12c users and CB logins */

--select username,default_tablespace as ts,temporary_tablespace as temp_ts, common,oracle_maintained
--from   dba_users 

select '''' || username || '''' from all_users order by 1;

-- updated for College Board

drop table nousers;

create table nousers as

select *
-- '''' || username || '''' 
from dba_users
where username not in 
('DBSNMP','OUTLN','WMSYS','ORDSYS' 
,'ORDPLUGINS','MDSYS','CTXSYS','WKSYS','XDB','ANONYMOUS','ODM','ODM_MTR' 
,'WKPROXY','OLAPSYS','ORACLE_OCM','SYS','SYSTEM','TSMSYS','PERFSTAT','APPQOSSYS','DIP'
,'AUDSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','SYSBACKUP','SYSDG','SYSKM','XS$NULL','DMSYS',
'WEBLOGIC','SYSMAN','EXFSYS','IGMON','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXADMIN','SQLTXPLAIN',
'AV_USER','FLOWS_FILES','FLOWS_030000','RMAN','TOAD','DVSYS','GGSYS','LBACSYS','OJVMSYS','SYSRAC')
and username not in ('I3_COLLECTOR','CMBUILD','ISO_TVM','PCI_QSA','HELPDESK','AV_USER','MAM_DISCOVER','MAM_USER','MVIEW')
--and default_tablespace in ('USERS_ENC','USERS','DATA_PROV_TEAM_DATA01')
and username not in 
('HR','OE','PM','SH','QS_ADM','QS','QS_WS', 
'QS_ES','QS_OS','QS_CBADM','QS_CB','QS_CS','SCOTT',
'EXFSYS','MGMT_VIEW','ORDDATA','SI_INFORMTN_SCHEMA') 
and username not in ('DBA_USER','IGMON','TOPAZ','REMOTE_DDM','ORACLE','MDDATA')
and username not like 'BOBJ%'
and username not like 'BOBK%'
and username not like 'OPS$%'
and username not like 'READID%'
and username not like 'FIREDBA%'
--and username not like '%_APP'
and username not like 'APEX%'
-- CB users
and username not in ('ABISWAS','AGENAUER','AHIRSI','BYEH','KLEUNG','RHARIHARAN','RSUNKARA','SATMURI','SFELDMAN','SKARIRO','TBOSS',
'ACAVANAGH','ALOPATNIKOV','BSHAH','CGUO','CPRICE','CTANG','DTIDD','EHSAING','FKHAN','FLNEMATI','FNEMATI','GCHEN',
'HVUDAYARAJU','ILAHHAM','JHE','JROBINSON','JZHOU','JZHU','KAJAGBA','KBECK','KDUBEY','KYANG','MCOMPARONE','MGODIN',
'MVACA','MYAO','PFOO','PKUMBLE','RGUNALAN','RHARGROVE','RSARINO','SDARDIS','SKAUR','SMAHAMBREY','SMALYSZKA','SRAO',
'XQIN','YWANG','ACAVANAGH','AGANESAN','AGENAUER','AIORDANOGLOU','ALICE','APACH','BBOYLAN','BOGDEN','BSHAH','BYEH',
'CCHADBOURNE','CGRITTON','CHAGAN','CKENNEY','CMCMICHEN','CPEKLO','CPRICE','CTANG','DPANDYA','DPILLAI','DTESSINO',
'DTIDD','EHSAING','FLNEMATI','FNEMATI','FSCHLENTER','GCHEN','HVUDAYARAJU','ICHEBUKINA','ILAHHAM','JCHANDLER','JCOLLICA',
'JCZAJKOWSKI','JGADE','JROBINSON','JZHOU','JZHU','KAJAGBA','KBECK','KDEBOUCHEL','KDUBEY','KFANG','KFUERST','KJOHN',
'KMOULTON','KPANGANAMALA','KTONEY','KVISWANATHAN','KYANG','LSTOCKOV','LYOUNG','MAEDWARDS','MGODIN','MGUPTA','MLUNNEY',
'MMAVULOORI','MNAGARWALA','MVACA','MYAO','OPS$MYAO','OPS$ORACLE','OPS$RHARIHARAN','OPS$RSUNKARA','PFOO','RBALAKRISHNAN',
'RBARNES','RGAJJALA','RGUNALAN','RHARIHARAN','RHCHOCHA','RSUNKARA','SATMURI','SBACHALI','SBALAKRISHNAN','SCHIGURUPATI',
'SJAIN','SKACHORIA','SKANDUKURI','SKAUR','SMAHAMBREY','SNADELLA','SRAGURU','SRAO','SSMITH','TBOSS','TKHEDRUP','TSHOU',
'VKADANS','WHUANG','YWANG','YZHAO','AIGNATOV','AJOY','AROY','BDAMMOJU','BLACOUL','BNJECK','BSRIDHARAN','CMCCREA','DHEATH',
'DLIEU','EHSIANG','GMANOCHA','GWANG','HVUDUYARAJU','ICLEMENTS','ICLEMENTS','LZHANG','JCHEN','MJAIN','NIPATEL','PSWAMY',
'RBHAGAT','RGARLICK','RPADMANABAN','SDOOSA','SDUGGIREDDY','SNAIR','TBLOSSER','VVIJAYAN','WZHOU','MBRIDGES','SGOURAVARAM',
'AGHOSAL','AHAUPTMANN','AJACKLIN','ANMICHAEL','ARAWLS','ASAFFORD','AKOV','APARUCHURI','ASAXENA','ASREENIVASAN','AWOODLEY',
'BPATTERSON','BSAMANT','BZELLEN','ACAVANAGH','AGENAUER','AGHOSAL','AHAUPTMANN','AIGNATOV','AJOY','AKOV','ANMICHAEL','APARUCHURI',
'ASAFFORD','ASAXENA','ASREENIVASAN','AWOODLEY','BNJECK','BPATTERSON','BSAMANT','BSHAH','BZELLEN','CCHADBOURNE','CGRITTON','CKENNEY',
'CMCCREA','CPRICE','CTANG','DCHUAH','DEMORGAN','DMIKELSON','ESHROYER','GCHRISTIAN','GGANDRA','GLI','GNORRIS','GSTALKER','GWILSON',
'HDENG','HVUDAYARAJU','JCHANDLER','JCHEN','JDONOHUE','JFARROW','JGADE','JJOHNSON','JMACK','JNILAK','JOLSON','JREYNOLDS','JWU',
'KAJAGBA','KCHINTAGUMPULA','KGODFREY','KKWON','KMOULTON','KSHARMA','KSHORT','KTONEY','KVISWANATHAN','LDELAROSA','LYOUNG','MAEDWARDS',
'MBRIDGES','MPOLIZZI','MREMIGIO','MSHERMAN','MSYED','MVACA','MYAO','NJOE','OGARCIA','PMENDELSON','PPATEL','PVEGESINA','RHARIHARAN',
'RHCHOCHA','RSUNKARA','RTANG','SABBARAJU','SATMURI','SFELDMAN','SFRUSTINO','SGARLAPATI','SGOURAVARAM','SGUDUGUNTLA','SJINKA',
'SKANDUKURI','SKARIRO','SKAUR','SMAHAMBREY','SMANOHARAN','SMOTUPALLI','SNADELLA','SPURANAM','SSATYAM','THEIMAN','TJAIN',
'VDANDAMUDI','VKURI','WZHOU','XZHANG','YFIRDAWCY','YWANG','ABISWAS','AGANESAN','AGENAUER','AGHOSAL','AIGNATOV','ANMICHAEL',
'ARAWLS','ASAXENA','AWOODLEY','BLACOUL','BNJECK','BSAMANT','BSRIDHARAN','BZELLEN','CCHADBOURNE','CGRITTON','CKENNEY','CMCCREA',
'CPEKLO','CPRICE','CTANG','DLIEU','EHSAING','GCHRISTIAN','HVUDAYARAJU','ICLEMENTS','ILAHHAM','JCHANDLER','JCZAJKOWSKI',
'JDONOHUE','JFARROW','JGADE','JJOHNSON','JMACK','JOLSON','JREYNOLDS','KFUERST','KTONEY','KVISWANATHAN','LDELAROSA','LZHANG',
'MAEDWARDS','MJAIN','MLUNNEY','MMAVULOORI','MREMIGIO','MSHERMAN','MSYED','MYAO','NIPATEL','OGARCIA','PFOO','PPATEL',
'RBALAKRISHNAN','RHARIHARAN','RTANG','SABBARAJU','SATMURI','SBALAKRISHNAN','SFELDMAN','SFRUSTINO','SGUDUGUNTLA','SJAIN',
'SJINKA','SKANDUKURI','SKARIRO','SKAUR','SMAHAMBREY','SMANOHARAN','SNADELLA','SNAIR','SRAO','SSATYAM','TJAIN','VVIJAYAN',
'WHUANG','YWANG','BVALENTINE','GEBERLY','HTRAN','JGUERRERO','KKATRAGADDA','MDUPONT','PHADYA','SVAGO','ASURESH','AZUCARO',
'BKAMATH','BKOTADIA','CCHANDRA','CREDDY','IPERIAKARUPPAN','JROBERTSON','KBAIRI','KJILLALAMUDI','KKONGARA','KSEKAR',
'LLE','MMAKSUDOV','MPIDISETTI','MPREBILIC','MROY','MVASS','NANDERSON','AKARAMCHEDU','AKOTHARI','JPOLICEPATIL','NKRISHNAPPA',
'NVITHANALA','VNAUMOV','MGARCIA','SBETHAPUDI','JACHANDLER','JGENG','BPETRO','CBLAKEY','HRANDHAWA','SBUDAMA','SRCHIGURUPATI',
'STAVAKOLIAN','SVEMULAPATI','UYADAV','ASHUKLA','ATARAZI','AVASAVADA','BNADELLA','BNAREGAL','JJANAKIRAMAN','JMORRISON','KATUMMALA',
'KBANKHAKAVI','KKESAVAN','KTENZIN','LNOOKALA','MSUTOR','NSHAH','NSUREKA','RGADESAM','RKOLANU','BYADAMS','CILI','CKACHANA','DRIORDAN',
'JBURGULA','RMOOLAKANDY','RSUDALAI','VAVALA','NGAMBOA','SARZA','VKOTHA','AFOULADI','AGAIKWAD','AGRAHAM','AGU','ALARORA',
'ANCHRISTOPHER','ASPATEL','BCOTE','BJOSHI','BLIPSON','BLIVERMON','BROBERTS','CHENSLEY','CHJONES','DAGRAWAL','AJAGADAM','DBHANOT','KMARATI',
'AGANESAN','AIGNATOV','AJAGADAM','AROY','AVASAVADA','BLACOUL','BNADELLA','BNAREGAL','BNJECK','BPETRO','BROBERTS','BSRIDHARAN','BYEH',
'CCHADBOURNE','CILI','CPEKLO','CPRICE','CTANG','DBHANOT','EHSIANG','FNEMATI','GGANDRA','HRANDHAWA','HVUDAYARAJU','HVUDUYARAJU','ICLEMENTS',
'JACHANDLER','JGENG','JZHU','KDEBOUCHEL','KKESAVAN','KMARATI','KREDDY','KSEKAR','LZHANG','MJAIN','MLUNNEY','MSAKINALA','MSUTOR','MTSAI',
'MYAO','NIPATEL','PFOO','PSWAMY','RBHAGAT','RBUYYANI','RGADESAM','RGAJJALA','RGARLICK','RGORTHI','RGUNALAN','RHARIHARAN','RMOOLAKANDY',
'RSUNKARA','RVERMA','SARZA','SATMURI','SBACHALI','SBALAKRISHNAN','SBUDAMA','SCHINTAPALLI','SGOURAVARAM','SJAIN','SKANDUKURI','SKAUR','SMAHAMBREY',
'SMEDEPALLI','SNAIR','SPATTASWAMY','SPILLAI','SRAGURU','SRAO','SRCHIGURUPATI','TMUMM','UYADAV','VAVALA','VNAUMOV','VRATNAPU','VVIJAYAN','YEKUMAR',
'ANADIKATLA','APAKANATY','BVADAPALLY','DBANOT','DBOGSTAD','DJEYARAJAN','DNANDA',
'DPOST', 'EPREMDASS', 'GPERFETTO', 'GSURIANARAYANAN', 'GTADISETTI', 'HALI', 'HMEDARAMETLA', 'HRAJAPANDIAN', 'HRAO',
'ILALWANI', 'JAMCCULLOUGH', 'JFENNINGER', 'JMOSER', 'JRANGI', 'JSTERN', 'JWENK', 'KAMARAVADI', 'KANANTHATHMAKULA',
'KGERSHENSON', 'KILEUNG', 'KKANDASAMY', 'KMITTA', 'KMU', 'LBAY', 'LTANNIRU', 'MBERRY', 'MCOLES', 'MHURWITZ',
'MKARAS', 'MMONTGOMERY', 'MNIMALA', 'MOSMAN', 'MUMMADISETTI', 'MVALA', 'NADASOJU', 'NBITRA', 'NMANOHARAN', 'NPATEL',
'NSERI', 'NVANBULCK', 'PFULMER', 'PGOVINDU', 'PKALAGA', 'PLSCHANGEPASS1', 'PRAJKHOWA', 'PRAWAT', 'PRKAMBAM', 'RAERRA',
'RAKNAPURAM', 'RCORNELIUS', 'RDEVATHA', 'RJEYARATNAM', 'RMOSLEY', 'RRAMAKIRISHNA', 'RSHAH', 'SBABEL', 'SBACHU',
'SBHATTACHARYYA', 'SEAGALA', 'SHERZ', 'SKARKI', 'SMADDALI', 'SMOPARTHI', 'SNADKARNI', 'SNOTTATH', 'SPADAMATI', 'SPAUL',
'SPERIYAKARUPPAN', 'SPURUSOTHAMAN', 'SSANDIREDDY', 'SSHRESTHA', 'TANDAPALLY', 'TDAY', 'TMISHRA', 'VJAGANATHAN', 'VKALSI',
'VKUDUPUDI', 'VKURA', 'VMAKA', 'VMEDIKONDA', 'VPALANISAMY', 'VPATLORI', 'VTATIKONDA', 'VWANCA', 'VWENG', 'WLAU',
'YKONO', 'jlinkov-johnson','TPROCTOR','AVAIRAVELU', 'AWANG', 'BHOTALING', 'CHNILAK', 'DATESSINO', 'DJANI', 'GCAMPBELL', 'GCURREY', 'GSEERLA', 'HLUDHER',
'JFANG', 'JPOKHAREL', 'JREDDY', 'KAHLUWALIA', 'KBURCHFIELD', 'KXIONG', 'LCALLISON', 'MANGUPTA', 'MOWELLEN', 'MYOU',
'NAHLUWALIA', 'NOUIMETTE', 'NPARGAONKAR', 'PJAVAJI', 'PKOSARAJU', 'PSABAPATHY', 'PSIDDAVATAM', 'RASUDALAI', 'RGRENFELL',
'RHUANG', 'RHURWITZ', 'RMANOHAR', 'RMETTU', 'RPHERWANI', 'RRACHABATTUNI', 'RYANDRAPU', 'SBERGERT', 'SCHEN', 'SCHOCKANARAYANAN',
'SGANAMENENI', 'SGNANASEGARAN', 'SKUEHM', 'SKULKARNI', 'SMILLONES', 'SNALIKALA', 'SREDICK', 'TCERAMI', 'TKONDURU',
'UMEDA', 'VNONDAPAKA', 'VPATHA', 'VSABAPATHY', 'WCHOI','DOSCHIER','JCLEWLEY','KTOMPKINS','SGABBITA','CSCROGGINS','DANPILOGOV',
'DKRISHNAMANENI','JAMJOHNSON','ASURENDRANATH','PGOEL','PKYATHAM','VBODDU','AAQUINO','CDOAN','PGIRIDHAR','HSAAVEDRA','KREIF','RMAISEL','SSHAIKH',
'AKOKKINIS','BWITT','JTRUXESS','ZJING','HAHT','ABRODSKY','JISTOUFFER','STHOMAS','DCHOW','ARYKOV','JKEMPE','PASTEINMAN','CFENG','LHARDY','SCHALIMEDA',
'KASRINIVASAN','JBEACH','ZLIN','AHASHMI','KSTANLEYMARKOWITZ','NYUEN','RMCGLYNN','MSUNG','TONYEKWELI','SROUT','DABHILASH','DBASH')
order by username;


select * from dba_tables where owner in ('SNOOPY');

select owner, object_type, count(*)
from dba_objects o
where o.object_type='TABLE' group by o.owner, o.object_type order by o.owner asc;
























select '''' || username || ''',' from dba_users where default_tablespace='USERS' order by username;

select * from dba_users
where default_tablespace not in ('SYSTEM','SYSAUX','USERS','USERS_ENC','READIDPLUS_DATA01','READIDPLUS_ENC_DATA01')
and username not like '%_APP'
order by username;


select owner,table_name from dba_tables where owner in (select  username from nousers)
order by owner,table_name;

select '''' || username || '''' from dba_users where default_tablespace='USERS' and username not in 
(select  username from nousers) order by 1;




/*************************************************************/ 
/* tables and last time analyzed */ 
alter session set NLS_DATE_FORMAT = "MM/DD/YYYY HH24:MI:SS";
set pagesize 100 
set linesize 120 
column table_name format a30 
set null null 
select table_name, num_rows, sample_size, last_analyzed from dba_tables 
--where table_name like 'SDS%' or table_name like 'EWSI%' 
where table_name in ('STG_BATCH_RPRT_DELTA_KEYS','STG_PERSON')
-- where owner='OMNIPNT' 
order by table_name; 

/* same for indexes */ 
set pagesize 100 
set linesize 120 
set null null 
column table_name format a30 
column index_name format a30 
column index_type format a10 
column tablespace_name format a20 
column owner format a10 
select owner,table_name, index_name, index_type, 
tablespace_name, last_analyzed from dba_indexes 
--where table_name like 'SDS%' or table_name like 'EWSI%' 
where table_name='SUPERLIST' 
--where owner in ('OMNIPNT','OPMPROD') 
order by table_name, index_name; 




/* use this one; tables, table partitions, indexes and index partitions */

alter session set NLS_DATE_FORMAT = "MM/DD/YYYY HH24:MI:SS";

select owner,table_name,partitioned,
num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,degree,instances,
sample_size, to_char(100*(sample_size/decode(num_rows,0,1,num_rows)),99999.99) as pct,
--100*(sample_size/decode(num_rows,0,1,num_rows)) pct,
last_analyzed
from dba_tables 
where owner in ('JDIETZ')
--WHERE owner in ('DPM','RAM_DATAMART','DW_ADMIN','DMSGR_IN','DMSGR_EX','METAOWNER','PCS','SMC_DATAMART')
--where owner like 'EDW%'
--where owner in ('NVAPP','AVAPP')
--where owner NOT IN ('SYSTEM','SYS','MDSYS','SYSMAN','EXFSYS','DBSNMP','ORAMON','CTXSYS','DMSYS','OLAPSYS',
--'ORDSYS','OUTLN','PERFSTAT','QUEST_SPOT','SCOTT','SDE','SYSMON','TSMSYS','WMSYS','XDB')
--table_name='F_PRSNL_ACT' 
--and owner='DW' 
and table_name not like 'MDRT%' -- skips tables used by spatial indexes
and table_name not like 'MLOG%' -- materialized view logs
and table_name not like 'RUPD%' -- also used by MV logs
--order by last_analyzed desc;
order by 1,2
;




select owner,table_name,num_rows,sample_size, 
to_char(100*(sample_size/decode(num_rows,0,1,num_rows)),99999.99) as pct,
--100*(sample_size/decode(num_rows,0,1,num_rows)) pct,
last_analyzed 
from dba_tables 
--where owner in ('SMC_DATAMART','PCS')
--WHERE owner in ('DPM','RAM_DATAMART','DW_ADMIN','DMSGR_IN','DMSGR_EX','METAOWNER','PCS','SMC_DATAMART')
where owner like 'EDW%'
--table_name='F_PRSNL_ACT' 
--and owner='DW' 
--order by 1,2;
order by last_analyzed asc;

select distinct table_owner,table_name from dba_tab_partitions order by 1,2;

select table_owner,table_name,partition_name,partition_position,num_rows,blocks,avg_row_len,
sample_size,last_analyzed 
from dba_tab_partitions 
--where table_owner in
--('DPM','RAM_DATAMART','DW_ADMIN','DMSGR_IN','DMSGR_EX','METAOWNER','PCS','SMC_DATAMART')
--and table_name in ('F_PRSNL_ACT')
--and partition_name='P1211'
where table_owner='GEO' and table_name='FACT'
order by table_name,partition_position asc;

select table_name,partition_name,subpartition_position,num_rows,blocks,avg_row_len,sample_size,last_analyzed 
from dba_tab_subpartitions
--where table_owner='DMSGR_EX'
--and table_name in ('F_PRSNL_ACT')
--and partition_name='P1211'
where table_owner='GEO' and table_name='FACT'
order by table_name,subpartition_position asc;

SELECT owner,table_name,index_name,blevel,leaf_blocks,distinct_keys,clustering_factor, 
num_rows, sample_size, last_analyzed 
FROM dba_indexes 
--where owner='OPMPROD' 
--where table_name in ('F_PRSNL_ACT')
where table_owner='GEO' and table_name='FACT'

and index_name='XBMP_PRSNL_ACT_AGENCY_SUBEL_F2';

select index_owner,index_name,partition_name, leaf_blocks,distinct_keys,num_rows,last_analyzed
from dba_ind_partitions 
where index_name='XBMP_PRSNL_ACT_AGENCY_SUBEL_F2'
and partition_name='P1211';


SELECT 
owner,table_name,column_name,num_distinct,density,num_nulls,num_buckets, 
sample_size, last_analyzed 
FROM dba_tab_cols 
WHERE table_name='CRNT_RFRNC_DATA'; 

column index_name format a30 
column column_name format a30 
column index_owner format a10 
SELECT index_owner,index_name,column_name FROM 
dba_ind_columns WHERE table_name='SUPERLIST' 
order by index_owner,index_name,column_name; 




/*************************************************************/ 
/* are there any objects in my tablespace? */

select * from dba_tables where tablespace_name='DM';
select * from dba_tab_partitions where tablespace_name='DM';
select * from dba_tab_subpartitions where tablespace_name='DM';

select * from dba_indexes where tablespace_name='DM';
select * from dba_ind_partitions where tablespace_name='DM';
select * from dba_ind_subpartitions where tablespace_name='DM';

select * from dba_users where default_tablespace='DM';


/*************************************************************/ 
/* Just the basics: sp_who

updated this 10/6/08 for rac/asm programs, as well as extra arc log writers */ 

column username format a12
column sid format 999 
column serial# format 99999 
column sidserial format a10
column machine format a15 
column osuser format a13 
column status format a9 
column program format a45 
set linesize 160
set pagesize 200
select sid || ',' || serial#  as "sidserial",saddr,status,username,osuser,machine,program
from v$session 
--where username = 'BICONNECT'
--where username <> 'CERCONNECT'
--where status='ACTIVE'
where program <> 'OMS' -- to get multiple Sysman/OMS processesa
and program not like '%(MMAN)' and program not like '%(PMON)' and program not like '&(PSP0)'
and program not like '%(ARC0)' and program not like '%(DBW0)' and program not like '%(LGWR)'
and program not like '%(CKPT)' and program not like '%(SMON)' and program not like '%(RECO)'
and program not like '%(CJQ0)' and program not like '%(MMON)' and program not like '%(MMNL)'
and program not like '%(ARC1)' and program not like '%(J000)' and program not like '%(PSP0)'
and program not like '%(q000)' and program not like '%(q001)' and program not like '%(QMNC)'
and program not like '%(DBW1)' and program not like '%(DBW2)' and program not like '%(DBW3)'
and program not like '%(DBW4)' and program not like '%(DBW5)' and program not like '%(DBW6)'
and program not like '%(DBW7)' and program not like '%(DBW8)' and program not like '%(DBW9)'
and program not like '%(DBWa)' and program not like '%(DBWb)' and program not like '%(DBWc)'
and program not like '%(ARC1)' and program not like '%(ARC2)' and program not like '%(ARC3)'
and program not like '%(ARC4)' and program not like '%(ARC5)' and program not like '%(ARC6)'
and program not like '%(ARC7)' and program not like '%(ARC8)' and program not like '%(ARC9)'
and program not like '%(ARCa)' and program not like '%(ARCb)' and program not like '%(ARCc)'
and program not like '%(ARCd)' and program not like '%(ARCe)' and program not like '%(ARCf)'
and program not like '%(ARCg)' and program not like '%(ARCh)' and program not like '%(ARCi)'
and program not like '%(ARCj)' and program not like '%(ARCk)' and program not like '%(ARCl)'
and program not like '%(ARCm)' and program not like '%(ARCn)' and program not like '%(ARCo)'
and program not like '%(ARCp)' and program not like '%(ARCq)' and program not like '%(ARCr)'
and program not like '%(ARCs)' and program not like '%(ARCt)' and program not like '%(ARCu)'
and program not like '%(ARCv)' and program not like '%(ARCw)' and program not like '%(ARCx)'
and program not like '%(ARCy)' and program not like '%(ARCz)' 
and program not like '%(q004)' and program not like '%(q005)' and program not like '%(q006)'
and program not like '%(q002)' and program not like 'emagent%'
and program not like '%(LMS1)' and program not like '%(LCK0)' and program not like '%(O005)'
and program not like '%(LNS1)' and program not like '%(ASMB)' and program not like '%(LMS0)'
and program not like '%(LMD0)' and program not like '%(DIAG)' and program not like '%(RBAL)'
and program not like '%(LMON)' and program not like 'racgimon%'
order by status, username
--order by sid,serial#

--same as above getting all programs....

select sid || ',' || serial#  as "sidserial",saddr,status,username,osuser,machine,program
from v$session 
order by status, username
--order by sid,serial#
/


/* with login time */
column username format a10 
column sid format 999 
column serial# format 99999 
column machine format a15 
column osuser format a10 
column status format a9 
column program format a30 
set linesize 160
set pagesize 100 
select sid,serial#,saddr,status,username,osuser,machine,program ,
to_char(logon_time,'mm/dd/yyyy hh:mi:ss') as logindate
from v$session 
--where username <> 'CERCONNECT'
--order by status, username,logindate; 
order by logindate desc;



/*************************************************************/ 
/* All connected users with OS pid */ 
column username format a15 
column osuser format a15 
column terminal format a12 
column command format a15
column process format a10
column lockwait format a3
set linesize 120
set pagesize 9999
select  S.SID 
       ,S.serial# 
       ,S.username 
       ,S.OSuser 
       ,initcap(S.status) Status 
       ,S.terminal 
       ,decode(S.lockwait,NULL,'No','Yes') Lockwait 
       ,initcap(A.Name) Command 
       ,S.process 
       ,P.SPID "UnixID" 
from    v$SESSION S 
       ,v$PROCESS P 
       ,sys.AUDIT_ACTIONS A 
where S.username is not null 
--and s.sid in (25,35) 
and   A.action = S.command 
and   P.ADDR = S.PADDR 
--and s.status <> 'INACTIVE' /* only shows active */ 
and s.username='DPM'
order by status,osuser 
/



/*************************************************************/ 
/* 8/25/09 Abhi's version to get process sizes */

select p.spid, p.program, trunc(p.pga_used_mem/1024/1024) USED, 
trunc(p.pga_alloc_mem/1024/1024) ALLOC , trunc(p.pga_max_mem/1024/1024) MAX ,
s.SID, s.SERIAL#, s.USERNAME  ,     
s.STATUS         ,
s.SCHEMANAME     ,
s.OSUSER         ,
s.MACHINE        ,
s.PROGRAM        
from v$process p, v$session s
where s.paddr = p.addr
order by pga_alloc_mem desc;



/*************************************************************/ 
/* additional memory diagnosing */

SELECT * FROM V$BGPROCESS 
WHERE  PADDR != '00'
ORDER BY NAME;

select * from V$RESOURCE_LIMIT order by resource_name;

--where resource_name in ('processes', 'sessions', 'enqueue_resources', 'parallel_max_servers'); 

select name,trunc(value/1024/1024) mb from v$pgastat
where name not in ('bytes processed','extra bytes read/written')
order by value desc;

-- gets all SGA allocations > 1mb
select pool,name,bytes,trunc(bytes/1024/1024) mb from v$sgastat 
where bytes > 1000000 order by bytes desc;

select * from v$pga_target_advice;

select * from v$sga_target_advice;



/*************************************************************/ 
-- getting a series of high-level details about databases.

select * from global_name;
select * from v$version;

select * from dba_data_files;
select sum(bytes)/1024/1024 as mb from dba_data_files;

select pool,name,trunc(bytes/1024/1024) mb from v$sgastat order by bytes desc;

select name,trunc(value/1024/1024) mb from v$pgastat
where name not in ('bytes processed','extra bytes read/written')
order by value desc;

select * from V$RESOURCE_LIMIT order by resource_name;

select name,value/1024/1024 as mb from v$parameter where name in ('pga_aggregate_target','sga_target','sga_max_size');





/*************************************************************/ 
/* Currently executing sql */ 

select SID, serial#
      ,S.username
      ,S.OSuser
,decode(A.users_executing,0,'*idle',A.users_executing) NOW
  ,s.status
      ,A.buffer_gets
      ,A.executions
      ,A.rows_processed
      ,substr(A.first_load_time,12,5) Firstload
      ,A.sql_text
from   v$SQLAREA A
      ,v$SESSION S
where A.address = S.sql_address
and   S.audsid != userenv('SESSIONID') -- to prevent this session from showing
--and s.serial# != 1 -- hides system jobs
and a.sql_text not like '%OracleOEM%' -- 9i style OEM jobs
and a.sql_text not like '%dbms_application_info%' -- hides sql developer/toad inactive windows
and a.sql_text not like '%EMD_NOTIFICATION%' -- hides sysman/10g grid control
--and sid=59
order by now,SID
/



/* Currently executing SQL for a SID ... gets ALL the sql, not just first 80chars */
select  sql_text 
from    v$sqltext st, v$session s 
where   s.sql_address = st.address and 
        s.sql_hash_value = st.hash_value and 
        s.sid=131 
order by address, piece 
/ 


/* doesn't really get what you need */
select u.username,v.first_load_time, v.last_load_time, v.sql_text, v.sharable_mem, v.elapsed_time
from v$sql v, dba_users u
where v.parsing_user_id = u.user_id
and u.username='BICONNECT'
order by first_load_time



/*************************************************************/ 
/* how to get LONG sql ... stuff that flows out of the limit of sql_text */

select first_load_time, last_load_time, hash_value, sql_text
from v$sql where parsing_user_id=53 order by last_load_time desc;

or specific search:
select * from v$sqltext where sql_text like '%115482483%'

-- now grab hash_value and run

select * from v$sqltext where hash_value='3016383486' order by piece;

select * from v$sqltext where sql_id='7qy1ajntk73n6' order by piece;




select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1368 and 1369
and s.sql_id = t.sql_id
and t.sql_id='abtnxxayrxsfw';

-- how many times has something run?
select * from DBA_HIST_SQLSTAT where sql_id='abtnxxayrxsfw';

select count(*) from DBA_HIST_SQLSTAT where sql_id='abtnxxayrxsfw';

select * from DBA_HIST_SQLSTAT where sql_id='887w7muh5pz35';

select count(*) from DBA_HIST_SQLSTAT where sql_id='49qc6s8sxfjk2';

select * from DBA_HIST_SQLTEXT where sql_id='abtnxxayrxsfw';






/*************************************************************/ 
/* nice version posted that gets longops and good consolidation
of other stuff.  Dustin Hayden to lazydba.com 8/8/07 */

column username format a10
column sid format 999
column machine format a15
column "OS User" format a8
column "OSProc" format a6
column "Machine Name" format a15
column status format a9
column Program format a20
column Action format a22
column "SQL text" format a110
set linesize 120
set pagesize 9999
select decode(sum(decode(s.serial#, l.serial#, 1, 0)), 0, 'No', 'Yes') "lng",
       s.sid "Sid",
       s.status "Status",
       s.username "Username",
       RTRIM(s.osuser) "OS User",
       b.spid "OSProc",
       s.machine "Machine Name",
       NVL(s.module, s.program) "Program",
       s.action "Action",
       c.sql_text "SQL text"
  from v$session s,
       v$session_longops l,
       v$process b,
       (select address, sql_text from v$sqltext where piece = 0) c
where (s.sid = l.sid(+))
   and s.paddr = b.addr
   and s.sql_address = c.address(+)
   and s.program <> 'OMS' -- to get multiple Sysman/OMS processes
and s.program not like '%(MMAN)' and s.program not like '%(PMON)' and s.program not like '&(PSP0)'
and s.program not like '%(ARC0)' and s.program not like '%(DBW0)' and s.program not like '%(LGWR)'
and s.program not like '%(CKPT)' and s.program not like '%(SMON)' and s.program not like '%(RECO)'
and s.program not like '%(CJQ0)' and s.program not like '%(MMON)' and s.program not like '%(MMNL)'
and s.program not like '%(ARC1)' and s.program not like '%(J000)' and s.program not like '%(PSP0)'
and s.program not like '%(q000)' and s.program not like '%(q001)' and s.program not like '%(QMNC)'
and s.program not like '%(DBW1)' and s.program not like '%(DBW2)' and s.program not like '%(DBW3)'
and s.program not like '%(DBW4)' and s.program not like '%(DBW5)' and s.program not like '%(DBW6)'
and s.program not like '%(DBW7)' and s.program not like '%(DBW8)' and s.program not like '%(DBW9)'
and s.program not like '%(DBWa)' and s.program not like '%(DBWb)' and s.program not like '%(DBWc)'
and s.program not like '%(ARC1)' and s.program not like '%(ARC2)' and s.program not like '%(ARC3)'
and s.program not like '%(ARC4)' and s.program not like '%(ARC5)' and s.program not like '%(ARC6)'
and s.program not like '%(ARC7)' and s.program not like '%(ARC8)' and s.program not like '%(ARC9)'
and s.program not like '%(ARCa)' and s.program not like '%(ARCb)' and s.program not like '%(ARCc)'
and s.program not like '%(q004)' and s.program not like '%(q005)' and s.program not like '%(q006)'
and s.program not like '%(q002)' and s.program not like 'emagent%'
group by s.sid,
          s.status,
          s.username,
          s.osuser,
          s.machine,
          NVL(s.module, s.program),
          s.action,
          b.spid,
          b.pid,
          c.sql_text
order by s.status,s.username
/








/*************************************************************/ 
-- How to monitor Undo activity

select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like 'UNDO%';

-- Number of "undo blocks" being used by a transaction 

select a.username,sum(b.used_ublk) x 
from v$session a, v$transaction b 
where a.taddr=b.addr group by a.username; 

-- summary of undo block breakdown
select status,sum(bytes) from dba_undo_extents
group by status;

-- undo stat monitoring.
select u.begin_time, u.end_time, 
round(86400*(u.end_time - u.begin_time)/60,2) as mins ,
undoblks,8192*undoblks as undospace,s.parsing_schema_name,s.sql_text, tuned_undoretention
from v$undostat u, v$sql s
where u.maxqueryid = s.sql_id(+)
--and (ssolderrcnt <> 0 or nospaceerrcnt <> 0)
order by begin_time desc;

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,  
  TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,  
  UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON", 
  MAXQUERYLEN, TUNED_UNDORETENTION  
  FROM v$UNDOSTAT;  



/*************************************************************/ 
/* Total size of all datafiles */ 
select sum(bytes), tablespace_name from dba_data_files 
group by TABLESPACE_NAME; 

/* count of tables per tablespace */ 
select count(table_name),tablespace_name from 
dba_tables 
group by tablespace_name; 

/* list the datafiles being used */ 
set linesize 120 
set pagesize 100 
column tablespace_name format a25 
column file_name format a70 
select tablespace_name,file_name from dba_data_files 
order by file_name; 

select * from v$datafile 

/* list of control files being used */ 
select name from v$controlfile 


  
/* list of redo log files being used */ 
select * from v$logfile; 

/* query that lists the size of each physical data file and the amn't of space used */
set timing on
set linesize 120
set pagesize 9999
column file_name format a60
column tablespace_name format a20
select
  f.tablespace_name,
  f.file_name,
  f.bytes / 1024 / 1024 ,
  sum(e.bytes) / 1024 / 1024 Used
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
group by f.tablespace_name, f.file_name, f.bytes
order by f.tablespace_name, f.file_name;


create table tsusage as
select
  f.tablespace_name,
  f.file_name,
  f.bytes / 1024 / 1024 as max,
  sum(e.bytes) / 1024 / 1024 Used
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
group by f.tablespace_name, f.file_name, f.bytes
order by f.tablespace_name, f.file_name;

select sum(max) from tsusage where tablespace_name not like 'UNDO%';




select sum(used) from tsusage where tablespace_name not like 'UNDO%';
select sum(max) from tsusage where tablespace_name  like 'UNDO%';
drop table tsusage;


/*************************************************************/ 
/* finds all data files that a particular object is using */ 
SELECT DISTINCT 
e.owner,e.segment_name,e.partition_name,e.segment_type,e.tablespace_name,d.file_name 

FROM DBA_EXTENTS e, DBA_DATA_FILES d 
WHERE e.owner='DW' 
AND e.file_id = d.file_id 
AND e.segment_name='EMPLY' 

/*************************************************************/ 
/* Bytes actually occupied by segments */ 
select sum(bytes), TABLESPACE_NAME from Dba_Segments 
group by TABLESPACE_NAME; 



/*************************************************************/ 
/* Space usage for a specific table */ 

/* size in bytes of segments allocated to object (tables, indexes) */ 
/* size in bytes of segments allocated to object (tables, indexes) */ 
SELECT owner, segment_name, SUM(bytes)/1024/1024/1024 gB 
FROM DBA_SEGMENTS 
--WHERE owner='JFMSDW' 
--and tablespace_name='DYNAMIC_BIG'
--AND SEGMENT_NAME = 'STS_RCRD_IDSA' 
--AND SEGMENT_NAME = 'STAGING_HR_STATUS' 
--and segment_name='STDW_GENRL_LDGR_FACT'
GROUP BY owner,segment_name
order by gb desc;


/* guarantees they're tables */

--select sum(mb) from (
SELECT s.segment_name, SUM(s.bytes)/1048576 MB 
FROM DBA_SEGMENTS s , dba_tables t
WHERE s.owner='JDIETZ'
and s.segment_name = t.TABLE_NAME 
--and tablespace_name='DYNAMIC_BIG'
--AND SEGMENT_NAME = 'STS_RCRD_IDSA' 
--AND SEGMENT_NAME = 'STAGING_HR_STATUS' 
GROUP BY s.segment_name
--order by mb desc )
order by mb desc;


/* guarantees they're indexes */
select sum(mb) from (
SELECT s.segment_name, SUM(s.bytes)/1048576 MB 
FROM DBA_SEGMENTS s , dba_indexes i
WHERE s.owner='SMC_DATAMART'
and s.segment_name = i.index_NAME 
--and tablespace_name='DYNAMIC_BIG'
--AND SEGMENT_NAME = 'STS_RCRD_IDSA' 
--AND SEGMENT_NAME = 'STAGING_HR_STATUS' 
GROUP BY s.segment_name
order by mb desc )

/* total sum of all tables for a user */
select sum(MB) from (
SELECT s.segment_name, SUM(s.bytes)/1048576 MB 
FROM DBA_SEGMENTS s , dba_tables t
WHERE s.owner='PCS'
and s.segment_name = t.TABLE_NAME 
--and tablespace_name='DYNAMIC_BIG'
--AND SEGMENT_NAME = 'STS_RCRD_IDSA' 
--AND SEGMENT_NAME = 'STAGING_HR_STATUS' 
GROUP BY s.segment_name
order by mb desc )


/* Same: where 2048 is your server block size */ 
select segment_type, segment_name, blocks*2048/1024 "KB" 
from dba_segments where segment_name='BOSS_TEST'; 

/* same: from extents */ 
column segment_name format a30 
set pagesize 10000 
select segment_name,sum(bytes) bytes 
from dba_extents 
where (segment_name like 'SDS%' or segment_name like 
'EWSI%') 
group by segment_name 
order by bytes desc 

/* Exact data usage.  Also the way to get average row 
size */ 
/* depends on uptodate stats!) */ 
select owner, table_name, last_analyzed, num_rows, 
avg_row_len, (num_rows * avg_row_len) as sizeKB 
from dba_tables 
where table_name in 
('CRNT_RFRNC_DATA','GVRNMNT_ORGNZTN','DTY_STN','DT','LD') 
order by owner,table_name; 

/* gets num rows, avg row size and total space used 
for all tables */ 
/* (depends on updated stats!) */ 
set pagesize 1000 
column totsize format 9,999,999,999,999 
select table_name, num_rows, avg_row_len, (num_rows * 
avg_row_len) totsize 
from dba_tables 
where (table_name like 'SDS%' or table_name like 
'EWSI%') 
and num_rows > 0 
order by totsize desc 

/*************************************************************/ 
/* Space usage for indexes (counts the extents) 

/* for one specifically */ 
SELECT   owner, 
         segment_name, 
         segment_type, 
         tablespace_name, 
         bytes/1048576 MB, 
         initial_extent, 
         next_extent, 
         EXTENTS, 
         pct_increase 
FROM     DBA_SEGMENTS 
WHERE OWNER = 'DW' 
AND SEGMENT_NAME = 'IEMPLY_PYN312' 
GROUP BY segment_name; 
--AND SEGMENT_TYPE = 'INDEX' 

SELECT SUM(bytes)/1048576 MB 
FROM DBA_SEGMENTS 
WHERE owner='DW' 
AND SEGMENT_NAME = 'IEMPLY_PYN312' 
GROUP BY segment_name; 




-- size of all indexes for a set of tables.
SELECT i.table_name, SUM(s.bytes)/1048576 MB 
FROM DBA_SEGMENTS s , dba_indexes i
WHERE 
s.segment_name = i.index_NAME 
and i.owner = 'CADMIRROR'
and lower(i.table_name) in (
'aeven',
'agenc',
'cd_crews',
'cd_units',
'event',
'trans_desc',
'un_hi',
'xref',
'divert')
--and tablespace_name='DYNAMIC_BIG'
--AND SEGMENT_NAME = 'STS_RCRD_IDSA' 
--AND SEGMENT_NAME = 'STAGING_HR_STATUS' 
GROUP BY i.table_name
order by mb desc 





/*************************************************************/ 
/* shows count of all extents in user */ 

SELECT COUNT(segment_name), segment_name FROM 
DBA_EXTENTS WHERE owner='DW' 
GROUP BY segment_name 
HAVING COUNT(*)>1 
ORDER BY COUNT(segment_name) DESC; 

/*************************************************************/ 
/* Query to determine if fragmenting is occuring in your tablespaces. */ 
/* the more separate segments, the more fragmented */ 

SELECT tablespace_name, count(*) FROM dba_free_space 
GROUP BY 
tablespace_name HAVING count(*) > 0 
order by count(*) desc; 

select tablespace_name,bytes,blocks from 
dba_free_space 
where tablespace_name='SDS_DATA'; 

-- fsfi == Free Space Fragmentation Index; goal is 100%, anything less than
-- 20-30% is bad

SELECT          tablespace_name
,               SQRT(MAX(blocks)/SUM(blocks)) *
                (100/SQRT(SQRT(COUNT(blocks)))) fsfi
FROM            DBA_FREE_SPACE
--WHERE tablespace_name='STATIC'
GROUP BY        tablespace_name
ORDER BY        1;


select *
    from dba_free_space
   where tablespace_name = 'T'
     and bytes <= ( select min(next_extent)
              from dba_segments
             where tablespace_name = 'T')
   order by block_id



/*************************************************************/ 
/* fragmenting by table: 

/* Lists number of extents used by table */ 
column owner format a10 
column segment_name format a30 
set pagesize 100 
SELECT owner,segment_name,extents FROM dba_segments 
WHERE segment_type = 'TABLE' 
and owner not in ('SYS','SYSTEM') 
AND extents > 1 
order by extents desc; 

/* same query for indexes */ 
column segment_name format a30 
set pagesize 100 
SELECT i.table_name,s.segment_name,s.extents 
FROM dba_segments s, dba_indexes i 
WHERE s.segment_name = i.index_name 
and s.segment_type = 'INDEX' 
and s.owner not in ('SYS','SYSTEM') 
AND s.extents > 1 
order by s.extents desc 

/* this lists row by row for your table ... you can eyeball */ 
/* and see if the rows are contiguous */ 

select dbms_rowid.rowid_to_restricted(rowid,0) 
from bender.sds_activity 
order by 1; 

/* this lists block by block w/ the number of rows ... if */ 

select distinct dbms_rowid.rowid_block_number(rowid) 
"BLOCK NUMBER", 
count(*) 
from bender.sds_activity 
group by dbms_rowid.rowid_block_number(rowid) 









/*************************************************************/ 
/* Prints out numbers of sorts by type: can be used */ 
/* to help size sort_area_size.  Commented out line 
will */ 
/* print out optimal/multipass/onepass sorting stats 
*/ 

/* (note: this ONLY gets sorts for your current 
session; useless! */ 

select n.name,s.value 
from v$statname n,v$sesstat s 
where n.statistic#=s.statistic# 
and n.name like '%sort%' 
-- and n.name like '%workarea executions%' 
and s.sid in (select sid from v$session 
              where 
audsid=sys_context('USERENV','SESSIONID')) 

/* system wide sorting info: lookign at the ratio of 
disk to memory sorts */ 
col name format a30 
select * from v$sysstat where name like '%sort%'; 




/*************************************************************/ 
/* Get info on shared_pool */ 

col pool_size format 9,999,999,999 
select sum ( bytes ) pool_size 
from v$sgastat 
where pool = 'shared pool'; 

show parameter pool; 

/* look for misses, failures */ 
select free_space,free_count,requests,request_misses, 
request_failures from v$shared_pool_reserved; 

/* prints SGA size */ 
show sga; 


/* how much keep pool am I consuming? */
SELECT sum(count(1))*32768 as "keep pool consumption"
  FROM DBA_OBJECTS o, V$BH bh, dba_tables t
 WHERE o.object_id  = bh.objd
--   AND o.owner    ='DW'
   and o.object_name = t.table_name
   and t.buffer_pool='KEEP'
 GROUP BY o.object_name

/* list of all tables in the keep pool */
SELECT o.object_name, COUNT(1) number_of_blocks, count(1)*32768 bytes
  FROM DBA_OBJECTS o, V$BH bh, dba_tables t
 WHERE o.object_id  = bh.objd
--   AND o.owner    ='DW'
   and o.object_name = t.table_name
   and t.buffer_pool='KEEP'
 GROUP BY o.object_name
 ORDER BY count(1) desc


/*************************************************************/ 
/* SQL statements to determine how much of a buffer cache your */ 
/* tables are taking up (adapted from Oracle P&T manuals */ 

select data_object_id, object_type from user_objects 
where object_name='SDS_CONTRACT'; 

select count(*) buffers from v$bh where objd = 3733; 

(then divide this result by total buffers) 

select value "total buffers" from v$parameter 
where name='db_block_buffers'; 
show parameter db_block_buffers; 

OR 

--create table vbh as 
delete from vbh; 
insert into vbh 
select objd , count(objd) count 
from v$bh 
group by objd 
having 
(count(objd)> 500) 
order by count(objd) desc; 



column pct format 99.99 
column object_name format a25 
column table_name format a25 
column object_type format a15 
set linesize 120 
set pagesize 100 
set null n/a 
select 
o.object_name,o.object_type,i.table_name,v.count, 
/* 8i version: (v.count / (select value from 
v$parameter where name='db_block_buffers')*100) pct */ 
(v.count / (select value from v$parameter where 
name='db_cache_size')*100) pct 
from all_objects o, vbh v, all_indexes i 
where v.objd = o.data_object_id 
and i.index_name (+)= o.object_name 
--and v.count > 1000 
--order by v.count desc; 
order by pct desc; 

delete from vbh; 

drop table vbh; 

/* Monitor buffer pool usefulness: the hit ratio will be */ 
/* 1 - physical reads/(block gets + consistent gets) */ 

select id, name, block_size, physical_reads, 
db_block_gets, consistent_gets 
from v$buffer_pool_statistics; 




-- Advise for cache sizing?
select size_for_estimate,buffers_for_estimate,estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name='KEEP'




/*************************************************************/ 
/* reading from 11g's new SQL Results Cache result_cache */ 

select * from v$result_cache_objects;

select * from v$result_cache_statistics;

select * from v$result_cache_memory;

select * from v$result_cache_dependency;








/*************************************************************/ 
/* find out which tables are candidates to up inittrans */ 

select * from v$segment_statistics 
where STATISTIC_NAME='ITL waits' and value>0 

/*************************************************************/ 
/* find out which tables are most read in the database by */

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads
FROM (SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') lr,
 (SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') pr,
dba_tables t
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
  AND lr.owner=t.owner AND lr.object_name=t.table_name
ORDER BY 3 desc;



/*************************************************************/ 
/* Rows returned below mean that UNDO_RETENTION needs 
to be increased */ 

select * from v$undostat where UNXPSTEALCNT > 0 or 
SSOLDERRCNT > 0; 



/*************************************************************/ 
/* Rows returned below mean that space needs to be added to the undo */ 
/* tablespace.  All space in the tablespace was used and no free space  *.
/* was available when requested */ 

select * from v$undostat where NOSPACEERRCNT > 0; 

/*************************************************************/ 
/* find biggest user of redo blocks */ 


SELECT MODULE, s.SID, osuser, sql_hash_value, VALUE / 
(SYSDATE - logon_time) redo 
FROM v$session s, v$sesstat ss, v$statname sn 
WHERE s.SID = ss.SID 
AND ss.statistic# = sn.statistic# 
AND NAME = 'redo size' 
ORDER BY redo DESC; 

/* same? */ 
SELECT SID, VALUE 
FROM 
    (SELECT s.SID,s.VALUE 
        FROM v$sesstat s, v$statname n 
        WHERE s.statistic#=n.statistic# AND 
        n.NAME='redo blocks written' 
        ORDER BY VALUE DESC); 




/*************************************************************/ 

/* This will print out a suggested value for 
OPTIMIZER_INDEX_COST_ADJ */ 
/* set it to db file sequential reads / db file 
scattered reads * 100 */ 

SELECT EVENT, 
  AVERAGE_WAIT 
FROM V$SYSTEM_EVENT 
WHERE EVENT LIKE '%db file%' 

/*************************************************************/ 
/* this prints out all undocumented/hidden parameters 
in your oracle server */ 
/* stolen from rda.sh scripts */ 

-- Note; commented out x$ksppcv call in 9i, only exists in 8i 
-- this works fine from 9->11
column parameter_name format a40 
column session_value format a20 
column system_value format a20 
SELECT substr(a.ksppinm,0,512) as Parameter_name, 
      --NVL(SUBSTR(b.ksppstvl,0,512), 'null value') as Session_value, 
      NVL(SUBSTR(c.ksppstvl,0,512), 'null value') as System_value 
      FROM x$ksppi a, 
           --x$ksppcv b, 
           x$ksppsv c 
      where 
         --a.indx = b.indx and 
         a.indx = c.indx 
         and substr(a.ksppinm,1,1) = '_' 
and a.ksppinm like '%ignore%'
      ORDER BY a.ksppinm 


-- 11g version
SELECT 
    a.ksppinm name
    , b.ksppstvl value
    , b.ksppstdf deflt
    , DECODE(a.ksppity, 
        1, 'boolean', 
        2, 'string', 
        3, 'number', 
        4, 'file', 
        a.ksppity) type
    , a.ksppdesc description
FROM
    sys.x$ksppi a,
    sys.x$ksppcv b
WHERE
    a.indx = b.indx
    --AND a.ksppinm like '\_%param_keyword%' escape '\'
ORDER BY
    NAME
;



/*************************************************************/ 
/* Gets first and last datetime stamps in v$sqlarea */ 
/* this is also a neat way to get the boot date/time 
of the server */ 

select min(first_load_time), max(first_load_time) from 
v$sqlarea 



/*************************************************************/ 

/* SQL in shared pool w/ high disk reads / executions ratio */ 
/* stolen from rda.sh scripts */ 

select sql_text,executions,disk_reads, 
disk_reads/decode(executions,0,1,executions)/50 
from v$sqlarea 
where disk_reads / 
decode(executions,0,1,executions)/50 > 100 
and executions > 1 
order by executions desc 

/* SQL in shared pool w/ high buffer gets / executions ratio */ 
/* stolen from rda.sh scripts */ 

select sql_text,executions,buffer_gets, 
buffer_gets / decode(executions,0,1,executions)/500 
from v$sqlarea 
where disk_reads / 
decode(executions,0,1,executions)/500 > 100 
and executions > 1 
order by executions desc 

/* SQL in shared pool w/ more than 15 loads */ 
/* stolen from rda.sh scripts */ 

select sql_text, loads 
from v$sqlarea 
where loads > 15 
order by loads desc; 

/* shows frequently run sql out of v$sql */ 
select executions,sql_text from v$sql where executions 
> 300000 
order by executions desc; 

/* Total disk reads */ 
SELECT disk_reads, executions, disk_reads/executions, 
sql_text 
FROM v$sqlarea WHERE disk_reads > 500000 ORDER BY 
disk_reads desc; 

/* total buffer gets */ 
SELECT buffer_gets, executions, 
buffer_gets/executions, sql_text 
FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY 
buffer_gets desc; 

/* Shows optimizer decisions across all sql commands executed */ 
/* Not sure how useful this is though... */ 

select optimizer_mode, count(*) from v$sql group by optimizer_mode; 

/* The most useful one: gets information needed */ 
SELECT sql_text,executions,disk_reads,buffer_gets 
FROM v$sqlarea--,dba_users 
--where disk_reads / decode(executions,0,1,executions)/500 > 100 
where executions > 1000 
ORDER BY executions DESC 

SELECT u.username,v.sql_text,v.executions,v.disk_reads,v.buffer_gets 
FROM v$sqlarea v,dba_users u 
--where disk_reads / decode(executions,0,1,executions)/500 > 100 
WHERE u.user_id = v.parsing_user_id 
AND u.username NOT IN ('SYSTEM','SYS','MDSYS','SYSMAN','EXFSYS','DBSNMP','ORAMON') 
ORDER BY executions DESC 


/* 10g version */

select s.sql_id, s.plan_hash_value, u.username, s.sql_text, s.last_active_time, 
round(s.elapsed_time/(1000000*60),2) elapsed_mins,round(s.cpu_time/(1000000*60),2) cpu_mins,
s.buffer_gets, s.rows_processed, s.executions, s.user_io_wait_time, s.total_sharable_mem
from v$sqlstats s, v$sqlarea v,dba_users u 
WHERE u.user_id = v.parsing_user_id 
AND u.username NOT IN ('SYSTEM','SYS','MDSYS','SYSMAN','EXFSYS','DBSNMP','ORAMON') 
and s.sql_id = v.sql_id
and s.elapsed_time > 1000000 -- this only gets jobs that lasted more than one second
-- and rownum < 30
and s.last_active_time > sysdate - 0.01041666 -- 1 is a day, .04166 is last hour, 0.01041666 is last 15 mins
-- and sql_text like '%DEPT%'
order by s.last_active_time desc;





/*************************************************************/ 

/* Shows a list of interesting server level stats, 
including the */ 
/* total number of commits and rollbacks */ 

select 
   name, 
   value 
from 
   sys.v_$sysstat 
where 
   name in 
    ('consistent changes', 
     'consistent gets', 
     'db block changes', 
     'db block gets', 
     'physical reads', 
     'physical writes', 
     'sorts (disk)', 
     'user commits', 
     'user rollbacks' 
    ) 
 order by 
1; 

/* queries to tell what segments are on what data files?  useless? */ 

select file_id, file_name from dba_data_files where 
tablespace_name='SDS_DATA'; 

select segment_name, tablespace_name, header_file from 
dba_segments 
where owner='BENDER' and tablespace_name='SDS_DATA' 
and rownum <= 5; 




/* One guy's attempt to define starting value for optimizer_index_avg_cost 
only problem is ... a.average_wait came out 0, so I had to add the decode 
 line to make this even complete */ 

col c1 heading 'Average Waits for|Full Scan Read I/O' 
      format 999.999 
col c2 heading 'Average Waits for|Index Read I/O'     
      format 999.999 
col c3 heading 'Percent of| I/O Waits|for Full Scans' 
      format 9.99 
col c4 heading 'Percent of| I/O Waits|for Index Scans' 
      format 9.99 
col c5 heading 
'Starting|Value|for|optimizer|index|cost|adj' format 
999 

select 
   a.average_wait                                  c1, 
   b.average_wait                                  c2, 
   a.total_waits /(a.total_waits + b.total_waits)  c3, 
   b.total_waits /(a.total_waits + b.total_waits)  c4, 
   (b.average_wait / 
decode(a.average_wait,0,1,NULL,1,a.average_wait))*100 
         c5 
--   a.total_waits /(a.total_waits + b.total_waits) * 
--   (b.average_wait / a.average_wait)*100          
c6 
from 
   v$system_event  a, 
   v$system_event  b 
where 
   a.event = 'db file scattered read' 
and 
   b.event = 'db file sequential read' 
/ 

/* Pull some important stats that the CBO looks at per 
index */ 
/* if the avg's are high, optimizer won't like doing 
range scans */ 
/* on the field in question */ 

SELECT  index_name, NUM_ROWS, 
        DISTINCT_KEYS, 
        AVG_LEAF_BLOCKS_PER_KEY, 
        AVG_DATA_BLOCKS_PER_KEY, 
        LAST_ANALYZED 
FROM    DBA_INDEXES 
WHERE   table_name = 'SDS_LAB_SESSION' 



/* sp_help_index query: shows table, index and columns 
*/ 

set linesize 120 
set pagesize 100 
select table_name,index_name,substr(column_name ,0,30) 
col 
from user_ind_columns where table_name='EWSI_STUDENT' 
order by table_name,index_name; 

/* shows current and previous sql per user (sid) */ 

col sid form 999 
set pagesize 100 
set linesize 120 
col curr form a40 head "     Current SQL" 
col prev form a40 head "     Previous SQL" 
bre on sid skip 2 
select a.sid sid,d.schemaname,max(b.sql_text) curr,max(c.sql_text) prev 
from v$session a, v$sql b,v$sql c, v$session d 
where a.sql_address=b.address 
and a.prev_sql_addr=c.address 
and a.sid = d.sid
--and sid > 50 
group by a.sid , d.schemaname
/ 

/* Buffer Cache Hit Ratio (BCHR) */ 

col bchr format 99.9999 
select 'bc_hit_ratio' ratio,( sum(decode(name, 
'consistent gets',value,0)) 
+ sum(decode(name,'db block gets', value,0)) 
- sum(decode(name,'physical reads', value,0))) 
/ ( sum(decode(name, 'consistent gets',value,0)) 
  + sum(decode(name,'db block gets', value,0)) ) * 100 
as bchr 
from v$sysstat; 

-- all the component parts
select sum(decode(name, 'consistent gets',value,0)) as consistentgets,
       sum(decode(name,'db block gets', value,0)) as dbblockgets, 
       sum(decode(name,'physical reads', value,0)) as physicalreads,
       'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) 
+ sum(decode(name,'db block gets', value,0)) 
- sum(decode(name,'physical reads', value,0))) 
/ ( sum(decode(name, 'consistent gets',value,0)) 
  + sum(decode(name,'db block gets', value,0)) ) * 100 
as bchr 
from v$sysstat; 



/* shows index leaf node splits */ 

col name format a25 
SELECT n.name, s.value, n.class 
FROM v$mystat s, v$statname n 
WHERE 
  n.name IN ('leaf node splits', 
             'branch node splits', 
             'consistent gets', 
             'db block gets', 
             'db block changes') 
AND n.statistic# = s.statistic#; 







/* Calculate a table's High Water Mark (HWM) */ 
/* these depend on uptodate statistics */ 

column segment_name format a30 
SELECT segment_name,BLOCKS FROM DBA_SEGMENTS  
WHERE SEGMENT_NAME in 
('SDS_PCK_ERRORS', 
'SDS_PCK_ERRORS','IMPORT_SNC_STUDENTS', 
'SDS_CP_CONTRACT','SDS_CP_BOOK','SDS_LAB_SESSION', 
'IMPORT_TMP_ACTIVITY_STUDENTS') 
-- and owner='bender' 
order by blocks desc 
/ 

set null null 
--set pause on 
SELECT table_name, EMPTY_BLOCKS FROM DBA_TABLES 
WHERE OWNER='BENDER' 
and empty_blocks is not null 
and empty_blocks > 514 
--table_name = 'SDS_LAB_SESSION' 
order by empty_blocks desc 
/ 

-- AND Thus, the tables' HWM = (query result 1) - 
(query result 2) - 1 



/* shows all the important storage parameters for a 
table */ 

select tablespace_name,INITIAL_EXTENT, 
NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE 
from dba_tablespaces 
where tablespace_name = 'SDS_DATA'; 

set null '' 
set linesize 120 
set pagesize 100 
column free format 999 
column used format 999 
column min format 999 
column pct format 999 
select table_name, PCT_FREE free, PCT_USED used, 
--INI_TRANS, MAX_TRANS, 
INITIAL_EXTENT iext, NEXT_EXTENT next, MIN_EXTENTS 
min, MAX_EXTENTS max, 
PCT_INCREASE pct 
from dba_tables 
--where owner='BENDER' 
where table_name in 
('SDS_LAB_SESSION','SDS_LAB_CONTRACT_INDX', 
'SDS_STUDENT_ACTIVITY','SDS_PCK_ERRORS','SDS_CP_CONTRACT', 
'SDS_ACTIVITY','SDS_CP_BOOK','EWSI_STUDENT','EWSI_SYNC_VILLAGE', 
'SDS_ACT_DATE_SLOT_INDX','SDS_CONTRACT','SDS_ACTIVITY_LEVEL_GROUP', 
'SDS_CONTINUOUS_FOLLOW_UP') 
order by table_name; 

/* same queries for indexes */ 

select tablespace_name,INITIAL_EXTENT, 
NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE 
from dba_tablespaces 
where tablespace_name = 'SDS_INDEX'; 

set null '' 
set linesize 120 
set pagesize 100 
column free format 999 
column used format 999 
column min format 999 
column pct format 999 
select table_name,index_name, 
--INI_TRANS, MAX_TRANS, 
INITIAL_EXTENT iext, NEXT_EXTENT next, MIN_EXTENTS 
min, MAX_EXTENTS max, 
PCT_INCREASE pct 
from dba_indexes 
where owner='BENDER' 
and (table_name like 'SDS%' or table_name like 
'EWSI%') 
--where table_name in 
('SDS_LAB_SESSION','SDS_LAB_CONTRACT_INDX', 
--'SDS_STUDENT_ACTIVITY','SDS_PCK_ERRORS','SDS_CP_CONTRACT', 
--'SDS_ACTIVITY','SDS_CP_BOOK','EWSI_STUDENT','EWSI_SYNC_VILLAGE', 
--'SDS_ACT_DATE_SLOT_INDX','SDS_CONTRACT','SDS_ACTIVITY_LEVEL_GROUP', 
--'SDS_CONTINUOUS_FOLLOW_UP') 
order by table_name,index_name; 



/* list of compiled packages in cache */ 
col owner form a8 
col name form a30 
col type form a12 
col sharable_mem  form 9999999 heading "Size" 
set linesize 120 
set pagesize 100 
SELECT owner,name,type ,sharable_mem ,executions,loads 
   
FROM v$db_object_cache 
  WHERE type in ('TRIGGER','PROCEDURE','PACKAGE 
BODY','PACKAGE') 
   AND  executions > 0 
   AND kept='NO' 
  ORDER BY executions desc, 
           loads desc, 
           sharable_mem desc 
/ 

/* Lists the tables and the number of indexes per 
table */ 
select table_name,count(table_name) 
from dba_indexes 
where owner='BENDER' 
and (table_name like 'SDS%' or table_name like 
'EWSI%') 
group by table_name 
order by count(table_name) desc; 

/* Lists all indexes and a cardinality percentage */ 
set null '' 
col table_name format a30 
column index_name format a30 
column pct_crd format 999.99999 
column num_rows format 9999999999 
select table_name,index_name,num_rows,distinct_keys, 
(distinct_keys/decode(num_rows,0,NULL,num_rows)*100) 
pct_crd 
from dba_indexes 
where table_name like 'SDS%' or table_name like 
'EWSI%' 
--and 
distinct_keys/decode(num_rows,0,NULL,num_rows)*100 < 1 
--order by table_name,index_name 
order by pct_crd; 



/* determine library cache hit ratios */ 
column "LC Hit Ratio" format 999.999999 
SELECT SUM(PINS) EXECS, SUM(RELOADS)MISSES, 
(SUM(pins-reloads))/SUM(pins)*100 "LC Hit Ratio" 
FROM v$librarycache; 

/* determine the data dictionary aka row cache hit 
ratio */ 
column "RC Hit Ratio" format 999.999999 
SELECT SUM(GETS) HITS, SUM(GETMISSES) LIBMISS 
,(SUM(gets-getmisses-USAGE-fixed))/SUM(gets)*100 "RC 
Hit Ratio" 
FROM v$rowcache; 



/* gets a list of all sql in v$sqlarea with more 
parses than executions. */ 
set linesize 120 
set pagesize 100 
column sql_text format a100 
select parse_calls, executions, sql_text 
from v$sqlarea 
where parse_calls > executions; 




/* Diagnosing buffer busy waits */ 
select event, p1, p2 from v$session_wait where event 
like '%buffer%'; 

then, using p1 and p2 from above, this will tell you 
the object. 

select segment_name, segment_type, owner, 
tablespace_name 
from sys.dba_extents 
where file_id = <p1> 
and <p2> between block_id and (block_id+(blocks-1)); 

select a.sid, 
    decode(command,0,'None',2,'Insert',3,'Select', 
            6,'Update',7,'Delete',10,'Drop 
Index',12,'Drop Table', 
            45,'Rollback',47,'PL/SQL',command) 
command, 
    event,p1,p2,p3,state,wait_time 
from v$session_wait a,V$session b 
where b.sid=a.sid 
and (a.sid>10 and event not in('SQL*Net message from 
client', 
                   'SQL*Net message to client') 
or (a.sid<=10 and event not in ('rdbms ipc 
message','smon timer', 
   'pmon timer','SQL*Net message from client'))) 
order by decode(event,'pipe get','A',event),p1,p2 



/* Diagnosting latch contention */ 
column ratio format .999999999 
select name, child#, sleeps/gets ratio from 
v$latch_children 
where name = 'cache buffers lru chain' 



/* Convoluted query to display what sql is using 
Rollback space */ 
select s.username, s.sid, st.sql_text, rn.name, 
rs.extents ,rs.status, 
t.used_ublk, t.used_urec ,do.object_name, 
do.subobject_name 
from v$transaction t ,v$session s ,v$sqltext st 
,v$rollname rn 
,v$rollstat rs ,v$locked_object lo ,dba_objects do 
where t.addr = s.taddr 
and s.sql_address = st.address 
and t.xidusn = rn.usn 
and rn.usn = rs.usn 
and t.xidusn = lo.xidusn(+) 
and do.object_id = lo.object_id 
order by s.username, s.sid, st.address, st.piece, 
do.object_name, do.subobject_name 

/* sp_block, sp_lock emulation */ 

select * from dba_waiters 
select * from dba_blockers 



/************************************************************************************/ 

-- Constraints queries

-- All PK constraints
select * from dba_constraints where owner='DM_BDGT' and constraint_type='P';


/* this gets all constraints directly on a particular table, with the PK table */ 

-select 'alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name || ';'
--select 'alter table ' || table_name || ' modify constraint ' || constraint_name || ' rely novalidate;'  
--select c.table_name, c.constraint_name, c.owner,  c.r_owner, p.table_name as r_tablename, c.r_constraint_name, c.status 
from dba_constraints c, 
(select owner, constraint_name, table_name from dba_constraints where owner='DM_TRVL' and constraint_type='P') p
where c.r_constraint_name = p.constraint_name
and  c.owner='DM_TRVL' 
and c.constraint_type='R' 
--and c.table_name in ('FACT_BDGT','FACT_SPNDG')
--('PRSN','EMPLY_BNFT','EMPLY_PSTN','EMPLY','PRSNL_ACTN','EMPLY_PY','EMPLY_LV','ANTNT')
;




/* this gets all constraints that reference a particular table */ 
select table_name, constraint_name 
--owner, constraint_name, table_name, 
--r_owner, r_constraint_name, status 
from dba_constraints 
where owner='DM_BDGT' and r_constraint_name in 
   (select constraint_name    
    from dba_constraints 
    where owner='DM_BDGT' and constraint_type = 'P' 
    and table_name = 'DIM_ALLOT');   


-- this will disable all FK constraints on a table
select 'alter table dm_bdgt.' || table_name || ' disable constraint ' || constraint_name || ';'
from dba_constraints where owner='DM_BDGT'  and constraint_type='R'
order by table_name, constraint_name;




/************************************************************************************/ 
/* Cursor monitoring */ 

select * from v$parameter where name like '%cursor%';

SELECT COUNT(*) FROM v$open_cursor; 

SELECT * FROM v$open_cursor ORDER BY SID; 

SELECT DISTINCT(SID) FROM v$open_cursor WHERE SID NOT 
IN 
(SELECT DISTINCT(SID) FROM v$session); 

-- number of cursors per session
SELECT COUNT(SID) COUNT, SID FROM v$open_cursor GROUP 
BY SID ORDER BY COUNT DESC; 

SELECT * FROM v$session ORDER BY SID; 




/************************************************************************************/ 
/* Row chaining/Row migration diagnosis */ 

/* lists all tables with issues; depends on updated 
stats* 
SELECT * FROM dba_tables WHERE owner='STG' and 
chain_cnt > 0 

/* lists absolute max size of a column ... tells you 
if in jeopardy */ 
SELECT SUM(data_length) FROM dba_tab_cols WHERE 
owner='STG' AND table_name='STS_RCRD_IDSA' 

/* lists chained rows into a table */ 
/* first run $ORACLE_HOME/rdbms/admin/utlchain.sql */ 
analyze table table list chained rows into 
chained_rows; 

/* select count(*) from suspected table and watch this 
stat ... */ 
SELECT * FROM v$sysstat WHERE NAME = 'table fetch 
continued row' 

/* then try CTAS w/ the table; this proves whether its 
migrated or chained */ 



/************************************************************************************/ 
/* What files are in hotbackup mode? */ 

SELECT d.file_name, d.tablespace_name, b.status 
FROM v$backup b, DBA_DATA_FILES d 
WHERE b.FILE# = d.file_id 
ORDER BY tablespace_name, file_name 



/************************************************************************************/ 

SET LONG 2000000
SET PAGESIZE 0
select dbms_metadata.get_ddl('TABLE','DW','F_EMPLOYEE_HISTORY') 
from dual ;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','DIM_ABC_GROUP','DMSGR_EX')
from dual

select dbms_metadata.get_ddl('USER','DW_ADMIN') 
from dual ;

/


/************************************************************************************/ 
/* lisa spory's query to show who's holding locks on objects; also allows deadlock diag */

SELECT SID
, username
, lockwait
, row_wait_obj#
, row_wait_row#
, row_wait_block#
, row_wait_file#
, owner, object_name
FROM v$session
, dba_objects
WHERE v$session.row_wait_obj# = dba_objects.object_id


* /* from lazydba disc user "Muziwandile" 8/1/07 */

SELECT DISTINCT O.OBJECT_NAME,
                SH.USERNAME||'('||SH.SID||')' "Holder",
                SW.USERNAME||'('||SW.SID||')' "Waiter",
                DECODE(LH.LMODE, 1, 'null',
                                 2, 'row share',
                                 3, 'row exclusive',
                                 4, 'share',
                                 5, 'share row exclusive' ,
                                 6, 'exclusive') "Lock Type"
FROM all_objects o,
     v$session sw,
     v$lock lw,
     v$session sh,
     v$lock lh
WHERE lh.id1  = o.object_id
AND lh.id1  = lw.id1
AND sh.sid  = lh.sid
AND sw.sid  = lw.sid
AND sh.lockwait is null
and  sw.lockwait is not null
and  lh.type = 'TM'
and  lw.type = 'TM';



/************************************************************************************/ 
/* sql to see if something is blocking an alter tablespace read only request for TTS */

select sql_text, saddr
from v$sqlarea, v$session
where v$sqlarea.address = v$session.sql_address
and sql_text like '%alter tablespace%'
and sql_text not like 'select sql_text, saddr%';

-- grab the saddr, then run this

select ses_addr, start_scnb
from v$transaction
order by start_scnb;

column username format a15 
column sid format 999 
column serial# format 99999 
column machine format a15 
column osuser format a10 
column status format a9 
column program format a30 
set linesize 150 
set pagesize 100 
select sid,serial#,saddr,substr(saddr,13,4) as end,status,username,osuser,machine,program 
from v$session 
order by status, username; 

alter system kill session '15,60672';


-- anything ABOVE your saddr is blocking your attempt.  alter system kill session them.

/************************************************************************************/ 
/* two queries to run to confirm TTS is possible */
/* these can also diagnose ORA-14407 errors (trying to drop TS when partitioned
tables still have partitions located within */

-- as sys
grant execute_catalog_role to tts;

-- as tts or whatever user you select
exec sys.dbms_tts.transport_set_check('staging_holding2',true);
SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;

/************************************************************************************/ 
/* RMAN enqueue holding query */

select s.sid, username, program, module, action, logon_time
from v$session s, v$enqueue_lock l
where l.sid = s.sid and l.type = 'CF' and l.id1 = 0 and l.id2 = 2; 


/************************************************************************************/ 
/* data dictionary version query */
column comp_id format A10
column version like comp_id
column comp_name format A30
set pagesize 0
select comp_id, status, version, comp_name from dba_registry order by 1;


/************************************************************************************/ 
/* Tom Kyte scripts to report tablespace sizes (by datafile) and then */
/* generate the alter database datafile resize commands */
/* run all three in order.  */

/* 7/13/05: somethings in HP servers breaks these queries, must investigate. 8/2: might
be the fact that tables can have differing block sizes than the server block size... */

/* 5/30/07: these seem to be broken in 10g.  Use code further down */

set pagesize 9999
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

set linesize 120
column cmd format a100 word_wrapped

select 'alter database datafile ''' || file_name || ''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/ 


/*************************************************************/ 
/* 10g version of the above, from Metalink Note: 130866.1 */

set serveroutput on
execute dbms_output.enable(2000000);

declare
  cursor c_dbfile is
        select  tablespace_name
                ,file_name
                ,file_id
                ,bytes
        from    sys.dba_data_files
        where   status !='INVALID'
        order   by tablespace_name,file_id;

  cursor c_space(v_file_id in number) is
        select block_id,blocks
        from   sys.dba_free_space
        where  file_id=v_file_id
        order  by block_id desc;

blocksize       binary_integer;
filesize        number;
extsize         number;

begin

/* get the blocksize of the database, needed to calculate the startaddress */
 
  select value 
  into   blocksize
  from   v$parameter 
  where  name = 'db_block_size';

/* retrieve all datafiles */

  for c_rec1 in c_dbfile
  loop
    filesize := c_rec1.bytes;
    <<outer>>
    for c_rec2 in c_space(c_rec1.file_id)
    loop
      extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
      if extsize = filesize
      then
        filesize := (c_rec2.block_id - 1)*blocksize;
      else
        /* in order to shrink the free space must be uptil end of file */
        exit outer;
      end if;
    end loop outer;
    if filesize = c_rec1.bytes
    then
      dbms_output.put_line('Tablespace: '
      ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
      dbms_output.put_line('Can not be resized, no free space at end of file.');
      dbms_output.put_line('.');
    else
      if filesize < 2*blocksize
      then
        dbms_output.put_line('Tablespace: '
        ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
        dbms_output.put_line('Can be resized to: '||2*blocksize
        ||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
        dbms_output.put_line('.');
      else
        dbms_output.put_line('Tablespace: '
        ||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
        dbms_output.put_line('Can be resized to: '||filesize
        ||' Bytes, Actual size: '||c_rec1.bytes);
        dbms_output.put_line('.');
      end if;
    end if;
  end loop;
end;

/

/* this is from Note:237654.1, which prints out tables/objects beyond the
limit of the datafile you want to shrink */

SELECT owner, segment_name, segment_type, tablespace_name, file_id,
((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) end_of_extent_is_at_this_byte
FROM dba_extents
WHERE ((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > 
(100*1024*1024) /* change first value to be <needed size in MB> */
AND tablespace_name='DW'
ORDER BY file_id, end_of_extent_is_at_this_byte;

/* To receive only the objects that have extents beyond the size in question, 
use the following query: if 0 rows returned your desired resize will work */

SELECT DISTINCT owner, segment_name, segment_type, tablespace_name, file_id
FROM dba_extents
WHERE ((block_id+1)*(SELECT value FROM v$parameter
WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > 
(<needed size in MB>*1024*1024)
AND tablespace_name='<tablespace_name>'
ORDER BY file_id, owner, segment_name, segment_type;






/********************************************* */
/* find object-level grants to users; */

Select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
From dba_tab_privs
Where grantee = 'TTS';

set pagesize 0
set long 90000
select dbms_metadata.get_granted_ddl('ROLE_GRANT','DW_ADMIN') as grants from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','DW_ADMIN') as grants from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','DW_ADMIN') as grants from dual;



/********************************************* */
/* finds table differences; fixed to find null vs nonnull */
/* used to diagnose ORA-14097 on ALTER TABLE EXCHANGE PARTITION issues */
/* will find columns not in the right order */
/* you could put in owners and change to be dba_ views for tables in diff. schemas */

select a.column_id "COLUMN_ID", a.table_name "TABLE_NAME",
       a.column_name, a.data_type, a.data_length, a.nullable
from dba_tab_columns a, dba_tab_columns b
where a.column_id (+) = b.column_id
and (a.data_type != b.data_type
    or a.data_length != b.data_length
    or a.nullable != b.nullable)
and a.table_name = upper('f_trng')
and a.owner = 'DW'
and b.table_name = upper('f_trng_64')
and b.owner='TTS'
union
select b.column_id "COLUMN_ID", b.table_name "TABLE_NAME",
       b.column_name, b.data_type, b.data_length, b.nullable
from dba_tab_columns a, dba_tab_columns b
where b.column_id (+) = a.column_id
and (a.data_type != b.data_type
    or a.data_length != b.data_length
    or a.nullable != b.nullable)
and a.table_name = upper('f_trng')
and a.owner='DW'
and b.table_name = upper('f_trng_64')
and b.owner='TTS'
order by column_name,table_name;

/* ORA-14098 on ALTER TABLE EXCHANGE PARTITION issues; index mismatch diagnosis */

select table_owner,table_name,column_name from dba_ind_columns where table_owner='TTS' and table_name='F_PRSNL_ACT_1155'
and column_name not in
(select column_name from dba_ind_columns where table_owner='DW' and table_name='F_PRSNL_ACT')
union
select table_owner,table_name,column_name from dba_ind_columns where table_owner='DW' and table_name='F_PRSNL_ACT'
and column_name not in
(select column_name from dba_ind_columns where table_owner='TTS' and table_name='F_PRSNL_ACT_1155')




/***************************************/
/* physical i/o for each datafile */

select d.file_id,d.file_name,d.tablespace_name,
f.phyrds,f.phywrts,f.phyblkwrt,f.singleblkrds,f.readtim,f.writetim
from dba_data_files d, v$filestat f
where d.file_id = f.file#
order by tablespace_name,file_name;

select d.tablespace_name tablespace,
d.file_name,f.phyrds,f.phywrts
from v$filestat f, dba_data_files d
where f.file#=d.file_id
and exists (select null
              from dba_segments o
              where o.tablespace_name = d.tablespace_name
                and o.owner = 'DW')
order by tablespace_name
/

/* nice formatted avg_read_time, avg_write_time per datafile */
select
to_char(begin_time,'dd-MON-yy hh:mi') begin_time,
to_char(end_time,'dd-MON-yy hh:mi') end_time,
a.file_id,
file_name,
average_read_time,
average_write_time,
physical_reads,
physical_writes,
physical_block_reads,
physical_block_writes
from sys.v_$filemetric_history a, sys.dba_data_files b
where a.file_id = b.file_id
order by 1,3
/



/* various statistics for a particular object.
select * from V$SEGMENT_STATISTICS where object_name='D_TIME_DAY'

/***************************************/
/* gets all grants for particular users */

set pagesize 0
set long 90000
select dbms_metadata.get_granted_ddl('ROLE_GRANT','DWLOOKUP') as grants from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','DWLOOKUP') as grants from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','DWLOOKUP') as grants from dual;

/***************************************/
/* get a list of all the physical objects in use on a database */

select * from dba_data_files order by file_name;
select * from v$logfile order by group#;
select * from v$log;
select * from dba_temp_files;
select * from v$archive_dest; -- destination field

/***************************************/
/* Shows who has what privileges ... across all roles and grants. */


select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;


select * from dba_role_privs order by grantee;




/***************************************/
/* shows unused space per table; put this in quick t.sql and run it */

VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
--exec DBMS_SPACE.UNUSED_SPACE('DW_ADMIN', 'ETL_ERR_LOG', 'TABLE', :total_blocks, :total_bytes, --:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock);
exec DBMS_SPACE.UNUSED_SPACE('DW_ADMIN', 'TMP_ENCOUNTER_CHG', 'TABLE PARTITION', :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock,'T0596');
print





/*************************************************************/ 
/* dba_jobs/dbms_scheduler queries */

select owner,job_name,job_type,job_action, schedule_name,schedule_type
,TO_CHAR(start_date, 'DD-MON-YYYY') start_date,repeat_interval,
TO_CHAR(end_date, 'DD-MON-YYYY HH24:MI:SS') end_date,run_count,failure_count, auto_drop,comments
 from dba_scheduler_jobs
where owner not in ('SYS','SYSMAN','EXFSYS');

SELECT owner,job_name,job_type,job_action, schedule_name,schedule_type
,TO_CHAR(start_date, 'DD-MON-YYYY') start_date,repeat_interval,
TO_CHAR(end_date, 'DD-MON-YYYY HH24:MI:SS') end_date,run_count,failure_count, auto_drop,comments
 FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

select * from dba_objects where object_name like 'EXP_%'

select window_name,schedule_name,schedule_type,repeat_interval,enabled,comments 
from dba_scheduler_windows;


/*************************************************************/ 
/* auditing */

select 
OS_USERNAME, USERNAME, USERHOST, TIMESTAMP, OWNER, OBJ_NAME, ACTION, ACTION_NAME,
-- COMMENT_TEXT, -- crashes toad
PRIV_USED,SQL_BIND, SQL_TEXT
from dba_audit_trail 
where action_name in ('LOGON','LOGOFF')
order by timestamp desc;

/* 4=deletes, 7=inserts, 11=updates */
select * from dba_audit_object where obj_name='AUDIT_TEST' 
and 
( substr(ses_actions,4,1) <> '-' 
or substr(ses_actions,7,1) <> '-'
or substr(ses_actions,11,1) <> '-' )
order by timestamp desc;

select * from dba_audit_object where obj_name='XREF_MSTR' order by timestamp desc;

select owner,obj_name,os_username,username,userhost,timestamp,
decode(substr(ses_actions,4,1),'-','No','Yes') as Deletes,
decode(substr(ses_actions,7,1),'-','No','Yes') as Inserts,
decode(substr(ses_actions,11,1),'-','No','Yes') as Updates
 from dba_audit_object where obj_name='XREF_MSTR' 
and 
( substr(ses_actions,4,1) <> '-' 
or substr(ses_actions,7,1) <> '-'
or substr(ses_actions,11,1) <> '-' )
order by timestamp desc;

/*************************************************************/ 

select * from v$session_waits; 


-- tanel poder's session wait query; somewhat uselsss.
col sw_event head EVENT for a40
col p1 for 99999999999999999999
select 
    sid, 
    CASE WHEN state != 'WAITING' THEN 'WORKING'
         ELSE 'WAITING'
    END AS state, 
    CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
         ELSE event
    END AS sw_event, 
    seq#, 
    seconds_in_wait, 
    p1,
    p2,
    p3 
from     v$session_wait 
--where sid in (105)
/


select * from v$session_event ;

select * from v$lock;

select * from v$system_event order by time_waited desc;

select * from v$session_event

/* non trivial waits */
select sid,event,seconds_in_wait/60 as mins_wait,state from v$session_wait 
where event not in ('SQL*Net message from client','rdbms ipc message','pmon timer')
and event not like 'Streams%'
order by seconds_in_wait desc;

select * from v$sesstat;

select s.sid, s.statistic#, d.name, s.value
from v$sesstat s, v$sysstat d
where s.statistic# = d.statistic#
order by sid,statistic#;

select * from v$sysstat;

select * from v$event_name;


/*************************************************************/ 
-- Diagnosing data guard failover issues (code from Oracle diag routines)

-- on the primary database:

-- gets the last 5 error messages
select * from (
  select message, timestamp
  from v\$dataguard_status
  where severity in ('Error','Fatal')
  order by timestamp desc)
where rownum < 6;


-- This gets the current log sequence and last one archived; compare to standby
select ads.dest_id,max(sequence#) "Current Sequence",
      max(log_sequence) "Last Archived"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id
and al.dest_id=ads.dest_id
group by ads.dest_id;

select * from v$archived_log where sequence# in (11879,11880,7384,7385) order by sequence#,thread#
select registrar,count(*) from v$archived_log group by registrar;
select * from v$archived_log order by sequence# desc;

-- this gets a list of the archived logs for the failover box (usually dest_id=2)
select name,sequence#,first_time,next_time,completion_time,creator,registrar,standby_dest,archived,
applied,deleted,status 
from v$archived_log where name='DCSSTGP_FAILOVER' order by sequence# desc;

-- shows the last log applied to the data guard standby.  
SELECT MAX (sequence#) last_log_applied
FROM   v$archived_log
WHERE  dest_id = 2
AND    applied = 'YES';

-- This is the current log sequence number on the primary.  If this is one off of the previous
-- query, you are probably fine.
SELECT MAX (sequence#) current_log FROM   v$log;

-- shows which log files were not received on the standby site
-- slow query b/c of the not in; takes 2 minutes on a busy archiving server.
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM 
   (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
    WHERE LOCAL.SEQUENCE# NOT IN 
   (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND 
   THREAD# = LOCAL.THREAD#);  


SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT';

-- then run this on both primary and standby; if not in sync, error
SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY 
WHERE RESETLOGS_CHANGE# in 
  (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;







----------------------------------------------------------
-- on the standby

-- last sequence received and applied
select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied"
from v$archived_log al, v$log_history lh;

-- this is the last log applied
SELECT MAX (sequence#) last_log_applied
FROM   v$log_history;

--- an overview of all the processes involved...
set pagesize 9999
select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

-- any errors that have occurred.
column error format a55 tru 
select dest_id,status,error from v$archive_dest; 

alter session set NLS_DATE_FORMAT = "MM/DD/YYYY HH24:MI:SS";
column message format a80 
select * from (
select message, timestamp 
from v$dataguard_status 
where severity in ('Error','Fatal') 
order by timestamp desc)
where rownum < 6; 



-- Possibly the most simple way to determine if you have problems...
select * from v$archive_gap; 



/*************************************************************/ 
-- hourly log switches.

SELECT to_date(first_time) DAY,       
 to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",       
 to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",       
 to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",       
 to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",     
 to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",  
 to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",  
 to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",  
 to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",  
 to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",  
 to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",  
 to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",  
 to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",  
 to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",  
 to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",  
 to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",  
 to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",  
 to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",  
 to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",  
 to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",  
 to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",  
 to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",  
 to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",  
 to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",  
 to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" 
 from 
 v$log_history  
 where to_date(first_time) > sysdate -7  
 GROUP by 
 to_char(first_time,'YYYY-MON-DD'), to_date(first_time)  
 order by to_date(first_time);










/*************************************************************/ 

-- Working with ASM

-- how do you find out how much ASM space you're taking?
select * from v$asm_disgroup;

select name,total_mb,ROUND(TOTAL_MB/1024) as total_mb,free_mb,ROUND(FREE_MB/1024) as free_mb from v$asm_diskgroup;

-- What is my list of ASM disks?
select group_number,disk_number,name,total_mb,free_mb,path from v$asm_disk;

-- What kind of i/o are my individual ASM disks getting?
select name,total_mb,path, reads,writes,bytes_read,bytes_written,read_time,write_time,read_errs,write_errs from v$asm_disk;

-- What is the striping format configuration for my various database parts in ASM?
select * from v$asm_template;


select * from v$asm_diskgroup where free_mb/total_mb < .20;

select name,
total_mb,ROUND(TOTAL_MB/1024) as total_TB_mirrored, ROUND(TOTAL_MB/1024/2) as total_TB_rough,
free_mb,ROUND(FREE_MB/1024)as free_TB_mirrored, ROUND(FREE_MB/1024/2)as free_TB_rough,
required_mirror_free_mb, usable_file_mb, round((total_mb-free_mb)/1024/2) as Used_TB_rough,
free_mb/total_mb as pct_free
from v$asm_diskgroup;

select ROUND(TOTAL_MB/1024/2) as total_TB_rough, 
round((total_mb-free_mb)/1024/2) as Used_TB_rough, 
ROUND(FREE_MB/1024/2)as free_TB_rough
from v$asm_diskgroup;


select count(*) from v$asm_diskgroup where usable_file_mb < 0;






/*************************************************************/ 
-- Streams.

-- gets list of tables being streamed.
select * from dba_apply_instantiated_objects order by source_object_name;


select * from dba_apply_error order by error_creation_time desc;

select error_message,count(*) from dba_apply_error  group by error_message;

----------------------------------------------------------------------------------
-- to print the error

select 'EXEC oraadmin.print_transaction('''||LOCAL_TRANSACTION_ID||''');'
from dba_apply_error
order by error_creation_time asc;

--Using above output execute following

set pagesize 1000
set serveroutput on size 1000000;
EXEC oraadmin.print_transaction('3.44.1379022');


-------------------------------------------------------------------------------
-- To rerun the transaction

select 'exec DBMS_APPLY_ADM.EXECUTE_ERROR('''||LOCAL_TRANSACTION_ID||''');'
from dba_apply_error
order by ERROR_CREATION_TIME asc;

--Using above output execute following

set serveroutput on size 1000000
exec DBMS_APPLY_ADM.EXECUTE_ERROR('3.44.1379022');

-------------------------------------------------------------------------------
-- To  delete the error; ONLY do this if you are positive the error is resolved.

BEGIN
DBMS_APPLY_ADM.DELETE_ERROR('<LOCAL_TRANSACTION_ID>');
END;
/

set pagesize 1000
select 'EXEC DBMS_APPLY_ADM.DELETE_ERROR('''||LOCAL_TRANSACTION_ID||''');'
from dba_apply_error
order by ERROR_CREATION_TIME asc

begin
EXEC DBMS_APPLY_ADM.DELETE_ERROR('10.32.1360232');
end;
/


/* ------------------------------------------------------------------------------------- */

/* How should I name my fks? */

drop table child cascade constraints;
drop table parent cascade constraints;

create table parent (parent_key integer, name varchar2(5));
ALTER TABLE parent ADD CONSTRAINT  parent_pk PRIMARY KEY (parent_key);
insert into parent values (1,'aaa');
insert into parent values (2,'bbb');
insert into parent values (3,'ccc');

create table child (child_key integer, parent_fk integer, value number);
ALTER TABLE child ADD CONSTRAINT child_pk PRIMARY KEY (child_key);
--ALTER TABLE child  ADD (CONSTRAINT r_parent_1 FOREIGN KEY (parent_fk) REFERENCES parent (parent_key));
ALTER TABLE child  ADD (CONSTRAINT r_child_27 FOREIGN KEY (parent_fk) REFERENCES parent (parent_key));
insert into child values (101,1,5);
insert into child values (110,2,1.3);

insert into child values (123,4,9.99);
/* returnss the following when named r_parent_1
ORA-02291: integrity constraint (BOSSTE.R_PARENT_1) violated - parent key not found

return the following when named r_child_27
ORA-02291: integrity constraint (BOSSTE.R_CHILD_27) violated - parent key not found


Conclusion: name the FK constraint after the PARENT or primary key table, NOT the fk or child table
*/


/*************************************************************/ 
-- licensing, number of CPUs on your machine, etc

SELECT
MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)) CPUS,
MAX(DECODE(STAT_NAME,'NUM_CPU_CORES',VALUE,1)) CPU_CORES,
MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0)) BUSY_TIME,
MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0)) IDLE_TIME,
(MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
3600/100 BUSY_DAYS,
(MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
3600/100 IDLE_DAYS
FROM V$OSSTAT;


SELECT value
FROM gv$osstat
WHERE stat_name = 'NUM_CPUS';


/*************************************************************/ 
-- Working with In-memory/inmemory in 12c


-- in memory buffer will show here.
show sga;

select * from v$parameter where upper(name) like '%INMEMORY%' order by name;

select * from v$sgainfo where name like '%Memory%';

select * from v$sgastat where upper(name) like '%INMEMORY%' order by name;


select * from v$IM_SEGMENTS 
--where owner <> 'BASE';
where populate_status <> 'COMPLETED'
--where segment_name ='STUDENT'
;

select owner,table_name,tablespace_name,num_rows,avg_row_len,blocks,
inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate
from dba_tables
where inmemory<>'DISABLED'
order by 1,2;


select table_owner,table_name,partition_name,tablespace_name,num_rows,avg_row_len,blocks,
inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate
from dba_tab_partitions
where inmemory<>'DISABLED'
order by 1,2,3

select * from v$IM_COLUMN_LEVEL ;

alter system set optimizer_inmemory_aware='FALSE';

alter system set optimizer_inmemory_aware='TRUE';

alter system set INMEMORY_QUERY='ENABLE';

alter system set INMEMORY_QUERY='DISABLE';



/*************************************************************/ 
-- RMAN catalog/catdba database monitoring scripts

-- All failed backup attempts in last 7 days irrespective of database
SELECT db_name,
         status,
         input_type,
         start_time,
         end_time,
         output_device_type,
         output_bytes_display,
         time_taken_display
    FROM RC_RMAN_BACKUP_JOB_DETAILS
   WHERE start_time > SYSDATE - 1
   and status not in ('COMPLETED')
   --and input_type not in ('RECVR AREA')
ORDER BY NVL (end_time, '01-JAN-1900') DESC;

select * from RC_RMAN_BACKUP_JOB_DETAILS;

-- RMAN configuration parameters
select * from rc_rman_configuration;

-- RMAN catalog databases registered
select * from node;


SELECT jd.db_key, n.db_unique_name, DB_NAME,
       to_char(to_date(start_time,'dd-mon-yyyy'),'DAY') WEEKDAY,
       to_char(start_time,'MM-DD-YYYY HH24:MI:SS') START_TIME,
       (OUTPUT_BYTES/1024/1024) BACKUP_DATA_MB,
       floor(((end_time-start_time)*24*60*60)/3600)|| ' HOURS ' ||
       floor((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600)/60) || ' MINUTES ' ||
       round((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600 - (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60) ))
       || ' SECS ' BACKUP_TIME,
       decode(INPUT_TYPE,'RECVR AREA','BACKUP_TO_NETAPP',INPUT_TYPE) INPUT_TYPE,
       round((OUTPUT_BYTES/1024/1024/1024)/((end_time-start_time)*24)) BACKUP_RATE_GB_HR,
       COMPRESSION_RATIO
from catdba.RC_RMAN_BACKUP_JOB_DETAILS jd, catdba.node n
where jd.db_key = n.db_key
and trunc(end_time) >= trunc(sysdate-5)
order by start_time desc;


SELECT decode(DB_KEY,8285,'CEDAR_EID1',6963,'CEDAR_SCR1',14270,'CEDAR_EDW1',79414,'CEDAR_SKA1', db_key) DB_KEY, 
       DB_NAME,
       to_char(to_date(start_time,'dd-mon-yyyy'),'DAY') WEEKDAY,
       to_char(start_time,'MM-DD-YYYY HH24:MI:SS') START_TIME,
       (OUTPUT_BYTES/1024/1024) BACKUP_DATA_MB,
       floor(((end_time-start_time)*24*60*60)/3600)|| ' HOURS ' ||
       floor((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600)/60) || ' MINUTES ' ||
       round((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600 - (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60) ))
       || ' SECS ' BACKUP_TIME,
       decode(INPUT_TYPE,'RECVR AREA','BACKUP_TO_NETAPP',INPUT_TYPE) INPUT_TYPE,
       round((OUTPUT_BYTES/1024/1024/1024)/((end_time-start_time)*24)) BACKUP_RATE_GB_HR,
       COMPRESSION_RATIO
from catdba.RC_RMAN_BACKUP_JOB_DETAILS
where trunc(end_time) >= trunc(sysdate-5)
order by start_time desc


SELECT jd.db_key, n.db_unique_name, DB_NAME,status,
       to_char(to_date(start_time,'dd-mon-yyyy'),'DAY') WEEKDAY,
       to_char(start_time,'MM-DD-YYYY HH24:MI:SS') START_TIME,
       (OUTPUT_BYTES/1024/1024) BACKUP_DATA_MB,
       floor(((end_time-start_time)*24*60*60)/3600)|| ' HOURS ' ||
       floor((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600)/60) || ' MINUTES ' ||
       round((((end_time-start_time)*24*60*60) - floor(((end_time-start_time)*24*60*60)/3600)*3600 - (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60) ))
       || ' SECS ' BACKUP_TIME,
       decode(INPUT_TYPE,'RECVR AREA','BACKUP_TO_NETAPP',INPUT_TYPE) INPUT_TYPE,
       round((OUTPUT_BYTES/1024/1024/1024)/((end_time-start_time)*24)) BACKUP_RATE_GB_HR,
       COMPRESSION_RATIO
from catdba.RC_RMAN_BACKUP_JOB_DETAILS jd, catdba.node n
where jd.db_key = n.db_key
and trunc(end_time) >= trunc(sysdate-6/24)
--and end_time > SYSDATE - 1
and n.db_unique_name like '%CEDAR'
-- db_name is one of 3 dbs->prod
and DB_NAME = 'EID1'
-- input type can be one of these 6 types but we only use 5
and INPUT_TYPE = 'ARCHIVELOG'
--and INPUT_TYPE = 'BACKUP_TO_NETAPP'
--and INPUT_TYPE = 'DB INCR'
--and INPUT_TYPE = 'DB FULL'
--and INPUT_TYPE = 'BACKUPSET' -- we don't use this one
--and INPUT_TYPE = 'ARCHIVELOG'
--and status <> 'COMPLETED'
order by start_time desc;


-- What are input_types?
select distinct(input_type) from catdba.RC_RMAN_BACKUP_JOB_DETAILS;

select * from catdba.RC_RMAN_BACKUP_JOB_DETAILS where input_type='BACKUPSET';



/* ------------------------------------------------------------------------------ */
-- Temp space monitoring and administration

select * from dba_objects where object_name like 'V$TEMP%';


-- list of tempfiles being used 
select * from dba_temp_files 

select * from v$tempfile ;

-- Size of temporary tablespace 
select * from v$sort_usage where sid = <sid>; 

-- Monitor activity on temporary tablespaces 
select * from v$tempstat; 

select * from v$tempseg_usage;

-- shows who is using space in TempTS

SELECT 
a.username, a.osuser, a.sid||','||a.serial# 
SID_SERIAL, c.spid Process, 
b.tablespace tablespace, a.status, sum(b.extents)* 
1024*1024 space 
FROM     v$session a,v$sort_usage b, v$process c, 
dba_tablespaces d 
WHERE    a.saddr = b.session_addr 
AND      a.paddr = c.addr 
AND      b.tablespace=d.tablespace_name 
--ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks 
group by a.username, a.osuser, 
a.sid||','||a.serial#,c.spid, 
b.tablespace, a.status ;


-- another view of same data; this one gets the SQL causing the issues.

select s.sid || ',' || s.serial# sid_serial, s.username, 
         o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, 
         o.sqladdr address, h.hash_value, h.sql_text 
         from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t 
         where o.session_addr = s.saddr 
         and o.sqladdr = h.address (+) 
         and o.tablespace = t.tablespace_name 
         order by s.sid;



/* ------------------------------------------------------- */
-- Exadata specific admin queries



select inst.instance_name,
b.name,
a.value/1024/1024/1024 value
from gv$sysstat a, gv$statname b, gv$instance inst
where a.statistic# = b.statistic#
and b.name in
('cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by Smart Scan')
and inst.inst_id=a.inst_id
and inst.inst_id=b.inst_id
order by 1,2;



/* ------------------------------------------------------- */
-- full set of diagnostic SQL to find troublesome SQL

-- https://www.linkedin.com/pulse/yes-sometimes-database-fault-merav-kedem

-- lists OEM sql performance retention period
select extract( day from snap_interval) * 24 * 60 +
extract( hour from snap_interval) * 60 +
extract( minute from snap_interval ) Interval_in_minutes,
extract( day from retention) retention_peroid_in_days,
extract( day from retention) * 24 * 60+
extract( hour from retention) * 60 +
extract( minute from retention ) retention_peroid_in_minutes
from dba_hist_wr_control;

-- this changes the retention period
execute dbms_workload_repository.modify_snapshot_settings (interval => 15, retention => 100800);

-- top SQLs running in your database for the last day
select a.sql_id,
count(a.sql_id),
sql_text
from v$active_session_history a,
v$sql b
-- where sample_time between (sysdate – 1) and sysdate
where sample_time > (sysdate - 1)
and a.sql_id=b.sql_id (+)
and session_type != 'BACKGROUND'
and a.sql_id is not null
group by a.sql_id , sql_text
order by count(a.sql_id) desc;


-- current performance information for selected sql_id
select sql_id,
plan_hash_value,
LAST_ACTIVE_TIME,
A.PARSING_SCHEMA_NAME,
executions,
trunc(elapsed_time/1000000/executions,5 ) avg_exec_sec,
trunc(a.ROWS_PROCESSED/executions,3) avg_rows,
a.*
from v$sql a
where sql_id='9jpcgwx97h0bt';

explore the historical behaviour of the same problematic SQL:

select b.begin_interval_time ,
a.plan_hash_value,
a.parsing_schema_name,
a.executions_delta,
trunc(a.elapsed_time_delta/1000000,4) total_elps_sec,
trunc(a.elapsed_time_delta/1000000/nullif(a.executions_delta,0),5) avg_elps,
trunc(a.rows_processed_delta/a.executions_delta,2) rows_avg,
trunc( a.buffer_gets_delta/a.executions_delta,2) buff_avg,
a.fetches_delta
from dba_hist_sqlstat a ,
dba_hist_snapshot b
where sql_id='9jpcgwx97h0bt'
and a.snap_id=b.snap_id
and executions_delta > 0
order by a.snap_id desc;