oracle 테이블스페이스 생성

SQL>SELECT * FROM DBA_TABLESPACES;
SQL>SELECT * FROM V$DATAFILE;

Warning: For operating systems that support raw devices, be aware that the STORAGE clause REUSE keyword has no meaning when specifying a raw device as a data file in a CREATE TABLESPACE command; such a command will always succeed even if REUSE is not specified.

Example I
This command creates a tablespace named TABSPACE_2 with one data file:

CREATE TABLESPACE tabspace_2
DATAFILE 'diska:tabspace_file2.dat' SIZE 20M
DEFAULT STORAGE (INITIAL 10K NEXT 50K
MINEXTENTS 1 MAXEXTENTS 999
PCTINCREASE 10)
ONLINE;

Example II
This command creates a tablespace named TABSPACE_3 with one data file; when more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:

CREATE TABLESPACE tabspace_3
DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 10M;

Example III
This command creates tablespace TABSPACE_5 with one data file and allocates every extent as a multiple of 64K:

CREATE TABLESPACE tabspace_3
DATAFILE 'tabspace_file5.dbf' SIZE 2M
MINIMUM EXTENT 64K
DEFAULT STORAGE (INITIAL 128K NEXT 128K)
LOGGING;


테이블스페이스 보기

SQL> desc dba_tablespaces;
이름                                                        널?      유형  
----------------------------------------- -------- ------------  
TABLESPACE_NAME                           NOT NULL VARCHAR2(30)  
INITIAL_EXTENT                                                  NUMBER  
NEXT_EXTENT                                                    NUMBER  
MIN_EXTENTS                                    NOT NULL NUMBER  
MAX_EXTENTS                                                   NUMBER  
PCT_INCREASE                                                   NUMBER  
MIN_EXTLEN                                                       NUMBER  
STATUS                                                              VARCHAR2(9)  
CONTENTS                                                         VARCHAR2(9)
LOGGING                                                            VARCHAR2(9)
EXTENT_MANAGEMENT                                      VARCHAR2(10)  
ALLOCATION_TYPE                                             VARCHAR2(9)  
PLUGGED_IN                                                       VARCHAR2(3)


SELECT B.FILE_ID FILE_NO, B.TABLESPACE_NAME TBS_NAME, B.BYTES / 1024 FILESIZE, ((B.BYTES - SUM(NVL(A.BYTES,0)))) / 1024 USEDSIZE, (SUM(NVL(A.BYTES,0))) / 1024 FREESIZE, TO_CHAR((SUM(NVL(A.BYTES,0)) / (B.BYTES)) * 100,'999') FREE FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID(+) = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.BYTES ORDER BY B.FILE_ID ;