oracle基本管理（2）

创建表空间：
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
表空间管理分为两类：
本地管理的表空间：在表空间内管理空闲区，使用位图来记录空闲区，每一位与一个块或一组块相对应，位的数值指明是空闲还是已占用
字典管理的表空间：由数据字典管理空闲区，在分配或回收区时更新对应的表。
本地管理表空间有以下优点：
减少了对数据字典表的争用，分配或回收空间时不生成还原数据，无需合并。
下面的脚本建立本地管理表空间：
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
字典管理表空间的特征：
在数据字典中管理区，存储在表空间中的每个段都可以有不同的存储子句，需要合并。
下面的脚本建立字典管理表空间：
CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf'
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(initial 1M NEXT 1M PCTINCREASE 0);

修改temp表空间为默认临时表空间：ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
查询 DATABASE_PROPERTIES 以确定数据库的缺省临时表空间：
SELECT * FROM DATABASE_PROPERTIES;
不能对缺省临时表空间执行下列操作：
将其删除，除非已经有新的缺省临时表空间，使其脱机，更改为永久表空间。
使用以下命令可将表空间置于只读模式：
ALTER TABLESPACE userdata READ ONLY;
可以对表空间进行脱机操作，在脱机之后，无法访问表空间的数据。
不能设为脱机的表空间：SYSTEM 表空间，具有活动的还原段的表空间，缺省临时表空间。
使用以下命令可使表空间脱机：
ALTER TABLESPACE userdata OFFLINE;
使用以下命令可使表空间联机：
ALTER TABLESPACE userdata ONLINE;
使用 ALTER TABLESPACE 命令更改存储设置：
ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);
不能更改在本地管理的表空间的存储设置。
查询 DBA_DATA_FILES 视图以确定是否启用了AUTOEXTEND。
使用下面的办法手动调整表空间的大小：
使用 ALTER DATABASE 可手动增加或减少数据文件的大小，调整数据文件大小可在无需添加更多数据文件的情况下添加更多空间，手动调整数据文件大小将回收数据库中的未用空间。
示例：ALTER DATABASE DATAFILE '/u03/oradata/userdata02.dbf' RESIZE 200M;
也可以通过向表空间添加数据文件的方法调整数据库大小：
通过添加其它数据文件来增加分配给表空间的空间，通过ADD DATAFILE 子句可添加数据文件。示例：
ALTER TABLESPACE user_data ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M;
使用ALTER TABLESPACE子句来移动数据文件，必须满足两个条件：表空间必须脱机，目标数据文件必须存在。
移动数据文件的步骤如下：
1、使表空间脱机。
2、使用操作系统命令移动或复制文件。
3、执行ALTER TABLESPACE RENAME DATAFILE 命令。
4、使表空间联机。
5、必要时使用操作系统命令删除该文件。
命令如下：
ALTER DATABASE
ALTER TABLESPACE userdata RENAME DATAFILE '/u01/oradata/userdata01.dbf' TO '/u02/oradata/userdata01.dbf';
如果使用ALTER DATABASE子句来一动数据文件，需要满足下面两个条件：数据库必须已装载，目标数据文件必须存在。命令如下：
ALTER DATABASE RENAME FILE '/u01/oradata/system01.dbf' TO '/u03/oradata/system01.dbf';
删除表空间的限制：
不能删除SYSTEM 表空间，不能删除具有活动段的表空间。
INCLUDING CONTENTS 将删除段；
INCLUDING CONTENTS AND DATAFILES 将删除数据文件；
CASCADE CONSTRAINTS 将删除所有引用完整性约束；
例如：
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;

可以使用如下视图获取表空间信息：
DBA_TABLESPACES、V$TABLESPACE。
获取数据文件信息：
DBA_DATA_FILES、V$DATAFILE。
获取临时文件信息：
DBA_TEMP_FILES、V$TEMPFILE
为执行 DML 的事务处理请求读一致性，方法是在事务处理开始发出下列命令： SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT = 'AUTO' scope =spfile;
SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS;
自动还原管理需要一个 UNDO 表空间。数据库中可能有多个UNDO 表空间，但只能有一个UNDO表空间处于活动状态。
在表空间 UNDOTBS 内的所有事务处理都已完成后删除它。要确定是否存在任何一个活动的事务处理，请使用以下查询：
/*SQL> SELECT a.name，b.status
FROM v$rollname a， v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS‘ ) AND a.usn = b.usn;*/
状态为 PENDING OFFLINE 的某个还原段仍包含活动的事务处理。如果查询没有返回任何行，则表明所有事务处理均已完成，并且可以使用以下命令删除该表空间。
SQL> DROP TABLESPACE UNDOTBS;

自动还原管理还有下面的两个参数：
UNDO_SUPPRESS_ERRORS 参数：
使用 UNDO_SUPPRESS_ERRORS，用户可以避免在自动还原管理模式下执行手动还原管理模式操作（例如，ALTER ROLLBACK SEGMENT ONLINE）时出现错误。
通过设置这个参数，用户可以在将所有应用程序和脚本转换成自动还原管理模式前使用还原表空间功能。
例如，如果有一个使用SET TRANSACTION USE ROLLBACK SEGMENT 语句的应用程序，则可以向该应用程序添加语句ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true 以避免ORA-30019 错误。
SQL> ALTER SYSTEM SET UNDO_RETENTION=900;
V$UNDOSTAT 视图显示了统计数据的频率分布，以说明数据库的运行状况。视图中的每一行保留的是每隔10 分钟就在例程中收集一次的统计数据。
您可以使用此视图估算当前工作量所需的还原空间量。Oracle 服务器使用此视图优化系统中对还原空间的使用。
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
/*SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
2 FROM (SELECT value AS UR
3 FROM v$parameter
4 WHERE name = 'undo_retention')，
5 (SELECT (SUM(undoblks)/SUM(((end_time -begin_time)*86400))) AS UPS
6 FROM v$undostat)，
7 (SELECT value AS DBS
8 FROM v$parameter
9 WHERE name = 'db_block_size');*/
为了获得最佳结果，应该在一天中数据库负载最繁重的时候进行计算。
还原限额
使用资源计划，可以对用户进行分组，并对某个组可使用的资源数量加以限制。
可以对某个组生成的还原数据量进行限制，方法是设置UNDO_POOL 的值：缺省值是无限大，UNDO_POOL 是“资源管理器” (Resource Manager) 指令，用于为资源组定义可用的空间量。
如果某个组超过了它的限制，就会收到一个错误，同时这个组不能进行新的事务处理，直到当前事务处理完成或中止。
ORA-30027：“超出还原限额— 无法获取%s（字节）”
原因：已超出为此会话的使用者组分配的还原空间量。
操作：请求 DBA 增加还原限额，或者等待其它事务处理提交后再继续执行。

从数据字典获取还原段信息
使用如下数据字典、视图获取还原段信息：
DBA_ROLLBACK_SEGS
V$ROLLSTAT
V$ROLLNAME 视图
V$TRANSACTION 和 V$SESSION 视图
要获取有关数据库中所有还原段的信息，请查询 DBA_ROLLBACK_SEGS 视图：
SQL> SELECT segment_name，owner，tablespace_name，status FROM dba_rollback_segs;
有关脱机的还原段的信息只能在 DBA_ROLLBACK_SEGS 中看到。动态性能视图只显示联机还原段。
联接 V$ROLLSTAT 和V$ROLLNAME 视图以获取例程当前使用的还原段的统计数据。示例：
SQL> SELECT n.name， s.extents， s.rssize，s.hwmsize，
2 s.xacts， s.status
3 FROM v$rollname n， v$rollstat s
4 WHERE n.usn = s.usn;
要检查活动事务处理当前使用的还原段的情况，请联接V$TRANSACTION 和V$SESSION 视图。示例：
SQL> SELECT s.username， t.xidusn， t.ubafil，
2 t.ubablk， t.used_ublk
3 FROM v$session s， v$transaction t
4 WHERE s.saddr = t.ses_addr;

行头：用来存储行中的列数、链接信息和行锁定状态
行数据：对于每一列，Oracle 服务器存储列的长度和值（如果该列不超过250 个字节，则需要一个字节来存储列长度；如果该列超过250 个字节，则需要三个字节来存储列长度。
列值在紧靠列长度字节后面存储。） 相邻的行之间不需要任何空格。块中的每一行在行目录中都有一个位置。目录位置指向行首。
CREATE TABLE 命令用于创建关系表或对象表。
关系表：这是存储用户数据的基本结构。
对象表：是一种将对象类型用于列定义的表。对象表是一种显式定义的表，用来存储特定类型的对象例程。
MINEXTENTS：这是要分配的最小区数。
MAXEXTENTS：这是要分配的最大区数。如果将MINEXTENTS 指定为一个大于1 的值，而表空间包含多个数据文件，则这些区将分布在不同的数据文件中。
PCTINCREASE：这是NEXT 区及以后的区有关区大小增长的百分比。
PCTFREE：指定表内每个数据块中空间的百分比。PCTFREE 的值必须介于 0 和99 之间。如果值为零，表示可以通过插入新行来填充整个块。缺省值为10。此值表示每个块中保留着10% 的空间，用于更新现有的行以及插入新行，每个块最多可填充到90%。
PCTUSED：指定为表内每个数据块维护的已用空间的最小百分比。如果一个块的已用空间低于PCTUSED，则可在该块中插入行。PCTUSED 的值为介于0 和99 之间的整数，缺省值为40。
结合 PCTFREE 和PCTUSED 就可以确定将新行插入到现有数据块中，还是插入到新块中。这两个参数值的和必须小于或等于100。使用这两个参数可以更有效地利用表内的空间。Oracle9i “自动段空间管理” 功能可替代PCTUSED、FREELISTS 和FREELIST GROUPS。
INITRANS：在分配给表的每个数据块内，指定分配的初始事务处理项数。此值的范围在1 到255 之间，缺省值为1 个INITRANS：确保最小数量的并发事务处理可以更新该块。通常，应该保留此值的缺省值。
MAXTRANS：指定可以更新分配给表的数据块的最大并发事务处理数量。此限制不适用于查询。值的范围在1 到255 之间，缺省值由数据块大小的函数确定。
TABLESPACE 子句指定将要在其中创建表的表空间。示例中的表将驻留在数据表空间内。如果省略TABLESPACE，则Oracle 在包含该表的方案的所有者的缺省表空间中创建对象。

将一个区分配给一个表：
ALTER TABLE [schema.]table
ALLOCATE EXTENT [ ([SIZE integer [K|M]]
[ DATAFILE ‘filename’ ]) ]
如果忽略 SIZE，Oracle 服务器将使用DBA_TABLES 中的NEXT_EXTENT 大小来分配区。
在DATAFILE 子句中指定的文件必须属于该表所属的表空间。
否则，该语句就会生成错误。如果未使用DATAFILE 子句，则Oracle 服务器将在包含该表的表空间中的一个文件中分配区。手动分配区不会影响DBA_TABLES 中的NEXT_EXTENT 的值。
执行此命令时，Oracle 服务器不会重新计算下一个区的大小。

重新组织非分区表时，将保留表的结构，但不保留表的内容。用于将表移到另一个表空间中或者重新组织区。
不必运行导出或导入实用程序即可移动非分区表。此外，它允许更改存储参数。该特性在以下情况下很实用：
将表从一个表空间移到另一表空间，重新组织表以避免行移植，移动表后，必须重建索引以避免发生以下错误：
删除一个表后，该表所使用的区将得以释放。如果这些区是相邻的，则可以在以后某个时间自动或手动将它们合并。
如果该表是外键关系中的父表，就必须使用CASCADE CONSTRAINTS 选项

删除列对于大表将是一个耗时很久的操作，可以先将列标记为删除，到空闲时再真正删除列。
直接删除列
使用 UNUSED 选项
删除列的限制
下面的语句可恢复中断的删除操作：ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

如果想删除同一表中的两列，则可先将列设置为“未使用” 然后再删除。
在删除两列时，表中的所有行都会更新两次；但如果将这些列设置为“未使用” 然后再删除，则所有的行仅更新一次。
将列标记为未使用：ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
删除未使用的列：
ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;
继续执行删除列操作：
ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;
要确定包含未使用列的表，可以查询视图DBA_UNUSED_COL_TABS。该查询可获取包含未使用列的表的名称及表中标记为未使用列的数目。
要确定已完成一部分DROP COLUMN 操作的表，可查询DBA_PARTIAL_DROP_TABS视图。

不能执行下列操作：
从对象类型表中删除列、从嵌套表中删除列、删除一个表中的所有列、删除分区键列、从SYS 拥有的表中删除列、从按索引组织的表中删除主键列、
如果有未使用但未删除的LONG 或LONG RAW 列，将无法向表中添加LONG 或LONG RAW 列。（即使表的说明显示没有LONG 或LONG RAW 列也是如此。）

B树索引：
结构与数据结构中的B-树类似
适合查找少量的行
位图索引
适合有大量重复值的列
用于低基数列时，位图索引比B 树索引更紧凑。由于位图使用位图段级锁定，所以位图索引中的键列的更新成本较高；而在B 树索引中，锁位于与表中单个行相对应的项上。
位图索引可用于执行位图布尔等操作。Oracle 服务器可以使用两个位图段执行逐位布尔操作并得到一个结果位图。这将允许在使用布尔谓词的查询中更有效地使用位图。
总之，B 树索引更适合索引动态表的OLTP 环境，而位图索引更适合在大型静态表上使用复杂查询的数据仓库环境。

初始化参数 CREATE_BITMAP_AREA_SIZE 决定了内存中用于存储位图段的空间量。缺省值为8 MB。
使用较大的值，可提高索引创建的速度。如果基数很小，可将该值设置为一个较小值。
例如，如果基数仅为2，则该值可以为千字节数量级而非兆字节数量级。一般来讲，基数越大，则获取最佳性能所需的内存越多。

ALTER INDEX ...REBUILD 命令不能用于将位图索引更改为B 树索引，反之亦然。只能为 B 树索引指定REVERSE 或NOREVERSE 关键字。
限制：
不能在临时表中重建索引
不能重建整个分区索引。必须分别重建每个分区或子分区。
也不能回收未用空间。
不能整个更改索引的 PCTFREE 参数值。
例如：ALTER INDEX orders_id_idx REBUILD ONLINE;
从索引中手动回收空间：
使用 ALTER INDEX 命令的DEALLOCATE 子句释放索引中超过高水位标记的未用空间。
当建立索引的表被截断时，回收索引空间。截断表将导致截断关联的索引。

分析索引以执行以下操作： 检查所有的索引块是否存在损坏。注意，此命令并不验证索引项是否与表中的数据对应。使用索引的有关信息填充INDEX_STATS 视图。
语法：ANALYZE INDEX [ schema.]index VALIDATE STRUCTURE
运行此命令后，查询 INDEX_STATS 以获取索引的有关信息
要开始监视索引的使用，请执行以下语句：
ALTER INDEX hr.dept_id_idx MONITORING USAGE
在V$OBJECT_USAGE 中收集和显示有关索引使用的统计信息。如果收集的信息表明索引从未使用过，则删除该索引。
此外，删除未用索引还可减少Oracle 服务器用于DML 操作的开销，从而改善了性能。每次指定MONITORING USAGE 子句时，将对指定的索引重置V$OBJECT_USAGE。以前的信息被清除或重置，并记录新的开始时间。
V$OBJECT_USAGE 列
INDEX_NAME：索引名
TABLE_NAME：对应的表
MONITORING：指示监视是ON 还是OFF
USED：指示YES 或NO，即在监视时间内是否使用了索引
START_MONITORING：索引监视的开始时间
END_MONITORING：索引监视的结束时间

下列情况应删除索引：
应用程序不再需要索引时，可将索引删除。
执行批量加载前，索引可能已删除。在大量加载数据前，先删除索引，加载后再重新创建索引，这样做的好处有：提高加载性能、更有效地使用索引空间
仅定期使用的索引无需不必要的维护，尤其在基于易失表时更是如此。这是 OLTP 系统中的通常情况，在该系统中，年末或季度末会生成特殊的查询，以收集在总结会上使用的信息。
当在某种类型的操作（如加载）期间出现例程失败时，可能会将索引标记为INVALID。在这种情况下，需要删除并重建索引。
索引已损坏。
不能删除约束所需的索引，因此，必须先禁用或删除相关的约束。删除索引的例子：
DROP INDEX hr.deptartments_name_idx;
通过如下三个视图获取索引信息：
DBA_INDEXES：提供有关索引的信息；
DBA_IND_COLUMNS：提供有关索引列的信息；
V$OBJECT_USAGE：提供有关索引使用情况的信息。