表空间.txt

本人在工程上收集的比较常用的oracle sql语句 有问题请发email:[e-mail]

/*  表空间和数据文件  */

多个表空间的优势:
1.能够将数据字典与用户数据分离出来，避免由于字典对象和用户对象保存在同一个数据文件中而产生的I/O冲突
2.能够将回退数据与用户数据分离出来，避免由于硬盘损坏而导致永久性的数据丢失
3.能够将表空间的数据文件分散保存到不同的硬盘上，平均分布物理I/O操作
4.能够将某个表空间设置为脱机状态或联机状态，以便对数据库的一部分进行备份和恢复
5.能够将某个表空间设置为只读状态，从而将数据库的一部分设置为只读状态
6.能够为某种特殊用途专门设置一个表空间，比如临时表空间等，以优化表空间的使用效率
7.能够更佳灵活的为用户设置表空间限额

SYSTEM表空间内存储：
1.数据库的数据字典
2.所有PL/SQL程序的源代码和解析代码
3.数据库对象的定义

所有表空间的数据文件总和不能超过创建数据库时指定的maxdatafiles参数的限制

本地管理表空间:
1.在存储分配过程中不需要访问数据库，可以提高存储分配操作的速度
2.能够避免在表空间的存储管理操作中产生的递归现象
3.不会产生重做和撤销记录
4.简化DBA对表空间的管理操作
5.降低用户对数据字典的依赖性

字典管理方式的表空间:
表空间中所有存储空间的管理信息都保存在数据字典中，在进行存储空间管理时会产生回退和重做记录

本地管理方式的表空间:
表空间中所有存储空间的管理信息都保存在数据文件头部的位图中

在9i中创建表空间默认就是本地管理方式.8i以前的版本，只能用字典管理方式的表空间.

/* 创建表空间 */
创建表空间(字典管理方式)
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs.dbf' size 50M
extent management dictionary;

多个数据文件(字典管理方式)
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs01.dbf' size 50M,
  'i:\oracle\oradata\dmusertbs02.dbf' size 50M,
  'i:\oracle\oradata\dmusertbs03.dbf' size 50M
extent management dictionary;

指定默认存储参数(字典管理方式)
create tablespace dmusertbs
datafile 'i:\oracle\oradata\dmusertbs.dbf' size 50M
default storage(
 initial 256K
 next 256K
 minextents 2
 pctincrease 0
 maxextents 4096)
minimum extent 128K
logging
online
permanent
extent management dictionary;

创建表空间(本地管理方式)

1.create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local autoallocate;
  (oracle对区的分配进行自动管理,这是默认的设置,在autoallocate方式下, 表空间中最小的区为64K)

2.create tablespace lmusertbs
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local uniform size 512K;
  (所有的区必须具有统一的大小,Uniform方式带来的最大优点是在表空间中不会产生任何存储碎片,
   如果在uniform关键字后面没有指定size参数的值,SIZE参数将使用1MB做为默认值)

3.create tablespace lmusertbs 
  datafile 'i:\oracle\oradata\lmusertbs.dbf' size 50M
  extent management local uniform size 512K
  segment space management auto;
  (具有自动段存储管理方式的表空间)

创建临时表空间(字典管理方式)
一个临时表空间可以被多个数据库用户共享, oracle只会为一个实例创建一个临时段,这个临时段被实例中所有的排序操作共享使用，
但是临时段中的每一个区只能由一个事务使用.临时段在数据库启动后执行第一次排序操作时被创建.

注意：字典管理方式的临时表空间可以象普通表空间那样使用alter tablespace来进行更改
 建议将initial和next参数设置成相同的值,并且应当是初始化参数sort_area_size和db_block_size
 两个值之和的整数倍,pctincrease参数应当设置成0
create tablespace usertemp
datafile 'i:\oracle\oradata\sort01.dbf' size 50M
extent management dictionary
default storage(
  initial 192K
  next 192K
  minextents 1
  pctincrease 0)
temporary;

创建临时表空间(本地管理方式,区的分配管理方式只能是uniform)
在Oracle 9i中，强烈建议使用本地管理方式的临时表空间来代替字典管理方式的临时表空间

create temporary tablespace lmtemp
 tempfile 'i:\oracle\oradata\lmtemp01.dbf' size 50M
 extent management local uniform size 136K
 (必须使用tempfile子句,tempfile为临时数据文件,与普通数据文件相比,临时数据文件不能用alter database来创建)

对于本地管理方式的临时表空间,alter tablespace语句的作用仅仅是为临时表空间添加新的临时文件.
增加新的临时文件
alter tablespace lmtemp
 add tempfile 'i:\oracle\oradata\lmtemp02.dbf' size 50M
(对于本地管理方式的表空间,alter tablespace语句的作用仅仅是为临时表空间添加新的临时文件,而不能对临时表空间进行其它任何修改)

/* 管理表空间 */

表空间的管理主要包括修改默认存储参数，手工合并碎片，设置表空间的可用性等几个方面.
尽量让表空间使用较少的数据文件，因为在某些操作系统中对一个进程可以同时打开的系统文件数量进行限制，由此会影响到同时处于联机状态的表空间数量。

修改表空间的默认存储参数
alter tablespace duusertbs
default storage(
  next 128K
  maxextents unlimited
  pctincrease 20);
(表空间创建之后不能对initial和minextents参数进行修改)

如果表空间的默认存储参数pctincrease不为0,oracle将定期启动SMON后台进程，合并表空间相邻的存储碎片,
如果pctincrease是0,SMON进程不会执行合并操作.
如果表空间中所有的区都具有相同的大小，则不需要手工合并存储碎片.

本地管理方式的表空间不需要进行存储碎片的合并操作，因为oracle会自动利用位图自动合并.
只需要在字典管理方式的表空间中手工合并存储碎片.

alter tablespace dmusertbs coalesce
(如果使用了coalesce子句，则不能在使用其它任何子句)

利用下面的查询可以看到表空间users中存在哪些存储碎片
SQL> select block_id,bytes,blocks
  2  from dba_free_space
  3  where tablespace_name='users'
  4  order by block_id;

改变表空间的可用性:

1.alter tablespace user01 offline normal(正常方式)
oracle会执行一次检查点，将SGA区中与该表空间相关的脏缓存块都写入数据文件中，然后再关闭表空间对应的数据文件,下一次将表空间
恢复为联机状态时,不需要进行数据库恢复.

2.alter tablespace user01 offline temporary(临时方式)
oracle执行检查点的时候并不会检查各个数据文件的状态，即使某些数据文件不可用，oracle也会忽视这些错误,下一次将表空间
恢复为联机状态时,可能需要进行数据库恢复.如果数据文件可用,oracle会将与该表空间相关的脏缓存块都写入数据文件中.

3.alter tablespace user01 offline immediate(立即方式)
oracle不会执行检查点，也不会检查各个数据文件的状态，而是直接将属于表空间的数据文件设置成脱机状态,下一次将表空间
恢复为联机状态时,需要进行数据库恢复.运行在noarchivelog方式下的数据库不允许用这种方式切换到脱机状态.

恢复表空间为联机状态
alter tablespace user01 online;

设置表空间为只读状态
alter tablespace user01 read only

设置表空间为读写状态
alter tablespace user01 read write

删除表空间(不包括对应的数据文件)
drop tablespace users including contents;

删除表空间(包括对应的数据文件)
drop tablespace users including contents and datafiles;

表空间数据字典
v$tablespace 控制文件中获取的表空间的名称和编号信息
v$datafile 控制文件中获取的数据文件的名称和编号信息
v$tempfile 所有临时数据文件的基本信息
v$sort_segment 实例所创建的排序区的信息