오라클 DB - Tablespace와 data file

Tablespace와 data file

---

실습 5.1 Tablespace와 Data file 상태 조회 (필)

---

사용 명령어

SQL> SELECT tablespace_name, status, contents, 
 2 extent_management, segment_space_management
 3 FROM dba_tablespaces;
   - Tablespace의 상태를 조회한다.
   - STATUS : 사용 가능여부
   - CONTENTS : 저장 Segment의 종류
   - EXTENT_MANAGEMENT : Extent의 할당 및 관리 방식
   - SEGMENT_SPACE_MANAGEMENT : Block내의 공간 관리 방식

SQL> SELECT tablespace_name, bytes, file_name 
 2 FROM dba_data_files;
   - Tablespace별 data file의 상태를 조회 한다.
   - BYTES : Data file의 크기
   - FILE_NAME : Data file의 경로명을 포함한 이름

SQL> SELECT t.name tablespace_name, d.bytes, d.name file_name
 2 FROM v$tablespace t, v$datafile d
 3 WHERE t.ts#=d.ts#;
   - Tablespace별 data file의 상태를 조회 한다.
   - Dictionary가 아니라 dynamic performance view를 조회하는 것이므로 
     MOUNT 상태에서도 조회 가능하다.

---

SQL> SELECT tablespace_name, status, contents,                       ← tablespace의 상태를 조회한다. 
 2 extent_management, segment_space_management
 3 FROM dba_tablespaces 
 4 ORDER BY 1;

TABLESPACE_NAME  STATUS CONTENTS  EXTENT_MAN SEGMEN
---------------- ------ --------  ---------- ------
SYSAUX           ONLINE PERMANENT LOCAL      AUTO
SYSTEM           ONLINE PERMANENT LOCAL      MANUAL
TEMP             ONLINE TEMPORARY LOCAL      MANUAL
UNDOTBS1         ONLINE UNDO      LOCAL      MANUAL
USERS            ONLINE PERMANENT LOCAL      AUTO

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files    ← data file의 상태를 조회한다.
 2 ORDER BY 1;

TABLESPACE_NAME      BYTES       FILE_NAME
----------------  ---------- ----------------------------------------
SYSAUX             482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
SYSTEM             838860800 /app/ora19c/oradata/DB19/system01.dbf
UNDOTBS1            68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
USERS                5242880 /app/ora19c/oradata/DB19/users01.dbf

문제. dba_data_files 와 dba_temp_files를 합쳐서 temp파일내용이 나오게하라.

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files
     2 UNION
     3 SELECT tablespace_name, bytes, file_name FROM dba_temp_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSAUX                597688320 /app/ora19c/oradata/DB19/sysaux01.dbf
SYSTEM                943718400 /app/ora19c/oradata/DB19/system01.dbf
TEMP                  135266304 /app/ora19c/oradata/DB19/temp01.dbf
UNDOTBS1              361758720 /app/ora19c/oradata/DB19/undotbs01.dbf
USERS                   5242880 /app/ora19c/oradata/DB19/users01.dbf

SQL> SELECT tablespace_name, bytes, file_name FROM dba_temp_files;        ← temp file의 상태를 조회 한다.

TABLESPACE_NAME     BYTES    FILE_NAME
---------------  ----------  ---------------------------------------------
TEMP               33554432  /app/ora19c/oradata/DB19/temp01.dbf

SQL> SELECT ts#, file#, name FROM v$datafile;

    TS#       FILE#                    NAME
---------- ----------   ----------------------------------------
         0          1   /app/ora19c/oradata/DB19/system01.dbf
         1          3   /app/ora19c/oradata/DB19/sysaux01.dbf
         2          4   /app/ora19c/oradata/DB19/undotbs01.dbf
         4          7   /app/ora19c/oradata/DB19/users01.dbf

SQL> SELECT ts#, name FROM v$tablespace;

   TS#                    NAME
---------- ----------------------------------------
         1 SYSAUX
         0 SYSTEM
         2 UNDOTBS1
         4 USERS
         3 TEMP

SQL> SELECT t.name tablespace_name, d.bytes, d.name file_name        ← 검색 환경이 MOUNT 이상에서 모두 조회가 가능하다.
 2 FROM v$tablespace t, v$datafile d                                                        
 3 WHERE t.ts#=d.ts# 
 4 ORDER BY 1;

TABLESPACE_NAME     BYTES                FILE_NAME
---------------- ---------- ----------------------------------------
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf

---

실습 5.2 Tablespace 생성 (필)

---

사용 명령어

SQL> CREATE TABLESPACE <tablespace명> 
 2 DATAFILE '<data file>' SIZE <크기>;
    - 오라클 10g 이후 버전에서 사용자용 tablespace생성
    - Extent management 는 locally 방식으로, segment space management는 AUTO 방식으로 생성된다.

SQL> CREATE TABLESPACE <tablespace명> 
 2 DATAFILE '<data file>' SIZE <크기>
 3 SEGMENT SPACE MANAGEMENT AUTO; 
    - 오라클 9i 버전에서 사용자용 tablespace생성 
    - Default로 Extent management는 locally방식으로 정의되지만 segment space management는 
      defalt가 manual이므로 반드시 정의해주어야 한다.

 

SQL> CREATE TABLESPACE insa                                    ← 사용중인 버전이 10g, 12c인 경우 
 2 DATAFILE '/app/ora19c/oradata/DB19/insa01.dbf' SIZE 1M;       tablespace생성 명령

tablespace created.

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;       ← 생성된 tablespace의 설정과 data file을 확인한다.
                                                                                      
TABLESPACE_NAME     BYTES                 FILE_NAME
---------------- ---------- ----------------------------------------
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
INSA                1048576 /app/ora19c/oradata/DB19/insa01.dbf

SQL> SELECT tablespace_name, status, contents, 
 2 extent_management, segment_space_management
 3 FROM dba_tablespaces 
 4 ORDER BY 1; 

TABLESPACE_NAME    STATUS   CONTENTS EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
INSA             ONLINE    PERMANENT LOCAL      AUTO
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
........

 

---

실습 5.3 Tablespace 확장 1 - 수동(필)

---

사용 명령어

SQL> ALTER DATABASE DATAFILE
 2 '<data file>' RESIZE <크기>;
    - 지정된 파일의 크기를 늘린다.

SQL> ALTER TABLESPACE <tablespace명>
 2 ADD DATAFILE '<추가될 data file>' SIZE <크기>;
   - Data file을 추가한다.

---

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files; 
 
TABLESPACE_NAME     BYTES                FILE_NAME
---------------- ---------- ----------------------------------------
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
UNDOTBS1            8157440 /app/ora19c/oradata/DB19/undotbs01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
INSA                1048576 /app/ora19c/oradata/DB19/insa01.dbf

SQL> ALTER DATABASE DATAFILE                              ← 원래 크기가 1M인 data file의 크기를 2M로 늘린다.
 2 '/app/ora19c/oradata/DB19/insa01.dbf' RESIZE 2M;

Database altered.

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

TABLESPACE_NAME     BYTES                FILE_NAME
---------------- ---------- ----------------------------------------
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
INSA                2097152 /app/ora19c/oradata/DB19/insa01.dbf

SQL> ALTER TABLESPACE insa                                          ← tablespace에 2M 크기의 data file을 추가 한다.
 2 ADD DATAFILE '/app/ora19c/oradata/DB19/insa02.dbf' SIZE 2M;

tablespace altered.

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

TABLESPACE_NAME     BYTES                 FILE_NAME
---------------- ---------- ----------------------------------------
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
INSA                2097152 /app/ora19c/oradata/DB19/insa01.dbf
INSA                2097152 /app/ora19c/oradata/DB19/insa02.dbf
6 rows selected.

 

---

실습 5.4 Tablespace 확장 2 - 자동(필)

---

사용 명령어

SQL> CREATE TABLESPACE <tablespace명> 
 2 DATAFILE '<data file명>' SIZE <크기>
 3 AUTOEXTEND ON NEXT <크기> MAX SIZE <크기>;
    - 자동으로 커지는 data file을 갖는 tablespace를 생성한다.
    - NEXT : 증가치
    - MAX SIZE : 최대 크기

SQL> ALTER TABLESPACE <tablespace명>
 2 ADD DATAFILE '<추가될 data file명>' SIZE <크기>
 3 AUTOEXTEND ON NEXT <크기> MAX SIZE <크기>;
    - tablespace에 자동으로 커지는 data file을 추가 한다.

SQL> ALTER DATABASE
 2 DATAFILE '<변경할 data file명>
 3 AUTOEXTEND [ON | OFF] NEXT <크기> MAX SIZE <크기>;
    - 지정한 data file을 자동증가를 설정한다.

SQL> CREATE TABLESPACE usr                           ← 각각 설정이 서로 다른 세 개의 data file을 추가 한다. 크기가 자동으로
 2 DATAFILE                                            증가하는 autoextend 설정은 data file 별로 따로 설정한다.
 3 '/app/ora19c/oradata/DB19/usr01.dbf' SIZE 2M AUTOEXTEND ON,
 4 '/app/ora19c/oradata/DB19/usr02.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M,
 5 '/app/ora19c/oradata/DB19/usr03.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

tablespace created.

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

TABLESPACE_NAME     BYTES                 FILE_NAME
---------------- ---------- ----------------------------------------
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
INSA                2097152 /app/ora19c/oradata/DB19/insa01.dbf
INSA                2097152 /app/ora19c/oradata/DB19/insa02.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr01.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr02.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr03.dbf
9 rows selected.

SQL> SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes
 2 FROM dba_data_files
 3 WHERE tablespace_name = 'USR';

TABLESPACE_NAME               FILE_NAME              AUT INCREMENT_BY  MAXBYTES
---------------- ----------------------------------- --- ------------ ----------
USR              /app/ora19c/oradata/DB19/usr01.dbf  YES            1 3.4360E+10
USR              /app/ora19c/oradata/DB19/usr02.dbf  YES          640 3.4360E+10
USR              /app/ora19c/oradata/DB19/usr03.dbf  YES          640 20971520

SQL> ALTER TABLESPACE insa 
 2 ADD DATAFILE '/app/ora19c/oradata/DB19/insa03.dbf' SIZE 2M 
 3 AUTOEXTEND ON NEXT 5M MAXSIZE 20M;                          ← data file의 자동 증가는 data file 각각의 
                                                                 설정이므로 기존 tablespace에 자유롭게 추가할 수 있다.
Tablespace altered.

SQL> SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes
 2 FROM dba_data_files
 3 WHERE lower(tablespace_name) = 'insa';

TABLESPACE_NAME              FILE_NAME                    AUT INCREMENT_BY  MAXBYTES
---------------- ---------------------------------------- --- ------------ ----------
INSA             /app/ora19c/oradata/DB19/insa01.dbf      NO             0          0
INSA             /app/ora19c/oradata/DB19/insa02.dbf      NO             0          0
INSA             /app/ora19c/oradata/DB19/insa03.dbf      YES          640   20971520

SQL> ALTER DATABASE DATAFILE '/app/ora19c/oradata/DB19/insa01.dbf'
 2 AUTOEXTEND ON;

Database altered.

SQL> SELECT tablespace_name, file_name, autoextensible, increment_by, maxbytes
 2 FROM dba_data_files
 3 WHERE lower(tablespace_name) = 'insa';
 
 TABLESPACE_NAME               FILE_NAME                  AUTOEX INCREMENT_BY  MAXBYTES
---------------- ---------------------------------------- ------ ------------ ----------
INSA             /app/ora19c/oradata/DB19/insa01.dbf      YES               1 3.4360E+10
INSA             /app/ora19c/oradata/DB19/insa02.dbf      NO                0          0
INSA             /app/ora19c/oradata/DB19/insa03.dbf      YES             640   20971520

 

 

---

실습 5.5 Tablespace 삭제 (필)

---

사용 명령어

SQL> DROP TABLESPACE <tablespace 명>
 2 [INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS];
 
    - 지정된 tablespace를 삭제한다.
    - INCLUDING CONTENT : Tablespace에 segment가 존재할 때 segment를 같이 삭제한다.
    - CASCADE CONSTRAINTS : 삭제되는 tablespace의 table을 다른 tablespace의
                            table이 참조하는 경우에 해당 constraint를 같이 삭제한다.
    - AND DATAFILES : Tablespace에 포함된 data file을 같이 지운다. 이 옵션을 
                      쓰지 않으면 데이터파일은 OS상에서 직접 지워야한다.

SQL> DROP TABLESPACE users;                    ← 10g 버전에 추가된 defaule permanent tablespace로   
DROP TABLESPACE users                            지정된 tablespace는삭제되지 않는다.
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

SQL> SELECT * FROM database_properties WHERE property_name LIKE '%DEFAULT_PERMAN%';

PROPERTY_NAME                  PROPERTY_V             DESCRIPTION
------------------------------ ---------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS      Name of default permanent tablespace

SQL> DROP TABLESPACE insa;                     ← 비어있는 tablespace만 삭제가 가능하다.

tablespace dropped.

SQL> !ls /app/ora19c/oradata/DB19/           ← tablespace를 삭제해도 data file이 OS상에서 지워지지 않는다.
insa01.dbf   insa03.dbf        system01.dbf   undotbs01.dbf   usr01.dbf   usr03.dbf
insa02.dbf   sysaux01.dbf   temp01.dbf       users01.dbf       usr02.dbf

SQL> !rm /app/ora19c/oradata/DB19/insa01.dbf         ← data file은 OS명령을 통해 직접 지워준다.
SQL> !rm /app/ora19c/oradata/DB19/insa02.dbf
SQL> !rm /app/ora19c/oradata/DB19/insa03.dbf

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

TABLESPACE_NAME     BYTES                 FILE_NAME
---------------- ---------- ----------------------------------------
SYSAUX            482344960 /app/ora19c/oradata/DB19/sysaux01.dbf
SYSTEM            838860800 /app/ora19c/oradata/DB19/system01.dbf
UNDOTBS1           68157440 /app/ora19c/oradata/DB19/undotbs01.dbf
USERS               5242880 /app/ora19c/oradata/DB19/users01.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr03.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr01.dbf
USR                 2097152 /app/ora19c/oradata/DB19/usr02.dbf

7 rows selected.

SQL> DROP TABLESPACE usr;

Tablespace dropped.

SQL> !rm /app/ora19c/oradata/DB19/usr*

SQL> !ls /app/ora19c/oradata/DB19/
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

 

 

---

실습 5.6 Tablespace READ ONLY

---

사용 명령어
SQL> ALTER TABLESPACE <tablespace명> [ READ ONLY / READ WRITE ]
          - 지정한 tablespace를 읽기 전용(읽기 쓰기)으로 변경한다.

SQL> ALTER TABLESPACE <tablespace명> [ READ ONLY / READ WRITE ]
                     - 지정한 tablespace를 읽기 전용(읽기 쓰기)으로 변경한다.

SQL> SELECT username, account_status, default_tablespace
 2 FROM dba_users
 3 WHERE username = 'ST';

USERNAME            ACCOUNT_STATUS                 DEFAULT_TA                 ← 만일 account_status가 EXPIRED & LOCKED 라면
----------------    ----------------------------   -------------------          계정의 lock을 풀어줘야 된다.
ST                  OPEN                           USERS

SQL> CREATE TABLE st.test (no NUMBER); 

Table created.

SQL> SELECT owner, table_name, tablespace_name FROM dba_tables 
 2 WHERE owner = 'ST' AND table_name = 'TEST'; 

OWNER            TABLE_NAME TABLESPACE_NAME
---------------- ---------- ----------------
ST               TEST       USERS

SQL> INSERT INTO st.test VALUES (10);

1 row created.

SQL> COMMIT;

commit complete.

SQL> SELECT * FROM st.test;

    NO
----------
        10

SQL> ALTER TABLESPACE users READ ONLY;             ← Users tablespace를 읽기 전용으로 변경한다.

Tablespace altered.

SQL> SELECT tablespace_name, status, contents, 
 2 extent_management, segment_space_management
 3 FROM dba_tablespaces 
 4 ORDER BY 1; 

TABLESPACE_NAME    STATUS  CONTENTS  EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
SYSTEM           ONLINE    PERMANENT LOCAL      MANUAL
TEMP             ONLINE    TEMPORARY LOCAL      MANUAL
UNDOTBS1         ONLINE    UNDO      LOCAL      MANUAL
USERS            READ ONLY PERMANENT LOCAL      AUTO

SQL> CONN st/st

Connected.

SQL> INSERT INTO test VALUES (20);           ← 읽기 전용 tablespace의 table에 대한 작업은 불가능하다.
INSERT INTO test VALUES (20)
 *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/app/ora19c/oradata/disk3/users01.dbf'

SQL> DELETE FROM test;

DELETE FROM st.test
 *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 4: '/app/ora19c/oradata/disk3/users01.dbf'

SQL> SELECT * FROM test;
    NO
----------
        10

SQL> DROP TABLE test; ← 읽기 전용 tablespace의 table은 삭제가능하다.

Table dropped.

SQL> SELECT * FROM tab WHERE tname = 'TEST';

no rows selected

SQL> CONN / AS sysdba

Connected.

SQL> ALTER TABLESPACE users READ WRITE; ← Ttablespace를 정상으로 되돌린다.

tablespace altered.

SQL> SELECT tablespace_name, status, contents, 
 2 extent_management, segment_space_management
 3 FROM dba_tablespaces 
 4 ORDER BY 1; 

TABLESPACE_NAME    STATUS  CONTENTS  EXTENT_MAN SEGMEN
---------------- --------- --------- ---------- ------
SYSAUX           ONLINE    PERMANENT LOCAL      AUTO
SYSTEM           ONLINE    PERMANENT LOCAL      MANUAL
TEMP             ONLINE    TEMPORARY LOCAL      MANUAL
UNDOTBS1         ONLINE    UNDO      LOCAL      MANUAL
USERS            ONLINE    PERMANENT LOCAL      AUTO