The content includes guides, troubleshooting tips, and best practices for managing database environments, with an emphasis on Oracle and PostgreSQL systems. You can read the full analysis at Anuroop Challa's DBA Blog.
Long 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;
=============================================================================
verify:
SELECT supplemental_log_data_min FROM v$database;
SELECT
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;
select * from v$version;
====================================================================
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from gv$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;
Table level logging create group for column and set
ALTER TABLE owner.table_name ADD SUPPLEMENTAL LOG GROUP GROUP_NAME (col1, col2..) ALWAYS;
Table level logging PK column set
ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table level logging UI column set
ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Table level logging ALL column set
ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE SCHEMA.TABLE DROP SUPPLEMENTAL LOG DATA (ALL) 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%';
View running queries in postgreSQL： SELECT * FROM pg_stat_activity;