Oracle Relation Query - DBA_CONSTRAINTS

Ref. http://www.shutdownabort.com/dbaqueries/Structure_Constraints.php
Ref. http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm

-- View The Relation of The Specific Table

col type format a10
col cons_name format a30
select decode(constraint_type,
  'C', 'Check',
  'O', 'R/O View',
  'P', 'Primary',
  'R', 'Foreign',
  'U', 'Unique',
  'V', 'Check view') type
, constraint_name cons_name
, status
, last_change
, R_CONSTRAINT_NAME
, RELY
from dba_constraints
where owner like '&owner'
and table_name like '&table_name'
order by 1
/

-- View The PK column of The Specific Table

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = '&table_name'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cons.owner ='&owner'
ORDER BY cols.table_name, cols.position;

Linux change atime,mtime using touch

Ref. http://www.csie.cyut.edu.tw/~dywang/linuxSystem/node37.html

1. 檔案變動時間
   1. atime (Access time)：檔案內容上次被讀取的時間；
   2. ctime (Change status time)：檔案狀態最近被更動的時間（不論是屬性、權限等）；
   3. mtime (Modification time)：檔案內容最近被修改的時間。
2. 查詢檔案 /etc/man.conf 變動時間
   
   

   [root@dywHome2 ~]# ll /etc/man.config <==預設為 mtime
   -rw-r--r-- 1 root root 4831 Feb  3  2008 /etc/man.config
   [root@dywHome2 ~]# ll --time=atime /etc/man.config
   -rw-r--r-- 1 root root 4831 Sep  5 09:30 /etc/man.config
   [root@dywHome2 ~]# ll --time=ctime /etc/man.config
   -rw-r--r-- 1 root root 4831 Feb  3  2008 /etc/man.config
   [root@dywHome2 ~]# ll --time=mtime /etc/man.config
   

   
   
3. touch：修改檔案時間，若檔案不存在，則產生新檔案。
   
   

   [root@linux ~]# touch [-acdmt] 檔案
   參數：
   -a  ：僅修訂 access time；
   -c  ：僅修改時間，而不建立檔案；
   -d  ：後面可以接日期，也可以使用 --date="日期或時間"
   -m  ：僅修改 mtime ；
   -t  ：後面可以接時間，格式為[YYMMDDhhmm]
   範例：
   
   範例一：新建一個空的檔案
   [root@linux ~]# cd /tmp
   [root@linux tmp]# touch testtouch
   [root@linux tmp]# ls -l testtouch
   -rw-r--r--  1 root root    0 Jul 19 20:49 testtouch
   
   範例二：將 ~/.bashrc 複製成為 bashrc，假設複製完全的屬性，檢查其日期
   [root@linux tmp]# cp ~/.bashrc bashrc
   [root@linux tmp]# ll bashrc; ll --time=atime bashrc; ll --time=ctime bashrc
   -rwxr-xr-x  1 root root 395 Jul  4 11:45 bashrc  <==這是 mtime
   -rwxr-xr-x  1 root root 395 Jul 19 20:44 bashrc  <==這是 atime
   -rwxr-xr-x  1 root root 395 Jul 19 20:53 bashrc  <==這是 ctime
   
   範例三：修改案例二的 bashrc 檔案，將日期調整為兩天前
   [root@linux tmp]# touch -d "2 days ago" bashrc
   [root@linux tmp]# ll bashrc; ll --time=atime bashrc; ll --time=ctime bashrc
   -rwxr-xr-x  1 root root 395 Jul 17 21:02 bashrc
   -rwxr-xr-x  1 root root 395 Jul 17 21:02 bashrc
   -rwxr-xr-x  1 root root 395 Jul 19 21:02 bashrc
   # 日期在 atime 與 mtime 都改變了，但是 ctime 則是記錄目前的時間！
   
   範例四：將上個範例的 bashrc 日期改為 2005/07/15 2:02
   [root@linux tmp]# touch -t 0507150202 bashrc
   [root@linux tmp]# ll bashrc; ll --time=atime bashrc; ll --time=ctime bashrc
   -rwxr-xr-x  1 root root 395 Jul 15 02:02 bashrc
   -rwxr-xr-x  1 root root 395 Jul 15 02:02 bashrc
   -rwxr-xr-x  1 root root 395 Jul 19 21:05 bashrc

MySQL Loop Usage --- select whole year status

-- Create Procedure
DELIMITER $$
CREATE PROCEDURE TIMELOOP()

   BEGIN
      DECLARE a INT Default 0 ;
      simple_loop: LOOP
         select DATE_ADD('20120101',interval a day) "Date", week(DATE_ADD('20120101',interval a day)) "Week", date_format(DATE_ADD('20120101',interval a day),"%m") "Month",quarter(DATE_ADD('20120101',interval a day)) "Season", date_format(DATE_ADD('20120101',interval a day),"%Y") "Year";
         SET a=a+1;
         IF a=366 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$

-- Run Procedure
call TIMELOOP();

-- Delete Procedure
DROP PROCEDURE IF EXISTS `TIMELOOP`;

-- Check If exists procedures
show PROCEDURE status;

換一個方式生活 !

不是只有一種生活~
除了朝九晚五的拼命賺錢~
還有其他的生活方式~
重點是了解自己要的是什麼~ 了解自我~!
人一生的時間不多~ 快 "準備好" 去追你的夢~!!
~~~ Share with 有夢，追夢，逐夢，的你 ~~~

Delete Files Older than 5 days

Ref. http://www.howtogeek.com/howto/ubuntu/delete-files-older-than-x-days-on-linux/

find /path/to/files* -mtime +5 -exec rm {} \;

[Android] Flash Android Official ROM without ROOT

Ref1. http://www.wugfresh.com/dev/nexus-root-toolkit/

Ref2. http://www.junefabrics.com/android/download.php

Ref3. https://developers.google.com/android/nexus/images#takju

ü  Prerequisite : (Warning – This might cause the warranty of product issue)

1.      Nexus Root Toolkit (NRT_v1.5.4.sfx.exe)

2.      PdaNet for Android (pdanetz350.exe - Version 3.50 installer)

3.      Galaxy Nexus Official ROM (takju-jzo54k-factory-92830c0b.tgz)

※ps. Version Desc

ü  Procedure :

1.          Install Driver - pdanetz350.exe

2.          Connect Galaxy Nexus to PC (enable usb debug mode)

※Setup à Developer options à Turn On à check USB debugging

↑Figure For step 3,4,5

3.          Choose the Current Version You are using. (Mine is 4.0.4)

4.          Use “Inial Setup” to help you automatically check if there is anything error.

5.          Unlock (Data would be gone in this step and reboot the mobile)

6.          Enable usb debug mode again

7.          Clikc “Flash Stock + Unroot” à Choose the ROM(takju) you just download

8.          Type in the md5 (4e79a918a9a366166d0c23f99cc240db)

9.         Done ! You can now upgrade via OTA from Google !!

Linux Package Management - RPM, DPKG

===============================================

dpkg

===============================================

Install Package ---->

 -i(--install)

Remove Package---->

 -r(--remove)：remove totally exluding config file

 -p(--purge)  ：remove totally including config file

Query Package ---->

 -l(--list)      ：list all package

 -L package：print info about package(file location…)

 -S file：Search which package is the file belong to (such as rpm –qf)

===============================================

apt-get

===============================================

apt-get

 -d：Only download the package file

 -y：force to answer yes

 install  package：install

 remove package：remove

 update：list available package

===============================================

rpm

===============================================

rpm db location: /var/lib/rpm

rpm  --rebuilddb

---------------------------------------------------------------------------

Package Verify

---------------------------------------------------------------------------

 -V  Package : Verify the changed file belonged to the package and list them.

 -Va : List all the changed file belonged to all packages.

 -Vp Package : List possibly changed file belonged to the package.

 -Vf  file : Verify if the file is been changed.

 -K Package : check the Package’s  MD5 checksum and pgp.

 --checksig：check Package’s MD5 checksum and pgp.

 S ：file Size differs

M ：Mode differs (includes permissions and file type)

5 ：MD5 sum differs

D ：Device major/minor number mis-match

L ：readLink(2) path mis-match

U ：User ownership differs

G ：Group ownership differs

T ：mTime differs

 import keys : rpm --import /usr/share/rhn/RPM-GPG-KEY

 /usr/share/doc/rpm-4.4.2/RPM-GPG-KEY

 /usr/share/doc/fedora-release-5/RPM-GPG-KEY

 /etc/pki/rpm-gpg/RPM-GPG-KEY

---------------------------------------------------------------------------

rpm -ivh Package

---------------------------------------------------------------------------

 -i ：install

 -v ：show the process

 -h ：show the process in percent.

 --nodeps :

 --nomd5 :

 --noscripts :

 --replacefiles :

 --replacepkgs :

 --force :

 --test :

---------------------------------------------------------------------------

Upgrade and Update

---------------------------------------------------------------------------

 -Uvh : Install and update.

 -Fvh : Upgrade only if the older package is installed

---------------------------------------------------------------------------

Remove Package

---------------------------------------------------------------------------

 rpm –e Package

 rpm –e Package --allmaches : Remove all versions of the

package which match PACKAGE_NAME.

 rpm –e Package --nodeps : Don't check dependencies before

uninstalling the packages.

---------------------------------------------------------------------------

Query

---------------------------------------------------------------------------

rpm  -q  Package : check if the package exists.

 rpm  -ql Package : List all the files belonged to the package.

 rpm  -qi Package : Print info about the package.

 rpm  -qf file : Search which package is the file belong to.

 rpm  -qc Package : List the config file of the package.

 rpm  -qd Package : List the document about the package location.

 rpm  -qR Package : List the dependency of the package(prequesite).

 rpm  -qa :List all the installed package.

Linux Bash Array Usage using IFS

Ref. http://go-linux.blogspot.tw/2007/03/basharray.html

#!/bin/bash
#Put everything into array.
array=(Redhat Novell MicroSoft Sun IBM HP Dell)


#Print all value in array , seperated by IFS(:).
IFS=:
echo "${array[*]}"

#Print all value in array , seperated by IFS(\n).
IFS=$'\n'
echo "${array[*]}"

#Print all value in array , seperated.
echo "${array[@]}"

#Print the count of array.
echo "${#array[@]}"

Re:
array[0]=Redhat
array[1]=Novell
array[2]=MicroSoft
array[3]=Sun
array[4]=IBM
array[5]=HP
array[6]=Dell
Redhat:Novell:MicroSoft:Sun:IBM:HP:Dell
Redhat
Novell
MicroSoft
Sun
IBM
HP
Dell
Redhat Novell MicroSoft Sun IBM HP Dell
7

Linux RPM Query command

Ref. http://www.cyberciti.biz/faq/how-do-i-find-what-dependencies-a-rpm-file-has/

How do I find what dependencies a rpm file has?

rpm -qpR {.rpm-file}
rpm -qR {package-name}

How do I remove rpm package recursively?

By using yum remove ---  

yum remove  {package-name}

PHP Time Calculate using "strtotime"

Ref. http://stackoverflow.com/questions/277247/increase-days-to-php-current-date
<?php

$day=3;
echo date("Y-m-d",strtotime("+$day days"));
?>

It would display the date in 3 days.

Shell Script Output with Color - Bash

Ref. http://linuxtidbits.wordpress.com/2008/08/11/output-color-on-bash-scripts/

#!/bin/bash
# tputcolors

echo
echo -e "$(tput bold) reg  bld  und   tput-command-colors$(tput sgr0)"

for i in $(seq 1 7); do
  echo " $(tput setaf $i)Text$(tput sgr0) $(tput bold)$(tput setaf $i)Text$(tput sgr0) $(tput sgr 0 1)$(tput setaf $i)Text$(tput sgr0)  \$(tput setaf $i)"
done

echo ' Bold            $(tput bold)'
echo ' Underline       $(tput sgr 0 1)'
echo ' Reset           $(tput sgr0)'
echo

#!/bin/bash
# scriptname - description of script

# Text color variables
txtund=$(tput sgr 0 1)          # Underline
txtbld=$(tput bold)             # Bold
bldred=${txtbld}$(tput setaf 1) #  red
bldblu=${txtbld}$(tput setaf 4) #  blue
bldwht=${txtbld}$(tput setaf 7) #  white
txtrst=$(tput sgr0)             # Reset
info=${bldwht}*${txtrst}        # Feedback
pass=${bldblu}*${txtrst}
warn=${bldred}*${txtrst}
ques=${bldblu}?${txtrst}

MySQL string to date function

Ref. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

MySQL String Replace

Ref. http://stackoverflow.com/questions/1806949/mysql-query-to-replace-spaces-in-a-column-with-underscores

update photos set filename = replace(filename, ' ', '_');

ie, you search for ' ' in the column filename and use '_' instead ; and put the result back intofilename.

Linux Search Disk Format using df

root# df -hT

See also:

• cat /etc/fstab
• cat /etc/mtab

Linux ssh login fail counter using pam_tally2.so

Configuration:

Ref. http://secureos.wordpress.com/2011/07/26/rhel6-ssh-lockouts/
Ref. http://fvue.nl/wiki/Bash:_Piped_%60while-read'_loop_starts_subshell
Choice 1:

1.  Configure /etc/pam.d/sshd ONLY

Add the following line to the auth section:

auth       required    pam_sepermit.so

auth       required     pam_tally2.so even_deny_root (deny=5 unlock_time=60 even_deny_root)

※must be exactly under pam_sepermit.so(only 1 line after)

Choice 2:

2. Configure /etc/pam.d/password-auth ONLY

auth        required      pam_env.so
auth        required      pam_tally2.so even_deny_root

※must be exactly under pam_sepermit.so(only 1 line after)

--------------------------------------------------------

Action:Someone Failed Attemp times reach 3. Do something:

fail_log_watch:

#!/bin/bash
pam_tally2|grep -v Login | while read line
do
        test -z "$line" && exit
        FAIL_ARRAY=($line)
        FAIL_ACCOUNT=${FAIL_ARRAY[0]}
        FAIL_COUNT="${FAIL_ARRAY[1]}"
        FAIL_DATE="${FAIL_ARRAY[2]} ${FAIL_ARRAY[3]}"
        FAIL_IP=${FAIL_ARRAY[4]}
        if (($FAIL_COUNT>=3))
        then
        #==========DO SOMETHING HERE==========
                Message="Account:\"${FAIL_ACCOUNT}\", Failed Login OVER 3 times at ${FAIL_DATE} from ${FAIL_IP}"
                echo ${Message}
        #==========DO SOMETHING HERE==========
        #After DO something reset it
        pam_tally2 -u ${FAIL_ACCOUNT} --reset
        fi
done

------------------------------------------------------------
Useful Reference Usage:
Ref. http://www.unix.com/aix/107866-how-send-alert-email-whenever-failed-login.html

# vi logwatch.sh

LOG=/logs/userauth.log
echo "\n\n" >> ${LOG}
tail -1 -f ${LOG} |
while read LINE
do
case "${LINE}" in
failed)
echo ${LINE} | mailx -s "Failed login" me@mail.com ;;
esac
done

--------------------------------------------------
/etc/pam.d/sshd

#%PAM-1.0

auth       required     pam_tally2.so deny=5 unlock_time=60 even_deny_root

auth       required     pam_sepermit.so

auth       include      password-auth

account    required     pam_nologin.so

account    include      password-auth

password   include      password-auth

# pam_selinux.so close should be the first session rule

session    required     pam_selinux.so close

session    required     pam_loginuid.so

# pam_selinux.so open should only be followed by sessions to be executed in the user context

session    required     pam_selinux.so open env_params

session    optional     pam_keyinit.so force revoke

session    include      password-auth