RESIZE로 TABLESPACE의 data file 크기 변경

RESIZE로 TABLESPACE의 data file 크기 변경

---

RESIZE 옵션을 사용하여 수동으로 data file의 크기를 변경할 수 있다.

Data file 확장하는 방법
방법1)
Data file 추가
ALTER TABLESPACE <tablespace명>
ADD DATAFILE '파일 경로' SIZE
방법2)
AUTOEXTEND로 추가
ALTER TABLESPACE <tablespace명>
ADD DATAFILE '파일 경로' SIZE AUTOEXTEND ON
방법3)
RESIZE
ALTER DATABASE [database명] DATAFILE '파일 이름'
RESIZE 크기 [K|M]


【형식】

        ALTER DATABASE <database명>
        DATAFILE 'filename' [, 'filename', ...]
        RESIZE 크기 [K|M];

【예제】
$ sqlplus '/as sysdba'
SQL> column file_name format a60;
SQL> column tablespace_name format a10;
SQL> SELECT tablespace_name,file_name,bytes from DBA_DATA_FILES;
 
TABLESPACE FILE_NAME                                                    BYTES
---------- ------------------------------------------------------------ ----------
USERS      /export/home/oracle/app/oracle/oradata/orcl/users01.dbf         5242880
SYSAUX     /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf      335544320
UNDOTBS1   /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf      26214400
SYSTEM     /export/home/oracle/app/oracle/oradata/orcl/system01.dbf      471859200
 
SQL> ALTER DATABASE
  2  DATAFILE '/export/home/oracle/app/oracle/oradata/orcl/users01.dbf'
  3  RESIZE 10M;
 
Database altered.
 
SQL> SELECT tablespace_name,file_name,bytes from DBA_DATA_FILES;
 
TABLESPACE FILE_NAME                                                    BYTES
---------- ------------------------------------------------------------ ----------
USERS      /export/home/oracle/app/oracle/oradata/orcl/users01.dbf        10485760
SYSAUX     /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf      335544320
UNDOTBS1   /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf      26214400
SYSTEM     /export/home/oracle/app/oracle/oradata/orcl/system01.dbf      471859200
 
SQL>
TABLESPACE가 공간이 부족하여 TABLESPACE에 data file을 추가할 수 있다.
1) TABLESPACE에 data file 추가
【형식】

	ALTER TABLESPACE <tablespace명>
	ADD DATAFILE 'filespec' [, 'filespec', ...]

2) TABLESPACE에 AUTOEXTEND 옵션으로 data file 추가
【형식】

	ALTER TABLESPACE <tablespace명>
	ADD DATAFILE '경로와 이름' SIZE [AUTOEXTEND ON]

[적용예]

D:\30.Project\01.WooriCapital\70.Ref\01.eactive\03.dump>sqlplus system/cho326

SQL*Plus: Release 11.2.0.2.0 Production on 월 4월 7 10:55:02 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> column file_name formta a60;
SP2-0158: unknown COLUMN option "formta"
SQL> column file_name format a60;
SQL> column tablespace_naem format a10;
SQL> select tablespace_name, file_name, bytes from DBA_DATA_FILES;

TABLESPACE_NAME
------------------------------------------------------------
FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
USERS
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF                   104857600

UNDOTBS1
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF                   83886080

SYSAUX
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF                692060160

TABLESPACE_NAME
------------------------------------------------------------
FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
SYSTEM
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF                  629145600

TS_EAI
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\DF_EAI01.DBF               2147483648

SQL> ALTER DATABASE
  2  DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
  3  RESIZE 2G;

Database altered.

SQL> select tablespace_name, file_name, bytes from DBA_DATA_FILES;

TABLESPACE_NAME
------------------------------------------------------------
FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
USERS
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF                   104857600

UNDOTBS1
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF                   83886080

SYSAUX
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF                692060160

TABLESPACE_NAME
------------------------------------------------------------
FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
SYSTEM
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF                 2147483648

TS_EAI
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\DF_EAI01.DBF               2147483648

SQL>