【数据库管理】Oracle 11g管理表空间

一 表空间数据字典视图

下面的数据字典和动态性能视图提供了关于表空间的信息：

• v$tablespace / v$encrypted_tablespaces；
• v$datafile / v$tempfile；
• v$temp_extent_map / v$temp_extent_pool；
• v$temp_space_header / v$tempseg_usage；
• v$sort_segment / v$sort_usage；
• dba_tablespaces / user_tablespaces；
• dba_segments / dba_extents；
• dba_free_space / dba_temp_free_space；
• dba_data_files / dba_temp_files；
• dba_users / dba_users；

二 表空间操作

1、创建表空间（不设置区、段管理方式）

SQL> set linesize 200
SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/orcl/test1_01.dbf' size 50M;

Tablespace created.

SQL> col tablespace_name for a30
SQL> col contents for a15
SQL> col extent_management for a15
SQL> col allocation_type for a15
SQL> col segment_space_management for a15
SQL> select tablespace_name,block_size,contents,extent_management,allocation_type,segment_space_management
  2  from dba_tablespaces
  3  where tablespace_name = 'TEST1';
TABLESPACE_NAME 	       BLOCK_SIZE CONTENTS	  EXTENT_MANAGEME ALLOCATION_TYPE SEGMENT_SPACE_M
------------------------------ ---------- --------------- --------------- --------------- ---------------
TEST1				     8192 PERMANENT	  LOCAL 	  SYSTEM	  AUTO

2、创建表空间（设置区、段管理方式）

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/orcl/test2_01.dbf'
  2  size 50m 
  3  extent management local
  4  segment space management auto;

Tablespace created.

SQL> select tablespace_name,block_size,contents,extent_management,allocation_type,segment_space_management
  2  from dba_tablespaces
  3  where tablespace_name = 'TEST2';

TABLESPACE_NAME 	       BLOCK_SIZE CONTENTS	  EXTENT_MANAGEME ALLOCATION_TYPE SEGMENT_SPACE_M
------------------------------ ---------- --------------- --------------- --------------- ---------------
TEST2				     8192 PERMANENT	  LOCAL 	  SYSTEM	  AUTO

3、将表空间离线

SQL> alter tablespace test2 offline;

Tablespace altered.

SQL> select tablespace_name,status,contents,extent_management,allocation_type,segment_space_management
  2  from dba_tablespaces
  3  where tablespace_name = 'TEST2';

TABLESPACE_NAME 	       STATUS	 CONTENTS	 EXTENT_MANAGEME ALLOCATION_TYPE SEGMENT_SPACE_M
------------------------------ --------- --------------- --------------- --------------- ---------------
TEST2			       OFFLINE	 PERMANENT	 LOCAL		 SYSTEM 	 AUTO

4、将表空间在线

SQL> alter tablespace test2 online;

Tablespace altered.

5、修改表空间的读写属性

SQL> alter tablespace test2 read only;

Tablespace altered.
SQL>  select tablespace_name,status,contents,extent_management,allocation_type,segment_space_management
  2   from dba_tablespaces
  3    where tablespace_name = 'TEST2';

TABLESPACE_NAME 	       STATUS	 CONTENTS	 EXTENT_MANAGEME ALLOCATION_TYPE SEGMENT_SPACE_M
------------------------------ --------- --------------- --------------- --------------- ---------------
TEST2			       READ ONLY PERMANENT	 LOCAL		 SYSTEM 	 AUTO

SQL> alter tablespace test2 read write;

Tablespace altered.

6、增加数据文件

SQL> alter tablespace test2 add datafile '/u01/app/oracle/oradata/orcl/test2_02.dbf' size 50M ;

Tablespace altered.
SQL> alter tablespace test2 add datafile '/u01/app/oracle/oradata/orcl/test2_03.dbf' size 50M
  2  autoextend on
  3  next 512K
  4  maxsize 100M;

Tablespace altered.
SQL> col file_name for a60
SQL> col tablespace_name for a10
SQL> select file_name,tablespace_name,bytes,blocks,autoextensible 
  2  from dba_data_files
  3  where tablespace_name='TEST2';

FILE_NAME						     TABLESPACE      BYTES     BLOCKS AUT
------------------------------------------------------------ ---------- ---------- ---------- ---
/u01/app/oracle/oradata/orcl/test2_01.dbf		     TEST2	  52428800	 6400 NO
/u01/app/oracle/oradata/orcl/test2_02.dbf		     TEST2	  52428800	 6400 NO
/u01/app/oracle/oradata/orcl/test2_03.dbf		     TEST2	  52428800	 6400 YES

7、重命名表空间

SQL> alter tablespace test2 rename to test_2;

Tablespace altered.

8、删除表空间

SQL> drop tablespace test_2 including contents;

Tablespace dropped.
SQL> drop tablespace test2 including contents and datafiles;

Tablespace dropped.
SQL> host ls -l /u01/app/oracle/oradata/orcl/
total 2025216
-rw-r-----. 1 oracle oinstall   9748480 Apr 29 23:21 control01.ctl
-rw-r-----. 1 oracle oinstall 363077632 Apr 29 21:15 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Apr 29 21:08 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Apr 29 21:10 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Apr 29 23:21 redo03.log
-rw-r-----. 1 oracle oinstall 576724992 Apr 29 23:20 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 796925952 Apr 29 23:21 system01.dbf
-rw-r-----. 1 oracle oinstall  30416896 Apr 29 23:19 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Apr 29 22:15 test1_01.dbf
-rw-r-----. 1 oracle oinstall 110108672 Apr 29 23:21 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Apr 29 21:15 users01.dbf

9、创建临时表空间

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp1_01.dbf' size 50M;

Tablespace created.

10、修改默认临时表空间

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> col property_name for a40
SQL> col property_value for a15
SQL> col description for a50
SQL> select property_name,property_value ,description
  2  from database_properties t
  3  where t.property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME				 PROPERTY_VALUE  DESCRIPTION
---------------------------------------- --------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE 		 TEMP1		 Name of default temporary tablespace

11、增加临时数据文件

SQL> alter tablespace temp1 add tempfile '/u01/app/oracle/oradata/orcl/temp1_02.dbf' size 30M     
  2  autoextend on next 10M ;

Tablespace altered.

12、将临时表空间离线/在线

SQL> alter tablespace temp1 tempfile offline;

Tablespace altered.
SQL> alter tablespace temp1 tempfile online;

Tablespace altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp1_01.dbf' offline;

Database altered.

13、修改临时文件大小

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp1_02.dbf' resize 50M;

Database altered.

14、删除临时表空间

SQL> drop tablespace temp1 including  contents;

Tablespace dropped.
SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.