Anuroop Challa

L ong running query from database: set pages 500 col sid for 9999 set lines 200 col MESSAGE for a85 col username format a15 select sid,serial#,inst_id,username,time_remaining,opname,ELAPSED_SECONDS/60 "Minutes elapsed",time_remaining/60 "Minutes remaining" from gv$session_longops where time_remaining>0;

 Archive generation per month: =========================== select to_char(FIRST_TIME,'YYYY') YEAR,to_char(FIRST_TIME,'MM') MONTH,to_char(FIRST_TIME,'DD') DAY,SUM(BLOCKS*BLOCK_SIZE)/1024/1024/1024,COUNT(1) from v$archived_log where THREAD# = 1 and dest_id = 1 group by to_char(FIRST_TIME,'YYYY') ,to_char(FIRST_TIME,'MM') ,to_char(FIRST_TIME,'DD') order by DAY /

 Sql to check transction details: ============================= select s.sid       ,s.serial#       ,s.username       ,s.machine       ,s.status       ,s.lockwait       ,t.used_ublk       ,t.used_urec       ,t.start_time from v$transaction t inner join v$session s on t.addr = s.taddr;

  Supplemental logging : =============== ====Sql's for verify and set: SELECT supplemental_log_data_min FROM v$database; ========================== SELECT supplemental_log_data_min MIN, supplemental_log_data_pk PK, supplemental_log_data_ui UI, supplemental_log_data_fk FK, supplemental_log_data_all "ALL" FROM v$database; Set minimal supplemental logging for database. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; For RDS: exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); ============================SET PK ====================================== ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; For RDS: exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY'); ======================HOW UI/ALL========================================= ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ==

  Get object id from rowid: =========================== Some time we have rowid casuing issue, but we dont know for which object you can find object id from the below query. Select DBMS_ROWID.rowid_object('give your row id here') from dual;

 Get init trans value of Object: ========================= select table_name, INI_TRANS  from dba_tables  where table_name = 'TABLE_NAME';  SELECT table_name, index_name, status, ini_trans   FROM DBA_INDEXES    INITTRANS value set double for index than object, like below ex: ALTER TABLE TABLE_NAME MOVE INITRANS 10; ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20; ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;

 Get DDL from database: ==================== SQL>set lines 120 SQL>set pages 99999 SQL>set long 1000000 SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL; select DBMS_METADATA.GET_DDL(object_type, object_name) from user_objects where object_type = 'TABLE' and object_name = 'MY_TABLE'

Try below 2 querys for blocking session detail: For which SQL is currently waiting on: ================================== select    sid,    sql_text  from    v$session s,    v$sql q  where    sid in    (select       sid    from       v$session   where       state in ('WAITING')   and       wait_class != 'Idle'   and        event='enq: TX - row lock contention'   and       (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)); The blocking session is: ================================ select    blocking_session,    sid,    serial#,    wait_class,    seconds_in_wait  from    v$session  where    blocking_session is not NULL order by    blocking_session;

   Query for checking archive log at RDS. =================================== select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR')) order by filename; select min(filename) from table (rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR')) where filename like 'archive_name%';