DB Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====Scripts=====

====Tablespaces / Data Files / Blocks====
<code SQL>
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;
</code>
<code SQL>
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;
</code>

<code SQL>
SET linesize 140
COL owner FORMAT a15
COL segment_name FORMAT a35

SELECT owner, segment_name, round(sum(bytes)/1024/1024,2) "SIZE MB",
round(sum(bytes)/1024/1024/1024,2) "SIZE GB"
FROM dba_segments
GROUP BY owner, segment_name
HAVING sum(bytes) > 102400000
ORDER BY bytes DESC;
</code>

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

====Sessions====
<code SQL>
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;
</code>

<code SQL>
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION
  from V$RESOURCE_LIMIT
 where RESOURCE_NAME in ('processes','sessions');
</code>

<code SQL>
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;
</code>

====Memory====
<code SQL>
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;
</code>

<code SQL>
select pool, round(bytes/1024/1024,0) FREE_MB 
from v$sgastat 
where name = 'free memory';
</code>

====Scheduler Jobs====
<code SQL>
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;
</code>

<code>
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;
</code> 
====Optimiser Statistics====
<code SQL>
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;
</code>

====Initialisation Parameters====
<code SQL>
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;
</code>

===Hidden Parameters===
<code SQL>
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;
</code>

====Licensing====
<code SQL>
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;
</code>

====ASM====
<code SQL>
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;
</code>

====Patching and Registry====
<code SQL>
SET LINESIZE 120
COL ACTION_TIME FORMAT A30
COL DESCRIPTION FORMAT A70

select PATCH_ID, ACTION_TIME, DESCRIPTION from DBA_REGISTRY_SQLPATCH;
</code>

<code SQL>
SET LINESIZE 120
COL COMP_NAME FORMAT A40
COL VERSION FORMAT A20

select COMP_NAME, VERSION, STATUS from DBA_REGISTRY;
</code>

====Endian Format====
<code SQL>
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;
</code>
====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.\\
<code SQL>
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');
</code>
<code SQL>
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');
</code>
<code SQL>                 
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');</code>
                  
====Dummy Data====
<code SQL>
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;
</code>