오라클 버전별 접속 가능 툴

오라클 버전에 따라서 접속 가능한 툴에 제한이 있었다.
너무 오래된 버전이다 보니 최신 툴은 지원하지 않았다. 아래는 과거의 오라클 DB에 접속 가능한 툴과 버전이다. 세부 버전이 약간만 달라도 지원되지 않는 경우가 발생 되었으니 감안 해야한다.

오라클 8i : Toad 9.7.2.5
오라클 9i : sqldeveloper-4.0.3.16.84

등록된 SYNONYMS과 DB LINK 확인하기

시노님 확인
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM ALL_SYNONYMS;
SELECT * FROM USER_SYNONYMS;

DB LINK 확인
SELECT * FROM USER_DB_LINKS;
SELECT * FROM DBA_DB_LINKS;

Oracle : 테이블 스키마 쿼리로 조회하기 - 산출물 작성용

--테이블 목록
SELECT * FROM USER_TAB_COMMENTS ORDER BY TABLE_NAME ASC;

--테이블 상세 조회
SELECT
    B.TABLE_NAME,
    B.COLUMN_ID AS 컬럼순서,
    B.COLUMN_NAME  AS 컬럼명,
    A.COMMENTS AS 설명,
    B.DATA_TYPE AS 테이터타입,
    B.DATA_LENGTH AS 길이,
    B.NULLABLE AS NULL구분
FROM USER_COL_COMMENTS A, USER_TAB_COLUMNS B, USER_TAB_COMMENTS C
WHERE B.TABLE_NAME = A.TABLE_NAME
    AND B.COLUMN_NAME = A.COLUMN_NAME
    AND A.TABLE_NAME = C.TABLE_NAME
ORDER BY B.TABLE_NAME, B.COLUMN_ID

--테이블 상세 조회 (PK 구분 포함)
SELECT
    AA.TABLE_NAME,
    AA.COLUMN_ID AS 컬럼순서,
    AA.COLUMN_NAME  AS 컬럼명,
    AA.COMMENTS AS 설명,
    AA.DATA_TYPE AS 테이터타입,
    AA.DATA_LENGTH AS 길이,
    AA.NULLABLE AS NULL구분,
    BB.PK AS 키타입
FROM (
    SELECT
        B.TABLE_NAME,
        B.COLUMN_ID,
        B.COLUMN_NAME,
        A.COMMENTS,
        B.DATA_TYPE,
        B.DATA_LENGTH,
        B.NULLABLE
    FROM USER_COL_COMMENTS A, USER_TAB_COLUMNS B, USER_TAB_COMMENTS C
    WHERE B.TABLE_NAME = A.TABLE_NAME
    AND B.COLUMN_NAME = A.COLUMN_NAME
    AND A.TABLE_NAME = C.TABLE_NAME
) AA, (
    SELECT A.TABLE_NAME, B.COLUMN_NAME, 'PK' AS PK
    FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
    WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND A.CONSTRAINT_TYPE = 'P'
) BB
WHERE AA.TABLE_NAME = BB.TABLE_NAME(+)
AND AA.COLUMN_NAME = BB.COLUMN_NAME(+)
ORDER BY AA.TABLE_NAME, AA.COLUMN_ID

YYYY-MM-DD HH24:MI:SS 형식의 날짜 조회하기

데이터베이스	쿼리문	쿼리결과
ORACLE	SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL	2009-12-10 16:55:11
MS-SQL	SELECT CONVERT(VARCHAR(19) ,GETDATE(), 120)	2009-12-10 16:55:11
MY-SQL	-	2009-12-10 16:55:11

Oracle : 통계 처리하는 쿼리문 예2

통계 처리하는 쿼리문 예2

--원본 데이터
SELECT * FROM test

--결과 데이터

--통계 처리한 데이터
SELECT
DECODE(SUBSTR(regdate,1,4),NULL,' 총계',SUBSTR(regdate,1,4)) yyyy,
DECODE(prpos,NULL,'소계',prposname) AS prposname,
DECODE(prpossort,NULL,99999,prpossort) AS prpossort,
prpos,
SUM(DECODE(SUBSTR(regdate,6,2),'01',1,null)) AS "1",
SUM(DECODE(SUBSTR(regdate,6,2),'02',1,null)) AS "2",
SUM(DECODE(SUBSTR(regdate,6,2),'03',1,null)) AS "3",
SUM(DECODE(SUBSTR(regdate,6,2),'04',1,null)) AS "4",
SUM(DECODE(SUBSTR(regdate,6,2),'05',1,null)) AS "5",
SUM(DECODE(SUBSTR(regdate,6,2),'06',1,null)) AS "6",
SUM(DECODE(SUBSTR(regdate,6,2),'07',1,null)) AS "7",
SUM(DECODE(SUBSTR(regdate,6,2),'08',1,null)) AS "8",
SUM(DECODE(SUBSTR(regdate,6,2),'09',1,null)) AS "9",
SUM(DECODE(SUBSTR(regdate,6,2),'10',1,null)) AS "10",
SUM(DECODE(SUBSTR(regdate,6,2),'11',1,null)) AS "11",
SUM(DECODE(SUBSTR(regdate,6,2),'12',1,null)) AS "12",
COUNT(hanoknum) AS total
FROM (
 SELECT * FROM test
)
GROUP BY CUBE(SUBSTR(regdate,1,4),prpos)
ORDER BY yyyy DESC, prpossort ASC

Oracle : 세로 데이터를 가로로 조회하기

세로 데이터를 가로로 조회하기

/******************************************************
* 원래 데이터
*******************************************************/
select
gi_num, m_id,
row_number() over(
    partition by gi_num
    order by gi_num, m_id
) rn
from user_group
where 1 = 1


    GI_NUM      M_ID                        RN
----------- -------------------- ----------
          2      jhee                          1
        25      cbcsky                    1
        25      jh22                          2
        25      jhee                          3
        26      cbcsky                      1
5 rows selected



/******************************************************
* 변환된 데이터
*******************************************************/
select
gi_num
, (max (decode (rn, 1, m_id))) id1
, (max (decode (rn, 2, m_id))) id2
, (max (decode (rn, 3, m_id))) id3
, (max (decode (rn, 4, m_id))) id4
, (max (decode (rn, 5, m_id))) id5
from (
    select
    gi_num, m_id,
    row_number() over(
        partition by gi_num
        order by gi_num, m_id
    ) rn
    from user_group
) group by gi_num


    GI_NUM ID1                  ID2                  ID3                  ID4
----------- -------------------- -------------------- -------------------- -----
          2      jhee                                                              
        25      cbcsky          jh22                jhee                    
        26      cbcsky                                                            
3 rows selected

Oracle : 셀렉트 문에서 순번을 매겨 조회하기

SELECT
ROW_NUMBER() OVER(ORDER BY c_sort ASC) AS rnum,
c_code, c_name, c_showchk, c_sort, rownum
FROM tableName

Oracle : 타 계정에 부여한 접근 권한 회수하기

--특정 테이블에 대한 권한의 부여
GRANT SELECT, INSERT, DELETE, UPDATE ON CODE TO USERID;

--특정 테이블에 대한 권한의 회수
REVOKE SELECT, INSERT, DELETE, UPDATE ON CODE FROM USERID;

--계정에 대해 권한 부여
GRANT 권한명 TO 유저명;

--계정에 대해 권한 회수
revoke 권한명 from 유저명;




--사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'USER_ID' ;

--사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER_ID' ;

--타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = 'USER_ID' ;

Oracle : 테이블스페이스 확인하는 쿼리

테이블스페이스 확인하기

SELECT * FROM USER_TABLES;

Oracle : random하게 select 해 주는 쿼리문

-- random하게 select 해 주는 쿼리문
select d_seq, row_number() over (order by dbms_random.value(1,3)) as rnum from cybr_doc



dbms_random.value 내장함수로 정렬 하면 아래 쿼리대로 데이터 10건을 무작위로 가져온다.
select no from (
select rownum no from tb_bbs order by dbms_random.value
) where rownum <= 10



1 ~ 100 까지 자연수를 가져온다.
select round(dbms_random.value(1,100)) from dual;

Oracle : 딸린 파일이 여러개인 게시판에서 대표되는 첨부파일 하나씩만 읽기

-- d_seq 로 그룹짓고 df_sort로 정렬하여 그룹 중 첫번째 것들만 가져오는 쿼리
-- 딸린 파일이 여러개인 게시판에서 대표되는 첨부파일 하나씩만 읽어올 때 사용하면 됨.
-- 이 쿼리와 게시판 내용 테이블을 조인하여 사용하면 됨.
SELECT * FROM (
    SELECT df_path, df_physical, df_type, d_seq,
    ROW_NUMBER() OVER(PARTITION BY d_seq ORDER BY df_sort) rn
    FROM cybr_doc_file WHERE df_type = 'A'
) WHERE rn = 1

Oracle : 옵션에 의한 백업(exp) 및 복원(imp)

exp 명령

1. 옵션 보기
exp help=y
2. 백업
exp scott/tiger full=y file=full.dmp
exp scott/tiger tables=emp,dept query='"where rownum<=100"' file=table100row.dmp
exp userid=scott/tiger file='exp1.dmp' tables=tableName


imp 명령

1. 옵션 보기
imp help=y
2. 백업
imp scott/tiger full=y grants=y file=full.dmp
imp scott/tiger file='dump_file_name' fromuser=가져올DB_id touser=보낼DB_id
imp scott/tiger tables=tableName grants=y file='exp1.dmp'



임포트 시에 테이블스페이스 부족으로 에러가 날 경우 테이블스페이스를 늘여준다.

alter tablespace test
add datafile 'D:/Oracle/oradata/test02.dbf' size 10000k;



!! 주의 : CLOB 형태의 컬럼이 포함된 경우 테이블스페이스 명칭을 동일하게 맞춰야만 import가 가능하다.

Oracle : 타 계정의 테이블에 접근 가능하도록 권한주기(시노님)

여기서
member 는 테이블 명
dbaUser 는 계정 명

1. 타 계정의 특정 테이블을 사용할 수 있도록 권한을 부여받는다.

2. 타 계정에 로그인 해서 명령을 내려준다.
예)
GRANT select, insert, delete, update ON 테이블명 TO 계정;
GRANT select ON member TO dbaUser;

3. 현재 계정으로 로그인 한다.

4. 권한을 부여받은 테이블에 별칭을 만들어 준다.
원래는 "타계정명.해당테이블명" 형식으로 테이블을 사용할 수 있으나,
테이블명을 바로 사용할 수 있도록 만든다.
예)
CREATE SYNONYM 테이블명 FOR 타계정명.사용할테이블명;
CREATE SYNONYM member FOR dbaUser.member;

5. 생성된 synonym은 타 계정에서 조회 가능하다.

6. synonym의 삭제도 현재의 계정에서 시행한다.
drop SYNONYM member;

Oracle : DTS를 이용하여 CLOB 포함된 형식의 데이터 가져오기

테이블 형식에 CLOB 가 포함된 경우의 엑셀 데이터 가져오기

1. 우선 txt 파일 형식(탭 구분)으로 저장한다.
2. 새로운 테이블을 생성함과 동시에 데이터를 저장한다.
    이때 날짜 데이터는 date형식으로, 긴 문자열은 잘리지 않도록 varchar(4000) 형식으로 설정한다.
    문자열이 길 경우 실패할 수 있다. 실제로 그랬었다.
3. 만들어진 테이블의 데이터를 쿼리문을 통해 실제 사용할 테이블로 옮긴다.
예)
  insert into cybr_bbs
      (BBSID, BBSSEQ, META)
  SELECT
      BBSID, SEQ, DAT
  FROM cybr_bbs2 Tbl

Oracle : 숫자만 select 해 오기

SELECT d_seq, d_time
FROM cybr_doc
WHERE ltrim(nvl(d_time,'no'), '0123456789') is null

Oracle : DTS로 데이터 가져올 때 오류 해결 법

엑셀 데이터,
또는 탭으로 구분하여 저장한 txt 데이터,
또는 csv 데이터를 오라클로 import 할 경우 작업이 이루어지지 않고 오류가 발생할 경우가 있다.
한번 오류가 발생하는 데이터는 수차례 반복해도 실패하기는 마찬가지이다.
여기서 얻은 결론은
import가 잘 안되어지는 이유는 한 컬럼에 긴 텍스트가 들어있는 경우였다.
이 문제를 해결하기 위하여 여러가지 방법을 동원해 보았다.
여러 방법을 동원해 보았으므로 결정적으로 어떤 방법이 직접적인 오류 해결 법인지는 명확하지 않지만, 유추 되는 사항을 적어본다.


해결법 1) 이건 아마 직접적인 해결 법은 아닌듯 하다. 이것 만으로 해결하지는 못했었다.

1. 시작 > 실행 > Regedt32
2. 레지스트리 편집기가 실행되면 아래의 키를 찾아간다.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
3. 항목중 TypeGuessRows를 더블 클릭.
4. DWORD 편집기 대화상자에서 단위는 10진수로 변경하고 값 데이터를 0~16사이로 조정한다.
16을 적용해 봤었음.
5. 레지스트리 편집기 종료 후 다시 Import 시도.


해결법 2) 이 방법을 사용 후 문제점이 해결 되었었다.

1. 데이터를 MS-SQL로 import 후 다시 MS-SQL에서 오라클로 import 했더니 한번에 처리가 되었다.

엑셀 데이터를 Oracle DB로 복사하기

이건 엑셀 데이터를 오라클 테이블로 가져올 때의 예이다.
MS DTS를 이용한다.

--> DTS 가져오기/내보내기 마법사를 실행한다
--> 다음
--> Microsoft Excel 97-2000을 선택한다
--> 파일을 선택한다
--> 다음
--> Microsoft ODBC Driver for Oracle를 선택한다
--> 서버 입력 : tns에 입력된 이름을 입력한다
--> 사용자이름 입력 : userName
--> 암호 입력 : password
--> 다음
--> 원본 데이터베이스에서 테이블 및 뷰 복사를 선택한다
--> 다음
--> 미리보기로 엑셀 데이터를 확인해 본다
--> 대상에서 데이터를 넣을 테이블을 선택한다
--> 다음
--> 다음
--> 마침

Oracle : 제약조건의 제거

primary key 또는 foreign key 등의 제약조건 삭제/제거


ALTER TABLE 테이블명칭
DROP CONSTRAINT 제약조건명칭

ALTER TABLE child_test_apps
DROP CONSTRAINT child_test_apps_fk1;

오라클 문자열 조작 함수

* 문자함수

LOWER : 소문자로 바꾸기
LOWER('SQL')

UPPER : 대문자로 바꾸기
UPPER('course')

INITCAP : 첫문자를 대문자로 바꾸기

CONCAT : 문자열 합치기
CONCAT('aaa', 'bbb')

SUBSTR : 문자열 자르기
SUBSTR('string', 1, 3) -> str
SUBSTR('abcdefghijklmn',5,5) -> efghi
!문자열이 null 이거나 빈 문자열인 경우에도 에러를 내지 않았고
프로그램 처리가 용이하게 안전한 빈 값을 반환하였음.

LENGTH : 문자수

NVL : null값 대체
NVL(deptno, '')

MS DTS를 이용하여 Oracle DB 복사하기

이건 오라클 DB를 복사할때의 예이다.

--> DTS 가져오기/내보내기 마법사를 실행한다
--> 다음
--> Microsoft ODBC Driver for Oracle를 선택한다
--> 서버 입력(서비스이름) : orcl
--> 사용자이름 입력 : userName
--> 암호 입력 : password
--> 다음
--> Microsoft ODBC Driver for Oracle를 선택한다
--> 서버 입력 : tns에 입력된 이름을 입력한다
--> 사용자이름 입력 : userName
--> 암호 입력 : password
--> 다음
--> 원본 데이터베이스에서 테이블 및 뷰 복사를 선택한다
--> 다음
--> 테이블을 선택한다
--> 다음
--> 다음
--> 마침