프로시저 결과를 화면에 출력하고 싶을때 사용

SQL> show serveroutput

serveroutput OFF

SQL> set serveroutput on

----테스트

SQL> begin

  2   dbms_output.put_line('확인');

  3  end;

  4  /

확인   >  출력되면 성공

PL/SQL procedure successfully completed.

 초보의 SQL 기초 - Oracle 접속

오라클 같은 DB는 별도의 서버에 설치하고 다른 서버나 PC에서 telnet이나 SSH로 접속하는 것이 일반적인 경우겠지만, 실습을 위해서 클라이언트 툴(토드, 오렌지 등) 없이 도스 프롬프트에서 sqlplus를 실행시키는 방법에 대해 알아본다.

Sqlplus로 접속하기

sqlplus를 통해 오라클에 로그인하기 위한 명령어는 아래와 같다. 

> sqlplus  [계정명]/[계정 패스워드][@<connect_identifier>] | 

           /  [AS {SYSDBA | SYSOPER | SYSASM}] 

( [ ] 부분 생략 가능 )

ex) sqlplus id/pw@ip:port/sid

- 원격접속이 아니고 오라클이 설치된 PC에서 직접 접속할 경우 @connect_identifier 생략가능 (ex1)

- sqlplus 계정명까지만 입력하면 패스워드 입력화면으로 넘어간다. (ex2)

- 또는 프로그램 실행시듯 sqlplus만 입력하고 다음 화면에서 계정명과 패스워드를 입력해도 된다. (ex3)

- 오라클 설치할 때 변경하지 않았다면 초기 비밀번호는 다음과 같다. 

    system / manager

    sys / change_on_install

    scott/tiger

- scott 계정이 잠겨있는 경우라면 아래 Lock 해제 부분 참고

ex1) sqlplus scott/tiger

ex2) sqlplus scott

ex3) sqlplus

sys계정 접속하기

오라클에서는 관리자 계정으로 sys와 system 계정이 있다. 

sys 계정이 일단 두목쯤 된다고 보고, system은 관리자 계정이라고 해두자. 

사용자 계정을 추가하거나 database를 새로 만들 때는 sys 계정으로 접속해야 한다. 

(정확한 의미에서는 계정이라기보다는 권한의 문제겠지만 이건 나중에...)

접속방법은 계정명 뒤에 /as sysdba를 붙인다.

- /as sysdba를 붙일 때 OS의 시스템 계정 권한으로 접속할 경우 별도의 패스워드 없이 접속 가능하다

(보안 때문에 시스템 계정 권한으로 변경하길 권장하는데 이것도 나중에 정리...)

sysdba로 접속하는 방법은 아래 방법 중 편리한 방법을 사용하면 된다. 

> sqlplus

 사용자명 입력 : /as sysdba

> sqlplus "/as sysdba"

connect 또는 conn 명령

SQL> connect 계정명 / 패스워드

- connect(또는 conn)sqlplus로 오라클 DB에 접속한 상태에서 계정을 변경하는 명령어.

- show user 명령어로 현재 접속한 계정명 확인 가능

- 다음 명령어로 sqlprompt 프롬프트 변경 가능

SQL> set sqlprompt "_USER > "

scott 계정 활성화

- 만약 오라클 설치 직후라면 실습용 계정인 scott은 잠겨있는 상태(Lock)이다. 확인해 보고  락을 해제한다. 

(sqlplus 접속 방법과 sysdba접속에 대한 내용은 조금 뒤에)

> sqlplus "as /sysdba"

SQL> SELECT username, account_status FROM dba_users WHERE username='SCOTT';

SQL> ALTER user scott account unlock;

- scott계정의 디폴트 패스워드는 tiger이지만 다른 패스워드로 변경하고 싶다면 아래 명령어를 실행한다. 

패스워드에 특수문자가 있는 경우는 반드시 큰 따옴표(" ")로 묶어주어야 한다. 

SQL> ALTER user scott IDENTIFIED BY "pw!!"

출처: http://ttend.tistory.com/607 [toward the end · · ·]

오라클 리스너 확인하기


1. 리스너 구동하기

이 명령어는 오라클이 설치되어 있는 서버에서 실행해야 한다. 당연 oracle 유저로 실행해야 먹을 것이다. 윈도우 커맨드모드에서 실행하는 client 용 명령어는 아니다.

2. 리스너 환경 파일

$ORACLE_HOME/network/admin  아래에 있는 listener.ora 

3. tnsping 사용하여 외부에서 리스너 동작 확인하기 

사용법 : tnsping TNS명

여기서 사용한 DBDCBS1 는 tns명으로서 client pc의 $ORACLE_HOME/network/admin   아래에 있는 tnsname.ora 파일 속에 정의되어 있다. oracle 10g 의 경우 보통 C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN  아래에 있다.

위와 같은 파일을 직접 울트라에디터 같은 것으로 열어서 편집을 할 수도 있으나 주의할 점이 있다. tnsname명 앞에 스페이스 같은 문자가 끼어들어가면 안된다. 즉  "DBDCBS1=" 이라고 써야 되는데 실수로  " DBCBS1=" 이라고 D자 앞에 스페이스나 탭문자가 들어가면 제대로 인식되지 못 한다는 말이다.

오렌지나 토드 같은 SQL 쿼리 툴이 있다면 위의 그림과 같이 쉽게 tnsname.ora 파일을 편집할 수 있다. 

그러나 위의 그림과 같이 오라클에서 기본 제공하는 tnsname설정하는 용도의 Net Configuration Assistant 프로그램이 있지만 사용하기가 약간 어렵워 초보자에게 혼란만 주게 된다.

해당 지정된 포트에 리스너가 떠 있지 않을 경우 위와 같은 메시지를 볼 수 있다. 


3. telnet 사용하여 외부에서 리스너 동작 확인하기  

사용법 : telnet  IP 포트번호

이 때 방화벽은 뚫려 있으나 해당 포트에 리스너가 떠 있지 않을 경우에는 "연결하지 못했습니다."  라는 메시지가 바로 나오고 방화벽으로 막혀 있다면 20여초 가량 지체되다가 연결하지 못 했다는 메시지가 나오게 된다. 

이것은 tcp/ip 프로토콜의 특성상 해당 destination 장비로 연결이 되지 못 했을 경우 연결(패킷 전송)을 여러번 재시도를 하기 때문이다.
만약 지정된 포트에 정상적으로 리스너가 떠 있고 방화벽이 뚫려있다면 다음과 같은 black 화면이 뜰 것이다. 이것은 해당 포트에 떠 있는 daemon 프로세서에 접속이 되어 다음 전문을 기다리고 있는 상태이다. 

이렇게 연결된 상황을 탈출하고자 한다면 CTRL+] 문자를 입력한다. (Ctrl을 누른 상태에서 ] 를 친다) 그런 후에 quit 를 입력하면 다시 커맨드 모드로 빠져나올 수 있다.

출처: http://pangate.com/138 [호주 멜번스토리]

오라클 클라이언트(WIN)문제 [ ORA-12154: TNS:could not resolve the connect identifier specified ]

오라클 10g설치 후, 클라이언트를 설치 후 서버로 접속하면, 이런 메시지를 출력하고 접속이 안될 경우가 있다.

ORA-12154: TNS:could not resolve the connect identifier specified

TNS위치 설정을 제대로 해 주었고, 값이 정상일 경우에도 이런 메시지가 나와서 고생을 많이 했는데, 오라클 포럼에서 답을 찾을 수 있었다.

결론은 어떠한 이유에서인지 환경변수가 필요한데 등록이 안되어 있는 것이었고, 수동적으로 환경변수를 등록하면 된다.

내컴퓨터->속성->시스템등록정보->고급->환경변수->시스템 변수 란을 확인하고, 없다면...

새로만들기,

변수이름 : ORACLE_HOME
변수 값   : E:\oracle\product\10.2.0\client_1

Windows 98의 경우 C:\autoexec.bat파일에 다음 내용 추가
• set JDKHOME=c:\jdk1.3.1;
• set PATH=c:\jdk1.3.1\bin;C:\windows\command;
• set CLASSPATH=.;C:\JavaProject

• set ORACLE_HOME=.;D:\orawin95\bin;

( 실제 설치된 경로는 E:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora 입니다 )

경우에 따라서 재부팅이 필요할 수 있다.

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

Error while trying to retrieve text for error ORA-12154

얼마전 개발서버 DB 셋팅을 하던중 ORA-12154 에러가 발생하여 온갖 삽질을 다했다 -ㅅ-

원래 이 에러는 client 에서 DB 접근시 tnsnames.ora 에 db_alias 나 tnsnames.ora 설정이

잘못되어 있을 경우 나타난다

하지만!!! DB 셋팅 한두번 해보나 -ㅅ-)!!! 그런 초보적인 실수를 할리가 !!!

그래서 책자 찾아 보고 oracle 에 문의 해본 결과

SQLNET 설정에서 문제가 있을 수도 있다는 결론 도출!!!

(문제는  방화벽이었음.. orz... 당연히 방화벽이 막혀 있으니 alias 를 제대로 해도 못읽지..)

그래서 해결책 !!!

SQLNET.ORA 파일에서 해당 접속 IP 의 접근을 허용하여 주면된다!! (물론 방화벽 제거는 기본)

# tcp.invited_nodes=(DB로컬 아이피와 접속을 허용할 ip)
tcp.invited_nodes=(192.167.3.224)
# tcp.excluded_nodes=(접속을 차단할 ip)

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

sqlnet.ora 파일에 SQLNET.AUTHENTICATION_SERVICES= (NTS) 이부분을 주석처리.. 하란다.

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

(출처 : https://m.blog.naver.com/PostView.nhn?blogId=whiteme7&logNo=110151847636&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F)