一、基本概念：
表空间数据库逻辑结构的一部分。物理上讲，数据库数据时存放在数据文件里；从逻辑上说，数据库数据存放在表空间（Tablespace）里。表空间由一个或多个数据文件组成。
数据库的逻辑结构：
二、官方文档写的很详细：
■ Guidelines for Managing Tablespaces
■ Creating Tablespaces
■ Specifying Nonstandard Block Sizes for Tablespaces
■ Controlling the Writing of Redo Records
■ Altering Tablespace Availability
■ Using Read-Only Tablespaces
■ Renaming Tablespaces
■ Dropping Tablespaces
■ Managing the SYSAUX Tablespace
■ Diagnosing and Repairing Locally Managed Tablespace Problems
■ Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
■ Transporting Tablespaces Between Databases
■ Viewing Tablespace Information
1）Guidelines for Managing Tablespaces
表空间的管理原则：1、使用多个表空间。2、对用户分配配额
1） Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database
operations. When a database has multiple tablespaces, you can:
■ Separate user data from data dictionary data to reduce I/O contention.
独立用户数据，从数据字典方面来减少i/o
■ Separate data of one application from the data of another to prevent multiple
applications from being affected if a tablespace must be taken offline.
其实还是独立用户数据，一个应用程序使用多个表空间，其中一个表空间脱机，不影响其他数据。
■ Store different the datafiles of different tablespaces on different disk drives to
reduce I/O contention.
存储数据文件到不同的磁盘，还是减少i/o
■ Take individual tablespaces offline while others remain online, providing better
overall availability.
一个表空间脱机，其他表空间在线，保持数据库的高可用性
■ Optimizing tablespace use by reserving a tablespace for a particular type of
database use, such as high update activity, read-only activity, or temporary
segment storage.
通过保留使用特定类型的表空间来优化数据库，例如，更新操作，设置为只读表空间，临时表空间等
■ Back up individual tablespaces.
可以独立备份单个表空间
2) Assigning Tablespace Quotas to Users
Grant to users who will be creating tables, clusters, materialized views, indexes, and
other objects the privilege to create the object and a quota(space allowance or limit) in
the tablespace intended to hold the object segment.
就是授权，各种权限和配额。
2） Creating Tablespaces
1）Oracle内部表空间介绍
Before you can create a tablespace, you must create a database to contain it. Theprimary tablespace in any database is theSYSTEMtablespace, which contains
information basic to the functioning of the database server, such as the data dictionary
and the system rollback segment. The SYSTEM tablespace is the first tablespace created
at database creation. It is managed as any other tablespace, but requires a higher level
of privilege and is restricted in some ways. For example, you cannot rename or drop
the SYSTEMtablespace or take it offline.
关于system表空间的一些特征
System表空间是主表空间，system表空间包含很多数据库运行相关信息，比如数据字典和回滚段；创建数据库时候第一个创建的表空间；管理其他表空间，他和别的表空间不一样，不能被重命名和删除，还有离线。
The SYSAUXtablespace, which acts as an auxiliary tablespace to theSYSTEM
tablespace, is also always created when you create a database. It contains information
about and the schemas used by various Oracle products and features, so that those
products do not require their own tablespaces. As for theSYSTEMtablespace,
management of the SYSAUXtablespace requires a higher level of security and you
cannot rename or drop it. The management of theSYSAUXtablespace is discussed
separately in "Managing the SYSAUX Tablespace"on page 8-20.
关于sysaux的一些特征
SYSAUX作为system的辅助表空间，也是创建数据库是创建的。他包含多种Oracle产品和特性所需要的模式和相关信息，那么这些相关的产品和特性就不需要有属于自己的表空间（在没有sysaux之前，这些信息写入system表空间），管理sysaux需要更高的安全级别，而且也不能重命名和删除。
The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles
will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace
by adding datafiles. Whether you are creating a new tablespace or modifying anexisting one, the database automatically allocates and formats the datafiles asspecified.To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATEEMPORARY TABLESPACE. You must have the CREATE TABLESPACE systemprivilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER
DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE
or ALTER DATABASE system privilege, correspondingly.
You can also use the CREATE UNDO TABLESPACE statement to create a special type of
tablespace called an undo tablespace, which is specifically designed to contain undo
records. These are records generated by the database that are used to roll back, or
undo, changes to the database for recovery, read consistency, or as requested by a
ROLLBACK statement. Creating and managing undo tablespaces is the subject of
Chapter 10, "Managing the Undo Tablespace".
下面讲讨论这些内容：
The creation and maintenance of permanent and temporary tablespaces are discussed
in the following sections:
■ Locally Managed Tablespaces （本地管理表空间）
■ Bigfile Tablespaces （大文件表空间
■ Temporary Tablespaces （临时表空间）
■ Multiple Temporary Tablespaces: Using Tablespace Groups（多个表空间，使用表空间组）
Locally Managed Tablespaces
oracle 现在的表空间管理方式分为本地管理表空间和字典管理表空间，在oracle 8后，oracle推出了全新的表空间管理方式：本地管理表空间，所谓本地管理，就是oracle不再利用数据字典来记录oracle表空间里面的使用状况，而是在每个表空间的数据文件的头部加入一个位图区，在其中记录每个区的使用状况。每当一个区被使用，或者被释放重新使用，oracle都会更新数据文件的头部这个记录，来记录反映这个变化（默认的非系统SYSTEM永久表空间是本地管理的）
本地管理（local Managed)时Oracle 10g 的默认表空间管理方法，Oracle使用位图管理。
■Fast, concurrent space operations. Space allocations and deallocations modify
locally managed resources (bitmaps stored in header files).
■ Enhanced performance
■ Readable standby databases are allowed, because locally managed temporary
tablespaces do not generate any undo or redo.
■ Space allocation is simplified, because when the AUTOALLOCATE clause is
specified, the database automatically selects the appropriate extent size.
■ User reliance on the data dictionary is reduced, because the necessary information
is stored in file headers and bitmap blocks.
■ Coalescing free extents is unnecessary for locally managed tablespaces
All tablespaces, including the SYSTEM tablespace, can be locally managed.
The DBMS_SPACE_ADMIN package provides maintenance procedures for locally
managed tablespaces
9i数据字典管理对比
数据字典空间管理 dictionary managed tablespace
通过管理两个主要的数据字典表，UET$(Used EXtends)和FET$(Free EXtends)来实现
在9i以后已淘汰
缺点：1并发访问争用
2 产生大量redo undo
3 空间碎片
Creating a Locally Managed Tablespace
这里官方文档写的太多，占用篇幅。
数据文件头部加入位图区域
extent management local
就是两种分配方式
Autoallocate---------------这是有oracle自动分配
uniform -------------------- uniform
创建表空间
create [temporary] tablespace 表空间名
datafile | tempfile ‘路径’
size 大小
extent management local | dictionary ←此处说明该表空间是本地管理还是字典管理
uniform size ***** | autoallocate ←如果选择AUTOALLOCATE，则表明让oracle来决定区块的使用方法；如果选择UNIFORM则还可以详细指定每个区块的大小，若不指定则为每个区使用1M大小（例如uniform size 128K，则在盘区位图中，每个128K的（如果该表空间块的大小为4K，等于32个ORACLE块）由一位（bit）来表示）
segment space management auto | manual ;←如何管理在一个段中管理空闲的和已用的空间。MANUAL，默认值，使用段中的空闲列表管理空闲空间；传统方法。AUTO使用位图来管理段中的空闲空间，更简单、更有效。
实验
1）UNIFORM使用相同的区尺寸管理表空间，区默认尺寸为1M
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/products/oradata/wolf/system01.dbf
/oracle/products/oradata/wolf/sysaux01.dbf
/oracle/products/oradata/wolf/undotbs01.dbf
/oracle/products/oradata/wolf/users01.dbf
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> create tablespace wolf datafile '/oracle/products/oradata/wolf/wolf01.dbf'size 10m uniform size 128k;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
WOLF
6 rows selected.
2)使用AUTOALLOCATE 选项用于指定区尺寸有系统自动分配
SQL> create tablespace laolang
2 datafile '/oracle/products/oradata/wolf/laolang01.dbf' size 10m autoallocate;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
WOLF
LAOLANG
7 rows selected.
SQL> create tablespace cuug
2 datafile '/oracle/products/oradata/wolf/cuug01.dbf' size 10m
3 extent management local uniform size 128k;
Tablespace created.
SQL> create tablespace mryu
2 datafile '/oracle/products/oradata/wolf/mryu01.dbf' size 10m
3 extent management local uniform size 128k;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
WOLF
LAOLANG
CUUG
MRYU
9 rows selected.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024 "extent",NEXT_EXTENT/1024,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces;
TABLESPACE_NAME extent NEXT_EXTENT/1024 EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------------- ---------- ---------
SYSTEM 64 LOCAL SYSTEM
SYSAUX 64 LOCAL SYSTEM
UNDOTBS1 64 LOCAL SYSTEM
TEMP 1024 1024 LOCAL UNIFORM
USERS 64 LOCAL SYSTEM
WOLF 128 128 LOCAL UNIFORM
LAOLANG 64 LOCAL SYSTEM
CUUG 128 128 LOCAL UNIFORM
MRYU 128 128 LOCAL UNIFORM
SQL> drop tablespace cuug including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
WOLF
LAOLANG
MRYU
8 rows selected.
SQL> create tablespace cuug datafile '/oracle/products/oradata/wolf/cuug01.dbf' size 10m extent management local autoallocate;
Tablespace created.
SQL> select tablespace_name,initial_extent/1024 "extent",next_extent/1024,extent_management,allocation_type from dba_tablespaces;
TABLESPACE_NAME extent NEXT_EXTENT/1024 EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------------- ---------- ---------
SYSTEM 64 LOCAL SYSTEM
SYSAUX 64 LOCAL SYSTEM
UNDOTBS1 64 LOCAL SYSTEM
TEMP 1024 1024 LOCAL UNIFORM
USERS 64 LOCAL SYSTEM
WOLF 128 128 LOCAL UNIFORM
LAOLANG 64 LOCAL SYSTEM
CUUG 64 LOCAL SYSTEM
MRYU 128 128 LOCAL UNIFORM
9 rows selected.
实从上面的结果可以看出来 oracle 9i后创建表空间，如果不指定管理方式，默认就是local管理
下面我们试试替换undo表空间，并删除
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
WOLF
LAOLANG
CUUG
MRYU
9 rows selected.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/products/oradata/wolf/system01.dbf
/oracle/products/oradata/wolf/sysaux01.dbf
/oracle/products/oradata/wolf/undotbs01.dbf
/oracle/products/oradata/wolf/users01.dbf
/oracle/products/oradata/wolf/wolf01.dbf
/oracle/products/oradata/wolf/laolang01.dbf
/oracle/products/oradata/wolf/cuug01.dbf
/oracle/products/oradata/wolf/mryu01.dbf
8 rows selected.
SQL> create undo tablespace undo01 datafile '/oracle/products/oradata/wolf/undo01.dbf' size 10m autoextend on next 10m maxsize 100m;
Tablespace created.
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select TABLESPACE_NAME,INITIAL_EXTENT/1024 "extent",NEXT_EXTENT/1024,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces;
TABLESPACE_NAME extent NEXT_EXTENT/1024 EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------------- ---------- ---------
SYSTEM 64 LOCAL SYSTEM
SYSAUX 64 LOCAL SYSTEM
UNDOTBS1 64 LOCAL SYSTEM
TEMP 1024 1024 LOCAL UNIFORM
USERS 64 LOCAL SYSTEM
WOLF 128 128 LOCAL UNIFORM
LAOLANG 64 LOCAL SYSTEM
CUUG 64 LOCAL SYSTEM
MRYU 128 128 LOCAL UNIFORM
UNDO01 64 LOCAL SYSTEM
SQL> drop tablespace undotbs1
2 ;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> alter system set undo_tablespace=undo01;
System altered.
SQL> show parameter uno
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO01
SQL> drop tablespace undotbs1;
Tablespace dropped.
修改表空间大小或者是添加数据文件（这里的修改，是指空间不够了的办法）
1）增加数据文件
SQL> alter tablespace users
2 add datafile '/oracle/products/oradata/wolf/users02.dbf' size 10m
3 autoextend on next 10m maxsize 200m;
Tablespace altered.
SQL> select file_name,file_id,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files;
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 4 5 YES
data/wolf/users01.db
f
/oracle/products/ora 3 10 YES
data/wolf/users02.db
f
/oracle/products/ora 2 540 YES
data/wolf/sysaux01.d
bf
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 1 680 YES
data/wolf/system01.d
bf
/oracle/products/ora 5 10 NO
data/wolf/wolf01.dbf
/oracle/products/ora 6 10 NO
data/wolf/laolang01.
dbf
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 7 10 NO
data/wolf/cuug01.dbf
/oracle/products/ora 8 10 NO
data/wolf/mryu01.dbf
/oracle/products/ora 9 10 YES
data/wolf/undo01.dbf
2）直接增大数据文件
SQL> alter database datafile 4 autoextend on next 10m maxsize 10m;
Database altered.
SQL> select file_name,file_id,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files;
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 4 5 YES
data/wolf/users01.db
f
/oracle/products/ora 3 10 YES
data/wolf/users02.db
f
/oracle/products/ora 2 540 YES
data/wolf/sysaux01.d
bf
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 1 680 YES
data/wolf/system01.d
bf
/oracle/products/ora 5 10 NO
data/wolf/wolf01.dbf
/oracle/products/ora 6 10 NO
data/wolf/laolang01.
dbf
FILE_NAME FILE_ID BYTES/1024/1024 AUT
-------------------- ---------- --------------- ---
/oracle/products/ora 7 10 NO
data/wolf/cuug01.dbf
/oracle/products/ora 8 10 NO
data/wolf/mryu01.dbf
/oracle/products/ora 9 10 YES
data/wolf/undo01.dbf
9 rows selected.
SQL>
扫一扫
1027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
