한번사는 인생 화끈하게!

1. ORACLE 11g admin(?)을 설치하고 scott계정으로 로그인 할때 발생했던 문제.
문제: ORA-28000:the account is locked
해결방법: ALTER USER SCOTT ACCOUNT UNLOCK;

2. 테이블 스페이스 생성 예제
CREATE TABLESPACE 테이블스페이스명
DATAFILE '/app/mymind/oradata/orcl/테이블스페이스명.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M
MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

예)
CREATE TABLESPACE GMS_TBS DATAFILE
'/oradata/SICCGMS/gms_tbs.dbf' SIZE 10000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;



3. 테이블 스페이스 확인 및 데이터 파일확인 (2번에서 만든 것)
-SELECT * FROM DBA_DATA_FILES; 
-SELECT * FROM DBA_TABLESPACES;

4. 유저 확인
-SELECT * FROM DBA_USERS;
테이블 데이터를 넣을 유저를 확인 - 예)GSS.RCOENT(여기서 GSS)

5. 유저 생성 및 테이블스페이스지정
CREATE USER 유저ID IDENTIFIED BY 유저비번 DEFAULT TABLESPACE 사용할테이블스페이스명TEMPORARY TABLESPACE TEMP;

6. 유저에게 권한

GRANT CREATE SESSION TO 유저ID;

GRANT CREATE TABLE, UNLIMITED TABLESPACE TO 유저ID;

GRANT CREATE SESSION TO 유저ID WITH ADMIN OPTION;

GRANT IMP_FULL_DATABASE TO 유저ID;

GRANT CREATE ROLE TO 유저ID;

GRANT CREATE TRIGGER TO 유저ID;

GRANT CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO 유저ID;

모든권한이 다 필요한지는 모르겠지만 IMPORT를 받으려면 GRANT IMP_FULL_DATABASE TO 유저ID;는 꼭필요하다.

참고: 권한 - http://itislord.tistory.com/250, http://kimsds.egloos.com/9629631, http://totoriver.egloos.com/2439843

7. 특정 테이블만 EXP
cmd창에
EXP 유저id/유저비번@sid명 file=EXPDAT.DMP LOG=EXPDAT.LOG TABLES=(exp할 테이블을 나열 tmp1, tmp2....)

EXP '유저id/유저비번@sid명 as sysdba' file=EXPDAT.DMP LOG=EXPDAT.LOG TABLES=(exp할 테이블을 나열 tmp1, tmp2....)

참고: http://icary.tistory.com/16, EXP-http://sangu12.egloos.com/2784067


8. import하기
cmd창에
IMP 유저id/유저비번@sid명 full=y
(넣을 테이블만 EXP한것이므로 full로 함.)

9. 만약 IMP를 다 하고 계정을 지우려고 하면 ORA-01922를 만나게 되는데
그때의 해결방법은
DROP USER 유저ID CASCADE;
출처: http://blog.naver.com/xxsicxx?Redirect=Log&logNo=90028355450

출처: http://www.statwith.pe.kr/ORACLE/functions114.htm

REGEXP_INSTR

문법

MAIN

regexp_instr::=

그림 설명
 

목적

MAIN

지정한 조건(정규 표현)을 만족하는 부분의 최초의 위치(무슨 문자인지)를 돌려줍니다. 또한, 검색을 시작하는 시작 위치를 지정하는 것도 가능합니다

REGEXP_INSTR함수는 정규 표현 패턴에 대한 문자열을 검색하는 INSTR함수의 기능을 확정한 것이다. 이 함수는 입력 문자 세트에 의해 정의되는것과 같은 문자를 이용하여 문자열을 평가한다. 그것은 return_option인수의 값에 의존하는 일치되는 substring의 처음과 종료 위치를 나타내는 정수를 반환한다. 만약 일치하는 값이 발견되지 않으면, 함수는 0을 반환한다.

이 함수는 POSIX 정규 표현 규격과 유니코드 정규 표현 가이드 라인을 따른다.

• source_string는 검색 값으로 다루어지는 문자 표현이다. 보통 문자열이고, CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB 데이터중에 하나이다.

• pattern은 정규 표현이다. 이것은 일반적으로 텍스트 문자이고, CHAR,VARCHAR2,NCHAR,NVARCHAR2의 데이터형이다. 최대 512바이트를 지정할수 있다. 만약 pattern의 데이터형이 source_string의 데이터형과 다른 경우에는, 오라클 데이터베이스는 pattern을 source_string의 데이터형으로 변환한다. pattern에서 지정할수 있는 연산자의 리스트는  Appendix C, " Oracle Regular Expression Support" 참조.

• Position은 오라클이 검색을 시작하는 source_string의 문자를 나타내는 양의 정수이다. 기본값은 1이고, source_char의 처음 문자를 검색을 시작한다.

• occurrence은 source_string에서 pattern의 발생하는 것 중에서 나타나는 양의 정수이다. 기본값은 1이고, 오라클이 pattern의 처음 발생하는것을 검색하는것을 의미한다.

• return_option은, 오라클이 발생하는 것과 관련한 것을 지정할수 있다.

  • 만약 0을 지정하면, 오라클은 발생의 첫 문자의 위치를 반환한다. 기본값이다.

  • 만약 1을 지정하면, 오라클은 발생한것의 다음 문자의 위치를 반환한다.

• match_parameter은 함수의 기본적 일치하는 행동을 변경하기 위한 텍스트 문자이다. match_parameter에 대한 다음 값의 하나 이상을 지정할수 있다.

  • 'i'는 대소문자를 구별하지 않는다.

  • 'c'는 대소문자를 구별한다.

  • 'n'은 새줄 문자와 일치하는 match-any-character문자인 period(.)을 허용한다. 만약 이 파라미터를 생략하면, 피어리드는 새줄 문자와 일치하지 않는다.  (http://pcrc.hongik.ac.kr/~progman/docs/regexp/node6.html)

  • 'm'는 다중 라인과 같은 소스 문자열을 처리한다. 오라클은 소스 문자에 임의의 라인에 선두와 끝으로써 ^과 $을 해석한다. 만약 이 파라미터를 생략하면, 오라클은 단일행으로써 소스 문자열을 처리한다.

  만약 복수의 모순된 값을 지정하면, 오라클은 마지막 값을 이용한다. 예를들어, 만약 'ic'를 지정하면, 오라클은 대소문자를 구별하는 검색을 한다. 만약 위에서 보이는것과 다른 문자를 지정하면, 오라클은 에러를 발생한다.

  만약 match_parameter를 생략하면,

  • 기본적 대소문자 구별은 NLS_SORT 파라미터의 값에 의해 정의 된다.

  • period(.)은 새줄문자와 일치하지 않는다.

  • 소스 문자열은 단일행으로써 취급된다.

    
    

    참 조 : INSTR and REGEXP_SUBSTR http://blog.empas.com/phpaspjsp/read.html?a=14672699&c=1232545 http://www.oracle.com/technology/global/kr/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html http://www.oracle.com/technology/global/kr/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html http://blog.empas.com/kk760105/17154912 도서 : Oracle Regular Expressions 참조

    
    

예제

MAIN

다음 예제는 문자열을 조사하고, 하나 이상의 공백 이외의 문자의 발생을 검색한다. 오라클은 문자열에서 처음 문자로부터 검색을 시작하여, 하나 이상의 비공백 문자의 6번째 발생의 시작 위치를 반환한다.

SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
          37

다음 예제는 문자열을 조사하고, 대소문자를 구별하지 않고, s,r,p로 시작하여, 임의의 6번째 알파벳 문자가 발생하는 단어를 검색한다. 

오라클은 문자열에서 3번째 문자에서 검색을 시작해서, 대소문자의 s,r,p로 시작되는 7 문자의 단어가 2번째 출현한 후의 문자열내의 위치를 반환한다.

SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
          28

이 함수는 패턴의 시작 위치를 반환하며, 따라서 INSTR 함수와 유사한 형태로 동작합니다. REGEXP_INSTR함수의 사옹 방법은 표 6에서 확인할 수 있습니다. 두 함수의 가장 중요한 차이는 REGEXP_INSTR를 이용하는 경우 특정 문자열이 아닌 패턴을 지정할 수 있으며, 따라서 훨씬 유연한 검색이 가능하다는 사실입니다. 다음 예에서는 REGEXP_INSTR을 사용하여 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234문자열에서 5 개의 숫자로 구성된 우편 번호 패턴의 시작 부분을 반환하고 있습니다. 정규 표현식 [[:digit:]]{5}를 사용하는 경우 우편 번호가 아닌 집 주소 번호의 시작 위치를 얻게 됩니다 (처음으로 검색되는 5 개 연속 숫자 패턴이 10045이기 때문입니다). 따라서 $ 메타문자를 사용하여 표현식의 앵커를 라인 끝부분으로 지정해야 합니다. 이렇게 하면 집 주소 번호에 관계없이 우편 번호의 시작 위치를 얻을 수 있습니다.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
       '[[:digit:]]{5}$')
       AS rx_instr
  FROM dual
  RX_INSTR
----------
        45

B(b)또는 C(c)가 2번 이상 문자열에 들어 있는 데이터 검색

SELECT * FROM TEST_REGEX WHERE REGEXP_INSTR(VALUE, '[b-c]', 1, 2, 0, I)>0;

출처: http://cherrykyun.tistory.com/194

13. INSTR
 INSTR(char1, char2, n, m) : char1 문자열 중에서 char2가 포함된 문자열의 위치가 char1 기준으로 앞에서 몇 번째 있는지 알려줌. n번째 위치부터 시작해서 m번째로 char2와 같은 위치를 돌려줌
 INSTRB(char1, char2, n, m)

출처: http://isecurity.textcube.com/12

SELECT
  ts.status
 ,data.name
 ,ts.contents
 ,extent_management
 ,data.Mbytes "SPACE(MB)"
 ,free.free   "FREE(MB)"
 ,trunc((data.Mbytes-free.free)/data.Mbytes*100,2) "Used(%)"
FROM ( select
     tablespace_name name
    ,trunc(sum(bytes/1024/1024)) Mbytes
   from dba_data_files
         group by tablespace_name
      ) data,
  ( select
      free.tablespace_name
     ,trunc(sum(free.bytes)/1024/1024,1) free
   from dba_free_space free
  group by free.tablespace_name
  ) free,
dba_tablespaces ts
WHERE data.name = free.tablespace_name
  AND data.name = ts.tablespace_name
;

궁금증: 사용자가 날짜 입력을 숫자만 하지 않고 30/08/2010 으로 입력한 경우
어떻게 날짜를 비교해야 할까?

방안1: REPLACE를 사용해서 '/'기호를 ''로 치환한 후 DATE형으로 변경한다.
TO_DATE(TO_CHAR(REPLACE(REC_DT, '/', '')), 'DDMMYYYY')

예) SELECT ITEM_CD, REC_DT
        FROM DMSIOM
      WHERE PROD_CD=NVL('',PROD_CD)
           AND TO_DATE(TO_CHAR(REPLACE(REC_DT, '/', '')), 'DDMMYYYY')
                  BETWEEN TO_DATE(TO_CHAR(REPLACE('01/08/2010', '/', '')), 'DDMMYYYY') 
                          AND TO_DATE(TO_CHAR(REPLACE('01/09/2010', '/', '')), 'DDMMYYYY')

방안2: /로 입력받은것도 TO_DATE의 날짜형식중 하나이므로 바로 DATE형으로 변경한다.
         TO_DATE(컬럼명,'DD/MM/YYYY')


내가 생각한건 1안이지만 최적의 방안은 2안이다.
TO_CHAR로 변경할 필요없이 TO_DATE형을 바로 사용했기 때문이다.

DBLink보기

SELECT db_link, username, password, host, created
   FROM user_db_links

Oracle/PLSQL: ORA-02069 Error

Error:

 ORA-02069: global_names parameter must be set to TRUE for this operation

Cause:

 You tried to execute an operation that requires a remote mapping. This can not be done because the parameter called GLOBAL_NAMES is not set to TRUE.

Action:

 The options to resolve this Oracle error are:

Try setting the GLOBAL_NAMES parameter to TRUE with the following statement:

ALTER SESSION SET GLOBAL_NAMES = TRUE;

 

http://www.techonthenet.com/oracle/errors/ora02069.php

 

여기 한번 참고해보셔도 되구요.. 위 방법은

instance 레벨에서의 처리입니다. 오라클을 다시 마운트하시면, 설정이 없어집니다.

 

영구적인 설정이라면 initSID.ora 파일의 parameter중

Golbal_names 라는 파라메터의 값이 true로 되어 있어야 합니다. 그래야 오라클을 재구동하여도 계속 적용되어 있습니다.

 

내용은 좀 더 찾아보니..

db_link master site와 slave site의 DB name Alias가 같아야 한다는 것 같네요 ~

참고출처 : http://www.okjsp.pe.kr/seq/129226

ORA-02085: 데이터베이스 링크 EKP가 EKP.US.ORACLE.COM에 연결됩니다

해결: 결론은, global_names = true여서 생긴 문제였습니다.

alter session set global_names = False로 변경후, 에러 없이 연결이 잘 되었고, ini parameter에 명시적으로 넣어서 해결하였습니다.

참고 출처: http://forums.oracle.com/forums/thread.jspa?threadID=420795&tstart=740