혼자공부하기

오라클에서 제공하는 표준 함수 외에도 사용자가 정의하여 사용하는 함수

사용자 정의 함수 조회

SELECT *

FROM all_objects

WHERE object_name like '%함수명%';

사용자 정의 함수 내용 조회

SELECT *

FROM all_source

WHERE name = '함수명';

COMMENT ON COLUMN 테이블명.컬럼명 IS '내용';

SELECT DBMS_METADATA.GET_DDL('TABLE','테이블명','권한자') 

FROM DUAL;

세그먼트 빈 공간 관리 방식에는 FLM(Free List Management)와 ASSM(Automatic Segment Space Management) 두 종류가 있다. 설정 방식은 테이블스페이스 생성시 SEGMENT SPACE MANAGEMENT절로 설정한다.

FLM은 테이블에 PCTUSED 파라미터를 설정하고 블록의 사용률이 PCTUSED 보다 아래로 내려가면 데이터를 입력할 수 있게 된다. PCTFREE보다 낮게 설정을 해야한다.

SEGMENT SPACE MANAGEMENT 절에 'MANUAL'로 지정.

오라클 9i~10g R1 까지 기본 관리 방식이다.

ASSM은 테이블스페이스가 데이터를 입력할 수 있는지를 오라클이 자동으로 판단한다. PCTUSED를 설정하더라도 무시한다. SEGMENT SPACE MANAGEMENT 절에 'AUTO'로 지정.

오라클 10g R2 부터 기본 관리 방식이다.

SELECT tablespace_name,segment_space_management

FROM DBA_TABLESPACES;

오라클을 구성하는 파일은 데이터 파일, REDO 로그 파일, 컨트롤 파일이 있다.

초기화 파라미터 정보는 컨트롤 파일에 있다.

초기화 파라미터는 오라클에 관한 특성을 결정하는 파라미터이다.

인스턴스 레벨에서 초기화 파라미터를 변경하면, 인스턴스 전체에 적용된다.

ALTER SYSTEM SET <파라미터명> = <새로운 설정값>

SCOPE = {MEMORY | SPFILE | BOTH}

MEMORY : 데이터베이스에 변경한 설정이 즉시 반영, 하지만 재기동하면 설정 초기화

SPFILE : 설정한 값이 서버 파라미터 파일 설정에만 변경되므로 재기동후 적용. (기동 중일 때 설정 변경할 수 없는 파라미터 경우 사용)

BOTH : 기본값. 변경 즉시 적용되며, 서버파라미터에도 반영되므로 재기동한 이후에도 설정값 유효

SELECT *

FROM V$PARAMETER;

세션 레벨에서 초기화 파라미터를 변경하면, 해당 명령어를 수행한 세션에만 적용된다.

ALTER SESSION SET <파라미터명> = <새로운 설정값>

이 명령어는 초기화 파라미터를 특정 SQL 처리시에만 임시로 변경하고 싶을 때 편리하게 사용

SELECT *

FROM V$SYSTEM_PARAMETER;

테이블 스페이스는 한 개 이상의 데이터 파일을 그룹으로 묶은 후 이름을 붙인 논리적인 공간

*영구(Permanent) 테이블 스페이스

  - 테이블이나 인덱스 등을 저장하기 위한 데이터 보존용 테이블 스페이스

* UNDO 테이블 스페이스

  - UNDO 정보만을 저장하기 위한 특별한 테이블 스페이스, 테이블이나 인덱스 등의 오브젝트를 저장하는 것은 불가능하다.

* 임시 테이블 스페이스

  - SQL 처리할 때 사용하는 임시 작업용 특별한 테이블 스페이스, 테이블이나 인덱스 등의 오브젝트를 저장하는 것은 불가능하다.

오라클 내부 정보를 확인할 수 있는 특수한 뷰인데 DBA_, USER_, ALL_로 시작한다.

* 테이블 스페이스 확인

SELECT * FROM ALL_TABLESPACES;

* 테이블 스페이스명과 데이터 파일의 디렉토리 확인

SELECT t.tablespace_name, f.file_name

FROM DBA_TABLESPACE t, DBA_DATA_FILES f

WHERE t.tablespace_name = f.tablespace_name;

* 임시 테이블 스페이스명과 임시 파일의 디렉토리 확인

SELECT t.tablespace_name, f.file_name

FROM DBA_TABLESPACE t, DBA_TEMP_FILES f

WHERE t.tablespace_name = f.tablespace_name;

DROP TABLE는 테이블을 잘못 만들었거나, 테이블이 더이 상 필요 없을 때 해당 테이블을 삭제 할 때 사용하는 명령어 이다.

DROP TABLE test.user;

실행 후

DESC test.user;

실행하면

위와 같은 테이블을 존재하지 않는다고 에러가 나게 된다.

TRUNCATE TABLE는 테이블 자체가 삭제되는 것이 아니고 해당 테이블에 있는 모든 행들이 제거가 되는 명령어 이다.

TRUNCATE TABLE test.user;

실행 후

DESC test.user;

실행하면

위와 같이 테이블이 존재 하는 것을 확인 할 수 있다.

TRUNCATE는 테이블을 일괄 삭제하는 명령어로 DML로 분류 할수 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL로 분류한다.

시스템 활용 측면에서 전제 행을 삭제하는 경우에는 TRUNCATE 명령어를 사용 하는 것이 더 좋다.

하지만 TRUNCATE는 정상적인 복구가 불가능하므로 주의를 해야 한다.

CTAS는 select문을 사용하여 테이블을 생성 하는 방법이다.

아래의 구문과 그림을 보자.

먼저 기본 테이블을 생성한다.

CREATE TABLE test.user (

no int(10),

name varchar(10),

address varchar(30)

);

실행 후

DESC test.user;

실행 하면

위와 같이 나오게 된다.

그리고 CTAS 방식으로 테이블을 생성 해본다.

CREATE TABLE test.user_tmp AS (

SELECT * FROM test.user);

실행 후

DESC test.user_tmp;

실행하면

위와 같이 나오게 된다.

몇번 써본 기억이 있기 때문에 알아두면 유용 할 것 같다.

SQL 명령어 종류에는 크게 4가지로 나누어 진다.

데이터를 조작하는 명령어를 의미한다. CRUD와 매칭이 된다.

SELECT : 데이터를 조회

INSERT : 데이터를 삽입

UPDATE : 데이터를 수정

DELETE : 데이터를 삭제

테이블과 같은 데이터 구조를 정의하는데 사용하는 명령어를 의미한다.

CREATE : 테이블을 생성

ALTER : 테이블을 수정

DROP : 테이블을 삭제

RENAME : 테이블 이름을 수정

데이터베이스에 접근하고 객체들을 사용하도록 권한을 주는 명령어를 의미한다.

GRANT : 권한 부여

REVOKE : 권한 회수

DML에 의해 조작된 결ㄹ과를 트랜잭션 별로 제어하는 명령어를 의미한다.

COMMIT : DML 수행 작업 저장

ROLLBACK : DML 수행 작업 되돌리기