OMF

▣ OMF : 파일의 위치와 이름이 자동

• Datafile : DB_CREATE_FILE_DEST
• Redo : DB_CREATE_ONLINE_LOG_DEST_n
• FRA : DB_RECOVERY_FILE_DEST

   

S SYS> alter tablespace ts1 add datafile size 20M;

   

Tablespace altered.

   

S SYS> show parameter db_create_file

   

NAME TYPE VALUE

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

db_create_file_dest string /u01/app/oracle/oradata

S SYS> alter system set db_create_file_dest=' ' scope=both;

   

System altered.

   

S SYS> create tablespace test;

create tablespace test

*

ERROR at line 1:

ORA-02199: missing DATAFILE/TEMPFILE clause

   

   

S SYS> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;

   

System altered.

   

S SYS> create tablespace test;

   

Tablespace created.

   

▣ Storage for Locally Managed Tablespaces

• ASSM : Free Space 할당에 대한 경합 줄임 by 트리구조로 빈 공간 연결해서.

   

▣ Tablespace 종류

• Data(permanent)
• Temp : user
• Undo : instance

   

▣ Tablespace in the Preconfigured Database

• 교체불가 필수 : SYSTEM, SYSAUX
• 필수 : UNDOTBS1, TEMP

   

▣ Changing the size : Datafile의 크기에 의해 결정 => 자동증가값 포함

Ex) a.dbf : 10M 최대 1G까지 늘어남

a2.dbf : 20M 최대 2G까지 늘어남

=> 가지고 있는 tablespace의 크기는 : 3G

   

▣ Datafile 삭제

   

   

   

S SYS> select OWNER,SEGMENT_NAME,SEGMENT_TYPE

2 from dba_segments

3 where TABLESPACE_NAME='TS3';

   

OWNER SEGMENT_NAME SEGMENT_TYPE

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

HR X TABLE

   

S SYS> truncate table hr.x;

   

Table truncated.

   

S SYS> alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf';

alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf'

*

ERROR at line 1:

ORA-03262: the file is non-empty

   

S SYS> select segment_name,segment_type from dba_segments

2 where tablespace_name='TS3';

   

SEGMENT_NAME SEGMENT_TYPE

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

BIN$cWrUG8tLFxHgQAB/ TABLE

AQA4Uw==$0

   

S SYS> purge dba_recyclebin;

   

S SYS> alter tablespace ts3 drop datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts3_58od8rw2_.dbf';

   

Tablespace altered.

   

▣ Actions with Tablespaces (EM)

General D이	Alter tablespace T/S명 add datafile ' ';
Make Locally Managed	Dbua로 업그레이드한 옛 파일
Make Readonly	Alter tablespace ts명 read only;
Make Writable	Alter tablespace ts명 read write;
Place Online	Alter tablespace ts명 online
Reorganize	Move + index 재구성+통계수집
Run Segment Advisor	공간 줄일 객체 찾아줌
Show Dependencies	관련요소 찾음
Show Tablespace Contents	Segment + extents 측면으로 보여줌 => tablespacemap이 나옴
Take Offline	Alter tablespace offline;