Some usefull TIPS

USER idle time

SELECT sid, osuser, username, status,
TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME,
FLOOR(last_call_et/3600)||':'||
FLOOR(MOD(last_call_et,3600)/60)||':'||
MOD(MOD(last_call_et,3600),60) IDLE, program
FROM v_$session
WHERE username IS NOT NULL
ORDER BY last_call_et;

USER temp tablespace

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
ORDER BY 1;

USER informations

SELECT u.username, u.default_tablespace, 
u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,
r.admin_option, r.default_role
FROM sys.dba_users u, sys.dba_role_privs r
WHERE u.username = r.grantee (+)
GROUP BY u.username, u.default_tablespace,
u.temporary_tablespace, u.profile, r.granted_role,
r.admin_option, r.default_role;

Create user sample

CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE tablespacename
TEMPORARY SPACE temp_tablespacename
QUOTA UNLIMITED ON tablespacename; 

GRANT CONNECT TO username;
GRANT RESOURCE TO username;

Active Processes(DBA)

SET pagesize 55;
SET linesize 170;
col SQL format a80;
col SERVER heading 'SVR' format a3;
col EVENT heading 'WAITING' format a30 fold_after;
col OSUSER heading 'OSUSER' format a8;
col USERNAME heading 'USERNAME' format a8;
col PID heading 'OSPID' format 99999;
col DISK_READS heading 'DISK I/O' format 99999999;
col BUFFER_GETS heading 'BUFFER|GETS' format 99999999;
 
SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
     V$SESSION.OSUSER OSUSER,
--        DECODE(V$SESSION.SERVER,'DEDICATED','D','SHARED','S','O') SERVER,
     V$SQLAREA.DISK_READS DISK_READS,
     V$SQLAREA.BUFFER_GETS BUFFER_GETS,
        SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
     V$SESSION.PROCESS PID,
     V$SESSION_WAIT.EVENT EVENT,
        V$SQLAREA.SQL_TEXT SQL
FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE   V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
     V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
        V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
     V$SESSION.STATUS = 'ACTIVE' AND
     V$SESSION_WAIT.EVENT != 'client message'
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;

Kill Session

Identify the Session to be Killed

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

SELECT SID, SERIAL#
FROM V$SESSION
WHERE USERNAME = 'LSHR';

kill session
https://oracle-base.com/articles/misc/killing-oracle-sessions

 The basic syntax for killing a session is shown below.

 SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION '7,15';

 In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

 SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

Show dead case

SELECT distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;

How to see the oldest flashback available?

Using the following query one can see the flashback data available.

SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;

List Earliest Flashback Database Time and SCN

--
-- List Earliest Flashback Database Time and SCN.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN oldest_flash_scn FOR 999,999,999
 
ALTER SESSION 
   SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
 
SELECT 
   oldest_flashback_scn,
   oldest_flashback_time
FROM  
   v$flashback_database_log
/

List Flashback Database Restore Points

Oracle Database » SQL Library » List Flashback Database Restore Points

--
-- List Flashback Database Restore Points
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
 
SELECT 
      database_incarnation# as Incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
FROM 
      v$restore_point
ORDER BY 4
/

désert/Oracle/PLSQL