usefulscripts

Scripts

Scripts

Tablespaces / Data Files / Blocks

SET PAGESIZE 140 LINESIZE 200
 
COLUMN used_pct FORMAT A11
COLUMN size_mb FORMAT 9,999,999
COLUMN free_mb FORMAT 9,999,999
COLUMN max_size_mb FORMAT 9,999,999
COLUMN max_free_mb FORMAT 9,999,999
 
SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name(+) = b.tablespace_name
       )
ORDER BY tablespace_name;

SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
 
SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               SUM(bytes) used_bytes
        FROM dba_extents
        GROUP BY file_id) E,
       (SELECT SUM(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = <FILE#>
AND BLOCK_ID BETWEEN <BLOCK#> AND (<BLOCK#> + BLOCKS);

Sessions

SET linesize 200 pages 100
col username format a20
col osuser format a20
col machine format a30
col program format a40
col logon_time format a10
 
SELECT SID, SERIAL#, USERNAME, OSUSER, MACHINE, 
       PROGRAM, STATUS, LOGON_TIME, SPID,  
       WAIT_EVENT, LOCKS_BLOCKING FROM (
          SELECT a.*
          , (SELECT event FROM v$session_wait WHERE sid = a.sid) wait_event
          , (SELECT COUNT(*) FROM v$lock WHERE sid = a.sid AND block != 0) locks_blocking FROM (
                SELECT s.status, s.saddr
                , (SELECT spid FROM v$process WHERE addr = paddr) spid
                , s.sid, decode(s.username, NULL, 'Server process', s.username) username
                , osuser, machine, terminal, program
                , logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server
                , process, s.serial#, TYPE, s.module, s.action, schemaname
    FROM 
    v$session s, V$SESS_IO
    WHERE V$SESS_IO.sid (+)= s.sid
  ) a
) b
ORDER BY PROGRAM, OSUSER;

SELECT RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION
  FROM V$RESOURCE_LIMIT
 WHERE RESOURCE_NAME IN ('processes','sessions');

SET LINES 180
col opname format a30
col target format a40
col start_time format a20
col last_update_time format a20
 
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI';
 
SELECT inst_id, sid, serial#, opname, target, start_time, last_update_time, time_remaining, elapsed_seconds
FROM gv$session_longops
WHERE to_char(start_time, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
AND target IS NOT NULL
AND time_remaining > 0
ORDER BY elapsed_seconds DESC, start_time;

Memory

col component format a30
SELECT  component,
        ROUND(current_size/1024/1024) AS current_size_mb,
        ROUND(min_size/1024/1024) AS min_size_mb,
        ROUND(max_size/1024/1024) AS max_size_mb
FROM    v$memory_dynamic_components
WHERE   current_size != 0
ORDER BY component;

SELECT pool, round(bytes/1024/1024,0) FREE_MB 
FROM v$sgastat 
WHERE name = 'free memory';

Scheduler Jobs

SET LINESIZE 200
 
COLUMN owner FORMAT A20
COLUMN job_name FORMAT A30
COLUMN job_class FORMAT A30
COLUMN next_run_date FORMAT A25
 
SELECT owner, job_name, run_count, failure_count, to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS') next_run_date
FROM   dba_scheduler_jobs
WHERE enabled = 'TRUE' AND owner NOT IN ('SYS','ORACLE_OCM','EXFSYS')
ORDER BY owner, job_name;

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

Optimiser Statistics

COLUMN client_name FORMAT A32
COLUMN window_name FORMAT A20
COLUMN job_start_time FORMAT A30
COLUMN job_duration FORMAT A20
COLUMN job_status FORMAT A10
 
SELECT client_name,
       window_name,
       to_char(job_start_time, 'DD-MON-YYYY HH24:MI:SS') job_start_time,
       job_duration,
       job_status
FROM   dba_autotask_job_history WHERE client_name LIKE '%optimizer%'
AND job_start_time > sysdate -3
ORDER BY job_start_time;

Initialisation Parameters

SET LINES 140 pages 40
col name format a40
col VALUE format a50
SELECT name, VALUE, isdefault, ismodified FROM v$parameter WHERE isdefault = 'FALSE' ORDER BY 1;

Hidden Parameters

SET LINESIZE 170 PAGESIZE 100
COL PARAMETER FORMAT A50
COL DESCRIPTION FORMAT A60 TRUNCATED
COL SESSION_VALUE FORMAT A25
COL INSTANCE_VALUE FORMAT A25
SELECT a.ksppinm PARAMETER, a.ksppdesc DESCRIPTION,
b.ksppstvl SESSION_VALUE, c.ksppstvl INSTANCE_VALUE
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
ORDER BY 1;

Licensing

col name format a60
col detected_usages format 999999
 
SELECT u1.name, u1.detected_usages, u1.currently_used, u1.version
FROM   dba_feature_usage_statistics u1
WHERE  u1.version = (SELECT MAX(u2.version)
                     FROM   dba_feature_usage_statistics u2
                     WHERE  u2.name = u1.name)
AND    u1.detected_usages > 0
AND    u1.dbid = (SELECT dbid FROM v$database)
ORDER BY name;

ASM

SET echo off
SET wrap off
SET LINES 155 pages 9999
col "Group Name" FOR a6    Head "Group|Name"
col "Disk Name"  FOR a10
col "State"      FOR a10
col "Type"       FOR a10   Head "Diskgroup|Redundancy"
col "Total GB"   FOR 9,990 Head "Total|GB"
col "Free GB"    FOR 9,990 Head "Free|GB"
col "Imbalance"  FOR 99.9  Head "Percent|Imbalance"
col "Variance"   FOR 99.9  Head "Percent|Disk Size|Variance"
col "MinFree"    FOR 99.9  Head "Minimum|Percent|Free"
col "MaxFree"    FOR 99.9  Head "Maximum|Percent|Free"
col "DiskCnt"    FOR 9999  Head "Disk|Count"
 
prompt
prompt ASM Disk Groups
prompt ===============
 
SELECT g.group_number  "Group"
,      g.name          "Group Name"
,      g.state         "State"
,      g.type          "Type"
,      g.total_mb/1024 "Total GB"
,      g.free_mb/1024  "Free GB"
,      100*(MAX((d.total_mb-d.free_mb)/d.total_mb)-MIN((d.total_mb-d.free_mb)/d.total_mb))/MAX((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
,      100*(MAX(d.total_mb)-MIN(d.total_mb))/MAX(d.total_mb) "Variance"
,      100*(MIN(d.free_mb/d.total_mb)) "MinFree"
,      100*(MAX(d.free_mb/d.total_mb)) "MaxFree"
,      COUNT(*)        "DiskCnt"
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number AND
d.group_number <> 0 AND
d.state = 'NORMAL' AND
d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
 
prompt ASM Disks IN USE
prompt ================
 
col "Group"          FOR 999
col "Disk"           FOR 999
col "Header"         FOR a9
col "Mode"           FOR a8
col "State"          FOR a8
col "Created"        FOR a10          Head "Added To|Diskgroup"
col "Path"           FOR a19
col "SecsPerRead"    FOR 9.000        Head "Seconds|PerRead"
col "SecsPerWrite"   FOR 9.000        Head "Seconds|PerWrite"
 
SELECT group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
,      mode_status   "Mode"
,      state         "State"
,      create_date   "Created"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
,      name          "Disk Name"
,      path          "Path"
,      read_time/reads "SecsPerRead"
,      write_time/writes "SecsPerWrite"
FROM   v$asm_disk_stat
WHERE header_status NOT IN ('FORMER','CANDIDATE')
ORDER BY group_number, disk_number;
 
Prompt File Types IN Diskgroups
Prompt ========================
col "File Type"      FOR a16
col "Block Size"     FOR a5    Head "Block|Size"
col "Gb"             FOR 9990.00
col "Files"          FOR 99990
break ON "Group Name" skip 1 nodup
 
SELECT g.name                                   "Group Name"
,      f.TYPE                                   "File Type"
,      f.BLOCK_SIZE/1024||'k'                   "Block Size"
,      f.STRIPED
,        COUNT(*)                               "Files"
,      round(SUM(f.BYTES)/(1024*1024*1024),2)   "Gb"
FROM   v$asm_file f,v$asm_diskgroup g
WHERE  f.group_number=g.group_number
GROUP BY g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED
ORDER BY 1,2;
clear break
 
prompt Instances currently accessing these diskgroups
prompt ==============================================
col "Instance" form a8
SELECT c.group_number  "Group"
,      g.name          "Group Name"
,      c.instance_name "Instance"
FROM   v$asm_client c, v$asm_diskgroup g
WHERE  g.group_number=c.group_number;
 
prompt Free ASM disks AND their paths
prompt ==============================
col "Disk Size" format a9
SELECT header_status              "Header"
, mode_status                     "Mode"
, path                            "Path"
, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"
FROM v$asm_disk
WHERE header_status IN ('FORMER','CANDIDATE')
ORDER BY path;
 
prompt CURRENT ASM disk operations
prompt ===========================
SELECT *
FROM   v$asm_operation;

Patching and Registry

SET LINESIZE 120
COL ACTION_TIME FORMAT A30
COL DESCRIPTION FORMAT A70
 
SELECT PATCH_ID, ACTION_TIME, DESCRIPTION FROM DBA_REGISTRY_SQLPATCH;

SET LINESIZE 120
COL COMP_NAME FORMAT A40
COL VERSION FORMAT A20
 
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

Endian Format

SELECT * FROM v$TRANSPORTABLE_PLATFORM ORDER BY platform_id;
 
SELECT tp.platform_name, tp.endian_format
  FROM v$database d, v$transportable_platform tp
 WHERE d.platform_name = tp.platform_name;

Grants

The below 3 scripts will detail what privileges have been granted to standard users.
It needs to be run as a user with DBA privilege.

SELECT 
CASE
  WHEN ADMIN_OPTION = 'NO' 
    THEN 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ';'
    ELSE 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ' WITH ADMIN OPTION;'
END
FROM dba_sys_privs 
WHERE grantee IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N');

SELECT
CASE
  WHEN GRANTABLE = 'NO'
    THEN 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || ';' 
    ELSE 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || ' WITH ADMIN OPTION;'
END
FROM dba_tab_privs 
WHERE grantee IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N');

SELECT 
CASE
  WHEN ADMIN_OPTION = 'NO' 
    THEN 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';'
    ELSE 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ' WITH ADMIN OPTION;'
END    
FROM dba_role_privs 
WHERE grantee IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N');

Dummy Data

SELECT round(dbms_random.value(0, 101),0) FROM dual;
SELECT sysdate + dbms_random.value(-28, 28) FROM dual;
SELECT dbms_random.string('A', 10) FROM dual;
 
CREATE TABLE t1 AS
SELECT trunc(dbms_random.value(0, 101),0) Number_Value,
       sysdate + dbms_random.value(-14, 14) Date_Value,
       dbms_random.string('A', 10) Text_Value
FROM dual
CONNECT BY level <= 1000;

Table of Contents

Scripts

Tablespaces / Data Files / Blocks

Sessions

Memory

Scheduler Jobs

Optimiser Statistics

Initialisation Parameters

Hidden Parameters

Licensing

ASM

Patching and Registry

Endian Format

Grants

Dummy Data