Saturday, August 29, 2015

iptables Template

11:43 AM Posted by Dilli Raj Maharjan No comments

Declaration of interpreter and iptables binary location

#!/bin/bash
IPFW="/sbin/iptables"

Declaration of LAN, Core Server, Corporate LAN and their subnets

# Network declearation
CORE_SERVER="A.B.C.D/subnet"
LAN="L.A.N.S/subnet"
CORPORATE_LAN="A.B.C.D/subnet"
RAC_IPS="A.B.C.D/subnet \
M.N.O.P/subnet \
ME="I.J.K.L"

Port Declaration, list all the port that host will serve

# Port declearation
SSH_PORT="22"
SNMP_PORT="161"
ORACLE_PORT="1521"
DNS_PORT="53"
HTTP_PORT="80"
HTTPS_PORT="443"
EM_PORT="1158"
RSYNC_PORT="873"
MYSQL_PORT="3306"
SMTP_PORT="25"
NTP_PORT="123"
RADIUS_PORTS="1645 1646"

Hosts and the subnets that the above mentioned service are allowed

# Service Allowed IPS
SSH_ALLOWS="$LAN $RAC_IP"
SNMP_ALLOWS="E.F.G.H/subnet"
HTTP_ALLOWS="E.F.G.H/subnet \
I.J.K.L"
ORACLE_ALLOWS="E.F.G.H/subnet \
I.J.K.L $RAC_IPS"
RADIUS_ALLOWS="A.B.C.D/subnet M.N.O.P/subnet"

IPs and Subnets of server that the host will connect to

DNS_SERVERS="D.N.S.1 D.N.S.2 D.N.S.3"
RSYNC_SERVERS="S.Y.N.C R.S.Y.N/subnet"
MYSQL_SERVERS="M.S.Q.L/subnet"
SMTP_SERVERS="S.M.T.P/subnet"
NTP_SERVERS="N.T.P.S/subnet"
SSH_SERVERS="S.S.H.D/subnet"

Turning on native Kernel IPv4 parameters at runtime

echo "[+] Turning on native Kernel IPv4 protection"
# disable Packet forwarning between interfaces
        echo 0 > /proc/sys/net/ipv4/ip_forward
# ignore all ICMP ECHO and TIMESTAMP requests sent to it via broadcast/multicast
        echo 1 > /proc/sys/net/ipv4/icmp_echo_ignore_broadcasts
# log packets with impossible addresses to kernel log
        echo 1 > /proc/sys/net/ipv4/conf/all/log_martians
# disable logging of bogus responses to broadcast frames
        echo 1 > /proc/sys/net/ipv4/icmp_ignore_bogus_error_responses
# do source validation by reversed path (Recommended option for single homed hosts)
        echo 1 > /proc/sys/net/ipv4/conf/all/rp_filter
# don't send redirects
        echo 0 > /proc/sys/net/ipv4/conf/all/send_redirects
# don't accept packets with SRR option
        echo 0 > /proc/sys/net/ipv4/conf/all/accept_source_route

Flush all the chains in the table

echo "[+] Flushing iptables rules"
${IPFW} -F

Set the policy for the INPUT, OUTPUT and FORWARD chain

### Default Policy ACCEPT
${IPFW} -P INPUT ACCEPT
${IPFW} -P OUTPUT ACCEPT
${IPFW} -P FORWARD ACCEPT

Setting iptables rules for state match

echo "[+] Setting up INPUT-OUTPUT chain for state"
${IPFW} -A INPUT -m state --state INVALID -j DROP
${IPFW} -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
${IPFW} -A OUTPUT -m state --state INVALID -j DROP
${IPFW} -A OUTPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

Setting iptables rules for services

echo "[+] Setting up INPUT-OUTPUT chain for ssh service"
for SSH_ALLOW in ${SSH_ALLOWS}
{
        ${IPFW} -A INPUT -p tcp -s $SSH_ALLOW --dport $SSH_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $SSH_ALLOW --sport $SSH_PORT -j ACCEPT
}

echo "[+] Setting up INPUT-OUTPUT chain for snmp service"
for SNMP_ALLOW in ${SNMP_ALLOWS}
{
        ${IPFW} -A INPUT -s $SNMP_ALLOW -p udp --dport $SNMP_PORT -j ACCEPT
        ${IPFW} -A INPUT -d $SNMP_ALLOW -p udp --sport $SNMP_PORT -j ACCEPT
}

echo "[+] Setting up INPUT-OUTPUT chain for iBill auth acct port"
for RADIUS_ALLOW in ${RADIUS_ALLOWS}
{
        for RADIUS_PORT in ${RADIUS_PORTS}
        {
                ${IPFW} -A INPUT -p udp -s $RADIUS_ALLOW --dport $RADIUS_PORT -j ACCEPT
                ${IPFW} -A OUTPUT -p udp -d $RADIUS_ALLOW --sport $RADIUS_PORT -j ACCEPT
        }
}

for ORACLE_ALLOW in ${ORACLE_ALLOWS}
{
        ${IPFW} -A INPUT -p tcp -s $ORACLE_ALLOW --dport $ORACLE_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $ORACLE_ALLOW --sport $ORACLE_PORT -j ACCEPT
}

Setting iptables rules for servers

for ORACLE_SERVER in ${ORACLE_SERVERS}
{
        ${IPFW} -A INPUT -p tcp -s $ORACLE_SERVER --sport $ORACLE_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $ORACLE_SERVER --dport $ORACLE_PORT -j ACCEPT
}

echo "[+] Setting up INPUT-OUTPUT chain for dns servers"
for DNS_SERVER in ${DNS_SERVERS}
{
        ${IPFW} -A INPUT -p udp -s $DNS_SERVER --sport $DNS_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p udp -d $DNS_SERVER --dport $DNS_PORT -j ACCEPT
}

echo "[+] Setting up INPUT-OUTPUT chain for mysql servers"
for MYSQL_SERVER in ${MYSQL_SERVERS}
{
        ${IPFW} -A INPUT -p tcp -s $MYSQL_SERVER --sport $MYSQL_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $MYSQL_SERVER --dport $MYSQL_PORT -j ACCEPT
}

for RSYNC_SERVER in ${RSYNC_SERVERS}
{
        ${IPFW} -A INPUT -p tcp -s $RSYNC_SERVER --sport $RSYNC_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $RSYNC_SERVER --dport $RSYNC_PORT -j ACCEPT
}

for SMTP_SERVER in ${SMTP_SERVERS}
{
        ${IPFW} -A INPUT -p tcp -s $SMTP_SERVER --sport $SMTP_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $SMTP_SERVER --dport $SMTP_PORT -j ACCEPT
}

for NTP_SERVER in ${NTP_SERVERS}
{
        ${IPFW} -A INPUT -p udp -s $NTP_SERVER --sport $NTP_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p udp -d $NTP_SERVER --dport $NTP_PORT -j ACCEPT
}

for SSH_SERVER in ${SSH_SERVERS}
{
        ${IPFW} -A INPUT -p tcp -s $SSH_SERVER --sport SSH_PORT -j ACCEPT
        ${IPFW} -A OUTPUT -p tcp -d $SSH_SERVER --dport SSH_PORT -j ACCEPT
}

Setting iptables rules for icmp protocol

echo "[+] Setting up INPUT-OUTPUT chain for icmp"
${IPFW} -A INPUT -s $CORE_SERVER -p icmp -j ACCEPT
${IPFW} -A OUTPUT -p icmp -j ACCEPT

Setting iptables rules for localhost

echo "[+] Setting up INPUT-OUTPUT chain for localhost"
${IPFW} -A INPUT -d 127.0.0.1 -j ACCEPT
${IPFW} -A OUTPUT -s 127.0.0.1 -j ACCEPT

Setting iptables rules for blacklisted ports as netbios

# Drop All blacklisted port
${IPFW} -A INPUT -p tcp --dport 135:139 -j DROP
${IPFW} -A INPUT -p tcp --dport 4444 -j DROP
${IPFW} -A INPUT -p tcp --dport 445 -j DROP
${IPFW} -A INPUT -p tcp --dport 1900 -j DROP
${IPFW} -A INPUT -p udp --dport 135:139 -j DROP
${IPFW} -A INPUT -p udp --dport 4444 -j DROP
${IPFW} -A INPUT -p udp --dport 445 -j DROP
${IPFW} -A INPUT -p udp --dport 1900 -j DROP

Logging all remaining packets for further diagnosis

echo "[+] Setting up Logging all remaining packets"
${IPFW}  -A INPUT -p tcp -j LOG
${IPFW}  -A OUTPUT -p tcp -j LOG
${IPFW}  -A INPUT -p udp -j LOG
${IPFW}  -A OUTPUT -p udp -j LOG

Setting default policy to DROP

echo "[+] Setting up DEFAULT policy to DROP"
### Default Policy ACCEPT
${IPFW} -P INPUT DROP
${IPFW} -P OUTPUT DROP
${IPFW} -P FORWARD DROP

Wednesday, July 29, 2015

Mysql database online backup and recovery using mysql enterprise backup tool

7:55 AM Posted by Dilli Raj Maharjan , No comments

MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.
The mysqlbackup command is an easy-to-use tool for all backup and restore operations. Following script creates scheduled incremental backup. MySQL Enterprise Backup is available on enterprise edition of mysql database only.

Create a user for backup specific task.

create user  'bckadmin'@'127.0.0.1' identified by 'hahaha_password';

Login to mysql as user root and grant following privileges to backup admin user;

GRANT RELOAD ON *.* TO 'bckadmin'@'127.0.0.1';GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'bckadmin'@'127.0.0.1';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'bckadmin'@'127.0.0.1';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'bckadmin'@'127.0.0.1';
GRANT REPLICATION CLIENT ON *.* TO 'bckadmin'@'127.0.0.1';
GRANT SUPER ON *.* TO 'bckadmin'@'127.0.0.1';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'localhost';

GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'bckadmin'@'127.0.0.1';
flush privileges;

Create a directory where executable, configuration, include and exclude files can be placed. Following files should be created under newly create directory. 

mkdir /opt/mysqlbck


Create blank file with the name of include.database that contains list of the database to be backup. The content of this file will be generated at run time. So better create blank file for it.

# include.database
cd /opt/mysqlbck
touch include.database

Create file, exclude.database that contains list of the database to be exclude from the backup. 

# exclude.database
cd /opt/mysqlbck
echo "test" >> exclude.database
echo "project_test" >> exclude.database


Create executable file, online_backup.sh that contains the code that will backup the database online.

online_backup.sh

#!/bin/bash

# Author : Dilli Maharjan(dilliraj.maharjan@gmail.com)
# Date : 2015-07-25
# Description : Script to backup mysql database Online


# Variable Declaration
DATE_N_TIME=$(date +%Y-%m-%d_%H%M%S)
DATE=$(date +%Y_%m_%d)
BACKUP_TYPE=''
BACKUP_OPT=''
CMD=''
COMMENT=''
ADDITIONAL_OPT=''
WORKING_DIR="$(dirname $0)"
. ${WORKING_DIR}/online_backup.conf

EXCLUDE_FILE="${WORKING_DIR}/exclude.database"
INCLUDE_FILE="${WORKING_DIR}/include.database"
FULLBCK_EXISTS=0


declare -a last_backup


# Find either backup history table exists or not.
BHTBL_EXISTS=$(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select count(*) from information_schema.tables where upper(table_name)='BACKUP_HISTORY' and upper(table_schema)='MYSQL';" 2>/dev/null | grep -v "+--")
FULLBCK_EXISTS=$(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select count(*) from mysql.backup_history where backup_type='FULL';" 2>/dev/null | grep -v "+--")
[ ${DEBUG} -eq "1" ] && echo "INFO: Backup table exists ${BHTBL_EXISTS}"

# Checking whether first day of the month or backup information table exists or not.
if [ $(date +%d) == "01" ] || [ "${BHTBL_EXISTS}" -eq 0 ] || [ "${FULLBCK_EXISTS}" -eq 0 ] ; then
    BACKUP_TYPE="Full_backup"
    BACKUP_OPT="backup-and-apply-log --backup-dir="
    COMMENT="Full Backup of ${DB}" 
    ADDITIONAL_OPT="--connect_timeout=7200"
else
    last_backup=( $(/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "select binlog_pos, binlog_file, start_lsn, end_lsn from mysql.backup_history where backup_type='FULL' order by  end_lsn desc limit 1;" 2>/dev/null | grep -v "+--"))
    BACKUP_TYPE="Diff_backup"
    BACKUP_OPT="backup --incremental-backup-dir="
    COMMENT="Differential Backup of ${DB}"
    ADDITIONAL_OPT="--incremental --start-lsn=${last_backup[3]} --connect_timeout=7200 "
fi
[ ${DEBUG} -eq "1" ] && echo "INFO: BACKUP_TYPE ${BACKUP_TYPE}, BACKUP_OPT ${BACKUP_OPT}, COMMENT ${COMMENT}, ADDITIONAL_OPT ${ADDITIONAL_OPT}" 

# Declare the log dir, logfile, and backup dir
LOG_FILE="${LOG_DIR}/${BACKUP_TYPE}_${DATE_N_TIME}.log"
BACKUP_DIR1="${BACKUP_DIR}/${BACKUP_TYPE}_${DATE_N_TIME}"

# Creating backup directory and logdirectory if doesnot exists
[ ! -e ${LOG_DIR} ] && mkdir -p ${LOG_DIR}
[ ! -e $BACKUP_DIR1} ] && mkdir -p ${BACKUP_DIR1}


# Increasing wait timeout to 2 hrs
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "set global wait_timeout=7200;" 2>/dev/null

# Backup recent include database file and generate new include file
/bin/mv -f ${INCLUDE_FILE} ${INCLUDE_FILE}_001 && /usr/bin/script -c "/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e \"SHOW GLOBAL STATUS LIKE 'Uptime';\"" -a -f ${LOG_FILE}

# Generate fresh new include.database file which contains the all the database that need to be included in the backup
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN} -h 127.0.0.1 -N -e "show databases;" | sed s/\+\-//g | grep -v -f ${WORKING_DIR}/exclude.database > ${WORKING_DIR}/include.database

# Start backup of the database.
/usr/bin/script -c "/usr/local/bin/mysqlbackup --slave-info ${ADDITIONAL_OPT} ${BACKUP_OPT}${BACKUP_DIR1} --databases-list-file=${INCLUDE_FILE} --comments=\"${COMMENT}\" --user=${USER_BCKADMIN} --password=${PASS_BCKADMIN}  --host=127.0.0.1 --read-threads=1 --write-threads=1  --process-threads=1" -a -f ${LOG_FILE}
sleep 7200 

# Reverting wait timeout to 1min
/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN}  -h 127.0.0.1 -N -e "set global wait_timeout=60;" 2>/dev/null

# logging the uptime in the logfile
/usr/bin/script -c "/usr/bin/mysql -u ${USER_BCKADMIN} -p${PASS_BCKADMIN}  -h 127.0.0.1 -N -e \"SHOW GLOBAL STATUS LIKE 'Uptime';\"" -a -f ${LOG_FILE}

# Include latest copy of my.cnf in the backup
cp /etc/my.cnf ${BACKUP_DIR1}/

# Compress the recently created backup and delete the source.
/usr/bin/script -c "/bin/tar --remove-files -czvf ${BACKUP_DIR1}.tar.gz ${BACKUP_DIR1}" -a -f ${LOG_FILE}
sleep 1200

# Replace password with ******** in logfile
/bin/sed -i 's/${PASS_BCKADMIN}/********/g' ${LOG_FILE}

# Purge backup on the first day of the month
[ $(date +%d) == "01" ] && ${WORKING_DIR}/purge_backup.sh ${BACKUP_DIR}>> ${LOG_FILE}

# Mail detail log to the admins.

/bin/mail -r "${SENDER}" -s "${SUBJECT}" ${RECEPTIANTS} < ${LOG_FILE}

Create configuration file online_backup.conf. This file includes the credential to be used while backup, backup location and mail configurations.


#online_backup.conf

# Either to enable debug mode or not

DEBUG=1



# Force to consider Fullbackup exists Do not change the value of this variable 

FULLBCK_EXISTS=0



# Creadential for generating backup
PASS_BCKADMIN="hahaha_password"
USER_BCKADMIN="bckadmin"


# Declaring variables for the mail
DB="dillidb"
SENDER="database@dilli.com.np"
SUBJECT="Backup Report ${DB} Database ${DATE}"
RECEPTIANTS="_dba@dilli.com.np"

# Backup and log directory
LOG_DIR="/bck/OnlineBackup/logs"
BACKUP_DIR="/bck/OnlineBackup/mysqlbackup"

Create file purge_backup.sh This file cleanup the old backup files created on basic of retention period in months.


# purge_backup.sh 
#!/bin/bash
if [ "$#" -lt 1 ]; then
    echo "USAGE: $0: <Backup_dir> [Retention month: default 1]"
    exit 1
fi

# Variables declaration
RETENTION_MNTH=1
[ "$2" != "" ] && RETENTION_MNTH=$1
PURGE_MONTHS=$(expr ${RETENTION_MNTH} + 1)
#echo $RETENTION_MNTH 
#echo $PURGE_MONTHS
PURGE_FILE_WC=$(date +%Y_%m -d "-${PURGE_MONTHS} months")
BACKUP_DEST=$1


/bin/echo "Current date $(date +%Y_%m)"
/bin/echo "Purge date ${PURGE_FILE_WC}"
/bin/echo "Purging Following files from backup destination"

/bin/ls -1 ${BACKUP_DEST}/*${PURGE_FILE_WC}*

/bin/rm -f ${BACKUP_DEST}/*${PURGE_FILE_WC}*

if [ $? -eq 0 ]; then
    /bin/echo  "Backup Purged Successfully"
else
    /bin/echo "Backup Purged Failed"
fi

Make the script file executable.

chmod 755 online_backup.*
chmod 755 purge_backup.sh

Backup can be scheduled and executed via cron job.s We can set the cron job as below.

# cron setting
05 00 * * * /home/dilli/bin/online_backup.sh > /dev/null

Wednesday, July 22, 2015

Automatic Oracle Virtualbox VMs startup and shutdown on Linux

7:40 AM Posted by Dilli Raj Maharjan No comments

  • Create /etc/init.d/myvirtualbox executable file with following contents.


#!/bin/bash
# /etc/init.d/myvirtualbox


# Variable Declaration
declare -a HOSTS
HOSTS=( "OEL1" "OEL2" )
ACTION=""
DEBUG=1
USER="oracle"
LOCK_DIR="/var/lock/myvirtualbox"
LOCK_FILE=""
LOG_DIR="/var/log"
LOG_FILE="${LOG_DIR}/myvirtualbox.log"


# Function to log the information to logfile
function logit {
    echo $1
    echo $1 >> ${LOG_FILE}
}

# Function to check existance of lock file
function checklock {
    case $1 in
        1)
            if [ -e ${LOCK_FILE} ]; then
                logit "${LOCK_FILE} already exists, Fail to start Virtualbox host ${HOST}"
                logit "Verify VM status, if it is stopped then manually remove ${LOCK_FILE} to start"
                exit 1
            fi
        ;;
        2)
            if [ ! -e ${LOCK_FILE} ]; then
                logit "${LOCK_FILE} doesnot exists, Fail to stop Virtualbox host ${HOST}"
                logit "Verify VM status, if it is running then manually stop Virtual host ${HOST} with command below"
                logit "su - oracle -c \"VBoxManage controlvm ${HOST} savestate\""
                exit 1
            fi
        ;;
        *)
        ;;
    esac
}


# checking lock dir exists
if [ ! -e ${LOCK_DIR} ]; then
    [ $DEBUG -eq "1" ] && logit "Lock dir does not exists, Creating ${LOCK_DIR}"
    mkdir -p ${LOCK_DIR}
fi

# Starting, Stopping or resetting on basic of argument
case $1 in 
    start)
        logit "Starting myvirtualbox VMS"
        for HOST in "${HOSTS[@]}"
        do

            LOCK_FILE="${LOCK_DIR}/${HOST}"
            checklock 1
            CMD="VBoxManage startvm ${HOST} --type=headless"
            [ $DEBUG -eq 1 ] && echo $CMD
            su - ${USER} -c "$CMD" | tee -a ${LOG_FILE}
            [ $? -eq "0" ] && touch ${LOCK_FILE} 
            [ $? -eq "0" ] && logit "Virtualbox host ${HOST} started successfully"
        done

    ;;
    stop)
        logit "Stopping myvirtualbox VMS"
        for HOST in "${HOSTS[@]}"
        do
            LOCK_FILE="${LOCK_DIR}/${HOST}"
            checklock 2 
            CMD="VBoxManage controlvm ${HOST} savestate"
            [ $DEBUG -eq 1 ] && echo $CMD
            su - ${USER} -c "$CMD" | tee -a ${LOG_FILE}
            [ $? -eq "0" ] && rm ${LOCK_DIR}/${HOST}
            [ $? -eq "0" ] && logit "Virtualbox host ${HOST} stopped successfully"
        done

    ;;

    restart)
        logit "Restarting myvirtualbox VMS"
        if [ "$#" -eq "2" ]; then
            LOCK_FILE="${LOCK_DIR}/${2}"
            CMD="VBoxManage controlvm ${2} reset"
            [ $DEBUG -eq 1 ] && echo $CMD
            su - oracle -c "$CMD"
        else
        for HOST in "${HOSTS[@]}"
        do
            LOCK_FILE="${LOCK_DIR}/${HOST}"
            CMD="VBoxManage controlvm ${HOST} reset"
            [ $DEBUG -eq 1 ] && echo $CMD
            su - oracle -c "$CMD" | tee -a ${LOG_FILE}
            [ $? -eq "0" ] && logit "Virtualbox host ${HOST} restarted successfully"
        done

        fi
    ;;
    *)
        echo "Usage: $0 {start|stop|restart} [VMname in case of restart]"
        exit 1


esac


  • Make the file executable.

chmod 755 /etc/init.d/myvirtualbox

  • Create symbolic link of the executable file to run level directories.


ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc2.d/S99myvirtualbox
ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc3.d/S99myvirtualbox
ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc4.d/S99myvirtualbox
ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc5.d/S99myvirtualbox


ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc0.d/K01myvirtualbox
ln -s /etc/init.d/myvirtualbox /etc/rc.d/rc6.d/K01myvirtualbox

Wednesday, July 8, 2015

Using Listagg function

9:25 PM Posted by Dilli Raj Maharjan , 1 comment
 create table dlee(
username varchar2(32),
table_name varchar2(32)
);
/

insert into dlee
values('Anjan','Yamaha');
insert into dlee
values('Anjan','Bike');
insert into dlee
values('Anjan','R15');
insert into dlee
values('Anjan','Gwarko');
insert into dlee
values('Anjan','Dream');
insert into dlee
values('Anjan','CAR');
insert into dlee
values('Dilli','Suzuki');
insert into dlee
values('Dilli','Car');
insert into dlee
values('Dilli','Kirtipur');


Figure 1. Output of table dlee

select
   username,
   listagg (table_name, ',')
WITHIN GROUP
(ORDER BY username) username
FROM
   dlee
GROUP BY
   username
/


Figure 2. Output of listagg output

column table_list format a50
select
   username, ceil(line/3) as Sequence,
   listagg (table_name, ',')
WITHIN GROUP
(ORDER BY username) as table_list
FROM
(select
   username, table_name, rank() over (partition by username order by table_name) as LINE from dlee)
GROUP BY
   username,ceil(line/3)
/


Figure 3. Output of listagg output with 3 list in single line.