Oracle : find duplicate row and total duplicate

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Oracle : display all date in month

Here some example for show all date in current/before/after month or specific month

select to_char( add_months(trunc(sysdate,'MM'),-1) + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(add_months(trunc(sysdate,'MM'),-1)) -  add_months(trunc(sysdate,'MM'),-1) + 1;

Above is before current month

select to_char( add_months(trunc(sysdate,'MM'),1) + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(add_months(trunc(sysdate,'MM'),1)) -  add_months(trunc(sysdate,'MM'),1) + 1;

Above is after current month

select to_char( trunc(sysdate,'MM') + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(trunc(sysdate,'MM')) -  trunc(sysdate,'MM') + 1;

Above is current month

select to_char( trunc(to_date('20140101','YYYYMMDD'),'MM') + level - 1, 'YYYYMMDD' ) from dual connect by level <= last_day(trunc(to_date('20140101','YYYYMMDD'),'MM')) -  trunc(to_date('20140101','YYYYMMDD'),'MM') + 1;

Above is specific month but you must enter date.

Windows : Continous ping ipaddress

using this command you can ping ipaddress continously to monitor stablity of network between you pc to the ipaddress.

ping 192.168.2.201 -t

UNIX : Kill PID

How to kill process in UNIX ?

first get pid by using grep command.

root 21122 21123 0 13:00:01 ? 0:00 /temp/dummyscript.sh

get the PID number, yellow highlight for above output.

run this command : kill -9 21122 21123

or you can run multiple process by just add the pid number.

example : kill -9 12343 24437 372846 8364 8273 82746

Unix : Grep

grep specific program

ps -ef | grep *batch*
ps -ef | grep batch.sh


grep by username

ps -ef | grep root
ps -ef | grep unixuser

Oracle : How to find all indexes under certain table.

How to find all indexes under certain table.

select index_name, column_name

 from user_ind_columns

 where table_name = '<table_name>';

Oracle rebuild index

Hi,

Here some reference command that you can re-index you index.

select ' ALTER INDEX ' || INDEX_NAME || ' REBUILD ; COMMIT;' from user_indexes;

run this command, and copy all the output. Paste into SQL script or editor, and run as script.