1.创建表空间

03:01:55 sys@ORADB11G> create tablespace TSPITR datafile '/u01/app/oracle/oradata/TSPITR01.dbf' size 5M autoextend on next 10m maxsize 5G;

Tablespace created.

 

autoextend :自动扩展( 可选)

next 10m自动扩展大小

maxsize :最大文件扩展到多少

UNIFORM SIZE：指定 extent(区)大小

MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO :指定表空间段管理方式为本地管理

事例:

03:29:15 sys@ORADB11G> create tablespace TSPITR datafile '/u01/app/oracle/oradata/TSPITR01.dbf' size 20m autoextend on next 10m extent management local uniform size 256k;

Tablespace created.

03:29:36 sys@ORADB11G> 

 

2.删除表空间及文件

03:20:56 sys@ORADB11G> drop tablespace TSPITR including contents and datafiles;

 

3.表空间只读

03:31:30 sys@ORADB11G> alter tablespace TSPITR read only;

Tablespace altered.

只读后无法在表空间上insert update create　操作

测试如下

03:31:36 sys@ORADB11G> create user tsuser identified by ts990Q default tablespace TSPITR;

User created.

03:32:53 sys@ORADB11G> grant connect,resource to tsuser;

Grant succeeded.

03:33:26 sys@ORADB11G>

03:31:36 sys@ORADB11G> create user tsuser identified by ts990Q default tablespace TSPITR;

User created.

03:32:53 sys@ORADB11G> grant connect,resource to tsuser;

Grant succeeded.

03:33:26 sys@ORADB11G> conn tsuser/ts990Q

Connected.

03:33:52 tsuser@ORADB11G> create table test (id int,name varchar(10));

Table created.

03:34:11 tsuser@ORADB11G> insert into test values(1,'aa');

insert into test values(1,'aa')

            *

ERROR at line 1:

ORA-01647: tablespace 'TSPITR' is read-only, cannot allocate space in it

此处报错

表空间恢复正常模式

03:35:28 sys@ORADB11G> alter tablespace TSPITR read write;

Tablespace altered.

03:35:46 sys@ORADB11G> conn tsuser/ts990Q

Connected.

03:36:07 tsuser@ORADB11G> insert into test values(1,'aa');

1 row created.

03:36:11 tsuser@ORADB11G> commit;

Commit complete.

03:36:14 tsuser@ORADB11G> 

03:36:14 tsuser@ORADB11G> select * from test;

     ID NAME

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

     1 aa

03:37:02 tsuser@ORADB11G> 

 

4.扩展表空间

增加数据文件

03:38:32 sys@ORADB11G> alter tablespace TSPITR add datafile '/u01/app/oracle/oradata/tspitr02.dbf' size 20m;

Tablespace altered.

重新设置表空间数据文件大小

03:43:02 sys@ORADB11G> alter database datafile  '/u01/app/oracle/oradata/TSPITR01.dbf' resize 30m;

Database altered.

03:43:43 sys@ORADB11G>

 

-rw-r-----  1 oracle oinstall  31M Aug 15 03:43 TSPITR01.dbf

 

5.移动表空间数据文件及修改表空间名称

表空间offline

03:44:09 sys@ORADB11G> alter tablespace TSPITR offline;

Tablespace altered.

 表空间物理文件路径移动

03:48:21 sys@ORADB11G> host mv /u01/app/oracle/oradata/TSPITR01.dbf  /u01/app/oracle/oradata1/TSPITR01.dbf

表空间逻辑文件路径移动

03:48:28 sys@ORADB11G> alter tablespace TSPITR rename datafile '/u01/app/oracle/oradata/TSPITR01.dbf' to '/u01/app/oracle/oradata1/TSPITR01.dbf';

Tablespace altered.

表空间online

03:49:27 sys@ORADB11G> alter tablespace TSPITR online;

Tablespace altered.

表空间重命名

03:53:12 sys@ORADB11G> alter tablespace TSPITR rename to TSPITRS;

Tablespace altered.

03:53:36 sys@ORADB11G>

 

03:55:07 sys@ORADB11G> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

RMAN

TSPITRS

7 rows selected.

注（system,sysaux无法重命名）