Top 10 - Biggest Tables and Indexes

Top 10 - Biggest Tables and Indexes

set lines 170
set pages 20
col owner      format a20
col object     format a30
col type       format a6
col tablespace format a20
col size_in_gb format 999,999.9
select * 
  from (select owner           owner,
               segment_name    object,
               segment_type    type,
               tablespace_name tablespace,
               round(bytes/1024/1024/1024,1) size_in_gb
          from dba_segments 
         where owner not in ('SYS','SYSTEM')
         order by bytes desc
       ) 
 where rownum < 11
 order by size_in_gb desc;

Script to Find Unopened files in ASM

Find Unopened files in ASM


set pagesize 0
set linesize 200
col full_alias_path format a80

select * from (
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
/

Sample Output
1      13460 +MYDG1/LEGACYDB1/DATAFILE/indx01.dbf          DATAFILE

1      12440 +MYDG2/LEGACYDB2/DATAFILE/temp01.dbf          TEMPFILE

Reading ASM Disk Header

How to Read an ASM Disk Header

As root,

/u01/app/11.2.0/grid/bin/kfed read /dev/mapper/mydisk1|egrep "(dsksize|provstr|dskname|grpname|fgname)"

kfdhdb.driver.provstr:  ORCLDISKMYDISK1 ; 0x000: length=15

kfdhdb.dskname:                 MYDISK1 ; 0x028: length=7

kfdhdb.grpname:               MYDISKGROUP ; 0x048: length=9

kfdhdb.fgname:                  MYDISK1 ; 0x068: length=7

kfdhdb.dsksize:                  524294 ; 0x0c4: 0x00080006

To list all ASM devices using blkid:

blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;

Query ASM Disks and Diskgroups

List all ASM devices:
/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` |
 cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;'


List all AVAILABLE ASM disks:
col path format a20
col header_status format a13
col os_mb format 999,999,999 heading 'Size (MB)'
SELECT inst_id, 
       path, 
       header_status, 
       os_mb 
  FROM GV$ASM_DISK 
 WHERE header_status in ('FORMER','PROVISIONED')
 ORDER BY path,
          inst_id;

Sample Output:
INST_ID  PATH                 HEADER_STATUS    Size (MB)
-------- -------------------- ------------- ------------
       1 ORCL:DISK25          FORMER             524,294
       2 ORCL:DISK25          FORMER             524,294
       1 ORCL:DISK26          FORMER             524,294
       2 ORCL:DISK26          FORMER             524,294
       1 ORCL:DISK30          FORMER             524,294
       2 ORCL:DISK30          FORMER             524,294
       1 ORCL:DISK33          PROVISIONED        524,294
       2 ORCL:DISK33          PROVISIONED        524,294


List All ASM DISKGROUPS:
set pagesize 60
set linesize 132
column aa format 99999 heading "DiskGroup"
column ab format a15 heading "DiskGroup"
column ac format a20 heading "Disk"
column ad format a15 heading "DiskGroup State"
column ae format a15 heading "Disk State"
break on ab skip 1
select substr(to_char(a.group_number),1,5) aa, substr(a.name,1,15) ab, substr(b.name,1,20) ac , b.total_mb, b.free_mb, a.state ad, b.state ae from
v$asm_diskgroup a, v$asm_disk b
where a.group_number = b.group_number
--and a.group_number = 4
order by 2,3
/

Sample Output:
DiskG DiskGroup       Disk              TOTAL_MB    FREE_MB DiskGroup State Disk State
----- --------------- --------------- ---------- ---------- --------------- ---------------
1     DATA            DATA1               517893     183780 MOUNTED         NORMAL
1                     DATA2               517893     183783 MOUNTED         NORMAL
1                     DATA3               517893     183781 MOUNTED         NORMAL
1                     DATA4               517893     183781 MOUNTED         NORMAL
1                     DATA5               517893     183780 MOUNTED         NORMAL
2     FRA             FRA1                517893     426005 MOUNTED         NORMAL
3     VOTING          VOTING                8631       8235 MOUNTED         NORMAL


Rebalance Operations:
11g
select inst_id, 
       operation, 
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c
select inst_id, 
       pass,
       state, 
       power, 
       sofar, 
       est_work, 
       est_rate, 
       est_minutes 
  from gv$asm_operation 
 order by inst_id, state
/

12c added a "COMPACT" pass to improve disk seek performance.

Sample Output:
   INST_ID OPERA STAT      POWER      SOFAR   EST_WORK   EST_RATE EST_MINUTES
---------- ----- ---- ---------- ---------- ---------- ---------- -----------
         1 REBAL RUN           5     314121     314121       1029           0
         1 REBAL WAIT          5
         2 REBAL RUN           5       9724     188000       1289         138
         2 REBAL WAIT          5

Query Disk Compatibility:
col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20
select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

Additional reference:
How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2 and 12.1? (Doc ID 470211.1)