A dica de hoje é bem simples, criei este script para auxiliar a listar os parâmetros de sga e pga de todos os databases instalados no servidor em execução:
for ORACLE_SID in `ps -e -o "cmd" | grep smon|grep -v grep|awk '!/ASM/'| awk -F "_" '{print$3}'`
do
ORAENV_ASK=NO
export ORAENV_ASK
export ORACLE_SID
echo "Instancia: "$ORACLE_SID
. /usr/local/bin/oraenv >/dev/null 2>&1
sqlplus -S / as sysdba <<< '
show parameter sga_max_size;
show parameter sga_target;
show parameter pga;';
echo ""
echo "========================================================="
echo ""
doneResultado:
Instancia: ora101
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 20G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 20G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 5G
=========================================================
Instancia: orahml31
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 3600M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 3600M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1200M
=========================================================
Instancia: oradsv21
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1536M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 1536M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 512M
=========================================================
Instancia: oradsv11
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 4928M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 4928M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1638M
=========================================================
Instancia: orahml21
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size big integer 4912M
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target big integer 0
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_target big integer 0
=========================================================
Instancia: oragbl41
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 7680M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 7680M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 5G
pga_aggregate_target big integer 2560M
=========================================================
Instancia: orapocd1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 50G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 50G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 512M
=========================================================