oracle数据库维护 Oracle数据库中索引的维护

大家好,今天小编来为大家解答以下的问题，关于oracle数据库维护，Oracle数据库中索引的维护这个很多人还不知道，现在让我们一起来看看吧！

怎样启动和关闭oracle数据库

1数据库的启动（STARTUP）

在Startup命令中，可以通过不同的选项来控制数据库的不同启动步骤。

1、STARTUP NOMOUNT

NONOUNT选项仅仅创建一个Oracle实例。读取init.ora初始化参数文件、启动后台进程、初始化系统全局区（SGA）。Init.ora文件定义了实例的配置，包括内存结构的大小和启动后台进程的数量和类型等。实例名根据Oracle_SID设置，不一定要与打开的数据库名称相同。当实例打开后，系统将显示一个SGA内存结构和大小的列表，如下所示：

SQL> startup nomount

ORACLE instance started.

Total System Global Area 35431692 bytes

Fixed Size 70924 bytes

Variable Size 18505728 bytes

Database Buffers 16777216 bytes

Redo Buffers 77824 bytes

2、STARTUP MOUNT

该命令创建实例并且安装数据库，但没有打开数据库。Oracle系统读取控制文件中关于数据文件和redo log文件的内容，但并不打开这些文件。这种打开方式常在数据库维护操作时使用，如对数据文件的更名、改变redo log以及打开归档方式、执行数据库的full database recovery。在这种打开方式下，除了可以看到SGA系统列表以外，系统还会给出" Database mounted."的提示。

3、STARTUP

该命令完成创建实例、安装实例和打开数据库的所有三个步骤。此时数据库使数据文件和redo log文件在线，通常还会请求一个或者是多个回滚段。这时系统除了可以看到前面Startup Mount方式下的所有提示外，还会给出一个" Database opened."的提示。此时，数据库系统处于正常工作状态，可以接受用户请求。

如果采用STARTUP NOMOUNT或者是STARTUP MOUNT的数据库打开命令方式，必须采用ALTER DATABASE命令来执行打开数据库的操作。例如，如果你以STARTUP NOMOUNT方式打开数据库，也就是说实例已经创建，但是数据库没有安装和打开。这时必须运行下面的两条命令，数据库才能正确启动。

ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;

而如果以STARTUP MOUNT方式启动数据库，则只需要运行下面一条命令即可以打开数据库：

ALTER DATABASE OPEN;

4、其他打开方式

除了前面介绍的三种数据库打开方式选项外，还有另外其他的一些选项。

(1) STARTUP RESTRICT

这种方式下，数据库将被成功打开，但仅仅允许一些特权用户（具有DBA角色的用户）才可以使用数据库。这种方式常用来对数据库进行维护，如数据的导入/导出操作时不希望有其他用户连接到数据库操作数据、数据装载、特定的迁移或者升级操作等。

(2) STARTUP FORCE

该命令其实是强行关闭数据库（shutdown abort）和启动数据库（startup）两条命令的一个综合。该命令仅在关闭数据库遇到问题不能关闭数据库时采用。

(3) ALTER DATABASE OPEN READ ONLY;

该命令在创建实例以及安装数据库后，以只读方式打开数据库。对于那些仅仅提供查询功能的产品数据库可以采用这种方式打开。

2数据库的关闭（SHUTDOWN）

对于数据库的关闭，有四种不同的关闭选项。

1、SHUTDOWN NORMAL

这是数据库关闭SHUTDOWN命令的确省选项。也就是说如果输入SHUTDOWN这样的命令，也就是执行SHUTDOWN NORNAL命令。

发出该命令后，任何新的连接都将再不允许连接到数据库。在数据库关闭之前，Oracle将等待目前连接的所有用户都从数据库中退出后才开始关闭数据库。采用这种方式关闭数据库，在下一次启动时不需要进行任何的实例恢复。但需要注意的是，采用这种方式，也许关闭一个数据库需要几天时间，或者更长。

2、SHUTDOWN IMMEDIATE

这是常用的一种关闭数据库的方式，想很快地关闭数据库，但又想让数据库干净的关闭，常采用这种方式。

当前正在被Oracle处理的SQL语句立即中断，系统中任何没有提交的事务全部回滚。如果系统中存在一个很长的未提交的事务，采用这种方式关闭数据库也需要一段时间（该事务回滚时间）。系统不等待连接到数据库的所有用户退出系统，强行回滚当前所有的活动事务，然后断开所有的连接用户。

3、SHUTDOWN TRANSACTIONAL

该选项仅在Oracle 8i后才可以使用。该命令常用来计划关闭数据库，它使当前连接到系统且正在活动的事务执行完毕，运行该命令后，任何新的连接和事务都是不允许的。在所有活动的事务完成后，数据库将和SHUTDOWN IMMEDIATE同样的方式关闭数据库。

4、SHUTDOWN ABORT

这是关闭数据库的最后一招，也是在没有任何办法关闭数据库的情况下才不得不采用的方式，一般不要采用。如果下列情况出现时可以考虑采用这种方式关闭数据库。

1、数据库处于一种非正常工作状态，不能用shutdown normal或者shutdown immediate这样的命令关闭数据库;

2、需要立即关闭数据库；

3、在启动数据库实例时遇到问题；

所有正在运行的SQL语句都将立即中止。所有未提交的事务将不回滚。Oracle也不等待目前连接到数据库的用户退出系统。下一次启动数据库时需要进行实例恢复，因此，下一次启动可能比平时需要更多的时间。

下表为上述四种不同关闭数据库的区别和联系。

关闭方式 Abort Immediate Transaction Nornal

允许新的连接××××

等待直到当前会话中止×××√

等待直到当前事务中止××√√

强制CheckPoint，关闭所有文件×√√√

Oracle数据库密码文件的使用和维护

在Oracle数据库系统中用户如果要以特权用户身份（INTERNAL／SYSDBA／SYSOPER）登录Oracle数据库可以有两种身份验证的方法即使用与操作系统集成的身份验证或使用Oracle数据库的密码文件进行身份验证因此管理好密码文件对于控制授权用户从远端或本机登录Oracle数据库系统执行数据库管理工作具有重要的意义

Oracle数据库的密码文件存放有超级用户INTERNAL／SYS的口令及其他特权用户的用户名／口令它一般存放在ORACLE_HOME＼DATABASE目录下

一密码文件的创建

在使用Oracle Instance Manager创建一数据库实例的时侯在ORACLE_HOME＼DATABASE目录下还自动创建了一个与之对应的密码文件文件名为PWDSID ORA其中SID代表相应的Oracle数据库系统标识符此密码文件是进行初始数据库管理工作的基础在此之后管理员也可以根据需要使用工具ORAPWD EXE手工创建密码文件命令格式如下

C:＼>ORAPWDFILE=＜ FILENAME＞PASSWORD=＜ PASSWORD＞

ENTRIES=< MAX_USERS>

各命令参数的含义为

FILENAME密码文件名

PASSWORD设置INTERNAL／SYS帐号的口令

MAX_USERS密码文件中可以存放的最大用户数对应于允许以SYSDBA／SYSOPER权限登录数据库的最大用户数由于在以后的维护中若用户数超出了此限制则需要重建密码文件所以此参数可以根据需要设置得大一些

有了密码文件之后需要设置初始化参数REMOTE_LOGIN_PASSWORDFILE来控制密码文件的使用状态

二设置初始化参数REMOTE_LOGIN_PASSWORDFILE

在Oracle数据库实例的初始化参数文件中此参数控制着密码文件的使用及其状态它可以有以下几个选项 NONE指示Oracle系统不使用密码文件特权用户的登录通过操作系统进行身份验证 EXCLUSIVE指示只有一个数据库实例可以使用此密码文件只有在此设置下的密码文件可以包含有除INTERNAL／SYS以外的用户信息即允许将系统权限SYSOPER／SYSDBA授予除INTERNAL／SYS以外的其他用户 SHARED指示可有多个数据库实例可以使用此密码文件在此设置下只有INTERNAL／SYS帐号能被密码文件识别即使文件中存有其他用户的信息也不允许他们以SYSOPER／SYSDBA的权限登录此设置为缺省值

在REMOTE_LOGIN_PASSWORDFILE参数设置为EXCLUSIVE SHARED情况下 Oracle系统搜索密码文件的次序为在系统注册库中查找ORA_SID_PWFILE参数值（它为密码文件的全路径名）若未找到则查找ORA_PWFILE参数值若仍未找到则使用缺省值ORACLE_HOME＼DATABASE＼PWDSID ORA其中的SID代表相应的Oracle数据库系统标识符

三向密码文件中增加删除用户

当初始化参数REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE时系统允许除INTERNAL／SYS以外的其他用户以管理员身份从远端或本机登录到Oracle数据库系统执行数据库管理工作这些用户名必须存在于密码文件中系统才能识别他们由于不管是在创建数据库实例时自动创建的密码文件还是使用工具ORAPWD EXE手工创建的密码文件都只包含INTERNAL／SYS用户的信息为此在实际操作中可能需要向密码文件添加或删除其他用户帐号

由于仅被授予SYSOPER／SYSDBA系统权限的用户才存在于密码文件中所以当向某一用户授予或收回SYSOPER／SYSDBA系统权限时他们的帐号也将相应地被加入到密码文件或从密码文件中删除由此向密码文件中增加或删除某一用户实际上也就是对某一用户授予或收回SYSOPER／SYSDBA系统权限

要进行此项授权操作需使用SYSDBA权限（或INTERNAL帐号）连入数据库且初始化参数REMOTE_LOGIN_PASSWORDFILE的设置必须为EXCLUSIVE具体操作步骤如下创建相应的密码文件设置初始化参数REMOTE_LOGIN_PASSWORDFILE＝EXCLUSIVE使用SYSDBA权限登录 CONNECTSYS／internal_user_passsword ASSYSDBA启动数据库实例并打开数据库创建相应用户帐号对其授权（包括SYSOPER和SYSDBA）授予权限 GRANTSYSDBATOuser_name收回权限 REVOKESYSDBAFROMuser_name现在这些用户可以以管理员身份登录数据库系统了

四使用密码文件登录

有了密码文件后用户就可以使用密码文件以SYSOPER／SYSDBA权限登录Oracle数据库实例了注意初始化参数REMOTE_LOGIN_PASSWORDFILE应设置为EXCLUSIVE或SHARED任何用户以SYSOPER／SYSDBA的权限登录后将位于SYS用户的Schema之下以下为两个登录的例子

以管理员身份登录

假设用户scott已被授予SYSDBA权限则他可以使用以下命令登录

CONNECTscott／tigerASSYSDBA

以INTERNAL身份登录

CONNECTINTERNAL／INTERNAL_PASSWORD

五密码文件的维护

查看密码文件中的成员

可以通过查询视图V$PWFILE_USERS来获取拥有SYSOPER／SYSDBA系统权限的用户的信息表中SYSOPER／SYSDBA列的取值TRUE／FALSE表示此用户是否拥有相应的权限这些用户也就是相应地存在于密码文件中的成员

扩展密码文件的用户数量

当向密码文件添加的帐号数目超过创建密码文件时所定的限制（即ORAPWD EXE工具的MAX_USERS参数）时为扩展密码文件的用户数限制需重建密码文件具体步骤如下

a)查询视图V$PWFILE_USERS记录下拥有SYSOPER／SYSDBA系统权限的用户信息

b)关闭数据库

c)删除密码文件

d)用ORAPWD EXE新建一密码文件

e)将步骤a中获取的用户添加到密码文件中

修改密码文件的状态

密码文件的状态信息存放于此文件中当它被创建时它的缺省状态为SHARED可以通过改变初始化参数REMOTE_LOGIN_PASSWORDFILE的设置改变密码文件的状态当启动数据库事例时 Oracle系统从初始化参数文件中读取REMOTE_LOGIN_PASSWORDFILE参数的设置当加载数据库时系统将此参数与口令文件的状态进行比较如果不同则更新密码文件的状态若计划允许从多台客户机上启动数据库实例由于各客户机上必须有初始化参数文件所以应确保各客户机上的初始化参数文件的一致性以避免意外地改变了密码文件的状态造成数据库登陆的失败

修改密码文件的存储位置

密码文件的存放位置可以根据需要进行移动但作此修改后应相应修改系统注册库有关指向密码文件存放位置的参数或环境变量的设置

删除密码文件

Oracle数据库中索引的维护

本文只讨论Oracle中最常见的索引即是B tree索引本文中涉及的数据库版本是Oracle i

在Oracle中 SYSTEM表是安装数据库时自动建立的它包含数据库的全部数据字典存储过程包函数和触发器的定义以及系统回滚段

一般来说应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象因为这样会带来数据库维护和管理的很多问题一旦SYSTEM表损坏了只能重新生成数据库我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在

select count(*) from dba_indexes where tablespace_name= SYSTEM and owner not in( SYS SYSTEM)

Oracle为数据库中的所有数据分配逻辑结构空间数据库空间的单位是数据块（block）范围（extent）和段（segment）

Oracle数据块（block）是Oracle使用和分配的最小存储单位它是由数据库建立时设置的DB_BLOCK_SIZE决定的一旦数据库生成了数据块的大小不能改变要想改变只能重新建立数据库（在Oracle i中有一些不同不过这不在本文讨论的范围内）

Extent是由一组连续的block组成的一个或多个extent组成一个segment当一个segment中的所有空间被用完时 Oracle为它分配一个新的extent

Segment是由一个或多个extent组成的它包含某表空间中特定逻辑存储结构的所有数据一个段中的extent可以是不连续的甚至可以在不同的数据文件中

一个object只能对应于一个逻辑存储的segment我们通过查看该segment中的extent可以看出相应object的存储情况

（）查看索引段中extent的数量

select segment_name count(*) from dba_extents where segment_type= INDEX and owner=UPPER(&owner) group by segment_name/

（）查看表空间内的索引的扩展情况

select substr(segment_name) SEGMENT NAME bytes count(bytes) from dba_extents where segment_name in( select index_name from dba_indexes where tablespace_name=UPPER(&表空间)) group by segment_name bytes order by segment_name/

索引的选择性是指索引列中不同值的数目与表中记录数的比如果一个表中有条记录表索引列有个不同的值那么这个索引的选择性就是/=

一个索引的选择性越接近于这个索引的效率就越高

如果是使用基于cost的最优化优化器不应该使用选择性不好的索引如果是使用基于rule的最优化优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引）并且不得不手工优化查询以避免使用非选择性的索引

确定索引的选择性可以有两种方法手工测量和自动测量

（）手工测量索引的选择性

如果要根据一个表的两列创建两列并置索引可以用以下方法测量索引的选择性

列的选择性=不同值的数目/行的总数/*越接近越好*/

select count(distinct第一列||%||第二列)/count(*) from表名

如果我们知道其中一列索引的选择性（例如其中一列是主键）那么我们就可以知道另一列索引的选择性

手工方法的优点是在创建索引前就能评估索引的选择性

（）自动测量索引的选择性

如果分析一个表也会自动分析所有表的索引

第一为了确定一个表的确定性就要分析表

*** yze table表名 pute statistics

第二确定索引里不同关键字的数目

select distinct_keys from user_indexes where table_name=表名 and index_name=索引名

第三确定表中行的总数

select num_rows from user_tables where table_name=表名

第四索引的选择性=索引里不同关键字的数目/表中行的总数

select i distinct_keys/t num_rows from user_indexes i user_tables here i table_name=表名 and i index_name=索引名 and i table_name=t table_name

第五可以查询USER_TAB_COLUMNS以了解每个列的选择性

表中所有行在该列的不同值的数目

select column_name num_distinct from user_tab_columns where table_name=表名

列的选择性=NUM_DISTINCT/表中所有行的总数查询USER_TAB_COLUMNS有助测量每个列的选择性但它并不能精确地测量列的并置组合的选择性要想测量一组列的选择性需要采用手工方法或者根据这组列创建一个索引并重新分析表

随着数据库的使用不可避免地对基本表进行插入更新和删除这样导致叶子行在索引中被删除使该索引产生碎片插入删除越频繁的表索引碎片的程度也越高碎片的产生使访问和使用该索引的I/O成本增加碎片较高的索引必须重建以保持最佳性能

（）利用验证索引命令对索引进行验证

这将有价值的索引信息填入index_stats表

validate index用户名索引名

（）查询index_stats表以确定索引中删除的未填满的叶子行的百分比

select name del_lf_rows lf_rows round((del_lf_rows/(lf_rows+))*) Frag Percent from index_stats

（）如果索引的叶子行的碎片超过%考虑对索引进行重建

alter index用户名索引名 rebuild tablespace表空间名 storage(initial初始值 next扩展值) nologging

（）如果出于空间或其他考虑不能重建索引可以整理索引

alter index用户名索引名 coalesce

（）清除分析信息

*** yze index用户名索引名 delete statistics

（）检查需要重建的索引

根据以下几方面进行检查确定需要重建的索引

第一查看SYSTEM表空间中的用户索引

为了避免数据字典的碎片出现要尽量避免在SYSTEM表空间出现用户的表和索引

select index_name from dba_indexes where tablespace_name= SYSTEM and owner not in( SYS SYSTEM)

第二确保用户的表和索引不在同一表空间内

表和索引对象的第一个规则是把表和索引分离把表和相应的索引建立在不同的表空间中最好在不同的磁盘上这样可以避免在数据管理和查询时出现的许多I/O冲突

set linesize col OWNER format a col INDEX format a col TABLE format a col TABLESPACE format a select i owner OWNER i index_name INDEX t table_name TABLE i tablespace_name TABLESPACE from dba_indexes i dba_tables t where i owner=t owner and i table_name=t table_name and i tablespace_name=t tablespace_name and i owner not in( SYS SYSTEM)/

第三查看数据表空间里有哪些索引

用户的默认表空间应该不是SYSTEM表空间而是数据表空间在建立索引时如果不指定相应的索引表空间名那么该索引就会建立在数据表空间中这是程序员经常忽略的一个问题应该在建索引时明确的指明相应的索引表空间

col segment_name format a select owner segment_name sum(bytes) from dba_segments where tablespace_name=数据表空间名 and segment_type= INDEX group by owner segment_name/

第四查看哪个索引被扩展了超过次

随着表记录的增加相应的索引也要增加如果一个索引的next extent值设置不合理（太小）索引段的扩展变得很频繁索引的extent太多检索时的速度和效率就会降低

set linesize col owner format a col segment_name format a col tablespace_name format a select count(*) owner segment_name tablespace_name from dba_extents where segment_type= INDEX and owner not in( SYS SYSTEM) group by owner segment_name tablespace_name having count(*)> order by count(*) desc/

（）找出需要重建的索引后需要确定索引的大小以设置合理的索引存储参数

set linesize col INDEX format a col TABLESPACE format a select owner OWNER segment_name INDEX tablespace_name TABLESPACE bytes BYTES/COUNT sum(bytes) TOTAL BYTES round(sum(bytes)/(*)) TOTAL M count(bytes) TOTAL COUNT from dba_extents where segment_type= INDEX and segment_name in(索引名索引名) group by owner segment_name segment_type tablespace_name bytes order by owner segment_name/

（）确定索引表空间还有足够的剩余空间

确定要把索引重建到哪个索引表空间中要保证相应的索引表空间有足够的剩余空间

select round(bytes/(*)) free(M) from***$ts_free where tablespace_name=表空间名/

（）重建索引

重建索引时要注意以下几点

a如果不指定tablespace名索引将建在用户的默认表空间 b如果不指定nologging将会写日志导致速度变慢由于索引的重建没有恢复的必要所以可以不写日志 c如果出现资源忙表明有进程正在使用该索引等待一会再提交

alter index索引名 rebuild tablespace索引表空间名 storage(initial初始值 next扩展值) nologging/

（）检查索引

对重建好的索引进行检查

select* from dba_extents where segment_name=索引名

（）根据索引进行查询检查索引是否有效

使用相应的where条件进行查询确保使用该索引看看使用索引后的效果如何

select* from dba_ind_columns where index_name like表名%

然后根据相应的索引项进行查询

select* from表名% where

（）找出有碎片的表空间并收集其碎片

重建索引后原有的索引被删除这样会造成表空间的碎片

select alter tablespace||tablespace_name|| coalesce; from dba_free_space_coalesced where percent_blocks_coalesced!=/

整理表空间的碎片

alter tablespace表空间名 coalesce

文章到此结束，如果本次分享的oracle数据库维护和Oracle数据库中索引的维护的问题解决了您的问题，那么我们由衷的感到高兴！