【Oracle】获取指定用户的所有表的行数（实时的）

#!/bin/bash

# 
ORACLE_USER="username"
ORACLE_PASSWORD="password"
ORACLE_HOST="hostname"
ORACLE_PORT="port"
ORACLE_SID="SID"

# row count
function get_table_row_count() {
    local table_name=$1
    sqlplus -S ${ORACLE_USER}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SID} << EOF
    set heading off
    set feedback off
    SELECT COUNT(*) FROM ${table_name};
    exit;
EOF
}

# current sysdate
current_date=$(sqlplus -S ${ORACLE_USER}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SID} << EOF
    set heading off
    set feedback off
    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
    exit;
EOF
)

# current System Change Number
current_scn=$(sqlplus -S ${ORACLE_USER}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SID} << EOF
    set heading off
    set feedback off
    SELECT SCN_TO_TIMESTAMP(CURRENT_SCN) FROM V$DATABASE;
    exit;
EOF
)

# all tables
table_names=$(sqlplus -S ${ORACLE_USER}/${ORACLE_PASSWORD}@${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SID} << EOF
    set heading off
    set feedback off
    SELECT table_name FROM dba_tables where owner='' order by 1;
    exit;
EOF
)

# for all tables
IFS=$'\n'
for table_name in ${table_names}
do
    row_count=$(get_table_row_count ${table_name})
    echo "Table ${table_name}: ${row_count} rows"
done

# print sysdate and SCN
echo "Current Date: ${current_date}"
echo "Current SCN: ${current_scn}"