Oracle Tablespace 생성.

1. oracle 접속.
SQL> connect /as sysdba

- tablespace 정보 확인
SQL> select tablespace_name, status, contents, extent_management from dba_tablespaces;

- 물리적 파일 위치 보기.
SQL> select tablespace_name, bytes, file_name from dba_data_files
         union all
         select tablespace_name, bytes, file_name from dba_temp_files;
 

2. tablespace 생성
SQL> create tablespace TESTDB
         datafile '/data/oracle/app/oradata/orcl/testdb.dbf'
         size 500M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
 ==> TESTDB 테이블스페이스 생성, 초기 500M  증가치 100M 단위, 최대 1000M


3. INDEX tablespace 생성.
SQL> create tablespace TESTIDX
         datafile '/data/oracle/app/oradata/orcl/testidx.dbf'
         size 500M AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;

4. Temp tablespace 생성.
SQL> create temporary tablespace TESTTEMP
         tempfile '/data/oracle/app/oradata/orcl/testtemp.dbf'
         size 200M AUTOEXTEND ON NEXT 200M MAXSIZE 1000M;

5. 사용자 생성.
SQL> create user testuser identified by testuser default tablespace TESTDB
         temporary tablespace TESTTEMP;

6. 권한 부여.
SQL> GRANT connect, resource TO tempuser;


7. 삭제
SQL> drop user testuser cascade;  --> 사용자 삭제
SQL> drop tablespace TESTDB including contents;
SQL> drop tablespace TESTIDX including contents;
SQL> drop tablespace TESTTEMP including contents;