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;

SQL Outlines or SQL Profiles

SQL OUTLINES or PROFILES

set lines 170
set pages 100
col created      format a14
col type         format a6
col status       format a10
col fm           format a3
col profile_name format a30
col sql_text     format a50
col comp_data    format a50
SELECT created,
       type,
       status,
       force_matching fm,
       profile_name,
       sql_text,
       comp_data
  FROM DBA_SQL_PROFILES PROF,
       DBMSHSXP_SQL_PROFILE_ATTR ATTR
 WHERE prof.name = attr.profile_name
 ORDER BY status, created desc;

Parallel Query Processes - Parent / Child Details

Parallel Process Details including Master/Slave relationships

SQL
set lines 200
set pages 100
col username   format a10
col qcslave    format a10
col slaveset   format a8
col program    format a30
col sid        format a5
col slvinst    format a7
col state      format a8
col waitevent  format a30
col qcsid      format a5
col qcinst     format a6
col reqdop     format 999
col actdop     format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME, 
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
       TO_CHAR( px.server_set) SLAVESET, 
       s.program PROGRAM, 
       TO_CHAR(s.SID) SID,
       TO_CHAR(px.inst_id) SLVINST, 
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
       CASE  sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
       DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
       TO_CHAR(px.qcinst_id) QCINST, 
       px.req_degree REQDOP, 
       px.DEGREE ACTDOP,
       DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
  FROM gv$px_session px, 
       gv$session s, 
       gv$px_process pp, 
       gv$session_wait sw
 WHERE px.SID=s.SID (+)
   AND px.serial#=s.serial#(+)
   AND px.inst_id = s.inst_id(+)
   AND px.SID = pp.SID (+)
   AND px.serial#=pp.serial#(+)
   AND sw.SID = s.SID
   AND sw.inst_id = s.inst_id
 ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), 
          px.QCSID,
          DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
          px.SERVER_SET, 
          px.INST_ID

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;

Rename ASM diskgroup (11.2)

Rename ASM diskgroup

$GRID_HOME/bin/renamedg phase=both verbose=true dgname=OLD_FRA newdgname=NEW_FRA asm_diskstring='ORCL:DISK01','ORCL:DISK02'

Important Note: This does not change the file names/locations in the controlfile. 

1. List all data/temp/control/redo files.

2. Relocate the controlfiles (search this blog for "Multiplexing Controlfiles"). 

3. Rename the diskgroup.

4. Rename datafiles, tempfiles, online/standby redo logfiles using "alter database rename file 'x' to 'y';" with the database in mount mode.

ORA-02030 v$lock v$session v$process v$rollname

Error

SQL> grant select on v$lock to oms;
grant select on v$lock to oms
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Solution

select synonym_name,
       table_name 
  from dba_synonyms
 where synonym_name in ('V$LOCK','V$SESSION','V$PROCESS','V$ROLLNAME');

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$LOCK                         V_$LOCK
V$PROCESS                      V_$PROCESS
V$ROLLNAME                     V_$ROLLNAME
V$SESSION                      V_$SESSION


SQL> grant select on v_$lock to supersmartuser;
Grant succeeded.