[사용 샘플 쿼리]

select sysdate,
    (sysdate-1/24/60) "1분 전",
    (sysdate-1/24/30) "2분 전",
    (sysdate-1/24/12) "5분 전",
    (sysdate-1/24/6) "10분 전",
    (sysdate-1/144) "10분 전",
    (sysdate-30/1440) "30분 전",
    (sysdate-1/24) "1시간 전",
    trunc(sysdate-1) "1일 전",
    trunc(sysdate-7) "7일 전",
    trunc(sysdate-30) "30일 전",
    trunc(sysdate+1)-6/24 "6AM 전",
    trunc(sysdate+1)-2/24 "2PM 전" 
from dual;

[결과]

테이블 데이터 삭제하는 방법에 대해 알아보겠습니다.

1) 테이블 삭제하기

 1-1) DROP

     DROP TABLE 테이블명;

2) 테이블 데이터 삭제하기

 2-1) DELETE

     DELETE FROM 테이블명 [WHERE 조건절];

 2-2) TRUNCATE

     TRUNCATE TABLE 테이블명;

DROP은 테이블 자체를 삭제하며 DELETE, TRUNCATE는 테이블 구조는 그대로 남고 테이블 내 삭제만 됩니다.

DELETE, TRUNCATE의 차이점

DELETE는 데이터만 지워지며 디스크 용량은 그대로 가짐

TRUNCATE는 데이터 뿐만아니라 용량도 삭제됨.

1. DB 전체 용량 확인
select sum(bytes) from dba_data_files;

select sum(bytes)/1024/1024/1024 ||'GB' from dba_data_files;    



2. DB 사용량 확인
select sum(bytes) from dba_segments;

select sum(bytes)/1024/1024/1024 ||'GB' from dba_segments;    



3. DB 여유량 확인
select sum(bytes) from dba_free_space;

select sum(bytes)/1024/1024/1024 ||'GB' from dba_free_space; 

프로시져 실행 시 리턴값으로 사용되는 out 변수가 있을 경우 실행하는 방법입니다.

1. 테이블 조회
  1) 테이블 목록 조회
     SELECT * FROM all_all_tables;

     SELECT * FROM dba_tables;

     SELECT * FROM ALL_OBJECTS 
     WHERE OBJECT_TYPE = 'TABLE';

  2) 접속한 계정의 테이블 목록 조회
     SELECT * FROM tabs;
 
     SELECT * FROM USER_OBJECTS 
     WHERE OBJECT_TYPE = 'TABLE';
 
     SELECT * FROM USER_TABLES;

  3) 테이블 코멘트 조회
     SELECT * FROM ALL_TAB_COMMENTS

     WHERE TABLE_NAME = '테이블명';
 
     SELECT * FROM USER_TAB_COMMENTS;


2. 컬럼 조회
  1) 컬럼 조회
     SELECT * FROM COLS 
     WHERE TABLE_NAME = '테이블명';

     SELECT * FROM ALL_TAB_COLUMNS 
     WHERE TABLE_NAME = '테이블명';

     SELECT * FROM USER_TAB_COLUMNS;

  2) 컬럼 코멘트 조회
     SELECT * FROM USER_COL_COMMENTS;

오라클 계정 잠금 해제하는 법에 대해 알아보겠습니다.

오라클에서 계정 접속시 패스워드가 틀리거나 180일동안 접속하지 않으면 계정이 잠깁니다.

계정 잠금 화면

잠김 계정을 해제하기위해 SYSTEM 계정으로 접속합니다.

1. 계정 정보 확인하는 쿼리

SELECT * FROM DBA_USERS WHERE username='잠긴계정";

ACCOUNT_STATUS의 값이 LOCK이라면 계정이 잠겼다는 의미입니다.

2. 잠금해제

ALTER USER [사용자계정] ACCOUNT UNLOCK;

3. 패스워드 재설정

ALTER USER [사용자계정] IDENTIFIED BY [비밀번호];

open으로 변경된 것을 확인할 수 있습니다.

오라클에서 제공하는 replcae함수로 update 하는 방법에 대해 알아보겠습니다.

데이터 내에 변경하려는 값이 간단하다면 update 테이블 set 컬럼="변경하려는데이터" 로 하면 되지만

만약 값이 복잡하다면  replace 함수를 사용해주면 됩니다.

replace 함수를 사용해 업데이트 하는 방법에 대해 알아보겠습니다.

UPDATE 테이블 set 컬럼 = REPLACE(컬럼명, "-", "");

위와 같이 사용해주시면 -이 공백으로 처리 됩니다.

ex) 010-1234-5678

01012345678

REPLACE 함수는 간단하지만 자주 사용하는 함수로 알아두면 유용하게 사용할 수 있을 것 같네요.

오라클에서 원격 접속 차단하는 방법에 대해 알아보겠습니다.

접속하는 피시에서 서버의 Listener를 통해 DB를 접속하는 경우가 있습니다.

제한을 두지 않는다면 모든 사용자가 접속을 할 수 있습니다.( DB접속 정보를 알고있다는 가정하에)

Listener 설정을 통해 차단 및 허용 IP 설정이 가능합니다.

sqlnet.ora파일을 수정해주시면 되는데요.

sqlnet.ora파일은 $ORACLE_HOME/network/admin/sqlnet.ora

오라클 설치경로 $ORACLE_HOME 디렉토리 하단에 존재합니다.

$ORACLE_HOME 오라클 설치 시 환경변수로 설정해두셨을 거라고 생각되는데요.

저는 D드라이브 밑에 오라클을 설치해두었습니다.

sqlnet.ora 파일을 찾으셨다면 에디터 프로그램으로 sqlnet.ora파일을 편집합니다.

편집후에는 listener를 재시작해줘야 해당 설정이 반영됩니다.

작업관리자 -> Listener 재시작

실제로 적용 되는지 테스트를 해보았습니다.

1. 접속 허용 IP만 지정 (TCP.INVITED_NODES)

접속 허용 IP를 지정한 경우 해당 IP외 모든 IP는 접속이 제한됩니다.

2. 접속 차단 IP지정(TCP.EXCLUDED_NODES)

 2.1) 차단IP외 다른 IP에서 접속한 경우

 2.2) 차단IP에서 접속한 경우

차단 IP에서 접속한 경우에도 동일한 에러 메세지가 발생하네요.

3. Listner 종료 시 발생한 에러

ORA-12505, TNS: listener does not currently know of SID given in connect descriptor

메세지와 함께 DB접속이 되지 않네요.

추가로 여러 개 IP를 넣고 싶다면 쉼표(,)를 통해 구분하여 작성할 수 있습니다.