테이블스페이스

[테이블스페이스 설계]

- 오라클에서 DB 작업은 테이블에 테이터를 저장

- 계정별로 무조건 user 테이블스페이스(폴더개념)에 저장이 됨

- 테이블스페이스는 논리적인 이름

EX. test01이 테이블을 생성

-> 테이블은 어느 특정한 파일에 저장시킬 수 있다. (~.dbf) 물리적인 이름

-> 이것을 테이블스페이스로 구분, 논리적인 이름

- 테이블스페이스는 주와 부로 나뉜다.

EX. 주가 되는 테이블스페이스(20M)에 데이터가 20M가 넘어가면

     부가 되는 테이블스페이스(임시 테이블스페이스명 temporary)에 저장

Q8. 테이블스페이스

1) 관리자 로그인

2) 테이블스페이를 생성하기(C:\ or D:\ 등 위치를 지정) 

형식)

create tablespace 테이블스페이스명(논리적인이름)

datafile '경로포함에서 저장할파일명.dbf' 

size Nm; // N은 용량을 지정할 숫자

EX) L:\2017NCS\1.db에 파일(oradata2.dbf 10M)로 저장

SQL> conn sys/sys12345 as sysdba

연결되었습니다.

SQL> show user

USER은 "SYS"입니다

SQL> create tablespace oradata2

  2  datafile 'L:\2017NCS\1.db\oradata2.dbf'

  3  size 10m;

테이블스페이스가 생성되었습니다.

3) 테이블스페이스 목록 확인하기 : v$tablespac

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

         0 SYSTEM                         YES NO  YES

         1 SYSAUX                         YES NO  YES

         2 UNDOTBS1                       YES NO  YES

         4 USERS                          YES NO  YES  // 기본 테이블스페이스

오라클에서 계정을 생성하고 테이블 작업(생성)하면 무조건 이곳에 저장

         3 TEMP                           NO  NO  YES

         6 EXAMPLE                        YES NO  YES

         7 ORADATA2                       YES NO  YES // 따로 저장할 위치를 지정 가능

7 개의 행이 선택되었습니다.

Q10. 테이블스페이스(oradata2)를 계정 또는 테이블에 연결하기

형식1) 테이블은 테이블스페이스와 연결하기

create table 테이블명(~) tablespace 저장시킬테이블명

1) 테이블(t1)을 생성하여 테이블스페이스(oradata2)에 연결하기

SQL> create table t1(c1 number)

  2   tablespace oradata2; // default로 ~ tablespace users가 있었던 것

테이블이 생성되었습니다.

형식2) 계정을 테이블스페이스로 연결하기

create user 생성시킬 유저명 identified by 암호

 default tablespace 연결시킬테이블명; // 여기가 없으면 defualt로 users에 저장됨

2) 계정을 테이블스페이스로 연결하기

SQL> create user kim2 identified by kim2

  2   default tablespace oradata2;

사용자가 생성되었습니다.

SQL> grant connect, resource to kim2;

권한이 부여되었습니다.

Q11. kim2 계정으로 로그인하여 이 계정과 연결 중인 테이블스페이를 확인하자

*) 조회하는 SQL (테이블)

- user_users : 테이블스페이스

- user_tables : 테이블

1) kim2와 연결되어 있는 테이블스페이스 조회하기

SQL> conn kim2/kim2

연결되었습니다.

SQL> select username, default_tablespace

  2  from user_users;

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

KIM2                           ORADATA2

2) 테이블과 연결되어있는 테이블스페이스 조회하기

SQL> create table s1(id number);

테이블이 생성되었습니다.

SQL> select table_name, tablespace_name

  2  from user_tables ;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

S1                             ORADATA2

3) 기존에 형성된 계정에 테이블스페이스 지정하려면 ALTER를 활용하여 tablespace를 추가한다.

*) 계정을 생성할 때 QUATA nM

: 계정별로 사용할 수 있는 데이터 공간

4) test01이 어느 테이블스페이스와 연결되어있는지 조회

SQL> select username, default_tablespace

  2  from user_users;

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

TEST01                         USERS

-> 계정생성시 테이블스페이스를 지정해주지 않았기에 default인 users 테이블스페이스로 연결

Q12. 기본으로 지정된 user 테이블스를 따로 생성된 oradata2에 연결시키기

형식) 테이블스페이스 변경하기

alter user 수정user명 default tablespace 새로변경할 ts명

1) test01 테이블스페이스 oradata2로 변경

SQL> conn test01/t1234

연결되었습니다.

SQL> alter user test01 default tablespace oradata2;

사용자가 변경되었습니다.

SQL> conn test01/t1234

연결되었습니다.

SQL> select username, default_tablespace

  2  from user_users;

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

TEST01                         ORADATA2

2) test01이 작업한 테이블 조회

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

B_PRTEST                       TABLE

DEPT                           TABLE

PRTEST                         TABLE

SAMPLE                         TABLE

SAWON                          TABLE

SQL> select table_name, tablespace_name

  2  from user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

SAMPLE                         USERS

SAWON                          USERS

DEPT                           USERS

B_PRTEST                       USERS

PRTEST                         USERS

-> 이전 작업한 테이블은 users 테이블스페이스에 저장되어있음

-> 앞으로 작업하는 테이블은 oradata2에 저장됨

Q13. 기존 ts에서 작업한 테이블을 새로 만든 ts(oradata2)로 옮기기

형식) 테이블스페이스 옮기기

alter table 이동시킬테이블명

 move tablespace 옮길 테이블스페이스명;

1) test01의 b_prtest를 oradate2로 옮기기

SQL> alter table b_prtest

  2  move tablespace oradata2;

테이블이 변경되었습니다.

SQL>  select table_name, tablespace_name

  2   from user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

B_PRTEST                       ORADATA2

SAMPLE                         USERS

SAWON                          USERS

DEPT                           USERS

PRTEST                         USERS

테이블스페이스 생성-유저와 연결

-> 임시 테이블스페이스를 작성하여 연결

-> 10M가에서 20, 40M 확장(extends)

-> 더이상 불필요하면 삭제

Q14. shop2(100M) 테이블 스페이스를 생성 

- 계정 : shopuser/t1234

- 경로 : L:\2017NCS\1.db\shop2.dbf

1) 테이블스페이스와 계정 생성

SQL> conn sys/sys12345 as sysdba

연결되었습니다.

SQL> create tablespace shop2

  2  datafile 'L:\2017NCS\1.db\shop2.dbf'

  3  size 100m;

테이블스페이스가 생성되었습니다.

SQL> create user shopuser identified by t1234

  2  default tablespace shop2;

사용자가 생성되었습니다.

SQL> grant connect, resource to shopuser;

권한이 부여되었습니다.

2) 부족한 관계로 임시 테이블스페이스를 하나 더 생성, 연결해서 작업

형식) 부가 되는 테이블스페이스

create temporary tablespace 임시ts명

 tempfile '경로및파일명'

 size nm;

EX) 임시 테이블스페이스 생성

SQL> create temporary tablespace temp3

  2  tempfile 'L:\2017NCS\1.db\temp3.dbf'

  3  size 50m;

테이블스페이스가 생성되었습니다.

첫번째장, p38.

-> 새로운 계정을 하나 생성하면서 default tablespace명과 temporary ts명을 같이 부여함

3) 새로운 계정 lee을 생성하고 oradate2,temp3에 저장하기

SQL> create user lee identified by t1234

  2  default tablespace oradata2

  3  temporary tablespace temp3;

사용자가 생성되었습니다.

SQL> grant connect, resource to lee;

권한이 부여되었습니다.

Q15. 테이블스테이스 공간(용량) 확장

1) 용량을 수정할 수 있는지 조회하는 SQL문 : dba_data_files 테이블

select tablespace_name, file_name, autoextensible

 from dba_data_files;

*

- tablespace_name : 논리적인 테이블스페이스 이름

- file_nam : 논리적인 테이블스페이스 이름

- autoextensible : 용량이 부족하면 확장이 가능한지 유무

2) 조회하기

SQL> select tablespace_name, file_name, autoextensible

  2   from dba_data_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

AUT

---

USERS

C:\ORACLE\ORADATA\ORCL\USERS01.DBF

YES // 용량이 부족하면 확장이 가능한 상태

UNDOTBS1

C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

YES

....

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

AUT

---

L:\2017NCS\1.DB\ORADATA2.DBF

NO // 새로 생성된 테이블스페이스는 확장 불가

SHOP2

L:\2017NCS\1.DB\SHOP2.DBF

NO

-> 기존에 존재하는 테이블스페이스 외 새로 만든 테이블스페이스는 확장이 제한적

-> 확장이 가능하도록 수정

3) [기존의 테이블스페이스 공간 확장시키기]

형식1) 증가치를 활용하여 확장하기(부족하면 최대값까지 늘어남)

alter database datafile '경로포함 변경할 파일명.dbf'

 autoextend on next 증가치(n)m

 maxsize 최대용량크기(n)m;

EX) oradata 용량 확장하기 : 10m -> 50m

SQL> alter database datafile 'L:\2017NCS\1.DB\ORADATA2.DBF'

  2   autoextend on next 2m

  3   maxsize 200m;

데이타베이스가 변경되었습니다.

-> 공간이 부족하면 2M씩 증가하고 되채 200m까지 증가 가능

형식2) 한번에 최대값을 설정하기

alter tablespace 변경시킬ts명 

 add datafile '경로포함한 새로운파일.dbf'

 size nm;

-> 파일을 추가시키고 논리적인 ts명에 연결됨

EX) oradat2 논리적인 ts명에 oradata2, oradata3,, 파일을 추가

SQL> alter tablespace oradata2

  2   add datafile 'L:\2017NCS\1.DB\ORADATA3.DBF'

  3   size 50m;

테이블스페이스가 변경되었습니다.

4) 오라클이 접속할 때 오류 

- 외부에서 ~.dbf을 삭제, 변경할 때

5) 테이블스페이스 삭제하기 : 내용과 파일 삭제

drop tablespace 삭제시킬ts명

 including contents and datafiles;

EX1) shop2 삭제하기

SQL> drop tablespace shop2

  2   including contents and datafiles;

테이블스페이스가 삭제되었습니다.