表空间的管理

1.创建临时表空间

> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/disk5/temp02.dbf' size 50m autoextend on;

2.更改默认临时表空间

> alter database default temporary tablespace temp

2;更改临时表空间大小

>ALTER TABLESPACE temp_demo ADD TEMPFILE 'temp05.dbf' SIZE 5 AUTOEXTEND ON;

3.更改表空间大小（只能增大不能减小）

两种方式

>ALTER DATABASE DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;

>ALTER TABLESPACE tbs_03 ADD DATAFILE 'tbs_f04.dbf' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 100K;

4.查看表空间的使用情况

>通过视图dba_data_files、dba_free_space能够查看

5、创建普通表空间

SQL> create tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/disk4/undotbs02.dbf' size 50m;

6、删除表空间和文件

SQL> drop tablespace undotbs2 including contents and datafiles;

7、创建undo表空间

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/disk4/undotbs02.dbf' size 50m;切换undo表空间到undotbs2上

SQL> alter system set undo_tablespace=undotbs2;

查看undo_tablespace参数的值

SQL> show parameter undo_tablespace;

NAME                                      TYPE                              VALUE

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

undo_tablespace                         string                        UNDOTBS2

删除之前的undo表空间内容及其文件

SQL> drop tablespace undotbs including contents and datafiles;

8、建一个临时表空间组，将临时表空间temptbs,temp2添加到一个组temp_grp1里SQL>CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M TABLESPACE GROUP group1;

SQL> alter tablespace temp2 tablespace group temp_grp1; SQL> alter tablespace temptbs tablespace group temp_grp1;

设置temp_grp1为临时表空间组

SQL> alter database default temporary tablespace temp_grp1;