Oracle에서 Tablespace 만들기

참조한 글
Oracle Tablespace 생성 – Fedora Release 7
Oracle Tablespace에 계정추가 및 관리


Tablespace Information

테이블 스페이스 만들기
create tablespace [테이블 스페이스 이름]
datafile ‘[데이타 파일명]’ size 400k
Autoextend on next 400k maxsize 10m;

사용자 생성하면서 테이블 스페이스 지정해주기
create user [사용자 이름] IDENTIFIED BY [비번]
DEFAULT TABLESPACE [테이블 스페이스 이름]
TEMPORARY TABLESPACE temp;

테이블 스페이스 확인
select *
from dba_data_files;

사용자의 테이블 스페이스 확인하기
select     USERNAME,
    CREATED,
    PROFILE,
    DEFAULT_TABLESPACE,
    TEMPORARY_TABLESPACE
from     dba_users
order     by USERNAME

테이블 스페이스 삭제
drop tablespace [테이블 스페이스 이름]

사용자 삭제
drop user [사용자 이름]

Chapter 14: Case Study: Building a Product Catalog

참조 : The Programmer’s Guide to SQL

오라클에서 테이블 만들기

CREATE TABLE Category (
CategoryID INT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Description VARCHAR (200) NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));

CREATE SEQUENCE CategoryIDSeq;

CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
BEFORE INSERT ON Category
FOR EACH ROW
BEGIN
   SELECT CategoryIDSeq.NEXTVAL
   INTO :NEW.CategoryID FROM DUAL;
END;
/
– 시퀀스 만들고, 트리거 만들어서 자동 증가값처럼 사용하기.

CREATE TABLE ProductCategory (
ProductID INT NOT NULL,
CategoryID INT NOT NULL,
PRIMARY KEY (ProductID, CategoryID),
FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID)
);
– Junction 테이블.

데이터 가져오기

SELECT Name, Price FROM
      (SELECT Name, Price
       FROM Product
       ORDER BY Price DESC, Name ASC)
WHERE ROWNUM<=5;
– 상위 다섯개의 데이터 가져오기.
– id를 사용하지 않는다. id가 꼭 순서대로 매겨진다는 보장이 없으니까..

SELECT ProductID, Name FROM
(
SELECT RANK() OVER (ORDER BY ProductID) As Ranking, ProductID, Name
FROM Product
ORDER BY PRODUCTID
)
WHERE Ranking BETWEEN 6 AND 8;
– 특정 범위의 값 가져오기.

SELECT C.Name as “Category Name”, P.Name as “Product Name”
FROM Product P
INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
INNER JOIN Category C ON PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> WHERE 절을 사용하도록 변경하면…
SELECT C.Name as “Category Name”, P.Name as “Product Name”
FROM Product P, ProductCategory PC, Category C
WHERE P.ProductID = PC.ProductID AND PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> 연관된 서브쿼리를 사용하도록 변경하면..
SELECT C.Name as “Category Name”, Product.Name as “Product Name”
FROM Product, Category C
WHERE Product.ProductID IN
  (SELECT ProductID FROM ProductCategory
   WHERE ProductCategory.CategoryID = C.CategoryID)
ORDER BY C.Name, Product.Name;

데이터 검색

SELECT Name, Description
FROM Product
WHERE (Description || Name LIKE ‘%devil%’)
  AND (Description || Name LIKE ‘%mask%’);
– 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.

Chapter 12: Working with Database Objects

참조 : The Programmer’s Guide to SQL

데이터베이스 만들기
    – CREATE DATABASE db이름
    – 오라클은 보통 DB를 한 개만 사용하고 스키마를 이용해서 관련있는 테이블들과 다른 객체를 그룹핑한다.
데이터베이스 제거
    – 오라클에서는DROP DATABASE가 아니라 CREATE DATABASE 로 제거

테이블 만들기
    – CREATE TABLE <table name>
   (<column name> <column data type> [<column constraints>]);
테이블 제거
    – DROP TABLE <table name>
    – FOREIGN KEY 제약으로 인해 참조 되고 있는 테이블이면 제거가 되지 않는다. 제거하기 전에 이 제약부터 제거해야 한다.

컬럼에 기본값 주기
    – DEFAULT <기본값>
    – 컬럼 기본값을 함수나 키워드를 사용할 수 있다.
    GETDATE() = SYSDATE = CURRENT_DATE
주키 설정하기
    – 주키 이름 설정하고 싶을 때

  CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));

    – 주키 이름에 관심없을 때

   CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   PRIMARY KEY (Name));

    – 이것을 좀 더 간단하게

  CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL);

    – 주키 제약을 설정하면 자동으로 고유한 인덱스가 생성된다.
UNIQUE 설정
    – 오라클에서는 기본으로 UNIQUE 속성의 컬럼에 다수의 NULL 넣을 수 있슴.
컬럼 제약 설정
    – CHECK 사용하기

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT,
   CONSTRAINT CheckAge CHECK (Age BETWEEN 10 and 100));

    – 제약 사항의 이름이 필요 없다면

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT CHECK (Age BETWEEN 10 and 100));

테이블 복사 하기
    – CREATE TABLE My_Friends AS SELECT * FROM Friend;
    – 단 제약 사항들(주키나 유니크 같은 것들)은 복사 되지 않는다. 그렇기 때문에 테이블을 만들 때는 기본 구조만 정의하고 Alter Table로 실행할 스크립트를 만들어 두는 것이 유용하다.
    – 데이터는 빼고 구조만 복사하고 싶다면 WHERE 절에 false가 되도록 설정해준다.
    CREATE TABLE My_Friends AS SELECT * FROM Friend
    WHERE 1=0;

테이블 변경하기
    – 컬럼 추가하기 : ALTER TABLE Friend ADD (EMail VARCHAR(25), ICQ VARCHAR(15));

        – NOT NULL 인 컬럼을 추가하려면, 테이블에 데이터가 들어있지 않아야 한다.
    – 컬럼 없애기 : ALTER TABLE Friend DROP COLUMN PhoneNo;

    – 제약 사항 삭제 : ALTER TABLE Friend DROP CONSTRAINT unq_name;
    – 제약 사항 추가 : ALTER TABLE Friend ADD CONSTRAINT PK_FriendName PRIMARY KEY (Name);

시퀀스 사용하기
    – CREATE SEQUENCE FriendIDSeq INCREMENT BY 1 START WITH 1000;
    – 테이블에서 사용하기
    INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, ‘Mike’, ‘123’);
    – 시퀀즈 제거하기 : DROP SEQUENCE FriendIDSeq;
    – 현재 시퀀스 값 가져오기 : SELECT FriendIDSeq.CurrVal FROM DUAL;

자동 증가 사용하기
    – 오라클에서는 트리거를 이용해야 함.

    CREATE OR REPLACE TRIGGER AUTONUMBER
    BEFORE INSERT ON Friend
    FOR EACH ROW
    BEGIN
       SELECT FriendIDSeq .NEXTVAL
       INTO :NEW.FriendID FROM DUAL;
    END;
    /

    – 시퀀스에서 값을 가져온 다음 새로운 row 생성 될 때 주키에 값을 집어넣는 트리거인듯.

인덱스 사용하기
    – 인덱스를 사용하면 SELECT 문의 속도는 증가하지만, INSERT, DELELTE, UPDATE의 속도는 느려진다.
    – 조회가 자주 발생하는 테이블의 경우 인덱스를 사용하는 것이 단점에 비해 눈에띄게 좋다.
    – 따라서 WHERE 나 ORDER BY 절에서 자주 사용하는 컬럼의 인덱스를 만들어 두는 것이 좋다.
    – 제약사항(주키나 유니크) 때문에 자동으로 생성된 인덱스는 제약사항이 제거 되면 같이 제거 된다.
    – 인덱스에는 유니크 인덱스와 논 유니크 인덱스 두 종류가 있다.
    – 인덱스 만들기 : CREATE UNIQUE INDEX NameIndex ON Friend (Name);
    – 유니크 인덱스를 사용하는 컬럼에 중복되는 값을 넣을 수 없다.
    – 텍스트에 인덱스는 단어의 왼쪽 글자부터 오른쪽 글자순으로 찾는다. 따라서 ‘%기선’ 과 같은 검색어는 데이블의 모든 데이터를 찾아보게 한다.
    – 인덱스 삭제하기 : DROP INDEX NameIndex;

참조 무결성
    – 외례키 제약 설정하기

    CREATE TABLE Friend (FriendID INT PRIMARY KEY NOT NULL,
                      Name VARCHAR(50));

    CREATE TABLE Phone (
       PhoneID INT PRIMARY KEY NOT NULL,
       FriendID INT,
       PhoneNo VARCHAR(20),
       CONSTRAINT FID_FK FOREIGN KEY(FriendID)
          REFERENCES Friend(FriendID));

Chapter 11: Users and Security

참조 : The Programmer’s Guide to SQL

인증 : 이부분에 대한 SQL-99 표준은 없다. 따라서 DBMS마다 각기 다른 방법을 사용하지만 대부분 비슷하다.
권한 : SLQ-99 표준에 두 개의 키워드를 정의하고 있다. GRANT, REVOKE

사용자 계정 만들기
    CREATE USER username IDENTIFIED BY password;
ROLE 종류
    CONNECTION : DB에 연결 가능
    RESOURCE
    DBA
ROLE 또는 권한 주기
    GRANT 권한 TO username
ROLE 또는 권한 제거
    REVOKE 권한 FROM username
사용자 정의 ROLE 만들기
    CREATE ROLE rolename
ROLE 삭제하기
     DROP ROLE rolename
Statement 단위로 권한 부여하기
    GRANT <privilege type> ON <resource> TO <username>
Statement 단위로 권한 취소하기
    – REVOKE <privilege type> ON <resource> FROM <username>
    – Role을 사용하여 부여된 여러 권한 중에서 특정 권한만 취소할 경우에 유용하게 사용할 수 있음.
    – RESOURCE로 인해 부여받는 권한 중에 테이블이 디스크의 공간을 무한대로 사용할 수 있는(UNLIMITED TABLESPACE)데, 이 권한을 취소할 수 있음.

Chapter 10: Transactions

참조 : The Programmer’s Guide to SQL

트랜잭션의 특징 ACID
    – Atomic
    – Consistency
    – Isolation
    – Durable

오라클은 기본으로 Auto Transaction 상태.
    – 첫 번째 SQL문을 실행할 때, 트랜잭션이 자동으로 시작 됨.
    – 따라서 START TRANSACTION(SQL-99 표준) 을 사용하지 않아도 됨.
    – 그러나 10g에서는 기본으로 오토커밋 상태임.

사용자 삽입 이미지
오토커밋
    – 이 상태에서는 모든 SQL문을 개별 트랜잭션으로 처리함.
    – SET AUTOCOMMIT ON/OFF 오라클에서 오토커밋 상태로 전환 하는 방법.

롤백하기
    – ROLLBACK [세이브포인트 이름]
    – SAVEPOINT 이름 생략하면, 트랜잭션 시작 이전 상태로 돌아감.

세이브포인트 만들기
    – SAVEPOINT [이름];

커밋하기
    – COMMIT

트랜잭션 예제

BEGIN

   INSERT INTO Student (StudentID, Name) VALUES (101, ‘Dave’);
   INSERT INTO Student (StudentID, Name) VALUES (102, ‘Claire’);

   SAVEPOINT BeforeAddingAnne;
   INSERT INTO Student (StudentID, Name) VALUES (103, ‘Anne’);
   ROLLBACK TO BeforeAddingAnne;

   COMMIT;

EXCEPTION
   WHEN OTHERS
      THEN ROLLBACK;
END;
/

오라클의 Isolation level
    – 기본은 Read Commited
    – Read Uncommited와 Repeatable Read는 지원하지 않음.
    – Isolaction Level 변경하는 방법
    SET TRANSACTION
    { { READ ONLY | READ WRITE }
      | ISOLATION LEVEL
        { READ COMMITTED
        | SERIALIZABLE } };

동기화 테스트 하려면, 오토커밋 상태가 아닌 상태에서 두 개의 창을 띄워 놓고 하면 됨.(READ COMMITED 상태)
    – 한 쪽 창에서 한 개의 레코드를 추가한다.
    – 추가 됐는지 SELECT 문으로 확인한다.
    – 다른 쪽 창에서 SELECT 해본다.
    – 다른 쪽 창에는 첫 번째 창에서 추가한 레코드가 보이지 않는다.
    – 첫 번째 창의 SQL이 아직 커밋되지 않았기 때문이다.
    – 첫 번째 창에서 COMMIT 을 실행한다.
    – 두 번째 창에서 SELECT로 확인한다. 이번에는 보인다.