--	tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/alert_ORCL1.log
--	tail -f /u01/app/oracle/diag/tnslsnr/servername01/listener/trace/listener.log
--	tail -f /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log
--	srvctl status database -d ORCL

--	free -m

show parameter sga
show parameter target

column name format a31;
select name, bytes/1024/1024 Mb from v$sgainfo;

--	Database space alerts (dropper objects, autoextending)

column object_name format a15;
column reason format a25;
column suggested_action format a30;
select object_name,reason,suggested_action from dba_outstanding_alerts;

--	Datafiles - Size, Used, Free (Mb)

SELECT * FROM
  (SELECT tablespace_name FROM dba_tablespaces)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024),0) AS total_Mb
     FROM dba_data_files
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024),0) AS used_Mb
     FROM dba_segments
     GROUP BY tablespace_name)
  USING (tablespace_name)
LEFT OUTER JOIN
  (SELECT tablespace_name, ROUND(SUM(bytes/1024/1024),0) AS free_Mb
     FROM dba_free_space
     GROUP BY tablespace_name)
  USING (tablespace_name) order by free_mb;

--	Tempfiles - Size, Used, Free (Mb)

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$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    A.tablespace_name = D.name
GROUP BY A.tablespace_name, D.mb_total;

--	Flashback Database (check, if flashback exists)

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

--	Resources, Look for processes and sessions
set linesize 200;
-- set pagesize 0;
column RESOURCE_NAME format a20;
column CURRENT_UTILIZATION format 999999;
column MAX_UTILIZATION format 999999;
column INITIAL_ALLOCATION format 999999;
column LIMIT_VALUE format 999999;
select * from v$resource_limit;

	show parameter processes
	alter system set processes=150 scope=spfile;

	show parameter sessions
	alter system set sessions=150 scope=spfile;	

show parameter sga
show parameter target

column name format a31;
select name, bytes/1024/1024 Mb from v$sgainfo;

--	Blocks
select count(USERNAME) from gv$session where blocking_session > 0;
  select * from gv$session where blocking_session > 0;

--	Kill user session, Standalone
SELECT SID,SERIAL#,STATUS FROM GV$SESSION WHERE USERNAME =  'USERNAME';
ALTER SYSTEM KILL SESSION '751,481' IMMEDIATE; 

--	Kill user session, RAC (@instance_number)
SELECT SID,SERIAL#,INST_ID,STATUS FROM GV$SESSION WHERE USERNAME =  'USERNAME';
ALTER SYSTEM KILL SESSION '751,481,@4' IMMEDIATE; 

--	If sqlplus can't kill user session, kill it in the Linux
SELECT USERNAME,SID,SERIAL#,INST_ID FROM GV$SESSION WHERE BLOCKING_SESSION > 0;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@3' IMMEDIATE; 

select spid from v$session s, v$process p where s.paddr = p.addr and s.sid=5140;
kill -9 13142

--	Connected Users
select count(username) from v$session where username!='SYS';
select username from v$session;

--	Show users status
SELECT username, account_status, lock_date FROM dba_users;