QUERY TO FIND USERS IDLE SESSIONS IN ORACLE 11G RAC

• SCRIPT FOR IDENTIFIYING IDLE SESSIONS SINCE 1HR IN RAC

set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,inst_id,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
gv$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

• SCRIPT FOR IDENTIFIYING IDLE SESSIONS SINCE 2HR IN RAC

set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,serial#,username,status,inst_id,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/7200)||':'||
floor(mod(last_call_et,7200)/120)||':'||
mod(mod(last_call_et,7200),120) "IDLE",
program
from
gv$session
where
type='USER'
and (LAST_CALL_ET / 120) > &minutes
order by last_call_et;

• To display all the INACTIVE sessions of a particular user idle since 2 hrs

SQL> SELECT 'alter system kill session '||''''||sid ||','|| serial#||''''||' immediate;' FROM gv$session WHERE status ='INACTIVE'
     and last_call_et > 7200 and username='FMIS';




Happy Learning ....

Query to Find Users whose names are starting from RN

SQL> select username from dba_users where username like ('RN%');



Happy Learning ...

How to check Database Size, Used and Free Space in a Database

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Database Size        Used space           Free space
-------------------- -------------------- --------------------
40 GB                       9 GB                 31 GB



Happy Learning .....

Query to Check Database Growth in Oracle

Hi Guy's,


> Monitoring daily database growth is helpful for a DBA to Track the data-files growth and to  understand how much your database is growing, which will be easy for you to plan the storage.

> You need to run anyone of these script on daily basis to know the data growth per day.

> I showed in Bytes,MB and GB, execute it as per your requirement.



SQL>  Select a.tablespace_name, a.file_name, a.bytes allocated_bytes,b.free_bytes FROM dba_data_files  a,(SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id order by a.tablespace_name;


TABLESPACE               FILE_NAME                                  ALLOCATED_BYTES        FREE_BYTES
---------- -----------------------------------------------------------------------------------------------------------------------
SYSAUX        +DATA/ieclive/datafile/sysaux.264.860628911           5368709120            3801284608
SYSAUX        +DATA/ieclive/datafile/sysaux.278.909316069           5368709120            5113905152
SYSTEM        +DATA/ieclive/datafile/system.258.860628911           5368709120            2097152
SYSTEM        +DATA/ieclive/datafile/system.292.953295613           5368709120            5166333952
SYSTEM        +DATA/ieclive/datafile/system.288.909316707           5368709120            2745106432
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.262.860628913       5368709120            4472242176
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.295.923579343       5368709120            4604297216
UNDOTBS1    +DATA/ieclive/datafile/undotbs1.298.923684595       5368709120            5336203264
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.297.923684447       5368709120            5276434432
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.296.923579441       5368709120            5209325568
UNDOTBS2    +DATA/ieclive/datafile/undotbs2.269.860629051       5368709120            4956094464
USERS          +DATA/ieclive/datafile/users.294.923569969               1.0737E+10             3300917248
USERS          +DATA/ieclive/datafile/users.256.860630027               1.2885E+10             3210149888
USERS          +DATA/ieclive/datafile/users.274.860630073               1.3959E+10             4204593152
USERS          +DATA/ieclive/datafile/users.282.909316279               1.5032E+10             8168407040
USERS          +DATA/ieclive/datafile/users.259.860628913               1.5032E+10             4981063680
         




SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,(select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE        SIZEMB           FREEMB
--------------------------------------------------------------------
SYSAUX                   10240               8501
UNDOTBS1              15360              13742.06
USERS                      64512              22759.56
SYSTEM                   15360              7546.94
UNDOTBS2              15360              14765.5



SQL>  select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space from dba_free_space group by tablespace_name UNION select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from dba_temp_free_space) a, (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name UNION select tablespace_name,      sum(bytes)/1024/1024/1024 tbs_size from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEGB     FREEGB
---------------------------------------------------------------------------
SYSAUX                                              10                8.3
SYSTEM                                              15                7.4
TEMP                                    63.9999695                 64
UNDOTBS1                                        15                 13.4
UNDOTBS2                                        15                 14.5
USERS                                                63                22.3





Happy Learning ....

Query to Display Archive Log Generation by Day in Oracle

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COL "Generation Date" FORMAT a20

SELECT TRUNC(completion_time)  "Generation Date" ,
   round(SUM(blocks*block_size)/1048576,0) "Total for the Day in MB"
FROM gv$archived_log
GROUP BY TRUNC(completion_time)
ORDER BY TRUNC(completion_time)
/


Happy Learning ....

To Find ASM Disk Usage in Database

 set lines 255
 col path for a35
 col Diskgroup for a15
 col DiskName for a20
 col disk# for 999
 col total_mb for 999,999,999
 col free_mb for 999,999,999
 compute sum of total_mb on DiskGroup
 compute sum of free_mb on DiskGroup
 break on DiskGroup skip 1 on report -

 set pages 255
 select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status
 from v$asm_disk b, v$asm_diskgroup a
 where a.group_number (+) =b.group_number
 order by b.group_number, b.disk_number, b.name
  /


Happy Learning ....

Query to check all the schemas and their sizes

SQL> set linesize 150
SQL>  set pagesize 5000
SQL>  col owner for a15
SQL>  col segment_name for a30
SQL>  col segment_type for a20
SQL>  col TABLESPACE_NAME for a30
SQL>  clear breaks
breaks cleared
SQL>  breaks cleared
SP2-0734: unknown command beginning "breaks cle..." - rest of line ignored.
SQL>  clear computes
computes cleared
SQL>  computes cleared
SP2-0734: unknown command beginning "computes c..." - rest of line ignored.
SQL>  compute sum of SIZE_IN_GB on report
SQL>  break on report
SQL>  select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

OWNER                       SIZE_IN_GB
--------------- ------------------------------------
APEX_030200                    .077375
CMIS                                  .122625
CTXSYS                            .003625
DBSNMP                           .002
EXFSYS                             .003625
HMIS                                  13.5956875
IDMS7                                6.3909375
MDSYS                              .0658125
OLAPSYS                          .005125
ORDDATA                        .0134375
ORDSYS                            .0004375
OUTLN                              .0005625
SCOTT                               .0003125
SYS                                      4.92725
SYSMAN                            .1731875
SYSTEM                             .1571875
WMSYS                               .0024375
XDB                                      .1576875
                                             ------------------
sum                                        25.6993125

18 rows selected.


Happy Learning ...

How To Find Schema Details in Oracle

• Query to check all the schemas of database with their rows.

SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in
     (select username from dba_users where default_tablespace = 'USERS') and object_type = 'TABLE';

OWNER                         ,  COUNT(1),             OBJECT_TYPE
------------------------------,----------,--------------------------------------------
HMIS                              ,      1893,                          TABLE
SCOTT                           ,         4,                             TABLE
ARCHIVEDB                 ,         1 ,                              TABLE
SEC                                 ,        28,                           TABLE
CMIS                              ,       410,                           TABLE
FMIS                               ,       579,                           TABLE
IDMS7                            ,      1883,                          TABLE

7 rows selected.

• How to check number of rows for all the tables in particular Schema

SQL> select count(*) from dba_segments where owner='IDMS7';

  COUNT(*)
----------
      3845

• To check how many segments a schema have and there sizes.

SQL> select segment_name,bytes/1024/1024 from dba_segments where owner='IDMS7';

• How to check size of Particular Schema 

SQL> select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='IDMS7' group by owner;

OWNER           SIZE in GB
--------------- ----------------------
IDMS7            5.6675415


Happy Learning ....

To check Tablespaces that are >=80% full, and how much to add to make them 80% again

SQL> set pages 999 lines 100
SQL> col        "Tablespace"    for a50
SQL> col        "Size MB"       for 999999999
SQL> col        "%Used"         for 999
SQL> col        "Add (80%)"     for 999999
SQL> select     tsu.tablespace_name "Tablespace"
  2  ,  ceil(tsu.used_mb) "Size MB"
  3  ,  100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
  4  ,  ceil((tsu.used_mb - tsf.free_mb) / .5) - tsu.used_mb "Add (50%)"
  5  from       (select tablespace_name, sum(bytes)/1024/1024 used_mb
  6     from    dba_data_files group by tablespace_name) tsu
  7  ,          (select ts.tablespace_name
  8     ,       nvl(sum(bytes)/1024/1024, 0) free_mb
  9     from    dba_tablespaces ts, dba_free_space fs
 10     where   ts.tablespace_name = fs.tablespace_name (+)
 11     group by ts.tablespace_name) tsf
 12  where      tsu.tablespace_name = tsf.tablespace_name (+)
 13  and        100 - floor(tsf.free_mb/tsu.used_mb*100) >= 50
 14  order      by 3,4
 15  ;

Tablespace                                            Size MB        %Used              Add (50%)
-------------------------------------------------- ---------- ----- ------------------------------
SYSTEM                                                5120                60                  1015
USERS                                                   30720              63                  7408

Happy Learning ....

Query to check ten largest objects and their sizes in the database

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select  owner, segment_name,segment_type,mb from (select owner,segment_name,
segment_type ,bytes / 1024 / 1024 "MB" from dba_segments order by bytes desc
) where rownum < 11;

OWNER           SEGMENT_NAME                                           SEGMENT_TYPE              MB
--------------- ------------------------------ --------------- ------------------------------------------------------------
SYS                         AUD$                                                                   TABLE                   2,112
IDMS7                    TBL_HIJRI_CONTROLS                                       TABLE                    1,357
HMIS                      SHSHTDTA_INDX                                                INDEX                    741
HMIS                      SHSHTDTA                                                          TABLE                     738
HMIS                      AHTRNHST                                                          TABLE                     684
HMIS                      ASDCAD                                                               INDEX                     625
HMIS                      OTOCURNC                                                         TABLE                     624
HMIS                      AHTRNHST_B4_OPENING_FINAL_BILL             TABLE                     584
HMIS                      IX_SHDATE                                                          INDEX                     472
IDMS7                    GLDOCENT                                                          TABLE                     312

10 rows selected.

Happy Learning ....

Query to find all the Schemas,Objects and Sizes

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
Select     obj.owner "Owner",  obj_cnt "Objects",  decode(seg_size, NULL, 0, seg_size) "size             GB" from       (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select               owner, ceil(sum(bytes)/1024/1024/1024) seg_size from dba_segments group by owner) seg
 where  obj.owner  = seg.owner(+) order      by 3 desc ,2 desc, 1;

Owner                               Objects                  size GB
------------------------------ ------------ ----------------------------
HMIS                                  3,554                       14
IDMS7                                4,092                       7
SYS                                    32,105                      5
SYSMAN                           3,659                        1
APEX_030200                   2,561                        1
ORDSYS                            2,512                        1
MDSYS                              1,973                        1
XDB                                   1,688                        1
CMIS                                   938                          1
OLAPSYS                           722                          1
SYSTEM                             584                          1
CTXSYS                              388                         1
EXFSYS                               312                        1
WMSYS                               306                        1
ORDDATA                          257                        1
DBSNMP                             65                          1
OUTLN                                10                          1
SCOTT                                 6                            1
PUBLIC                                33,305                  0
FLOWS_FILES                    13                         0
OWBSYS_AUDIT               12                         0
ORDPLUGINS                     10                         0
ORACLE_OCM                     8                         0
SI_INFORMTN_SCHEMA   8                         0
APPQOSSYS                         5                         0
ARCHIVEDB                        3                         0
OWBSYS                               2                         0
HMISDMS                             1                         0

28 rows selected.


Happy Learning ...

Query to find Log-switches Per Hour in RAC database

SQL> set pages 999 lines 400
 col h0 format 999
 col h1 format 999
 col h2 format 999
 col h3 format 999
 col h4 format 999
 col h5 format 999
 col h6 format 999
 col h7 format 999
 col h8 format 999
 col h9 format 999
 col h10 format 999
 col h11 format 999
 col h12 format 999
 col h13 format 999
 col h14 format 999
 col h15 format 999
 col h16 format 999
 col h17 format 999
 col h18 format 999
 col h19 format 999
 col h20 format 999
 col h21 format 999
 col h22 format 999
 col h23 format 999
 SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
     COUNT (1) "Total",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
     SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
    SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
    ROUND (COUNT (1) / 24, 2) "Avg"
   FROM gv$log_history
   WHERE thread# = inst_id
   AND first_time > sysdate -7
   GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
   ORDER BY 1,2;

Output for above Query

Happy Learning ...

ASM Diskgroup Sizes

• To check Diskgroup name, Diskname, Disk Path, Total size & Free size in ASM:-

SQL> col diskgroup for a10
          col diskname for a12
          col path for a20
         select a.name DiskGroup,b.name DiskName, b.total_mb, b.free_mb,b.path, b.header_status                              from v$asm_disk b, v$asm_diskgroup a where a.group_number (+) =b.group_number order by                          b.group_number,b.name;

         DISKGROUP    DISKNAME   TOTAL_MB    FREE_MB       PATH           HEADER_STATUS
            --------------------------------------------------------------------------------------------------------------------
           DATA           DATA_0000          255997      179547   \\.\ORCLDISKDATA0              MEMBER
           DATA           DATA_0001          255997      179548   \\.\ORCLDISKDATA1              MEMBER
       OCRVOTE    OCRVOTE_0000    102397      102002   \\.\ORCLDISKOCRVOTE0     MEMBER
           RECO         RECO_0000         511997      496066    \\.\ORCLDISKRECO0           MEMBER

• To check status & State in Diskgroups.

      SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, dg.name AS diskgroup FROM                 V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

                        ASMDISK           MOUNT_STATUS            STATE                     DISKGROUP
                   ---------------- ------- -------- --------------------------------------------------------------------
                       DATA_0000               CACHED                  NORMAL                  DATA
                       DATA_0001               CACHED                  NORMAL                 DATA
                       RECO_0000              CACHED                  NORMAL                RECO
                   OCRVOTE_0000           CACHED                  NORMAL              OCRVOTE

• TO check Diskgroup Mapped to which instance,Db name & Compatible.

     SQL>  SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
                SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS                                            software, SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
                FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c WHERE dg.group_number =                                               c.group_number;

                   DISKGROUP  INSTANCE     DBNAME   SOFTWARE             COMPATIBLE
              --------- ------------ -------- ------------ -----------------------------------------------------
                       DATA       +asm2              IECLIVE      11.2.0.3.0                11.2.0.0.0
                       RECO      +asm2              IECLIVE      11.2.0.3.0                11.2.0.0.0




Happy Learning ....

Query to find Total Size & Free size of SGA

SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------------------
    25.5105934



SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';

        GB
----------------
 14.027292


Happy Learning ...

How to find Sessions on RAC

> Here we are querying how many business sessions are running in each node from the users.
   we can see here on node1 179 sessions are running and on node2 171 sessions.

> This query is not including SYS or any other  default oracle sessions.


SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;


             INST_ID      COUNT(*)
            --------- ------------------
                  1                 179
                  2                 181


Querying SESSIONS NODE WISE:-

> You can observer some users are connected to INST_ID 1 which is nothing but node1 and some are connected to INST_ID 2 which nothing but node2.
> Count(*) column contains sessions opened per user some has opened 2 sessions from their application and some are opened 3 , 1 etc...

SQL>  select username,inst_id,count(*) from gv$session where username is not null group by                        username,inst_id order by count(*);

           USERNAME      INST_ID   COUNT(*)
         --------- ---------- ---------------------------------
            RNLUAM              1                 1
            RNAIDA                2                 4
            RNFRSA               2                 2
            RNANME              1                 3
            ADTAYOM            2                 1
            RTHUHA              1                  5
            DRSHMO              2                 2



> In this query we can see only sessions opened by the user

SQL> select username,count(*) from gv$session where username is not null group by username order by count(*);

USERNAME            COUNT(*)
---------------------------------------------
RNJEDI                          5
RNMATA                        2
LABMUHA                      1
DRSHMO                        3
DRAHTA                         1




Happy Learning ...

Query to check Active & Inactive Sessions in RAC

> Here we can see its showing 98 active session and 174 inactive sessions from both the nodes.

> Oracle provides gv$ view for RAC databases which collects information from all the nodes in your  cluster.



SQL> select inst_id,username,status from gv$session where status='INACTIVE';

   INST_ID      USERNAME              STATUS
-----------------------------------------------------------------
         1              SYS                          INACTIVE
         2              SYS                          INACTIVE
         2              SYS                          INACTIVE
         2              SYS                          INACTIVE


SQL> select count(*) from gv$session where status='INACTIVE';

  COUNT(*)
-------------
        4


SQL> select count(*) from gv$session where status='ACTIVE';

  COUNT(*)
----------
        98

• Using Below link You can find Active & Inactive Sesssion Excluding Oracle Default Users

http://moindba.blogspot.com/2020/03/how-to-find-active-inactive-sessions-of.html

Happy Learning ....

Query To Check Tablespace Allocated Size and Free Size in GB

SQL> select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from
    (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space
    from dba_free_space group by tablespace_name UNION
    select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from                                         dba_temp_free_space) a,
    (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
    from dba_data_files group by tablespace_name UNION
    select tablespace_name, sum(bytes)/1024/1024/1024 tbs_size
    from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEGB               FREEGB
------------------------------ ---------- ---------------------------------------
SYSAUX                                            2                             1.4
SYSTEM                                            2                             1.3
TEMP                                                 2.34472656            2.3
UNDOTBS1                                       2                             2
USERS                                              36.5344238            14.7


Happy Learning ...

Query To Find Table Details in Oracle

SQL> select owner,segment_name,segment_type,tablespace_name from dba_segments where owner='HMIS';
----------------------------------------------------------------------------------------------------------------------
HMIS        G_LINE_ITEMS                                TABLE                                  USERS
HMIS        G_LINE_ITEMS_PK                         INDEX                                   USERS
HMIS        G_ORDERS                                     TABLE                                  USERS
HMIS        G_ORDERS_PK                               INDEX                                  USERS
HMIS        H                                                       INDEX                                  USERS
HMIS        HACD                                               INDEX                                   USERS
HMIS        HAFZA_S                                         INDEX                                   USERS
HMIS        HCCOMASS                                    TABLE                                   USERS
HMIS        HCDASDTL                                     TABLE                                   USERS
HMIS        HCDASMST                                     TABLE                                   USERS
HMIS        HCDEVSTP                                      TABLE                                   USERS
HMIS        HCNRSREQ                                     TABLE                                  USERS


> Here Owner name is ARCHIVEDB and it contains only one table ARCHIMG

SQL> select owner,table_name from all_tables where owner='ARCHIVEDB';

OWNER                TABLE_NAME
----------- ------------------------------
ARCHIVEDB         ARCHIMG


Happy Learning ....

Query to find table owner name with its default Tablespace

SQL> select owner,segment_name,segment_type,tablespace_name from dba_segments where                       owner='HMIS';

 OWNER       SEGMENT_NAME              SEGMENT_TYPE               TABLESPACE_NAME
----------- --------------------------------------------------------------------------------- ------------------ ----------

HMIS        SYS_C0029177                                   INDEX                                      USERS
HMIS        SYS_IL0000085883C00002$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00007$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00008$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00009$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00010$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00011$$           LOBINDEX                               USERS
HMIS        SYS_IL0000085886C00012$$           LOBINDEX                                USERS
HMIS        SYS_IL0000085899C00009$$           LOBINDEX                                USERS
HMIS        SYS_IL0000085902C00005$$           LOBINDEX                                USERS
HMIS        SYS_IL0000085905C00003$$           LOBINDEX                                USERS



Happy Learning ....

BLOCKER AND WAITER IN RAC

> First find out the blocker and waiter from both the nodes using gv$ view.


SQL> select b.inst_id,b.sid "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID    blocker    INST_ID     waiter
---------- ---------- ---------- ------------
     1         772          2          1223


> Then find out the SID & SERIAL number with usernames.


SQL>  select username,inst_id,sid, serial# from gv$session where sid in (772,1223);

USERNAME      INST_ID     SID      SERIAL#
---------- ---------- ---------- ------------
DRAHAH          2         1223      12405
RNJUMO          1         772       5891


> Now kill the blocker by giving node id you can see here i am     killing RNJUMO user session from node 2.


SQL> alter system kill session '772,5891,@2' immediate;

System altered.

> Check the RNJUMO session status killed or not.


SQL> select username,status from v$session where username='RNJUMO';

USERNAME       STATUS
---------- ---------------------------
RNJUMO         KILLED

> Now check the database any blockers.

SQL> select b.inst_id,b.sid     "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

no rows selected

                                ========= XXX ==========

Example :2

You can also find out table name which is accessed by the Blocker and Waiter:-

> First find out the Blocker & Waiter.

SQL> select b.inst_id,b.sid "blocker",w.inst_id,w.sid "waiter" from gv$lock b,gv$lock w where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID    blocker      INST_ID     waiter
------------------------------------------------
      1        772           1          1223

> By using Blocker & Waiter Sid find out the object id.

SQL> select inst_id,object_id from gv$locked_object where session_id in (772,1223);

   INST_ID   OBJECT_ID
-------------------------
       1      81299
       1      81299

> You can get the object name by using object_id.

SQL> select owner,object_name from dba_objects  where object_id=81299

OWNER       OBJECT_NAME
-------------------------
HMIS         AMCASDTA





Happy Learning ...

TOP 10 SQL STATEMENT WITH FULL TABLE SCANS

SQL> column "SQL_TEXT" format a100
SQL> column OPERATION format a12
SQL> column OPTIONS format a12
SQL> column USER FORMAT a10
SQL> select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",p.operation,p.options from v$sqlarea t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and p.options='FULL' and p.object_owner not in ('SYS','SYSTEM') and ROWNUM < 11 order by DISK_READS DESC, EXECUTIONS DESC;

SQL_TEXT                                                       USER              OPERATION                   OPTIONS
----------------------------------------------------------------------------------------------------------------------------------
select SVSVCD , SVSVNL                             CNARBW        TABLE ACCESS                 FULL
 , SVSVNA From TYSvrty
 Where SVACTV = 'Y'

update pur_tax_dis set                                       IDMS7            TABLE ACCESS                FULL
 pur_tax_dis.act_fl ='
I' WHERE ( pur_tax_dis
.comp =:1 ) AND ( pur_
tax_dis.mod_

select ccdgcd from pmc                                  MMGERA         TABLE ACCESS                  FULL
hmcin where ccdugp = '
'and cccmnm = '' and n
vl(ccdgtp,'C') like 'A
'

SELECT ROWID,DUDRCD,DU                   MMGERA          TABLE ACCESS                  FULL
BRNM,DUSGN,DUSGUT,DUSZ
UT FROM PMDRGMST WHERE
 DUHSCD = '01' AND dud
utp like '%'

SELECT ROWID,DUDRCD,DU                   MMGERA           TABLE ACCESS                  FULL
BRNM,DUSGN,DUSGUT,DUSZ
UT FROM PMDRGMST WHERE
 DUHSCD = '01' AND dud
utp like '%'

select  PIIMAG from am                                  RNEVDI             TABLE ACCESS                 FULL
patbio, ampatIMG where
 PBMEDN= PIMEDN and
 PIMEDN = 100001601


SELECT ROWID,DUDRCD,DU                    MMGERA            TABLE ACCESS                FULL
BRNM,DUSGN,DUSGUT,DUSZ
UT FROM PMDRGMST WHERE
 DUHSCD = '01' AND dud
utp like '%'

select ccdgcd from pmc                                  DRKAAH            TABLE ACCESS                  FULL
hmcin where ccdugp = '
OT'and cccmnm = 'T936'
 and nvl(ccdgtp,'C') l
ike 'C'


SELECT ROWID,CHCHIT,CH                     DRAHIB               TABLE ACCESS                 FULL
ITNL,CHITNA,chcupr,CHS
XCD,CHCRED,CHGUID FROM
 AMCHARGE WHERE chhscd
 ='01' and u

SELECT * FROM SHNRMVAL                   DRKAAH                TABLE ACCESS                FULL
  WHERE NRHSCD =:1
AND NRUFCD =:2    AND
NRKTCD =:3    AND (NRS
EX =:4 or NR

Happy Learning.....

TOP 10 SQL STATEMENTS WITH LARGE NO. OF DISK READS

set pagesize 1000
col username format a10
col terminal format a10
col program format a15
col sql_text format a30
select a.username, a.terminal, a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value;

How to Find SQL text of a User in Oracle

SQL> select user_name,sql_id,sql_text from v$open_cursor where user_name='SAM';

USER_NAME  SQL_ID                                  SQL_TEXT
---------- ------------- --------------------------------------------------------------------------------------------------------
SAM        g4y6nw3tts7cc             BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;

SAM        dyk4dprp70d74             SELECT DECODE('A','A','1','2') FROM DUAL

SAM        0qcfrjfvv1n4b                 SELECT * FROM DEPT WHERE  DNAME='SALES'

SAM        4vs91dcv7u1p6               insert into sys.aud$( sessionid,entryid,statement,ntimestamp

SAM        d6vwqbw6r2ffk               SELECT USER FROM DUAL

SAM        cw6vxf0kbz3v1               SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE  (UPPER('
SAM        7hys3h7ysgf9m               SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE


7 rows selected.

SQL> set pagesize 1000
SQL> col username format a10
SQL> col terminal format a10
SQL> col program format a15
SQL> col sql_text format a30
SQL> select a.username, a.terminal, a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value;

USERNAME   TERMINAL         PROGRAM                                        SQL_TEXT
---------- ---------- --------------- ------------------------------------------------------------------------------------
MMADTO     IECVM049                                              SELECT SYSDATE   FROM SYS.DUAL
SYSMAN       unknown                  OMS                       BEGIN EMD_NOTIFICATION.QUEUE_R
                                                                                        EADY(:1, :2, :3); END;

RNAILO         VM0188                                                 SELECT OPPROB, OPDGCD   FROM O                                                                                                                TORDPRO  WHERE OPCHIT = :1

IDMS7           IECVM213                                             SELECT shwalrt FROM zpi WHERE zirid =:1

SYS                ORADB1                                                sqlplus.exe     on a, v$sqltext b where a.sql_
                                                                                       hash_value = b.hash_value

SYS                ORADB1                                               sqlplus.exe     select a.username, a.terminal,
                                                                                      a.program,b.sql_text from v$sessi

RNMALU      VM0188                                                 SELECT USER   FROM SYS.DUAL
RNAILO        VM0188                                                 SELECT USER   FROM SYS.DUAL
IDMS7           VM095                                                   SELECT shwalrt FROM zpi WHERE zirid =:1

SYS                ORADB1           oraagent.exe                out = 'Y', duplicates_ok  => TRUE);    END;

SYS                ORADB1            oraagent.exe                 lert_timeout_seconds, immediate_timeout
                                                                                        => :immed_time

SYS                ORADB1           oraagent.exe                 nt_id   => event_id,timeout_seconds  => :a

SYS                ORADB1         oraagent.exe                 nt_time, cardinality => :cardinality,      eve

SYS                 ORADB1         oraagent.exe                 son   => :event_reason,event_time  =>  eve

SYS                 ORADB1         oraagent.exe                 e,        incarnation => :incarnation,  event_rea

SYS                 ORADB1          oraagent.exe                  => :service_name,host_name  => :host_nam

SYS                 ORADB1          oraagent.exe                 instance_name  =>  inst_name, service_name

SYS                 ORADB1          oraagent.exe                ld_alert      => FALSE, database_unique_name                                                                                                  => db_name,

SYS                ORADB1           oraagent.exe                reason_id,same_transaction=> FALSE, clear_o

SYS                ORADB1           oraagent.exe                bms_ha_alerts_prvt.post_ha_alert( reason_id=>

SYS                ORADB1           oraagent.exe                id := dbms_server_alert.RSN_FAN_NODE_                                                                                                      DOWN;      END CASE;
SYS                ORADB1           oraagent.exe                RVICE_MEMBER_DOWN; WHEN                                                                                                       'NODE_DOWN' THEN          rea
                 

MMMADA    VM097                                                    SELECT SYSDATE   FROM SYS.DUAL
HMISDMS    MAHMOOD-PC                                      SELECT USER   FROM SYS.DUAL
IDMS7           KALEEM-PC        INV.exe                     pny.cmpny =:1
IDMS7           KALEEM-PC       INV.exe                     SELECT glcmpny.cmpdesa , glcmp
                                                                                        ny.cmpdesl FROM glcmpny WHERE glcm

HMISDMS    MAHMOOD-PC                                     select sysdate from dual
IDMS7           BASEMPC                                              pny.cmpny =:1
IDMS7           BASEMPC                                              SELECT glcmpny.cmpdesa , glcmpy.cmpdesl                                                                                                   FROM glcmpny WHERE glcm
MMMADA    VM097                                                    WHERE ERRCOD = :b2
MMMADA    VM097                                                    SELECT DECODE(:b1,'L',ERRTXL,'
                                                                                        F',ERRTXF),ERRTXA   FROM ERR_TXT


51 rows selected.

How to check Tablespace Space in MB

 column "Tablespace" format a13
 column "Used MB"    format 99,999,999
 column "Free MB"    format 99,999,999
 column "Total MB"   format 99,999,999
 select fs.tablespace_name  "Tablespace", (df.totalspace - fs.freespace) "Used MB",
 fs.freespace  "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct.  Free"  from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace
 from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) /  1048576) FreeSpace from dba_free_space group by tablespace_name) fs where df.tablespace_name  =  fs.tablespace_name;

Tablespace        Used MB     Free MB      Total MB               Pct. Free
------------- ----------- ----------- ----------- -------------------------------------
SYSAUX              1,191          857                2,048                     42
UNDOTBS1          70              5,050             5,120                     99
USERS                  10,218       20,502           30,720                    67
SYSTEM               914            1,134             2,048                      55
UNDOTBS2          71              5,049             5,120                      99


Happy Learning ...

FRA USED & FREE SPACE

SQL> select name, floor(space_limit/1024/1024) "Size MB",ceil(space_used/1024/1024) "Used MB"             from v$recovery_file_dest order by name;

NAME          Size MB          Used MB
---------- ---------- -----------------------
+RECO          500000             1369


SQL> select (case when percent_used>100 then 0 else (100-percent_used) end) percent_free
  2  from (select(sum(percent_space_used)- sum (percent_space_reclaimable)) percent_used from
  3  v$flash_recovery_area_usage);

PERCENT_FREE
------------
       99.82

Happ Learning ...

Find User Details With Machines Names

SQL>  select username,sid,serial#,machine from v$session where status='INACTIVE';

USERNAME          SID              SERIAL#                            Client Machine
---------- ---------- ---------- -------------------------------------------------------
FMIS                        41                1589                                      Blade6
FMIS                       69                 6595                                      Blade6
SYS                        113                8787                              IECC\ORADB1
FMIS                      115               3861                                     Blade6
SEC                       128                 535                                       Blade6
SYS                       134                  5                                   IECC\ORADB1
SEC                       159               8529                                     Blade6
SEC                       200              46973                                    Blade6
SYS                       255                11                                 IECC\ORADB1


9 rows selected.



Happy Learning ....

Check Tablespace is Autoextensible or not

SQL> col FileName format a15
          col AUTO format a5
          select tablespace_name,file_name,round(bytes/1024/1024,0) bytesMB, autoextensible AUTO,                           (increment_by*8192)/1024/1024 Incrmnt,round(maxbytes/1024/1024,2) MaxBytes
           from dba_data_files;


TABLESPACE       FILE_NAME                      BYTESMB      AUTO       INCRMNT   MAXBYTES
---------- ------------------------------ ---------- ----- ---------- -----------------------------------------------------
SYSTEM     +DATA/ieclive/datafile/system.        2048              YES         1024               32767.98
                       258.860628911

SYSAUX     +DATA/ieclive/datafile/sysaux.         2048             YES         1024               32767.98
                       264.860628911

UNDOTBS1   +DATA/ieclive/datafile/undotbs      5120            YES          128                32767.98
                        1.262.860628913

USERS      +DATA/ieclive/datafile/users.2              10240        YES         1024                32767.98
                   59.860628913

UNDOTBS2   +DATA/ieclive/datafile/undotbs       5120           YES         1024              32767.98
                        2.269.860629051

USERS      +DATA/ieclive/datafile/users.2               10240        YES         1024              32767.98
                   56.860630027

USERS      +DATA/ieclive/datafile/users.2               10240        YES         1024             32767.98
                   74.860630073


7 rows selected.


Happy Learning ....