06_tablespace

A.조회

1.tablespace의 상태를 조회 

SELECT tablespace_name, status, contents,               
extent_management, segment_space_management
FROM dba_tablespaces                                      
ORDER BY 1;

 

 

2. data file의 상태를 조회한다

SELECT tablespace_name, bytes, file_name
FROM dba_data_files
ORDER BY tablespace_name;

 

 

 

3.temp file의 상태를 조회 한다

SELECT tablespace_name, bytes, file_name FROM dba_temp_files;

 

 

4.검색 환경이 MOUNT 이상에서 모두 조회

 

SELECT t.name tablespace_name, d.bytes, d.name file_name
FROM v$tablespace t, v$datafile d
WHERE t.ts# = d.ts#
ORDER BY 1;

 

 

 

 

 

 

 

B. Tablespace 생성

 

 

1. 사용 명령어

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 <tablespace명>                                         
  2  DATAFILE '<data file>' SIZE <크기>
  3  EXTENT MANAGEMENT LOCAL;

     - 오라클 8i 버전에서 사용자용 tablespace생성
     - Extent management의 deault가 dictionary이므로 locally방식을 사용하기 위해서는 반드시 정의해줘야 한다.

 

 

 

2.실습

 

1)생성

CREATE TABLESPACE insa
DATAFILE '/app/ora19c/oradata/DB19/insa01.dbf' SIZE 1M;

 

2)

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

 

 

 

c.확장

 

1. 사용 명령어

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을 자동증가를 설정한다

 

 

 

 

2.실습

1)각각 설정이 서로 다른 세 개의 data file을 추가 한다.

크기가 자동으로 2 DATAFILE 증가하는 autoextend 설정은 data file 별로 따로 설정한다

 

 CREATE TABLESPACE usr                   
    DATAFILE                                 
    '/app/ora19c/oradata/DB19/usr01.dbf' SIZE 2M AUTOEXTEND ON,
    '/app/ora19c/oradata/DB19/usr02.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M,
    '/app/ora19c/oradata/DB19/usr03.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

 

 

 

 

2)기존 테이블에 추가

ALTER TABLESPACE insa 
ADD DATAFILE '/app/ora19c/oradata/DB19/insa03.dbf' SIZE 2M  
AUTOEXTEND ON NEXT 5M MAXSIZE 20M; 

 

 

 

 

 

 

 

 

 

D. Tablespace 삭제

 

1. 사용 명령어

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상에서 직접 지워야한다. 

 

 

2.실습

1) 아래 상항에서는 삭제가 안된다 

 

- 비어있는 tablespace만 삭제가 가능하다.

DROP TABLESPACE users;

 

 

-tablespace를 삭제해도 data file이 OS상에서 지워지지 않기 떄문에 OS명령을 통해 직접 지워준다.

 !rm /app/ora19c/oradata/DB19/insa01.dbf  
 !rm /app/ora19c/oradata/DB19/insa02.dbf
 !rm /app/ora19c/oradata/DB19/insa03.dbf

 

 

-성공적으로 지워지는 것을 볼 수 있다;

 

 

 

 

 

 

 

 

E. READ ONLY

 

1. 사용 명령어

SQL> ALTER TABLESPACE [ READ ONLY / READ WRITE ]

- 지정한 tablespace를 읽기 전용(읽기 쓰기)으로 변경한다.

 

 

 

2.실습

-  Users tablespace를 읽기 전용으로 변경한다.

ALTER TABLESPACE users READ ONLY;

 

 

READ ONLY

 

 

- 읽기 전용 tablespace의 table에 대한 작업은 불가능하다.

INSERT INTO test VALUES (20); 

conn st/st로 계정전환

 

 

-읽기 전용은 삭제는 가능하다.

DROP TABLE test;

 

 

 

 

-다시 읽기 쓰기 모드로 전환

conn / AS sysdba

ALTER TABLESPACE users READ WRITE;

 

 

 

 

 

 

F. OPEN 상태에서 tablespace 이동

1. 사용 명령어

SQL> ALTER TABLESPACE OFFLINE;

- 지정된 tablespace OFFLINE 한다.

- Offline된 tablespace는 더 이상 접근이 불가능하다.

 

SQL> ALTER TABLESPACE RENAME DATAFILE 2

<원래 data file명> TO <이동한 data file>;

- Data file 등록 정보 수정(rename 한다.)

 

SQL> ALTER TABLESPACE ONLINE;

- 지정된 tablespace를 ONLINE한다. 필요하다면 DATA FILE 레벨에서도 OFFLINE 가능하다.

 

SQL> ALTER DATABASE DATAFILE '' OFFLINE [DROP];

- DROP은 데이터 파일 손상시 사용한다.

 

 

2.실습

(step1) 이동 하고자 하는 tablespace를 먼저 OFFLINE 시킨다. 

ALTER TABLESPACE users OFFLINE;

 

 

(step2) tablespace의 OFFLINE을 확인한다. 

SELECT tablespace_name, status, contents, 
       extent_management, segment_space_management 
FROM dba_tablespaces 
ORDER BY 1;

 

 (step3) data file을 이동한다. 

!mv /app/ora19c/oradata/DB19/users01.dbf /app/ora19c/oradata/disk3

 

 

 (step4)   data file의 등록 정보를 수정 한다. 

ALTER TABLESPACE users RENAME DATAFILE 
'/app/ora19c/oradata/DB19/users01.dbf' 
TO 
'/app/ora19c/oradata/disk3/users01.dbf';

 

 (step5) 이동된 tablespace를 ONLINE 한다.

ALTER TABLESPACE users ONLINE; 

 

 

 

 

G. Close 상태에서 tablespace 이동

 

1.명령어

▶ MOUNT 상태에서 data file을 이동하는 과정

step 1. DB SHUTDOWN

step 2. DATA FILE 이동

step 3. DB MOUNT

step 4. ALTER DATABASE 명령으로 DATA FILE 등록

step 5. DB OPEN

 

▶ 사용 명령어

SQL> ALTER DATABASE RENAME FILE

2 <원래 data file명> TO <이동한 data file명>;

- Data file 등록 정보 수정

- ALTER TABLESPACE ~ RENAME DATAFILE 명령과 동일한 효과가 있다.

 

2.실습

(step1)  DB를 SHUTDOWN 한다

SHUTDOWN IMMEDIATE

 

 (step 2)  DATA FILE을 이동한다.

!mv /app/ora19c/oradata/DB19/sysaux01.dbf /app/ora19c/oradata/disk3/

!mv /app/ora19c/oradata/DB19/system01.dbf /app/ora19c/oradata/disk3/

!mv /app/ora19c/oradata/DB19/undotbs01.dbf /app/ora19c/oradata/disk3/

!mv /app/ora19c/oradata/DB19/temp01.dbf /app/ora19c/oradata/disk3/

 

 (step3) DB를 MOUNT 한다. 

STARTUP MOUNT 

 

(step4) Data file은 이동했으나 아직 오라클은 파일이 이전 경로에 위치하는 것으로 알고 있다

ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/sysaux01.dbf' 
TO '/app/ora19c/oradata/disk3/sysaux01.dbf';

 

ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/system01.dbf' 
TO '/app/ora19c/oradata/disk3/system01.dbf';

 

ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/undotbs01.dbf' 
TO '/app/ora19c/oradata/disk3/undotbs01.dbf';

 

ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/temp01.dbf' 
TO '/app/ora19c/oradata/disk3/temp01.dbf';

 

 

>확인

SELECT t.name AS tablespace_name, 
       d.bytes, 
       d.name AS file_name
FROM v$tablespace t, v$datafile d
WHERE t.ts# = d.ts#
ORDER BY 1;

 

 

>temporary tablespace의 파일은 data file이 아니라 temp file로 9i 버전부터 별도로 관리 된다.

SELECT t.name AS tablespace_name, 
       d.bytes, 
       d.name AS file_name
FROM v$tablespace t, v$tempfile d
WHERE t.ts# = d.ts#
ORDER BY 1;

 

 

>불필요한 DB19파일을 제거해준다

!rm -rf /app/ora19c/oradata/DB19

 

 

 

 

 

 

 

 

H.Default permanent tablespace

 

▶ 사용 명령어

SELECT * FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE';

- DB전체에 대해서 정의된 DEFAULT TABLESPACE를 조회한다

 

 

 

 

 

 

 

*실습

 

SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%DEFAULT_PER%';

본 실습에서는ㄴ USERS 에서 te로 바꿀것이다.

 

 

 

-계정생성

CREATE USER te01

IDENTIFIED BY te01;

 

 

 

 

-User 별 DEFAULT TABLESPACE를 지정하지 않으면 DB에 지정된 DEFAULT TABLESPACE가 자동으로 지정된다.

SELECT username, default_tablespace, temporary_tablespace, account_status, profile 
FROM dba_users 
ORDER BY 1;

 

 

 

 

 - DB전체에 정의된 default tabelsapce를 te로 변경한다.

CREATE TABLESPACE te 
     DATAFILE '/app/ora19c/oradata/DB19/te03.dbf' SIZE 10M;

 

ALTER DATABASE DEFAULT TABLESPACE te;

 

 

 

SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%DEFAULT_PERMANENT%';

 

 

 

 

-새로 생성된 사용자뿐아니라 user 레벨에서 DEFAULT TABLESPACE가   
정의되지 않은 모든 사용자의 DEFAULT TABLESPACE가 변경된다.

SELECT username, default_tablespace 
 FROM dba_users
 WHERE username IN ('TE01', 'TE02');