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....

How to Find To 10 SQL Statements Consuming More CPU in Oracle.

SQL> select substr(t.SQL_TEXT,0,100)as SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.CPU_TIME from v$sqlarea t where ROWNUM < 11 order by  CPU_TIME DESC,EXECUTIONS DESC;

SQL_TEXT                                                              USER              EXECUTIONS       CPU_TIME
-------------------------------------------------------------------------------------------------------------------------------
/* OracleOEM */                                                      DBSNMP                  3774            6318036
   SELECT SEVERITY_IND
EX, CRITICAL_INICDENTS
, WARNING_INCIDENTS fr
om v$incmete

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

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

select ccdgcd from pmc                                           PHMAAL                     24                 15600
hmcin where ccdugp = '
CP'and cccmnm = 'CP12'
 and nvl(ccdgtp,'C') l
ike 'C'

SELECT PAMLTI FROM AMP                              RNJABR                       7                    15600
ATACT WHERE PAHSCD ='0
1' AND PAMEDN = 100002
493 AND PACASN = 1

 Select *  FROM  SHSHT                                        LABMAWA                  4                    15600
DTA   WHERE ( ( SHSHTD
TA.SDHSCD ='01' ) AND
   ( SHSHTDTA.SDSHID =
 'LA0131' or

select RCNSCD, RCRMCD,                                    DRHAAB                     1                      15600
 RCBDCD from AMRABCNS
where RCMEDN = 1000014
96 and RCACTN = 1796 a
nd RCEXST='Y

SELECT CHITNL,CHITNA                                       DRAHIB                    284                      0
 FROM AMCHARGE  WHERE
CHCHIT = :b1  AND CHHS
CD = :b2

insert into SHSHTDTA (                                            LABMUHA                   1                        0
 SDHSCD , SDSHID , SDM
EDN , SDCASN , SDSEQN
, SDFDNM , SDDTAN , SD
FLDT , SDORD

insert into MMCrossLab                                             DRNAMO                     1                      0
Res (CLRSERIAL,CLRHSCD
,CLRUSER,CLRTYPE,CLRHE
ADER,CLRTEST,CLRUNIT,C
LRNRMLRNG,CL


10 rows selected.

                        For Testing You Can Kill Session for MMGERA User. 

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

USERNAME   TO_CHAR(LOGON_TIME,'   OSUSER   TERMINAL       SID  SERIAL#     INST_ID   COUNT(*)
------------ -------------------- ------------ ---------------- ---------- ---------- ---------- ---------------------------------------------------
MMGERA       10-mar-2020 09:49:52            store3     IECVM030           1475     56493         1                 1



SQL> alter system kill session '1475,56493,@1' immediate;



Happy Learning......

How to find the user name in Oracle who is accessing particular object.

Hi Guy's,

Its very easy to find the 'User-Name' if you want to kill his session.

In my prod database where indexes are not getting rebuild even after giving the 'Rebuild-Online' option.
because this table is not getting released by the user. I already know the table name which has hold the index rebuild and then killed the sessions.


SQL> select object,owner,sid from v$access where object='MPMCSTK '


OBJECT       OWNER         SID
------------ ----------------------------------
MPMCSTK     DAVID          268
MPMCSTK     DAVID         584
MPMCSTK     JOHN          1413
MPMCSTK     KETE          1474
MPMCSTK     CATHY        842

28 rows selected.


SQL> select USERNAME,inst_id,sid,serial# from gv$session where username='DAVID';

USERNAME      INST_ID        SID          SERIAL#
------------------------------------------------------------------------
DAVID                      1              268          6921
DAVID                      1              584          2427


SQL> alter system kill session '268,6921,@1' immediate;

SQL> alter system kill session '584,2427,@1' immediate;





Happy Learning....

Oracle Scripts

Hi, Guy's,

It's very easy to make a script by using the 'CONCATENATE' function in SQL to simplify our DB tasks.


 Killing Sessions:

SQL> select 'alter system kill session ''' || sid ||','|| serial# ||',@'|| inst_id ||''''||'immediate;' FROM gv$session WHERE status ='INACTIVE' and username like 'DR%';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||',@'||INST_ID||''''||'IMMEDIATE;'

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

alter system kill session '69,60037,@1'immediate;

alter system kill session '137,54957,@1'immediate;

alter system kill session '140,25457,@1'immediate;

alter system kill session '200,63197,@1'immediate;

alter system kill session '266,21920,@1'immediate;

alter system kill session '333,13739,@1'immediate;

alter system kill session '774,50855,@1'immediate;

alter system kill session '903,20285,@1'immediate;

alter system kill session '1158,53245,@1'immediate;

alter system kill session '1226,50285,@1'immediate;

alter system kill session '1352,52379,@1'immediate;

alter system kill session '1418,27389,@1'immediate;

alter system kill session '1483,55652,@1'immediate;

alter system kill session '331,21769,@2'immediate;

alter system kill session '332,63307,@2'immediate;

alter system kill session '451,45973,@2'immediate;

alter system kill session '455,53137,@2'immediate;

alter system kill session '521,53017,@2'immediate;

 Index Rebuild:


SQL> select DISTINCT 'alter index '||index_name||' rebuild ONLINE;' from user_ind_columns where table_name='OTOCURNC';

'ALTERINDEX'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index    ACTIVE_MED               rebuild ONLINE;
alter index    IDX_OCU_MCC            rebuild ONLINE;
alter index    PK_OCURNC_PK         rebuild ONLINE;
alter index    IDX_OCU1_MCC          rebuild ONLINE;
alter index    IND_CRDFLR                rebuild ONLINE;
alter index    INX_AWRABOCU         rebuild ONLINE;
alter index    OTOCURNC_MED        rebuild ONLINE;
alter index    INDX_APPNMBR           rebuild ONLINE;

8 rows selected.


 Index Rebuild  for a Schema : 

SQL> set head off

           set pagesize 20000

           set linesize 20000

           select 'alter index '||index_name||' rebuild online;' from user_indexes where table_owner='KMIS';

alter index SYS_IL0000103129C00027$$ rebuild online;

alter index MRMSCANNERBUTTONS_PK rebuild online;

alter index PK_PROVCD rebuild online;

alter index PK_SKQUALIF rebuild online;

alter index PK_SKRACCOD rebuild online;

alter index PK_SKREFRAL rebuild online;

alter index PK_SKRELIGN rebuild online;

alter index PK_SKRLTCOD rebuild online;

alter index PK_SKSERCOD rebuild online;

alter index PK_SKSEXCOD rebuild online;

 Index Move in New Tablespace : 

SQL> set head off
           set pagesize 20000
           set linesize 20000
          select 'alter index '||index_name||' rebuild online tablespace new_tablespace;' from 
          user_indexes where table_owner='HMIS';

alter index SYS_IL0000103129C00027$$ rebuild online tablespace new_tablespace;
alter index OBSKEY rebuild online tablespace new_tablespace;
alter index RTINSREQ_IDX rebuild online tablespace new_tablespace;
alter index PK_EXTSUP rebuild online tablespace new_tablespace;
alter index PK_EXTANT rebuild online tablespace new_tablespace;
alter index K_CLNCSSUP rebuild online tablespace new_tablespace;
alter index CLN_K rebuild online tablespace new_tablespace;
alter index RTAPROVL_PK rebuild online tablespace new_tablespace;
alter index SSSS rebuild online tablespace new_tablespace;
alter index PK_PAYTRN rebuild online tablespace new_tablespace;
alter index PK_DOCSTS rebuild online tablespace new_tablespace;
alter index PK_UN rebuild online tablespace new_tablespace;
alter index RNRNLSCD_C_X rebuild online tablespace new_tablespace;
alter index RMROMTYP_X rebuild online tablespace new_tablespace;
alter index PK_ROMSPC rebuild online tablespace new_tablespace;
alter index JTN rebuild online tablespace new_tablespace;
alter index HFGHYHJ rebuild online tablespace new_tablespace;





Happy Learning.

How to Check Startup Time in Oracle RAC

SQL> select instance_name,status,to_char (startup_time,'dd-mon-yyyy hh24:mi:ss') from gv$instance order by instance_name;

INSTANCE_NAME    STATUS       TO_CHAR(STARTUP_TIME
---------------- ------------ ----------------------------------------------------
mtlive1                         OPEN         13-feb-2020 07:05:23
mtlive2                         OPEN         13-feb-2020 07:01:51




Happy Learning.