Oracle table space to learn

Table spatial learning

Personally feel that table space relative to the database is critical, is generally divided into permanent (permanent table space), undo (undo tablespace), temporary (temporary table space), generally by the following SQL View table space type.

select distinct t.contents from dba_tablespaces t;

View the table space corresponding to the data files.
select t.name, t1.name
from v $ tablespace t,
v $ datafile t1
where t.ts # = t1.ts #;

The following write statement to create three table space.

a created temporary table space

create temporary tablespace tbs_temp1
tempfile ‘/ opt/oracle/oradata/orcl/tbs_temp1.dbf’ size 10M;

create undo tablespace

create undo tablespace tbs_undo
datafile ‘/ opt / oracle / oradata / orcl / tbs_undo.dbf’ size 10M;

c. permanent table space

create tablespace tbs_cb4
datafile ‘/ opt/oracle/oradata/orcl/tbs_cb4.dbf’ size 10M;

I create a user, for example the specified table space and allow users to use another table space

create user hwdw identified by jctlinux
default tablespace tbs_cb4;

grant connect to hwdw;
grant resource to hwdw;

connect and resource privileges were rolename

Change users default table space

alter user hwdw default tablespace tbs_cb3;

Allows the user to user to another table space

alter user hwdw quota 5m on tbs_cb4;
alter user hwdw quota unlimited on tbs_cb4;