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
/

Datapump Export Script

#
# Title:     datapump_backup.ksh
# Purpose:   Used to take full datapump exports
# Called by: n/a
#
# Notes:     None
#
# Author:    Mark ramsay
# Date:      05 April 2012
#
# Notes:     Requires an oracle directory to be present called data_pump_dir which points to 
#            /u01/app/oracle/admin/${ORACLE_SID}/dpdump
#            Parameter \$1 should be set to the Oracle SID
#            *BE CAREFUL* - The script will remove an dump files from the data_pump_dir
#                           that match the find command.
#

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

SDS_date=`date +%d%b%Y`
export ORACLE_SID=$1
export ORAENV_ASK=NO

. oraenv

expdp \"/ as sysdba\" DUMPFILE=${ORACLE_SID}_full_backup_${SDS_date}.dmp DIRECTORY=data_pump_dir Full=y

cd /u01/app/oracle/admin/${ORACLE_SID}/dpdump

find . -name "${ORACLE_SID}_full_backup_*.dmp" -mtime +14 -follow -exec rm {} \;

Using the redo log sizing advisor

The following SQL will give advice on the optimal size of the redo logs, but manual inspection of redo log switch frequency is always the best approach:

SELECT
   (SELECT
      ROUND (AVG (BYTES) / 1024 / 1024, 2)
   FROM
      V$LOG) AS "Redo size (MB)",
   ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
   (SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS   
   AVERAGE_PERIOD
   FROM
   V$ARCHIVED_LOG
   WHERE
   FIRST_TIME > SYSDATE - 3
   AND
   TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '16:00' AND '17:00'); 

How to check if tablespaces are autoextend

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'TS__';

Free space in Auto extensible tablespaces

WITH my_ddf AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   my_ddf.tablespace_name,
         ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) totfree,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (my_ddf.mysize)
                 * 100
               ) perc
    FROM my_ddf, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE my_ddf.file_id = dfs.file_id(+)
         AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
GROUP BY my_ddf.tablespace_name
ORDER BY 6 DESC

To get all information about all tablespaces

http://docs.gz.ro/oracle-display-tablespace-information.html

set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a100
column tablespace format a15
column status format a10 trunc
column autoextend format a10

select
        file_name "Filename",
        tablespace_name "Tablespace",
        round(bytes/1024/1024 ,2) "Current Size (MB)",
        autoextensible "Autoextend",
        round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
        round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
order by TABLESPACE_NAME;

Script to Report Tablespace/Datafile Space Utilization

REM LOCATION:   Object Management\Tablespaces and DataFiles\Reports
REM FUNCTION:   Generate a report of Tablespace Space Availability
REM             including autoextend related space availability.
REM
REM TESTED ON:  10.2.0.3 and 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dba_tablespaces, dba_data_files
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
SET lines 232 pages 66 feedback off
COLUMN tablespace_name        format a15             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       ) total_used_space,
       fragments.total_tbs_free_bytes total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
                )
             )
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);