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无法重命名）