#19_140709_WSHOP_TABLESPACE

# TABLESPACE

문제19. data01 이라는 테이블 스패이스를 생성하고 data01 테이블 스패이스에 emp02 테이블을 생성하시오

CREATE TABLESPACE data01

DATAFILE 'c:\data01.dbdf' SIZE 50m;

l data01 테이블 스패이스를 생성

CREATE TABLE emp302

(empno NUMBER(10),

ename VARCHAR2(10),

sal NUMBER(10))

TABLESPACE data01;

SELECT table_name, tablespace_name

FROM user_tables

WHERE table_name='EMP302';

문제22. data01 테이블 스패이스의 50MB 공간을 더 늘리시오(data file 을 data01에 추가한다)

ALTER TABLESPACE data01

ADD DATAFILE 'c:\data01b.dbf' SIZE 50m;

• 이후에 데이터를 지속적으로 입력시켜 50MB 를 초과하면 아래와 같은 오류 메세지를 출력한다

INSERT INTO emp302 VALUES(1111, 'scott',3000);

INSERT INTO emp302 SELECT * FROM emp302;

/

문제39. 현재 DB에 있는 테이블 스패이스와 데이터파일들을 조회하시오

col tablespace_name for a10

col file_name for a55

SQL> select tablespace_name, file_name

from dba_data_files;

문제40. ts01 이라는 테이블 스패이스를 크기 100MB 로 생성하시오

SQL> create tablespace ts01

datafile '/u01/app/oracle/oradata/dbdb/ts01.dba'

size 100m;

문제41. 위에서 만든 ts01 테이블 스패이스에 아래의 테이블을 생성하시오

테이블명 : emp50

컬럼명 : empno, ename, sal

SQL> create table emp50

(empno number(10),

ename varchar2(10),

sal number(10))

tablespace TS01;

점심시간문제. 테이블 스패이스 사용량을 출력하는 프로시져 생성하시오

exec pro36

tablespcae 이름        사용량

system                  96%

sysaux                  89%

SELECT u.tablespace_name tablesapce

        , sum(nvl(u.bytes,0)) / 1024000 large

        , (sum(nvl(u.bytes,0)) - sum(nvl(f.bytes,0))) / 1024000 used

        , (sum(nvl(f.bytes,0))) / 1024000 unused

        , trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) unused_percent

        , 100 - trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) used_percent

         FROM DBA_DATA_FILES u

         , DBA_FREE_SPACE f

         WHERE u.file_id = f.file_id(+)

         GROUP BY

         u.tablespace_name

         ORDER BY u.tablespace_name;

CREATE OR REPLACE PROCEDURE tablespace_used

IS

 CURSOR table_cursor IS

  SELECT u.tablespace_name space

        , sum(nvl(u.bytes,0)) / 1024000 large

        , (sum(nvl(u.bytes,0)) - sum(nvl(f.bytes,0))) / 1024000 used

        , (sum(nvl(f.bytes,0))) / 1024000 unused

        , trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) unused_percent

        , 100 - trunc((sum(nvl(f.bytes,0)) / sum(nvl(u.bytes,0))) * 100,2) used_percent

         FROM DBA_DATA_FILES u

         , DBA_FREE_SPACE f

         WHERE u.file_id = f.file_id(+)

         GROUP BY

         u.tablespace_name

         ORDER BY u.tablespace_name;

         

 BEGIN

 dbms_output.put_line('TABLESPACE NAME     USED(percent)');

 dbms_output.put_line('---------------------------------');

  FOR table_record IN table_cursor LOOP

   dbms_output.put_line(RPAD(table_record.space,20,'  ') || '   ' || table_record.used_percent || '%' );

  END LOOP;

 END;

/

• tablespace 공간을 늘리는 방법은 2가지가 있다

1.     data file 추가

alter tablespace ts01

add datafile ‘/u01/app/oracle/oradata/dbdb/ts01.dba’

size 100m;

문제42. system tablespace 의 공간을 늘리시오

alter tablespace system

add datafile '/u01/app/oracle/oradata/dbdb/system01b.bdf'

size 100m;

2.     테이블 스패이스에 속해 있는 기존 data file의 사이즈를 resize한다

- size 확인

select file_name, bytes/1024/1024

from dba_data_files;

FILE_NAME                                               BYTES/1024/1024

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

/u01/app/oracle/oradata/dbdb/users01.dbf                              5

/u01/app/oracle/oradata/dbdb/undotbs01.dbf                           40

/u01/app/oracle/oradata/dbdb/sysaux01.dbf                           460

/u01/app/oracle/oradata/dbdb/system01.dbf                           670

/u01/app/oracle/oradata/dbdb/example01.dbf                          100

/u01/app/oracle/oradata/dbdb/ts01.dba                               100

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:data01.db              50

df

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:TS02.dbdf              30

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:TS03.dbdf              40

/u01/app/oracle/oradata/dbdb/system01b.bdf                          100

alter database datafile '/u01/app/oracle/oradata/dbdb/sysaux01.dbf'

resize 520m;

점심시간문제. 패키지를 생성하는데 테이블 스패이스의 공간을 늘리는 패키지를 생성하시오(2가지 방법)

- data file 추가(users 테이블 스패이스에 users02.dbf 를 30m 로 추가)

exec dbfile.add_file(‘USERS’, ‘USER02.DBF’, 30);)

- 기존 data file을 resize(기존의 users01.dbf 파일의 사이즈를 500m 으로 늘려라)

exec dbfile.resize_file(‘users01.dbf’, 500);

CREATE OR REPLACE PACKAGE dbfile_space IS

 PROCEDURE add_file(p_table VARCHAR2, p_file VARCHAR2, p_size number);

 PROCEDURE resize_file(p_file VARCHAR2, p_size number);

END dbfile_space;

/

CREATE OR REPLACE PACKAGE BODY dbfile_space IS

 PROCEDURE add_file(p_table VARCHAR2, p_file VARCHAR2, p_size number) IS

 BEGIN

  execute IMMEDIATE

   'alter tablespace ' || p_table

   || ' add datafile '|| '''/u01/app/oracle/oradata/dbdb/' || p_file || ''' size ' || p_size || 'm';

 END add_file;

 

 PROCEDURE resize_file(p_file VARCHAR2, p_size number) IS

 BEGIN

  execute IMMEDIATE

   'ALTER DATABASE DATAFILE '||'''/u01/app/oracle/oradata/dbdb/' ||p_file||''' RESIZE '|| p_size||'m';

 END resize_file;

END dbfile_space;

/

EXEC dbfile_space.add_file('users','user06.dbf',30);

EXEC dbfile_space.resize_file('user06.dbf',50);

• TABLESPACE 삭제

문제48. ts20 이라는 테이블 스패이스를 생성하시오. 사이즈는 100m 로 하시오

CREATE TABLESPACE ts20

DATAFILE '/u01/app/oracle/oradata/dbdb/ts20.dba'

SIZE 100m;

SELECT tablespace_name FROM dba_tablespaces;

문제49. ts20 테이블 스패이스를 삭제하시오

DROP TABLESPACE ts20 INCLUDING CONTENTS AND datafiles;

l including contents and datafiles – 테이블 스패이스 안의 내용을 전부 날리면서 테이블 스패이스와 연관된 data file 들을 전부 삭제

• TABLESPACE 사용량 제한

문제75. ts20이라는 테이블 스패이스를 50m으로 생성하고 adams 라는 유저를 생성해서 adams유저의 default tablespace 를 ts20으로 지정하시오 emp500 테이블을 만들고 어느 테이블 스패이스 인지 조회하고 quota 를 0으로 변경해보시오

EXEC pack_tablespace.spa_create('ts20',50)

DROP USER adams CASCADE;

CREATE USER adams

IDENTIFIED BY tieger

DEFAULT TABLESPACE ts20

QUOTA 15m ON ts20;

GRANT CREATE SESSION TO adams;

grant create table to adams;

CREATE TABLE emp300

(ename VARCHAR2(10),

sal NUMBER(10));

SELECT table_name, tablespace_name FROM user_tables;

- 테이블스패이스 조회

ALTER USER adams

QUOTA 0m ON ts20;

테이블스패이스 사용량 제한 0으로 변경

l quota를 0으로 변경하면서 사용량을 제한시키면 테이블은 만들수 있지만 insert는 되지 않고 아래와 같은 오류 출력

SQL> insert into emp300 values('afd',3000); insert into emp300 values('afd',3000)             * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'TS20'

• TABLESPACE 의 정보를 보는 뷰 3가지

1.     dba_tablespaces

SQL> SELECT tablespace_name, status

FROM dba_tablespaces;

  2 

TABLESPACE STATUS

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

SYSTEM     ONLINE

SYSAUX     ONLINE

UNDOTBS1   ONLINE

TEMP       ONLINE

USERS      ONLINE

EXAMPLE    ONLINE

DATA01     ONLINE

TS02       ONLINE

TS03       ONLINE

2.     dba_data_files

3.     v$tablespace

SQL> SELECT NAME, bigfile

FROM v$tablespace;  2 

NAME                           BIG

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

USERS                          NO

TEMP                           NO

EXAMPLE                        NO

DATA01                         NO

TS02                           NO

TS03                           NO

• TABLESPACE의 종류 2가지

- tablespace 의 종류 2가지

1.     small file tablespace – tablespace 에 data file 을 여러 개를 둬서 관리하는 테이블스패이스

2.     big file tablespace – tablespace 에 data file 을 한 개만 둬서 관리하는 테이블스패이스.

파일의 크기를 8 엑사바이트까지 생성가능. (1엑사바이트 = 1024 페타 바이트 = 1024*104테라바이트 = 1024*1024*1024기가바이트)

big file 은 테이블스패이스에 한 개만 가능

문제51. big file 테이블 스패이스를 생성하시오

CREATE bigfile TABLESPACE ts40

DATAFILE '/u01/app/oracle/oradata/dbdb/ts40.dbf'

SIZE 100m;

ALTER TABLESPACE ts40

ADD DATAFILE '/u01/app/oracle/oradata/dbdb/ts40b.dbf'

SIZE 100m;

l big file을 생성한 후 사이즈를 늘리면 오류가 발생한다

l big file 은 add를 이용해서 공간을 늘리는 것은 안되나 resize는 가능함

문제52. 방금 만든 ts40 테이블 스패이스를 삭제하시오

DROP TABLESPACE ts40 INCLUDING CONTENTS AND datafiles;

• OMF(Oracle Managed File)

- OMF 란 오라클이 알아서 오라클의 file 들의 생성 및 관리를 알아서 하는 것

show parameter db_create_file_dest

- db_create_file_dest 파일에 테이블 스패이스를 저장할 폴더 경로를 지정한다

ALTER SYSTEM SET db_create_file_dest='/home/oracle' scope=both;

- db_create_file_dest에 경로 저장

create tablespace ts50;

select tablespace_name, file_name

from dba_data_files;

- 지정한 경로로 생성됐는지 확인

TABLESPACE FILE_NAME

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

USERS      /u01/app/oracle/oradata/dbdb/users01.dbf

UNDOTBS1   /u01/app/oracle/oradata/dbdb/undotbs01.dbf

SYSAUX     /u01/app/oracle/oradata/dbdb/sysaux01.dbf

SYSTEM     /u01/app/oracle/oradata/dbdb/system01.dbf

EXAMPLE    /u01/app/oracle/oradata/dbdb/example01.dbf

TS50       /home/oracle/DBDB/datafile/o1_mf_ts50_9vssm0p8_.dbf

DATA01     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:data01.db

           df

TS02       /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:TS02.dbdf

TS03       /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c:TS03.dbdf

SYSTEM     /u01/app/oracle/oradata/dbdb/system01b.bdf

문제53. OMF 기능을 끄시오

alter system set db_create_file_dest='' scope=both;

• 성능향상을 위해 확인해야할 2가지 

select tablespace_name, extent_management, segment_space_management

from dba_tablespaces;

TABLESPACE EXTENT_MAN SEGMEN

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

SYSTEM     LOCAL      MANUAL

SYSAUX     LOCAL      AUTO

UNDOTBS1   LOCAL      MANUAL

TEMP       LOCAL      MANUAL

USERS      LOCAL      AUTO

EXAMPLE    LOCAL      AUTO

DATA01     LOCAL      AUTO

TS02       LOCAL      AUTO

TS03       LOCAL      AUTO

TS50       LOCAL      AUTO

TS700      LOCAL      AUTO

TS_EMP_RAT LOCAL      AUTO

E

 # 위의 명령어로 extent 와 segment 의 상태를 확인할 수 있다

1.     extent 관리 방법(free space 관리)

- local 관리 방법 : 테이블 스패이스에 data를 입력할 때 free space 를 찾아야 하는데 free space가 어디있는지 지도정보가 local data file 헤더에 있는 관리 방법

- dictionary 관리 방법 : 테이블 스패이스에 data를 입력할 때 free space 를 찾아야 하는데 free space 가 어디있는지 지도정보가 system tablespace 에 데이터 사전으로 관리하는 방법

- ts01을 포함한 모든 테이블 스패이스에 입력을 위해서는 system tablespace의 dictionary를 찾아봐야 한다

- dictionary 방법은 모든 입력에서 system tablespace를 찾기 때문에 부하가 걸린다

- 그래서 Local 방법으로 이용한다

2.     segment 관리 방법

- manual(수동) : 오라클 논리구조의 최소 저장단위인 블록의 빈공간을 관리하는 파라미터인pctfree 와 pctused를 수동으로 관리하겠다

- auto(자동) : 오라클 논리구조의 최소 저장단위인 블록의 빈공간을 관리하는 파라미터인pctfree와 pctused를 자동으로 관리하겠다

l pct free 주는 법(pct : percentage)

- pct free 는 한계선으로 여유공간을 갖도록 한다

- row migration 만약 pct free 를 넘어서 꽉 채우진 상태에서 갑작스럽게 더 큰사이즈로 update를 하게 되면 다른 block으로 옮겨가는 현상

- pct free가 작으면 row migration 현상이 줄어들고 크면 충분히 데이터를 입력할 수가 없다

- 그래서 DBA들이 관리하기가 너무어려워서 나온 것이 auto 관리 방법이다

CREATE TABLE emp507

(empno NUMBER(10),

ename VARCHAR2(10))

PCTFREE 40;

SELECT table_name, PCT_FREE FROM user_tables;

TABLE_NAME                       PCT_FREE

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

EMP507                                 40

EMP                                    10

DEPT                                   10

ABC                                    10

SALGRADE                               10

BONUS                                  10

l segment space management를 auto로 생성한는 법(default : auto 방식)

CREATE TABLESPACE ts700

DATAFILE '/home/oracle/ts700.dbf' SIZE 50m

SEGMENT SPACE management auto;

CREATE TABLE emp499

(empno NUMBER(10),

ename VARCHAR2(10))

TABLESPACE ts700;

• ONLIEN / OFFLINE

1. 어느 테이블스패이스가 존재하는지 확인


SQL> select table_name, tablespace_name
from dba_tables
where table_name='EMP';

TABLE_NAME                     TABLESPACE
------------------------------ ----------
EMP                            USERS


2. users 라는 테이블스패이스를 offlne 으로 변경후 조회

SQL> alter tablespace users offline;

Tablespace altered.

SQL> select * from scott.emp;

select * from scott.emp

                    *

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/u01/app/oracle/oradata/dbdb/users01.dbf'

l 테이블 스패이스를 접근하지 못하게 하고 싶을 때 offline 시킨다

l 위처럼 오프라인 된 테이블을 조회하면 오류가 발생한다

• READ / WRITE

상태 확인

select t.name, d.enabled

from v$tablespace t, v$datafile d

where t.ts# = d.ts#;

문제45. users 테이블 스패이스를 read only로 변경하시오

alter tablespace users read only;

l read only 로 변경했을 때 drop 은 되는데 delete는 안됨