본문 바로가기

컴퓨터활용/오라클

정규식 이용 SQL 소문자가 하나라도 들어있는 데이터만 추출하기 위하여 정규식을 이용한 SQL 구문을 작성하고자 인터넷을 찾아보니 아주 정리를 잘 해 놓은 블로그가 있어서 갈무리 한다.참조사이트 : http://goalker.tistory.com/71 [사용예제] 소문자 영문자가 들어있는 행 출력 - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]') ;대문자 영문자가 들어있는 행 출력 - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]') ;대소문자 영문자가 들어있는 행 출력 - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-zA-Z]') ; 소문자로 시작하고 뒤에 공백이 있는 모든.. 더보기
윈도우7에서 Oracle 11g Client 삭제하기 오라클 클라이언트 프로그램 설치하다가 잘못 설치하게 되면 보통일이 아니다. uninstall 하는 방법이 졸라리 복잡하다. 한두시간 날아가기는 일아니기 때문에 훗날 다시 삽질하는 분들이 또 있을까봐 글을 남긴다. 오라클 클라이인트 프로그램을 설치할 때 설치소스가 있는 디렉토리에 한글이 있다든지 할 때 제대로 깔리지 않고 중단되는 수가 있다. 그러면 이렇게 찌꺼기가 남게 되는 것이다. uninstall이 이렇게 어렵게 되는 이유가 뭔지 이해가 안 되지만 어쩔 수 없다. 나의 작업 환경 첫번째 설치시도를 c:\app\oracle\product\11.2.0\client_1 에 했다. 그런데 실패. 그래서 다시 설치를 시도했는데 c:\app\oracle\OraHome_2 로 시도했다가 다시 실패. 찌꺼기로 남은.. 더보기
오라클 비밀번호 대소문자구분 없애기 오라클11g 부터는 비밀번호가 대소문자 구분이 된다. 그 이전버젼에서 오라클은 userid 와 비밀번호에 대소문자 구분없이 사용해 왔던 개인 사용자들은 당황스러운 상황을 만날 수 있다. SQL> connect / as sysdba연결되었습니다. 먼저 sysdba로 데이터베이스에 접속한다. 그 다음 아래와 같은 문장을 입력한다. SQL> alter system set sec_case_sensitive_logon=false; 이 문장은 오라클 접속 시 패스워드에 대한 대소문자 구분 여부를 설정하는 것으로만약 대소문자를 구분하고 싶다면 당연히 true로 설정하고 그렇지 않으면 false로 설정한다. 다음 문장을 통해 현재 설정 값이 제대로 바뀌었는지 확인한다. SQL> show parameter sec_cas.. 더보기
ORA-28000: the account is locked ORA-28000: the account is locked 오류를 만났을 때 해결방법 오랫동안 사용하지 않다가 접속했더니 로그인이 안 되네요. SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE FROM DBA_USERS; 했더니 LOCK 걸린 것은 아니고 패스워드가 EXPIRED 되었네요. USERNAME ACCOUNT_STATUS LOCK_DATE------------------------------ -------------------------------- ----------------MGMT_VIEW OPENSYS OPENSYSTEM OPENDBSNMP OPENSYSMAN OPENBXM EXPIRED(.. 더보기
ORA-00257: archiver is stuck 오류 오라클 db 에 이미지 파일을 왕창 올리고 났더니 갑자기 sqlplus 로 login할때 ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed 에러를 만났다. PC에서나 UNIX 서버에서나 어떻게든 sqlplus 접속이 원천 차단되었기에 별도리가 없었다. 물론 JEUS DB 접속도 안 되고 있다. 인터넷을 찾아보니 대부분의 원인은 redo log file을 적재하는 장치의 용량 부족으로 발생하며, 이런 경우 connect internal만 가능하다고 나와 있다. 그래서 df -k 명령으로 디스크별 사용량을 보았더니 아래와 같이 /arch 디스크가 100% 차 있는 것이 보인다. 이 에러는 archive 디렉토리가 디스크가 full 나서 그렇다.. 더보기
오라클 SUBSTR 으로 LEFT, RIGHT 처리 ORACLE 내장함수에는 LEFT(), RIGHT() 함수가 없다. substr 함수를 통해 똑같이 구현이 가능하다. @ 3번째 자리부터 쭉- SELECT substr('ABC홍길XY', 3) FROM DUAL; C홍길동XY @ 4번째 자리부터 2글자 SELECT substr('ABC홍길동XY', 4, 2) FROM DUAL; 홍길 한글은 한글자로 인식된다는 점에 주의 SELECT substr('일이삼사오육칠팔구', 5, 3) FROM DUAL; 오육칠 @ 우측부터 추출하고 싶다면 마이너스(-) 기호를 사용하면 된다. 우측 3글자 SELECT substr('12345678', -3) FROM DUAL; 678 더보기
오라클 테이블스페이스 용량 늘리기 1. tablespace 이름으로 관련 data file 찾기 SELECT file_name, tablespace_name, bytes FROM dba_data_files WHERE tablespace_name = '' 2. tablespace 크기 늘리기 (1) data file 추가(가장 많이 쓰는 방법) ALTER TABLESPACE ADD DATAFILE '' SIZE ; (2) 기존의 data file 크기 변경 ALTER DATABASE DATAFILE '' RESIZE ; 예제) 20GB 로 늘림. alter DATABASE datafile '/user01/oradata/hera/users01.dbf' resize 20000M (3) datafiledml size를 자동으로 늘어나게 하는 명.. 더보기
오라클(Oracle) SID 및 DB_NAME 확인 방법 jdbc 에서 thin 드라이버로 오라클에 접속할 때는 SID를 알아야 한다. 최근에는 SID로 직접 기술하여 접근하는 것보다는 service name 이라는 것을 tnsname.ora 파일에 지정해 놓고 이것을 사용한다. 아무래도 SID가 공개되는 것이 문제가 될 수 있을 것이다. PRODDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 152.25.24.15)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) ) 서비스명과 인스턴스명과 데이타베이스명과 SID는 서로 비슷한 듯 하면서 약간 다르다. 1. 오라클 데이타베이스명을 확인하는 방법 SELECT NAME, DB_UNIQ.. 더보기
오라클 테이블의 디스크 용량 예측 현재 테이블들이 얼마의 데이타를 사용하고 있는지는 테이블스페이스의 사용량을 보고 대략 짐작할 수 있다. 그러나 테이블의 각각의 레코드 사이즈에 데이타건수를 곱하여 세밀하고 조사하고 싶을 경우에는 아래와 같이 테이블의 레코드 사이즈를 구해볼 수 있다. 테이블의 디스크사용량이나 예측용량을 구하기 위하여 우선 아래의 sql를 사용할 수 있다. 물론 SYS 유저 이거나 system dictionary 테이블을 grant 받아서 조회가 가능해야 하겠다. SELECT OWNER 소유자, TABLE_NAME 테이블ID, TABLE_COMMENT 테이블명, SUM(DATA_LENGTH) 레코드사이즈 FROM ( SELECT A.OWNER OWNER, A.TABLE_NAME TABLE_NAME, B.COMMENTS AS.. 더보기
Oracle Locking Survival Guide Oracle Locking Survival Guide 1. Overview 멀티유저시스템에서는 여러사람이 동일한 정보를 동시에 변경하게 된다. 락은 오직 한 사용자만이 특정 데이타를 변경할 수 있도록 허용하는 것이다. 다른 사람은 동일데이타를 변경할 수 없다. The basic idea of locking is that when a user modifies data through a transaction, that data is locked by that transaction until the transaction is committed or rolled back. The lock is held until the transaction is complete - this known as data concur.. 더보기
오라클 LOCK 조회 및 KILL 방법 프로젝트에서 LOCK 이 자주 걸리는 문제가 발생하여 연구하던 중에 아래의 sql 문을 이용하여 lock 세션을 찾아서 관리할 수 있다는 것을 알았다. 그러나 이 SQL 은 System 유저만이 사용할 수 있다는 제약이 있다. LOCK 이 발생하게 되면 프로그램이 동작하다가 멈추고 기다리게 된다. 원인을 파악하는데 시간이 허비하게 되는데 알고 보니 테이블에 LOCK이 걸린 것이었다면 허탈함을 금할 수 없다. 자주 LOCK 이 걸리는 테이블이라면 SELECT 할 때 WAIT 타임을 1 정도로 주면 1초동안 LOCK 이 풀리기를 대기하다가 오류 처리되므로 개발자가 쉽게 LOCK 걸린 상황을 인지할 수 있다. -- 락걸린 테이블 확인 SELECT DO.OBJECT_NAME, DO.OWNER, DO.OBJECT.. 더보기
오라클 매뉴얼 각종 매뉴얼 : http://download.oracle.com/docs/cd/E14072_01/index.htm http://download.oracle.com/docs/cd/E11882_01/server.112/e17118.pdf 더보기
Golden 에서 접속 오류 날 때 처음 실행해서 접속을 시도할 때 Initialization error SQL*Net not properly installed OracleHomeKey:SOFTWARE\ORACLE OracleHomeDir: 라는 오류 팝업이 뜨면 다음과 같이 조치한다. 이것은 10g에서 잘 사용하다가 개발환경이 11g로 업그레이드되면서 Golden 이 제대로 인식을 하지 못 하기 때문인데 최신버젼으로 Update 를 하면 해결된다. GOLDEN 화면 ----> HELP ----> CHECK THE WEB FOR A NEW VERSION.... 선택 후 최신 버전으로 업그레이드 하여 사용하시면 됩니다. 더보기
INDEX_DESC HINT 게시판 프로그램을 작성할 때 최신 게시물이 먼저 나오도록 하는 경우에 index_desc 를 많이 사용하게 된다. 사용방법 SELECT /*+ INDEX_DESC(TABLE_ALIAS_NAME INDEX_NAME) */ * FROM TABLE_NAME TABLE_ALIAS_NAME WHERE KEY1 = :B1 (index_name index가 key1 column에 생성되어 있다) 윗 문장에 rownum=1 조건을 추가하면 key가 제일 큰 것 하나만 조회되므로, max function의 기능을 대신할 수 있다. 최신글 조회하고자 한다면 다음과 같이 한다. SELECT * FROM ( SELECT /*+ INDEX_DESC(B TB_CO_ARTICLE_PK) */ ROWNUM AS RN, B.* FRO.. 더보기
ORA-01003 에러 발생 원인 및 조치 ORA-01003 에러 발생 원인 및 조치 현황에 대한 참고할 만한 글이 있다. 주로 발생되는 경우로는 배치서버에서 가결산 작업 중 금리제공모듈, 고객정보모듈 등 에서 ORA-01003 오류 다수 발생되었다. ORA-01003 발생 원인은 배치 프로그램 또는 TP서비스 구동 중 DB(서버) 메모리 내의 테이블 정보와 AP(서버) 프로그램단 SQL내에서 참조하는 테이블 정보가 서로다른 경우 발생 되므로 이런 경우는 대부분 프로그램 구동중에 테이블 재생성 작업을 수행하는 경우에 발생 가능하다. 프로젝트에서 데이타 전환을 하는 과정에서 현재 데이터 재적재(전환원본DB -> 운영DB)가 일중 수시로 진행되고 있고, 이 재적재는 내부적으로 테이블 재생성(drop & create)과정을 거치게 됨으로써 발생하고 있.. 더보기
오라클 날짜 함수 SELECT /* 오늘날짜 시분초 포함*/ TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL; SELECT /* 오늘날짜 00시 00분 00초 */ TO_CHAR(TRUNC(SYSDATE),'YYYY/MM/DD HH24:MI:SS') FROM DUAL; SELECT /* 오늘날짜 00시 00분 00초 위와 동일*/ TO_CHAR(TRUNC(SYSDATE,'DD'),'YYYY/MM/DD HH24:MI:SS') FROM DUAL; SELECT /* 마이크로세컨드 */ TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS.FF') FROM DUAL; SELECT /* 밀리세컨드 */ TO_CHAR(CURRENT_TIMESTAMP(3).. 더보기
오라클 리스너 떠 있는지 확인하는 방법 오라클 리스너 확인하기 1. 리스너 구동하기 lsnrctl start : 리스너 구동하기 lsnrctl stop : 리스너 중단하기 이 명령어는 오라클이 설치되어 있는 서버에서 실행해야 한다. 당연 oracle 유저로 실행해야 먹을 것이다. 윈도우 커맨드모드에서 실행하는 client 용 명령어는 아니다. 2. 리스너 환경 파일 $ORACLE_HOME/network/admin 아래에 있는 listener.ora 3. tnsping 사용하여 외부에서 리스너 동작 확인하기 사용법 : tnsping TNS명 C:\Documents and Settings\SHCHOI> tnsping DBDCBS1 TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Product.. 더보기
오라클에 ' 문자(작은 따옴표) 를 데이타로 저장하고 싶을 때 SQL로 작은따옴표 문자를 DB에 넣고자 한다면 어떻게 하냐고 물어온 사람이 있다. 문자열은 작은따옴표('') 로 값을 앞뒤로 묶어서 구분을 해 주기 때문에 정작 작은따옴표를 값으로 넣으려면 어떻게 해야 할 지 난감할 수가 있다. 예를 들어 저장하고자 하는 데이타가 '최성환' 와 같이 ' 문자를 포함한 것이라고 한다면 다음과 같은 SQL을 작성하여 실행해 보고 그 결과를 본다면 이해가 되실 것이다. UPDATE afee_comm_base SET comm_mclas_nm = '''최성환''' WHERE comm_cd = '4607391018'; SELECT * FROM afee_comm_base WHERE comm_cd = '4607391018'; 그렇다면 \ 나 " [ ] - 이런 문자 들은 어떻게 하면.. 더보기
ORACLE exp 예제 개발 중에 데이타를 수시로 백업을 해 둘 필요가 있다면 오라클에서 제공하는 exp 를 이용해서 데이타를 백업할 수 있다. 우선 다음과 같이 백업하는 shell 프로그램을 간단하게 작성하고 backupdb.sh 라고 저장을 한다. db 접속할 user와 password, 저장할 백업파일명, 그리고 테이블들의 list는 당연히 본인의 것으로 수정을 해야 할 것이다. 유닉스에서는 테이블수가 많을 경우 역슬래쉬문자(\) 로 라인을 끊어서 나열열할 수 있다. 테이블을 지정하지 않고 특정 유저의 모든 오브젝트를 내려받고자 한다면 USER=user_id 로 기술하면 되겠다. #!/usr/bin/ksh today=`date '+20%Y%m%d-%H%M%S'` export back_file=~/backup/fee_dat.. 더보기
PL SQL 문법 정리 SQL문 SELECT DML(데이터 조작어) INSERT, UPDATE, DELETE DDL(데이터 정의어) IMPLICIT COMMIT CREATE, ALTER, DROP, RENAME, TRUNCATE TCL(트랜잭션 제어) COMMIT, ROLLBACK, SAVEPOINT DCL(데이터 제어어)IMPLICIT COMMIT GRANT, REVOKE [1] Writing Basic SQL Statements 1. SELECT 기본 문장(선택, 프로잭션, 조인) SELECT [DISTINCT] { *, column [alias], ... } FROM table ; 2. SELECT 예제 SELECT * FROM dept ; SELECT deptno, loc FROM dept ; SELECT ename, .. 더보기
쉘에서 ORACLE SQL 실행하여 결과 받아오기 SELECT 결과를 shell에서 참조해야 하는 경우 다음과 같이 하면 된다. 방법 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 #!/bin/ksh VALUE=`sqlplus -silent "user/passwd@sid" null "usr/passwd@sid" 더보기
오라클 기본 정보 select 더보기
oracle 테이블스페이스 생성 SQL>SELECT * FROM DBA_TABLESPACES; SQL>SELECT * FROM V$DATAFILE; Warning: For operating systems that support raw devices, be aware that the STORAGE clause REUSE keyword has no meaning when specifying a raw device as a data file in a CREATE TABLESPACE command; such a command will always succeed even if REUSE is not specified. Example I This command creates a tablespace named TABSPACE_2 with one d.. 더보기
clob 컬럼 데이타 조회하기 컬럼의 데이터 타입이 CLOB인 경우 SELECT를 해보면 데이터가 80 byte 정도만 보입니다. CLOB에는 매우 큰 데이터가 들어갈 수 있기 때문에 항상 데이터 전체를 보여줄 수 있는 것이 아니므로 미리 지정된 길이 만큼만 잘라서 보여주게 되어 있는데, 디폴트는 80 byte 입니다. 때에 따라서는 80 byte 만 보는 것으로는 부족할 때도 있는데, 이럴 때는 다음과 같은 방법으로 보이게 할 데이터의 길이를 지정할 수 있습니다. SQL*Plus 상에서는 SQL> set long 500 과 같이 하면 CLOB 데이터를 500 byte까지 보이게 합니다. Orange 에서도 역시 80 byte만 보이도록 디폴트로 설정되어 있는데, 메뉴바>Option>Options... 를 선택해 다이얼로그를 띄운 다.. 더보기
v$session SQL>select * from v$session; SQL> desc v$session; 이름 널? 유형 ----------------------------------------- -------- ---------------- SADDR RAW(8) SID NUMBER SERIAL# NUMBER AUDSID NUMBER PADDR RAW(8) USER# NUMBER USERNAME VARCHAR2(30) COMMAND NUMBER OWNERID NUMBER TADDR VARCHAR2(16) LOCKWAIT VARCHAR2(16) STATUS VARCHAR2(8) SERVER VARCHAR2(9) SCHEMA# NUMBER SCHEMANAME VARCHAR2(30) OSUSER VARCHAR2(30) PR.. 더보기
oracle user SQL> desc dba_users; 이름 널? 유형 ----------------------------------------- -------- -------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUME.. 더보기
오라클 유저 생성 SQL>SELECT * FROM DBA_USERS; SQL>CREATE USER USER_ID IDENTIFIED BY PASSWD DEFAULT TABLESPACE TBS_CLUB TEMPORARY TABLESPACE TEMP; /* CONNECTION 등 기본적인 권한을 부여함 */ SQL>GRANT CONNECT, RESOURCE TO UEBPP; SQL>ALTER USER USER_ID IDENTIFIED BY PASSWD DEFAULT TABLESPACE TBS_CLUB2 TEMPORARY TABLESPACE TEMP; 더보기
oracle listener status check #krsead001457712[/oracle/OraHome1/bin] lsnrctl LSNRCTL for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 15-FEB-2001 10:53:28 (c) Copyright 1998 Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> help The following operations are available An asterisk (*) denotes a modifier or extended command: start stop status services version reloa.. 더보기
ORACLE export / import 예제 Using Command-Line Keywords Keywords are optionally separated by commas. They are entered in any order. Keywords are followed by valid arguments. For example: SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis, DISCARDMAX=5 최초 insert할 때 control file ============================== LOAD DATA badfile apt.bad insert into table t.. 더보기
ORACLE DATA DICTIONARY TABLE Data Dictionary Views Available Through Heterogeneous Services This appendix lists the data dictionary views that are supported through heterogeneous services mapping: ALL_CATALOG ALL_COL_COMMENTS ALL_COL_PRIVS ALL_COL_PRIVS_MADE ALL_COL_PRIVS_RECD ALL_CONSTRAINTS ALL_CONS_COLUMNS ALL_DB_LINKS ALL_DEF_AUDIT_OPTS ALL_DEPENDENCIES ALL_ERRORS ALL_INDEXES ALL_IND_COLUMNS ALL_OBJECTS ALL_SEQUENCES AL.. 더보기