ASM Diskgroup Total Size, Free Size with Percentage

 

SQL>  select name,total_mb,free_mb, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;

              NAME          TOTAL SIZE(GB)     FREE SIZE (GB)      %Free

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

               CRS                        19,085               18,689                   97.925072

               DATA                     19,085               17,397                   91.1553576

               FRA                        23,846               23,751                   99.6016103

              ARCH                     23,846               23,705                    99.4087059

Enjoy Learning.....

Hot to Extract All Triggers Of a Particular Table in Oracle

 

Hi Guy's,

You can easily extract the DDL of  triggers available in your table.

 SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SQL> SELECT DBMS_METADATA.GET_DDL('TRIGGER', TRIGGER_NAME) FROM USER_TRIGGERS where table_name=' TABLE_NAME';

Enjoy....

How to find who locked the User in Oracle

Hi Guy's,
Pleas check this query output,
How to find Machine,Terminal of the user from where it get locked.


SQL> select os_username,username,userhost,terminal,timestamp,action_name from dba_audit_session where username='DRDISA';


SQL> select os_username,username,userhost,terminal,timestamp,action_name from dba_audit_session where username='DRDISA' order by timestamp desc;





Happy Reading....

How to Find Users in Oracle and Excluding Oracle Default Users

SQL> select username from dba_users where username not in
  ('ANONYMOUS'
  ,'APEX_040200'
  ,'APEX_PUBLIC_USER'
  ,'APPQOSSYS'
  ,'AUDSYS'
  ,'BI'
  ,'CTXSYS'
  ,'DBSNMP'
  ,'DIP'
  ,'DVF'
  ,'DVSYS'
  ,'EXFSYS'
  ,'FLOWS_FILES'
  ,'GSMADMIN_INTERNAL'
  ,'GSMCATUSER'
  ,'GSMUSER'
  ,'HR'
  ,'IX'
  ,'LBACSYS'
  ,'MDDATA'
  ,'MDSYS'
  ,'OE'
  ,'ORACLE_OCM'
  ,'ORDDATA'
  ,'ORDPLUGINS'
  ,'ORDSYS'
  ,'OUTLN'
  ,'PM'
  ,'SCOTT'
  ,'SH'
  ,'SI_INFORMTN_SCHEMA'
  ,'SPATIAL_CSW_ADMIN_USR'
  ,'SPATIAL_WFS_ADMIN_USR'
  ,'SYS'
  ,'SYSBACKUP'
  ,'SYSDG'
  ,'SYSKM'
  ,'SYSTEM'
  ,'WMSYS'
  ,'XDB'
  ,'SYSMAN'
  ,'RMAN'
  ,'RMAN_BACKUP'
  ,'OWBSYS'
  ,'OWBSYS_AUDIT'
  ,'APEX_030200'
  ,'MGMT_VIEW'
  ,'OJVMSYS'
  ,'OLAPSYS'
  ,'XS$NULL'
  );

USERNAME
------------------------------
SAM
JOHN
TOM
CATHY

How to Check Constraints of a Table in Oracle

SQL> col type format a15
           col constraint_name format a30
         select decode (constraint_type,
         'C', 'Check',
         'O', 'R/O View',
         'P', 'Primary',
         'R', 'Foreign',
         'U', 'Unique',
         'V', 'Check view') type,
       constraint_name,status,last_change
       from user_constraints where
       table_name = 'EMP' order by 1;

TYPE            CONSTRAINT_NAME      STATUS           LAST_CHANGE
--------------- -------------------------------------------------------------------------------
Foreign         FK_DEPTNO                      ENABLED         30-MAR-10
Primary         PK_EMP                             ENABLED         30-MAR-10



Happy Learning....

How to Find Total Users and Active,Inactive Sessions By Excluding Oracle Default Users

 SQL> select count(*) from gv$session where status='INACTIVE' and username not in
('ANONYMOUS'
,'APEX_040200'
,'APEX_PUBLIC_USER'
,'APPQOSSYS'
,'AUDSYS'
,'BI'
,'CTXSYS'
,'DBSNMP'
,'DIP'
,'DVF'
,'DVSYS'
,'EXFSYS'
,'FLOWS_FILES'
,'GSMADMIN_INTERNAL'
,'GSMCATUSER'
,'GSMUSER'
,'HR'
,'IX'
,'LBACSYS'
,'MDDATA'
,'MDSYS'
,'OE'
,'ORACLE_OCM'
,'ORDDATA'
,'ORDPLUGINS'
,'ORDSYS'
,'OUTLN'
,'PM'
,'SCOTT'
,'SH'
,'SI_INFORMTN_SCHEMA'
,'SPATIAL_CSW_ADMIN_USR'
,'SPATIAL_WFS_ADMIN_USR'
,'SYS'
,'SYSBACKUP'
,'SYSDG'
,'SYSKM'
,'SYSTEM'
,'WMSYS'
,'XDB'
,'SYSMAN'
,'RMAN'
,'RMAN_BACKUP'
,'OWBSYS'
,'OWBSYS_AUDIT'
,'APEX_030200'
,'MGMT_VIEW'
,'OJVMSYS');

  COUNT(*)
----------
       216

SQL> select count(*) from gv$session where status='ACTIVE' and username not in

('ANONYMOUS'
,'APEX_040200'
,'APEX_PUBLIC_USER'
,'APPQOSSYS'
,'AUDSYS'
,'BI'
,'CTXSYS'
,'DBSNMP'
,'DIP'
,'DVF'
,'DVSYS'
,'EXFSYS'
,'FLOWS_FILES'
,'GSMADMIN_INTERNAL'
,'GSMCATUSER'
,'GSMUSER'
,'HR'
,'IX'
,'LBACSYS'
,'MDDATA'
,'MDSYS'
,'OE'
,'ORACLE_OCM'
,'ORDDATA'
,'ORDPLUGINS'
,'ORDSYS'
,'OUTLN'
,'PM'
,'SCOTT'
,'SH'
,'SI_INFORMTN_SCHEMA'
,'SPATIAL_CSW_ADMIN_USR'
,'SPATIAL_WFS_ADMIN_USR'
,'SYS'
,'SYSBACKUP'
,'SYSDG'
,'SYSKM'
,'SYSTEM'
,'WMSYS'
,'XDB'
,'SYSMAN'
,'RMAN'
,'RMAN_BACKUP'
,'OWBSYS'
,'OWBSYS_AUDIT'
,'APEX_030200'
,'MGMT_VIEW'
,'OJVMSYS');

  COUNT(*)
----------
       19

• You can also find Total count of  Active & Inactive Sessions Using below link

http://moindba.blogspot.com/2015/10/query-to-check-active-inactive-sessions.html


Happy Learning.....

How to Find User Status, State, Sid, Serial# in Oracle Database

Hi, Guy's,

You can find the USER information like Status,State, SID, SERIAL# etc...which will also help you for killing sessions and also finding User-Machine locations.


For all the RAC-Database Users :-

SQL> set pagesize 50000
set linesize 5000
set long 50000
select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session
group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);


For Specific Departments Users Whose Names are Starting From 'AC' :-

SQL> set pagesize 50000
set linesize 5000
set long 50000
select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session where username like 'AC%'
group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);


For Specific User :-

SQL> select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session where username=('PHRASU')
     group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);



Happy Learning....