Oracle tablespace oracle10g Learning Series (10)

Four states of tablespace: Online,offline,read Write and ready only

Sql> select file#,status,enabled from V$datafile;

file# STATUS ENABLED

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

1 SYSTEM READ WRITE

2 ONLINE READ WRITE

3 ONLINE READ WRITE

4 ONLINE READ WRITE

5 ONLINE READ WRITE

Users table space state changes:

Sql>alter tablespace users offline;

Sql>alter tablespace users online;

Sql>alter tablespace users Read write;

Sql>alter tablespace users Read only;

To create a table space:

Sql>create tablespace tb01 datafile '/u01/oracle/oradata/orcl/tb01.dbf ' size 10m autoextent on;

Table Space Rename:

Sql>alter tablespace tb01 Rename to tb02;

Table Space Auto-expansion, autoextensible Auto-expand field:

Sql> select tablespace_name,file_name,autoextensible from Dba_data_files;

Sql> select tablespace_name,file_name,autoextensible from Dba_data_files;

Tablespace_name file_name autoextensible

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

USERS/HOME/ORACLE/ORADATA/ORCL/USERS01.DBF YES

SYSAUX/HOME/ORACLE/ORADATA/ORCL/SYSAUX01.DBF YES

UNDOTBS1/HOME/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF YES

SYSTEM/HOME/ORACLE/ORADATA/ORCL/SYSTEM01.DBF YES

EXAMPLE/HOME/ORACLE/ORADATA/ORCL/EXAMPLE01.DBF YES

Sql> select File#,name from V$datafile;

file# NAME

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

1/home/oracle/oradata/orcl/system01.dbf

2/home/oracle/oradata/orcl/undotbs01.dbf

3/home/oracle/oradata/orcl/sysaux01.dbf

4/home/oracle/oradata/orcl/users01.dbf

5/home/oracle/oradata/orcl/example01.dbf

Sql>alter Database datafile 6 resize autoextend on;

Sql>alter tablespace tb01 Add datafile '/u01/oracle/oradata/orcl/tb01.dbf ' size 30m autoextent on;

Table Space File Rename:

The premise is that the database is an archive mode, the database is in mount or open mode, open mode, the table space to offline, and finally the table space online:

Sql>alter tablespace tb01 offline;

Sql>ho cp/u01/oracle/oradata/orcl/tb01.dbf/u01/oracle/oradata/orcl/tb02.dbf

Sql>alter tablespace rename datafile '/u01/oracle/oradata/orcl/tb01.dbf ' to '/u01/oracle/oradata/orcl/tb02.dbf ';

Sql>alter tablespace TB01 Online;

If media recovery is required:

Sql>recover tablespace tb01;

To delete a table space:

Sql>drop tablespace tb01 including contents and datafiles;

To view a table space:

Sql> select Tablespace_name from Dba_data_files;

Tablespace_name

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

USERS

Sysaux

UNDOTBS1

SYSTEM

EXAMPLE

Sql> select name from V$tablespace;

NAME

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

SYSTEM

UNDOTBS1

Sysaux

USERS

TEMP

EXAMPLE

This article is from "Kaka West" blog, please be sure to keep this source http://whnba.blog.51cto.com/1215711/1604183

Oracle tablespace oracle10g Learning Series (TEN)