728x90
Oracle 10g에서는 REGEXP_로 시작하는 함수를 지원합니다. 레귤러 익스프레션 지원이죠. 아래는 그 함수들의 사용방법을 공유합니다.

1. REGEXP_INSTR 함수
 - 문법 : REGEXP_INSTR(소스 문자열, Pattern [, 검색 시작 위치 [, 발생 횟수 [, 반환 옵션 [, Match를 시도할 때의 옵션]]]])
 - 예제
SQL> SELECT REGEXP_INSTR('Regular Expression', 'a') REG_INS 

  2  FROM dual;


REG_INS

----------

         6
2. REGEXP_LIKE
 - 문법 : REGEXP_LIKE(소스 문자열, Pattern [, Match를 시도할 때의 옵션])
 - [[:digit:]] : 숫자인것
 - [^[:digit:]] : 숫자가 아닌것
 - [[:alpha:]]
 - 필듯가 숫자와 문자가 같이 있을 경우 잘 판단해야 함
 - 예제
SQL> SELECT SSN 

  2  FROM   TEST

  3  WHERE  SSN > ' '

  4  and    REGEXP_LIKE(SSN, '[^[:digit:]]');


SSN

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

******2229149

******2228659

******2223591
3. REGEXP_REPLACE
 - REGEXP_REPLACE(소스 문자열, Pattern [, 바꿀 문자열 [, 위치 [, 발생횟수 [Match 파라미터]]]])
 - 예제
SQL> SELECT REGEXP_REPLACE(SSN, '[0-9]', '*' , 7) REG_REP

  2  FROM(

  3            SELECT '7901061842210' SSN

  4            FROM dual

  5  );


REG_REP

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

790106*******
4. REGEXP_SUBSTR
 - REGEXP_SUBSTR(소스 문자열, Pattern [, 위치 [, 발생 횟수 [, Match를 시도할 때의 옵션]]])
 - 예제
SQL> SELECT REGEXP_SUBSTR(EMAIL, '[^@]+') REG_SUB

  2  FROM(

  3           SELECT 'pepsi@paran.com' EMAIL

  4           FROM dual

  5  );


REG_S

-----

pepsi
원문 : http://mimul.com/pebble/default/2009/07/03/1246617840000.html
저작자표시

'데이터베이스 > 오라클' 카테고리의 다른 글

다중 인서트 insert all 오라클 쿼리문  (0) 2011.03.07
[펌] 유용한 오라클 쿼리 정리  (0) 2011.03.07
Protected: Presentation Scripts  (0) 2010.09.29
Oracle DBA Scripts and Tips  (0) 2010.09.28
오라클 튜닝 & 유지보수 관련정리  (0) 2010.09.28
728x90

Protected: Presentation Scripts

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2. ################################################################################
  3. # Script Name: check_alert_log.sh                                              #
  4. #                                                                              #
  5. # Oracle Shell Scripting, Chapter 12                                           #
  6. #                                                                              #
  7. # Usage: check_alert_log.sh SID                                                #
  8. #                                                                              #
  9. # Notes: If ORACLE_SID is set in the environment then the SID argument is      #
  10. #        optional                                                              #
  11. #                                                                              #
  12. # This script is from the book Oracle Shell Scripting by Jon Emmons            #
  13. # Copyright 2007 Rampant TechPress www.rampant-books.com                       #
  14. #                                                                              #
  15. # DISCLAIMER: Every environment is different.  This scrip may need to be       #
  16. #             customized before use and any script should be tested in a       #
  17. #             development environment before being used in production.         #
  18. ################################################################################
  19.  
  20. # Add /usr/local/bin to the PATH variable so the oraenv command can be found
  21. PATH=$PATH:/usr/local/bin; export PATH
  22.  
  23. # If a SID is provided as an argument it will be set and oraenv run
  24. # otherwise we will use the current SID.  If no SID is set or provided
  25. # an error message is displayed and the script exits with a status of 1
  26. if [ $1 ]
  27. then
  28.     ORACLE_SID=$1
  29.     ORAENV_ASK=NO
  30.     . oraenv
  31. else
  32.     if [ ! $ORACLE_SID ]
  33.     then
  34.         echo "Error: No ORACLE_SID set or provided as an argument"
  35.         exit 1
  36.     fi
  37. fi
  38.  
  39. # Set the ORACLE_BASE variable
  40. ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
  41.  
  42. cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
  43.  
  44. # Copy the current alert log into a temporary file and empty the original
  45. cp alert_$ORACLE_SID.log alert_$ORACLE_SID.log.temp
  46. cp /dev/null alert_$ORACLE_SID.log
  47.  
  48. # Check the copy in the temporary file for ORA- errors
  49. grep 'ORA-' alert_$ORACLE_SID.log.temp> /dev/null
  50. # If found, email the Oracle user with the contents of the alert log
  51. if [ $? = 0 ]
  52. then
  53.     mail -s "$ORACLE_SID database alert log error" oracle <\
  54.         alert_$ORACLE_SID.log.temp
  55. fi
  56.  
  57. # Move the contents of the temp file onto the permanent copy of the log
  58. # and remove the temp file.
  59. cat alert_$ORACLE_SID.log.temp>> alert_$ORACLE_SID.log.1
  60. rm alert_$ORACLE_SID.log.temp

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2. ################################################################################
  3. # Script Name: check_filesystem_space.sh                                       #
  4. #                                                                              #
  5. # Oracle Shell Scripting, Chapter 16                                           #
  6. #                                                                              #
  7. # Usage: check_filesystem_space.sh #percent /filesystem /filesystem2           #
  8. #                                                                              #
  9. # Notes: You can specify as many file systems as you would like by adding      #
  10. #        arguments.                                                            #
  11. #                                                                              #
  12. # This script is from the book Oracle Shell Scripting by Jon Emmons            #
  13. # Copyright 2007 Rampant TechPress www.rampant-books.com                       #
  14. #                                                                              #
  15. # DISCLAIMER: Every environment is different.  This scrip may need to be       #
  16. #             customized before use and any script should be tested in a       #
  17. #             development environment before being used in production.         #
  18. ################################################################################
  19.  
  20. # Set the percentage used at which you would like to be alerted
  21. # Use argument 1 if provided or use a given default.
  22. if [ ! $2 ]
  23. then
  24.     echo "No filesystems specified."
  25.     echo "Usage: check_filesystem_space.sh 90 / /u01"
  26.     exit 1
  27. fi
  28.  
  29. max=$1
  30.  
  31. # Email addresses are listed here separated by commas
  32. mail_to='root, oracle'
  33.  
  34. tempfile=/tmp/check_filesystem_space.txt
  35.  
  36. alert=n
  37.  
  38. # Take each percentage from the df command and check it against the defined max
  39. # Some platforms may not require (or even recognize) the -P option for df
  40. while [ $2 ]
  41. do
  42.     percent=`df -P $2 | tail -1 |  awk '{ print $5 }' | cut -d'%' -f1`
  43.  
  44.     if [ $percent -ge $max ]
  45.     then
  46.         alert=y
  47.         break
  48.     fi
  49.  
  50.     shift
  51.  
  52. done
  53.  
  54. # If a partition was above the threshold send a message with df output
  55. if [ ! $alert = 'n' ]
  56. then
  57.     df -k> $tempfile
  58.     mail -s "Disk usage above $max% on `hostname`" $mail_to <$tempfile
  59.     rm $tempfile
  60. fi

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2. ################################################################################
  3. # Script Name: check_for_invalid_objects.sh                                    #
  4. #                                                                              #
  5. # Oracle Shell Scripting, Chapter 14                                           #
  6. #                                                                              #
  7. # Usage: check_for_invalid_objects.sh SID                                      #
  8. #                                                                              #
  9. # Notes: If ORACLE_SID is set in the environment then the SID argument is      #
  10. #        optional.                                                             #
  11. #                                                                              #
  12. # This script is from the book Oracle Shell Scripting by Jon Emmons            #
  13. # Copyright 2007 Rampant TechPress www.rampant-books.com                       #
  14. #                                                                              #
  15. # DISCLAIMER: Every environment is different.  This scrip may need to be       #
  16. #             customized before use and any script should be tested in a       #
  17. #             development environment before being used in production.         #
  18. ################################################################################
  19.  
  20. # If a SID is provided as an argument it will be set and oraenv run
  21. # otherwise we will use the current SID.  If no SID is set or provided
  22. # an error message is displayed and the script exits with a status of 1
  23. if [ $1 ]
  24. then
  25.     ORACLE_SID=$1
  26.     ORAENV_ASK=NO
  27.     . oraenv
  28. else
  29.     if [ ! $ORACLE_SID ]
  30.     then
  31.         echo "Error: No ORACLE_SID set or provided as an argument"
  32.         exit 1
  33.     fi
  34. fi
  35.  
  36. # Define the location of the temporary file this script will use
  37. tempfile=/tmp/check_for_invalid_objects_$ORACLE_SID.txt
  38.  
  39. # Start sqlplus and connect as sysdba
  40. sqlplus -S "/ as sysdba" <<EOF1> /dev/null
  41.  
  42.     define exit_status = 0
  43.  
  44.     column xs new_value exit_status
  45.  
  46.     select 1 as xs from dba_objects where status!='VALID';
  47.  
  48.     exit &exit_status
  49.  
  50. EOF1
  51.  
  52. # If the exit status of sqlplus was not 0 then we will lauch sqlplus
  53. # to run utlrp.sql and send an email
  54. if [ $? != 0 ]
  55. then
  56.  
  57.     sqlplus -S "/ as sysdba" <<EOF2> $tempfile
  58.        
  59.         set pagesize
  60.  
  61.         select count(*) || ' invalid objects found.  Running utlrp.'
  62.         from dba_objects where status!='VALID';
  63.  
  64.         set pagesize 32
  65.  
  66.         @?/rdbms/admin/utlrp.sql
  67.  
  68. EOF2
  69.  
  70.     mail -s "Invalid objects found in $ORACLE_SID" oracle <$tempfile
  71.  
  72. fi
  73.  
  74. rm $tempfile

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2. ################################################################################
  3. # Script Name: rman_hot_backup.sh                                              #
  4. #                                                                              #
  5. # Oracle Shell Scripting, Chapter 13                                           #
  6. #                                                                              #
  7. # Usage: rman_hot_backup.sh SID #backuplevel                                   #
  8. #                                                                              #
  9. # Notes: If not specified the backup level will default to 1 and the SID       #
  10. #        will be taken from the environmental variable ORACLE_SID              #
  11. #                                                                              #
  12. # This script is from the book Oracle Shell Scripting by Jon Emmons            #
  13. # Copyright 2007 Rampant TechPress www.rampant-books.com                       #
  14. #                                                                              #
  15. # DISCLAIMER: Every environment is different.  This scrip may need to be       #
  16. #             customized before use and any script should be tested in a       #
  17. #             development environment before being used in production.         #
  18. ################################################################################
  19.  
  20. # Add /usr/local/bin to the PATH variable so the oraenv command can be found
  21. PATH=$PATH:/usr/local/bin; export PATH
  22.  
  23. # A second argument can be provided to give a backup level
  24. # if the backup level is not provided a level 0 backup will be performed.
  25. if [ $2 ]
  26. then
  27.     backup_level=$2
  28. else
  29.     backup_level=0
  30. fi
  31.  
  32. # If a SID is provided as an argument it will be set and oraenv run
  33. # otherwise we will use the current SID.  If no SID is set or provided
  34. # an error message is displayed and the script exits with a status of 1
  35. if [ $1 ]
  36. then
  37.     ORACLE_SID=$1
  38.     ORAENV_ASK=NO
  39.     . oraenv
  40. else
  41.     if [ ! $ORACLE_SID ]
  42.     then
  43.         echo "Error: No ORACLE_SID set or provided as an argument"
  44.         exit 1
  45.     fi
  46. fi
  47.  
  48. ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
  49.  
  50. admin_dir=$ORACLE_BASE/admin/$ORACLE_SID; export admin_dir
  51.  
  52. backup_user=backup_admin
  53. backup_user_pw=`cat $ORACLE_BASE/admin/$ORACLE_SID/pw/$backup_user.pw`
  54.  
  55. catalog_user=rman
  56. catalog_user_pw=`cat $ORACLE_BASE/admin/$ORACLE_SID/pw/$catalog_user.pw`
  57.  
  58. # Backup variables.  Set the backup_dir to the appropriate
  59. # location for your site.
  60. log_file=$admin_dir/scripts/rman_hot_backup_$ORACLE_SID.log
  61.  
  62. # This adds some text and a date stamp to the beginning of the backup log
  63. echo "Beginning hot backup of $ORACLE_SID"> $log_file
  64. date>> $log_file
  65.  
  66. rman target=$backup_user/$backup_user_pw \
  67. catalog=$catalog_user/$catalog_user_pw@rman <<EOF>> $log_file
  68.  
  69. backup incremental level=$BACKUP_LEVEL database plus archivelog delete input;
  70.  
  71. delete noprompt obsolete;
  72.  
  73. quit;
  74.  
  75. EOF
  76.  
  77. # Add some end text and timestamp to the log file
  78. echo "Finished hot backup of $ORACLE_SID">> $log_file
  79. date>> $log_file
  80.  
  81. # Email appropriate folks only if an error is found
  82. grep "ORA-" $log_file> /dev/null
  83. ora_err=$?
  84. grep "RMAN-" $log_file> /dev/null
  85. rman_err=$?
  86. if [ $ora_err = 0 -o $rman_err = 0 ]
  87. then
  88.     mail -s "$ORACLE_SID Hot Backup Problem" oracle <$log_file
  89. fi

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2. ################################################################################
  3. # Script Name: dba_schema_export.sh                                            #
  4. #                                                                              #
  5. # Oracle Shell Scripting, Chapter 13                                           #
  6. #                                                                              #
  7. # Usage: dba_schema_export.sh SID schema                                       #
  8. #                                                                              #
  9. # Notes: This script must be run as a user who can connect as sysdba           #
  10. #                                                                              #
  11. # This script is from the book Oracle Shell Scripting by Jon Emmons            #
  12. # Copyright 2007 Rampant TechPress www.rampant-books.com                       #
  13. #                                                                              #
  14. # DISCLAIMER: Every environment is different.  This scrip may need to be       #
  15. #             customized before use and any script should be tested in a       #
  16. #             development environment before being used in production.         #
  17. ################################################################################
  18.  
  19. exp_arguments='USERID="/ as sysdba" BUFFER=10485760 FULL=N'
  20.  
  21. # Add /usr/local/bin to the PATH variable so the oraenv command can be found
  22. PATH=$PATH:/usr/local/bin; export PATH
  23.  
  24. ORACLE_SID=$1
  25. ORAENV_ASK=NO
  26. . oraenv
  27.  
  28. ORACLE_BASE=/u01/app/oracle
  29.  
  30. schema=$2
  31.  
  32. log_file=$ORACLE_BASE/admin/$ORACLE_SID/scripts/dba_schema_export_$ORACLE_SID_$schema.log
  33.  
  34. exp_arguments="$exp_arguments OWNER=$schema"
  35.  
  36. # If a third argument was specified use it as the file destination
  37. if [ $3 ]
  38. then
  39.     exp_arguments="$exp_arguments FILE=$3"
  40. else
  41.     exp_arguments="$exp_arguments FILE="$ORACLE_SID"_"$schema"_export.dmp"
  42. fi
  43.  
  44. # Put some informational text in the log file
  45. echo "Starting export `date`"> $log_file
  46. echo "Exporting with the following arguments: $exp_arguments">> $log_file
  47.  
  48. # Run the export with the arguments provided and capture the result code
  49. exp $exp_arguments>> $log_file 2>&1
  50.  
  51. exp_result=$?
  52.  
  53. echo "Completed export `date`">> $log_file
  54.  
  55. # Email appropriate folks only if an error is found
  56. if [ exp_result != 0 ]
  57. then
  58.     mail -s "$ORACLE_SID Export Problem" oracle <$log_file
  59. fi

PLAIN TEXT
CODE:
  1. @echo off
  2. REM Script Name: run_report.bat                                               
  3. REM                                                                           
  4. REM Oracle Shell Scripting, Chapter 17                                       
  5. REM                                                                           
  6. REM Usage: run_report.sh                                                     
  7. REM                                                                           
  8. REM Notes: The variables sould be set to the appropriate credentials and     
  9. REM script location for your script.
  10. REM                                                                           
  11. REM This script is from the book Oracle Shell Scripting by Jon Emmons         
  12. REM Copyright 2007 Rampant TechPress www.rampant-books.com                   
  13. REM                                                                           
  14. REM DISCLAIMER: Every environment is different.  This scrip may need to be   
  15. REM             customized before use and any script should be tested in a   
  16. REM             development environment before being used in production.     
  17.  
  18. REM Set the variables below with your connection and script information
  19. REM All output from sqlplus will be sent to the output file
  20. set USERNAME=scott
  21. set PASSWORD=tiger
  22. set SID=ossw
  23. set SQL_SCRIPT=D:\oracle\product\10.1.0\admin\common\report.sql
  24. set OUTPUT_FILE=D:\oracle\product\10.1.0\admin\common\logs\report.txt
  25.  
  26. REM run sqlplus in silent mode with the parameters set above
  27. sqlplus -S %USERNAME%/%PASSWORD%@%SID% @%SQL_SCRIPT%> %OUTPUT_FILE%
  28.  
  29. @echo on

This post is password protected. Enter the password to view comments.

저작자표시

'데이터베이스 > 오라클' 카테고리의 다른 글

[펌] 유용한 오라클 쿼리 정리  (0) 2011.03.07
오라클 정규식 사용 팁  (0) 2010.11.05
Oracle DBA Scripts and Tips  (0) 2010.09.28
오라클 튜닝 & 유지보수 관련정리  (0) 2010.09.28
소계 & 총계 구하는 SQL (ROLLUP)  (0) 2010.08.16
728x90

Scripts sorted by Title


  1. Cold Database Backup ( Oracle : 8i 9i )
    Shell Script : Retreives a list of all database files from the database whilst still running an
    Updated: 24-NOV-04

  2. Cold Database Backup ( Oracle : 7 8 )
    Shell Script : Retreives a list of all database files from the database whilst still running an
    Updated: 08-JAN-02

  3. Create Database TEmplate ( Oracle : 7 8 )
    Shell Script : Creates a database. You will need to edit this and enter you system tablespace d
    Updated: 26-OCT-98

  4. Create Database Template ( Oracle : 8i 9i )
    Shell Script : Creates a database. You will need to edit this and enter you system tablespace d
    Updated: 18-APR-02

  5. Database Shutdown ( Oracle : 7 8 )
    Shell Script : Simple database shutdown script. Gets the ORACLE_HOME from /etc/oratab. This scr
    Updated: 29-JUL-98

  6. Database Shutdown ( Oracle : 8i 9i 10g )
    Shell Script : Simple database shutdown script. Gets the ORACLE_HOME from /etc/oratab. This scr
    Updated: 18-APR-02

  7. Database Size ( Oracle : 7 8 )
    Shell Script : Displays physical size off all databases on a UNIX Machine.
    Updated: 08-JAN-02

  8. Database Size ( Oracle : 8i 9i )
    Shell Script : Displays physical size off all databases on a UNIX Machine.
    Updated: 03-APR-02

  9. Database Startup ( Oracle : 7 8 )
    Shell Script : Simple database startup script. Gets the ORACLE_HOME from /etc/oratab. This scri
    Updated: 29-JUL-98

  10. Database Startup Script ( Oracle : 8i 9i 10g )
    Shell Script : Simple database startup script. Gets the ORACLE_HOME from /etc/oratab. This scri
    Updated: 18-APR-04

  11. Display the Number Of CPUS ( Oracle : )
    Shell Script : Display the number of CPUS in the major UNIX platforms
    Updated: 31-JAN-03

  12. Full Database Export ( Oracle : 7 8 8i 9i )
    Shell Script : Full database export and compress using named pipes. Use the full database impor
    Updated: 29-JUL-98

  13. Full Database Import ( Oracle : 7 8 8i 9i )
    Shell Script : Full database import and uncompress using named pipes.You will need this if you
    Updated: 24-MAY-99

  14. Hot Database Backup ( Oracle : 7 8 )
    Shell Script : Generates SQL script put the tablespace in backup mode and to do a unix copy to
    Updated: 29-JUL-98

  15. Hot Database Backup ( Oracle : 8i 9i )
    Shell Script : Generates SQL script put the tablespace in backup mode and to do a unix copy to
    Updated: 13-SEP-02

  16. Set Database ARCHIVELOG Mode ( Oracle : 7 8 )
    Shell Script : Shut database down and sets the database in Archive Log Mode. This script us
    Updated: 29-JUL-98

  17. Set Database ARCHVELOG Mode ( Oracle : 8i 9i )
    Shell Script : Shut database down and sets the database in Archive Log Mode.
    Updated: 18-APR-02

  18. Show Installed Oracle Version ( Oracle : 7 8 8i )
    Shell Script : This script will list out all Oracle products installed by the last Oracle insta
    Updated: 08-OCT-02

  19. Solaris Memory Calculation ( Oracle : )
    Shell Script : Gives actual memory usage of Oracle from a Solaris Operating System using the 'p
    Updated: 28-FEB-02

  20. Standby Database Gap Detection Script. ( Oracle : 8i )
    Shell Script : Detects if a standby database is up to date. Send an OEM alert is not. Script
    Updated: 02-JUN-05

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links

저작자표시

'데이터베이스 > 오라클' 카테고리의 다른 글

오라클 정규식 사용 팁  (0) 2010.11.05
Protected: Presentation Scripts  (0) 2010.09.29
오라클 튜닝 & 유지보수 관련정리  (0) 2010.09.28
소계 & 총계 구하는 SQL (ROLLUP)  (0) 2010.08.16
오라클 NLS_LANG 설정변경  (0) 2010.08.16

+ Recent posts

티스토리툴바