tablespace 추가 및 삭제 등

tablespace 추가 및 삭제 등

 

Datafile - 확인
   SQL> select name from v$datafile;

 NAME
 ---------------------------------------------------------
 /u01/app/oracle/oradata/hwdb/system01.dbf
 /u01/app/oracle/oradata/hwdb/undotbs01.dbf
 /u01/app/oracle/oradata/hwdb/sysaux01.dbf

  SQL> select name from v$tempfile;

 NAME
 ---------------------------------------------------------
 /u01/app/oracle/oradata/hwdb/temp01.tmp

 

  SQL> select TABLESPACE_NAME, CONTENTS from dba_tablespaces;

 

 - Tablespace 추가 및 삭제

  SQL> create tablespace users datafile '/u01/app/oracle/oradata/hwdb/users01.dbf' size 10m;
  SQL> create tablespace tools datafile '/u01/app/oracle/oradata/hwdb/tools01.dbf' size 10m;

  SQL> create UNDO      tablespace undotbs02 

           datafile '/u01/app/oracle/oradata/hwdb/undotbs02.dbf' size 10m;
  SQL> create TEMPORARY tablespace TEMP_TS 

           tempfile '/u01/app/oracle/oradata/hwdb/temp_ts01.dbf' size 10m;

  SQL> ed ts.sql

           col tablespace_name format a20
           col file_name format a60

           select d.file_id, t.TABLESPACE_NAME,t.CONTENTS,           d.FILE_NAME,d.AUTOEXTENSIBLE
           from dba_tablespaces t, dba_data_files d
           where t.tablespace_name = d.tablespace_name
        order by tablespace_name;

  SQL> @ts

  SQL> drop tablespace users including contents and datafiles;
  SQL> drop tablespace tools including contents and datafiles;

  SQL> @ts

  SQL> create tablespace users
       datafile '/u01/app/oracle/oradata/hwdb/users01.dbf' size 10m autoextend on next 10m maxsize 2G,
                '/u01/app/oracle/oradata/hwdb/users02.dbf' size 10m;

 - Tablespace 수정

  SQL> alter database datafile '/u01/app/oracle/oradata/hwdb/users02.dbf' autoextend on next 10m maxsize 2G;
  SQL> alter database datafile '/u01/app/oracle/oradata/hwdb/users02.dbf' resize 20m;

  SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/hwdb/users03.dbf' size 10m;

  SQL> alter tablespace users read only;
  SQL> alter tablespace users read write;

  SQL> alter tablespace users offline;
  SQL> alter tablespace users online;

 - Database-wide 설정

  SQL> ed db.sql

 col PROPERTY_NAME format a40
 col PROPERTY_VALUE format a60

 select PROPERTY_NAME, PROPERTY_VALUE
 from database_properties
 where property_name like '%TABLESPACE';

  SQL> @db

 PROPERTY_NAME                            PROPERTY_VALUE
 ---------------------------------------- ------------------------------------------------------------
 DEFAULT_TEMP_TABLESPACE                  TEMP
 DEFAULT_PERMANENT_TABLESPACE             SYSTEM

  SQL> alter database default tablespace users;                   --> 10gNF
  SQL> alter database default temporary tablespace temp_ts;       --> 9iNF

  SQL> @db

 PROPERTY_NAME                            PROPERTY_VALUE
 ---------------------------------------- ------------------------------------------------------------
 DEFAULT_TEMP_TABLESPACE                  TEMP_TS
 DEFAULT_PERMANENT_TABLESPACE             USERS

  SQL> show parameter undo_tablespace

 NAME                                 TYPE                   VALUE
 ------------------------------------ ---------------------- ------------------------------
 undo_tablespace                      string                 undotbs01

  SQL> alter system set undo_tablespace = undotbs02;

  SQL> show parameter undo_tablespace

 NAME                                 TYPE                   VALUE
 ------------------------------------ ---------------------- ------------------------------
 undo_tablespace                      string                 UNDOTBS02

 - Datafile의 위치 변경 : 새 Disk를 구매해서 일부 파일을 이동하려는 상황을 가정합니다.

  SQL> create tablespace ts1000 datafile '/u01/app/oracle/oradata/hwdb/ts1000.dbf' size 10m;

  SQL> -- 새로운 disk를 구매
  SQL> !mkdir /u01/app/oracle/oradata/new_disk

  SQL> alter tablespace ts1000 offline;
  SQL> !mv /u01/app/oracle/oradata/hwdb/ts1000.dbf /u01/app/oracle/oradata/new_disk
  SQL> alter database rename file '/u01/app/oracle/oradata/hwdb/ts1000.dbf' to '/u01/app/oracle/oradata/new_disk/ts1000.dbf';
  SQL> alter tablespace ts1000 online;

  SQL> @ts

 TABLESPACE_NAME      CONTENTS           FILE_NAME
 -------------------- ------------------ ------------------------------------------------------------
 TS1000               PERMANENT          /u01/app/oracle/oradata/new_disk/ts1000.dbf

 ... 생략...

  SQL> drop tablespace ts1000 including contents and datafiles;
  SQL> !rm -rf /u01/app/oracle/oradata/new_disk