Oracle Object Detail  ... 

Oracle objects info ..

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 300

col owner       for a15

col object_type for a15

col object_name for a30

col temporary  for a10

col generated  for a20

select owner,

       object_name,

    -- subobject_name,

    -- object_id,

    -- data_object_id,

       object_type,

       created,

       last_ddl_time,

    -- timestamp,

       status,

       temporary,

       generated,

       secondary,

    -- namespace,

       edition_name

from   dba_objects

where  upper(object_name) like upper('%&OBJECT_NAME%')

order by owner, object_name;

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300 
col owner       for a15
col object_type for a15
col object_name for a30
col temporary  for a10
col generated  for a20
select owner,
       object_name,
    -- subobject_name,
    -- object_id,
    -- data_object_id,
       object_type,
       created,
       last_ddl_time,
    -- timestamp,
       status,
       temporary,
       generated,
       secondary,
    -- namespace,
       edition_name
from   dba_objects
where 1=1   
and last_ddl_time > sysdate -1   ---- last one day

order by owner, object_name;

compile the objects ...

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set pagesize 300 linesize 300 

col info for a80

select

decode( object_type, 'PACKAGE BODY',

'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',

'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) info ,

'-- ', status,created,last_ddl_time

from dba_objects a

where 1=1

and STATUS = 'INVALID' 

and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW' )

and owner not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

order by object_type,object_name;

Object with TableSpace 

set pagesize 300 linesize 150

col owner   for a25

col object_name  for a30

col SEGMENT_NAME for a25

select object_name,o.owner,tablespace_name ,o.object_type, o.status,count(*) from dba_objects o,dba_segments s

where 1=1

-- and STATUS='INVALID' 

--and o.OWNER='OWNER'

and s.segment_name = o.object_name 

and s.segment_type = o.object_type 

and s.owner = o.owner

 and  upper(object_name) like upper('%&OBJECT_NAME%')

group by object_name,o.owner,o.object_type, o.status ,tablespace_name 

order by owner

/

OBJECT_NAME                    OWNER                     TABLESPACE_NAME                OBJECT_TYPE             STATUS    COUNT(*)

------------------------------ ------------------------- ------------------------------ ----------------------- ------- ----------

EMP                            ANUJ                      TEST                           TABLE                   VALID            1

EMP                            ANUJ1                     USERS                          TABLE                   VALID            1

PK_EMP                         ANUJ1                     USERS                          INDEX                   VALID            1

EMP                            ANUJ10                    USERS                          TABLE                   VALID            1

PK_EMP                         ANUJ10                    USERS                          INDEX                   VALID            1

EMP                            ANUJ3                     USERS                          TABLE                   VALID            1

set linesize 500 pagesize 300

col OWNER for a20

col OBJECT_NAME  for a20

col stime for a27

select u.name as owner, o.name as object_name, 

 decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',

 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

 11, 'PACKAGE BODY', 12, 'TRIGGER',

 13, 'TYPE', 14, 'TYPE BODY',

 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',

 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',

 32, 'INDEXTYPE', 33, 'OPERATOR',

 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',

 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',

 43, 'DIMENSION',

 44, 'CONTEXT', 47, 'RESOURCE PLAN',

 48, 'CONSUMER GROUP',

 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNKNOWN') as type,

o.ctime, o.mtime,

 to_char(o.stime, 'DD-MM-YYYY HH24:MI:SS') stime,

 decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') as status

 from sys.obj$ o, sys.user$ u

 where o.owner# = u.user#

 and o.linkname is null

 and (o.type# not in (1 , 10) or

 (o.type# = 1 and 1 = (select 1 from sys.ind$ i

 where i.obj# = o.obj#

 and i.type# in (1, 2, 3, 4, 6, 7, 9))))

 --and O.NAME like  '%Queue%'

 ;

object search from Web 

====

set linesize 500 pagesize 300

undef object_string

define object_string='EMP'

col oname format a42 head 'Object Name'

col suboname format a30 head 'Sub Object Name'

col objtype format a10 head 'Obj Type' trunc

col sta format a03 head 'Sta' trunc

col syn format a40 head 'Synonym'

col created_pdt format a14 head 'Creation PDT'

col lastddl_pdt format a14 head 'Last DDL PDT'

col tlastana_pdt format a14 head 'Table|Last Ana PDT'

col ilastana_pdt format a14 head 'Index|Last Ana PDT'

col objid format 99999999 head 'Object Id'

col ar format a02 head '=>'

col cf format a01 head 'x'

col df format a01 head 'x'

col tf format a01 head 'x'

col if format a01 head 'x'

col sep format a01 head '|'

select  

o.con_id

,decode(s.synonym_name,null,'   ',s.owner||'.'||s.synonym_name) syn

,decode(s.synonym_name,null,'  ','=>') ar

        ,o.owner||'.'||o.object_name oname

,o.object_type objtype

--,o.subobject_name suboname

,o.status sta

,o.object_id objid

,'|' sep

,to_char(new_time(o.created,'GMT','PDT'),'YY/MM/DD HH24:MI') created_pdt

,decode(sign(sysdate-(o.created+2/24)),-1,'x',' ') cf

,'|' sep

,to_char(new_time(o.last_ddl_time,'GMT','PDT'),'YY/MM/DD HH24:MI') lastddl_pdt

,decode(sign(sysdate-(o.last_ddl_time+2/24)),-1,'x',' ') df

,'|' sep

,to_char(new_time(t.last_analyzed,'GMT','PDT'),'YY/MM/DD HH24:MI') tlastana_pdt

,decode(sign(sysdate-(t.last_analyzed+2/24)),-1,'x',' ') tf

,'|' sep

,to_char(new_time(i.last_analyzed,'GMT','PDT'),'YY/MM/DD HH24:MI') ilastana_pdt

,decode(sign(sysdate-(i.last_analyzed+2/24)),-1,'x',' ') if

,'|' sep

from cdb_objects o

,cdb_synonyms s

,cdb_tables t

,cdb_indexes i

where 1=1

-- and (o.object_name like upper('%&&object_string%')       or s.synonym_name like upper('%&&object_string%'))

and (o.object_name like upper('&&object_string%')       or s.synonym_name like upper('&&object_string%'))

and o.object_type not in ('SYNONYM')

and o.object_type not like '%PARTITION%'

--and o.owner not in ('SYS','SYSTEM')

and o.owner = s.table_owner (+)

and o.object_name = s.table_name  (+)

and o.owner = t.owner (+)

and o.object_name = t.table_name (+)

and o.owner = i.owner (+)

and o.object_name = i.index_name (+)

order by o.owner||'.'||o.object_name

;

  col oname format a40

col subobj format a30

col objtype format a10 trunc

col sta format a03 trunc

col syn format a20

col created format a15

col lastddl format a15

col objid format 9999999999

col ar format a02 head '=>'

break on syn

select decode(s.synonym_name,null,'   ',s.owner||'.'||s.synonym_name) syn

,decode(s.synonym_name,null,'  ','=>') ar

        ,o.owner||'.'||o.object_name oname

,subobject_name subobj

,object_type objtype

,status sta

,object_id objid

,to_char(created,'dd-mm-YY HH24:MI:SS') created

,to_char(last_ddl_time,'dd-mm-YY HH24:MI:SS') lastddl

from cdb_objects o

,cdb_synonyms s

where 1=1

-- and (o.object_name like upper('%&&object_string%')       or s.synonym_name like upper('%&&object_string%'))

and (o.object_name like upper('&&object_string%')       or s.synonym_name like upper('&&object_string%'))

and o.owner = s.table_owner (+)

and o.object_name = s.table_name  (+)

and object_type not like '%PARTITION%'

and object_type not in ('SYNONYM')

order by o.owner||'.'||o.object_name

,s.owner||'.'||s.synonym_name

,object_type

;

ynonym                                      => Object Name                              SUBOBJ                         Obj Type   Sta   Object Id CREATED     LASTDDL

-------------------------------------------- -- ---------------------------------------- ------------------------------ ---------- --- ----------- --------------- ---------------

                                                ANUJ.EMP                                                                TABLE      VAL      224527 27-02-18 06:54: 27-02-18 07:19:

                                                                                                                                                   27          16

                                                ANUJ1.EMP                                                               TABLE      VAL      201415 09-01-18 15:48: 09-01-18 15:49:

                                                                                                                                                   44          23

                                                ANUJ10.EMP                                                              TABLE      VAL      202855 12-01-18 13:13: 12-01-18 13:14:

   

Kill session in AWS Oracle Database ... 

Oracle AWS RDS kill session
connections on an RDS Oracle DB
Kill session in AWS Oracle Database

[oracle@rac01 ~]$ sqlplus 'vihaan@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=************.compute.amazonaws.com)(Port=1521))(CONNECT_DATA=(SID=orcl)))'

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 5 14:58:57 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

SQL>

*******************************************
This Will NOT work !!!!!!!!
set linesize 200 pagesize 300 
col kill for a70
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,username,sql_id from gv$session 
where 1=1
-- and username='username'
-- and status = 'INACTIVE'
and type != 'BACKGROUND';
      

*********************************************

select LOGINS from V$INSTANCE;
 
we need to use this pakage rdsadmin.rdsadmin_util to kill session in oracle aws rds
 
exec rdsadmin.rdsadmin_util.kill(627,35083);
 

select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN
  

SQL> select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.

SQL>
   

set linesize 200 pagesize 300
sql> select sid, serial#, status,username from v$session where status='INACTIVE';

 SID SERIAL# STATUS USERNAME
---------- ---------- -------- ------------------------------
 40 61773 INACTIVE RDSADMIN

SQL> exec rdsadmin.rdsadmin_util.kill(40,61773);

PL/SQL procedure successfully completed.
 
 

begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

SQL> exec rdsadmin.rdsadmin_util.kill(40,61777,'IMMEDIATE');

PL/SQL procedure successfully completed.


to Kill session !
begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid, 
 serial => &serial_number,
 method => 'IMMEDIATE');
end;
/

Via Script 

set linesize 200 pagesize 300
col kill for a70
select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ');' kill from v$session where username in ('&Username'); 


-- session with Immediate !!!

set linesize 200 pagesize 300
select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ',username ,status,blocking_session,state,final_blocking_session,sql_id
from v$session s
where 1=1 
-- and username = upper('')
 and STATUS != 'ACTIVE'
 and s.Type != 'BACKGROUND';
 
 
KILL '--' USERNAME STATUS BLOCKING_SESSION STATE FINAL_BLOCKING_SESSION SQL_ID
---------------------------------------------------------------------- ---- -------------------- -------- ---------------- ------------------- ---------------------- -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- RDSADMIN INACTIVE WAITING

 
SQL> SQL> exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE');

PL/SQL procedure successfully completed.



set linesize 300 
col username for a20
select 
'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill ,
 ' -- ',
 s.inst_id,
 -- s.sid,
 -- s.serial#,
 p.spid,
 s.username,
 s.program,
 s.sql_id
 from gv$session s, gv$process p
 where 1=1 
 and p.addr = s.paddr 
 and p.inst_id = s.inst_id
and s.type != 'BACKGROUND' ;

KILL '--' INST_ID SPID USERNAME PROGRAM SQL_ID
---------------------------------------------------------------------- ---- ---------- ------------------------ -------------------- ------------------------------------------------ -------------
exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- 1 27035 RDSADMIN JDBC Thin Client
exec rdsadmin.rdsadmin_util.kill(21,62867, 'IMMEDIATE'); -- 1 23889 VIHAAN sqlplus@rac01.*****.net (TNS V1-V3) 6v5f6v3upz5mu

Oracle How write into alert.log

Oracle How to write into alert.log

custom messages in alert log

package dbms_system.ksdwrt

        option 1- for trace file
        option 2- for alert log
        option 3- for both

connect / as sysdba

begin
    sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' --- Anuj test --- ');
end;
/

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location'); 
exec dbms_system.ksdwrt(2, 'This message goes to the alert log');
exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location')

begin
    exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
end;
/

or 

exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Anuj---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start---******');
exec sys.dbms_system.ksdwrt(2, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' ******--- Start Failover Change No: 7777777 ---******');


execute sys.dbms_system.ksdwrt(2,('ORA-07445: DB Monitoring Test'));

execute sys.dbms_system.ksdwrt(2,'ORA-07445: DB Monitoring Test');

Spfile asm in ASM  11gR2/12C

How to find out spfile in +ASM
How to find out OCR in +ASM 


SQL> !olsnodes -c

olsnodes -c  <<<< To Find Out cluster Name 

set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = LOWER('&CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

with date

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER,CREATION_DATE  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type,c.CREATION_DATE
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE','PARAMETERFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
            where a.group_number = b.group_number
            and (mod(a.parent_index, power(2, 24))) = 0
--          and a.name = LOWER('CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

ASMCMD [+] > find --type PARAMETERFILE '*' '*'
ASMCMD [+] > spget
ASMCMD> spcopy +DATA/racluster/asmparameterfile/registry.253.792630659 /tmp/spfilecopyASM.ora
ASMCMD> spbackup +DATA....   +LOGS/spfileBackASM.bak

Oracle CREATE ASM DISKGROUP manually in 11gR2/12C

Create ASM DiskGroup ....!!!!

Node 1

#ls -ltr  /dev/mapper/

lrwxrwxrwx 1 root root       8 Mar 29 10:01 ora_fra_0003p1 -> ../dm-29

lrwxrwxrwx 1 root root       8 Mar 29 10:01 ora_acfs_0001 -> ../dm-43

#oracleasm createdisk -v ORA_0001 /dev/mapper/ora_acfs_0001

# oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

ls -alrt /dev/oracleasm/disks/ORA*

scan and list 

# date ; hostname;  oracleasm scandisks ; for d in `oracleasm listdisks | sort`; do oracleasm querydisk -p $d; done

Node2

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

Node3

#oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "ORA_0001"

Scanning system for ASM disks...

#oracleasm listdisks

DATA_0000

FRA_0000

GRID_0000

ORA_0001  <<<<<< New disk 

-- for  Candidate disk !!!

ASMCMD [+] > lsdsk --candidate -p

Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout  on trimspool on  verify  off

clear columns breaks computes

column disk_group_name        format a12           head 'disk group name'

column disk_file_path         format a45           head 'path'

column disk_file_name         format a12           head 'file name'

column disk_file_fail_group   format a12           head 'fail group'

column total_mb               format 999,999,999   head 'file size (mb)'

column used_mb                format 999,999,999   head 'used size (mb)'

column pct_used               format 999.99        head 'pct. used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

   NVL(a.name, '[CANDIDATE]')                       disk_group_name

 , b.path                                           disk_file_path

 , b.name                                           disk_file_name

 , b.failgroup                                      disk_file_fail_group

 , b.total_mb                                       total_mb

 , (b.total_mb - b.free_mb)                         used_mb

-- , ROUND((1- (b.free_mb / b.total_mb))*100, 2)    pct_used

FROM v$asm_diskgroup a ,v$asm_disk b 

where a.group_number (+) =b.group_number 

ORDER BY a.name;

Disk Group N Path                                          File Name    Fail Group   File Size (MB) Used Size (MB)

------------ --------------------------------------------- ------------ ------------ -------------- --------------

************

[candidate]  /dev/oracleasm/disks/ORA_0001                                                        0              0

************                                                                         -------------- --------------

                                                                                                  0              0

SQL> CREATE DISKGROUP ORAFS EXTERNAL REDUNDANCY disk '/dev/oracleasm/disks/ORA_0001';

Check the disk group ...

[grid]$ asmcmd -p

ASMCMD [+] > lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512   4096  1048576     51200    51150                0           51150              0             N  ORAFS/

ASMCMD [+] > ls -l +         <<<< for DiskGroup !!!

ASMCMD [+] > lsdsk -t -G DATA

Create_Date  Mount_Date  Repair_Timer  Path

01-FEB-18    01-FEB-18   0             ORCL:DATA0

01-FEB-18    01-FEB-18   0             ORCL:DATA1

01-FEB-18    01-FEB-18   0             ORCL:DATA2

01-FEB-18    01-FEB-18   0             ORCL:DATA3

set echo off feedback 6 heading on linesize 200 pagesize 300 termout on timing off trimout  on

set trimspool on  verify  off

clear columns breaks computes

column disk_group_name        format a12           head 'disk group name'

column disk_file_path         format a45           head 'path'

column disk_file_name         format a12           head 'file name'

column disk_file_fail_group   format a12           head 'fail group'

column total_mb               format 999,999,999   head 'file size (mb)'

column used_mb                format 999,999,999   head 'used size (mb)'

column pct_used               format 999.99        head 'pct. used'

break on report on disk_group_name skip 1

compute sum label ""              of total_mb used_mb on disk_group_name

compute sum label "grand total: " of total_mb used_mb on report

SELECT

   NVL(a.name, '[CANDIDATE]')                       disk_group_name

 , b.path                                           disk_file_path

 , b.name                                           disk_file_name

 , b.failgroup                                      disk_file_fail_group

 , b.total_mb                                       total_mb

 , (b.total_mb - b.free_mb)                         used_mb

 , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used

FROM v$asm_diskgroup a ,v$asm_disk b 

where a.group_number (+) =b.group_number 

ORDER BY a.name;

*********************************************************************

Imp ......

On all the Nodes mount the DiskGroup !!!!!!!!!!!!!!!!!!!!!

SQL>  alter diskgroup ORAFS mount ;

Diskgroup altered.

ASMCMD [+] > lsdg -g     <<<<<<<< check for all node 

*********************************************************************

If Required .. 

sqlplus> alter diskgroup ORACFS set attribute 'compatible.asm'='11.2.0.0.0'; 

set linesize 200 

col compatibility          form a15

col database_compatibility form a25

col name                   form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

set linesize 200  pagesize 300

col value for a10

col name  for a50

select group_number, name, value from v$asm_attribute

where 1=1

and group_number=1

;

GROUP_NUMBER NAME                                               VALUE

------------ -------------------------------------------------- ----------

           1 idp.type                                           dynamic

           1 idp.boundary                                       auto

           1 disk_repair_time                                   3.6h

 asmcmd lsattr -G DATA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           12.1.0.0.0
compatible.rdbms         10.1.0.0.0
content.check            FALSE
content.type             data
disk_repair_time         3.6h
failgroup_repair_time    24.0h
idp.boundary             auto
idp.type                 dynamic
phys_meta_replicated     true
sector_size              512
thin_provisioned         FALSE




[grid@rac01 ~]$ asmcmd -p

ASMCMD [+] > lsattr -G DATA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  1048576
cell.smart_scan_capable  FALSE
compatible.asm           12.1.0.0.0
compatible.rdbms         10.1.0.0.0
content.check            FALSE
content.type             data
disk_repair_time         3.6h
failgroup_repair_time    24.0h
idp.boundary             auto
idp.type                 dynamic
phys_meta_replicated     true
sector_size              512

thin_provisioned         FALSE

=====

set pagesize 0

select

'GROUP_NUMBER            : '||GROUP_NUMBER  ||chr(10)||

'NAME            : '||NAME    ||chr(10)||

'SECTOR_SIZE            : '||SECTOR_SIZE    ||chr(10)||

'BLOCK_SIZE            : '||BLOCK_SIZE    ||chr(10)||

'ALLOCATION_UNIT_SIZE      : '||ALLOCATION_UNIT_SIZE    ||chr(10)||

'STATE              : '||STATE    ||chr(10)||

'TYPE          : '||TYPE ||chr(10)||

'TOTAL_MB              : '||TOTAL_MB      ||chr(10)||

'FREE_MB      : '||FREE_MB ||chr(10)||

'HOT_USED_MB          : '||HOT_USED_MB    ||chr(10)||

'COLD_USED_MB    : '||COLD_USED_MB            ||chr(10)||

'REQUIRED_MIRROR_FREE_MB  : '||REQUIRED_MIRROR_FREE_MB  ||chr(10)||

'USABLE_FILE_MB    : '||USABLE_FILE_MB  ||chr(10)||

'OFFLINE_DISKS              : '||OFFLINE_DISKS    ||chr(10)||

'COMPATIBILITY            : '||COMPATIBILITY    ||chr(10)||

'DATABASE_COMPATIBILITY         : '||DATABASE_COMPATIBILITY     ||chr(10)||

'VOTING_FILES            : '||VOTING_FILES

from  v$asm_diskgroup

where 1=1

order by 1

;

set pagesize 100