oracle DBA 基础培训教程－从实践中学习oracleDBA

第一章    oracle 的体系结构

1.2 oracle 数据库中的常用术语

进程: (process)

后台进程: (background process)

缓冲区: (buffer)

主机: (host)

服务器: (server)

客户机: (client)

1.3 oracle 数据库管理系统的体系结构

1.4 oracle 服务器

   3种安装模式:

(1)              基于主机方式:

(2)              客户端-服务器两层方式:

(3)              客户端-应用服务器-服务器 三层模型

1.5 oracle实例

   instance=sga+background process

1.6 oracle数据库

   控制文件(control files),重做日这文件(redo log files),数据文件(data file)

1.7 oracle其他的关键文件

   初始化参数文件:parameeter files

   密码文件(password files)

    归档重做日志文件(archived redo log files)

1.10 服务器进程

    专用服务器进程的内存结构:

(1)              排序区(sort area):用于处理SQL语句所需的排序;

(2)              游标状态区(cursor state) 当前所使用的sql语句的处理状态

(3)              会话信息区(session information ): 会话的用户权限和优化统计信息;

(4)              堆栈区(stack space):其他的会话变量

共享服务器进程或多线程配置: 以上这些结构除了堆栈区外大部分将存在sga中,如果有 large pool,他们就会被存在large pool,否则它们就会被存放在共享池(share pool)中.

1.11 oracle执行sql查询语句的步骤:

   编译(parse),执行(execute)和提取数据(fetch)

1.18 内存缓冲区信息的获取:

  Sql>show parameter

  // 显示参数文件中可以设置的全部参数

 获取SGA的相关信息:

   Sql>show sga

 

第二章    数据库管理工具

第三章    oracle 实例的管理

3.1静态参数文件(pfile)

在windows nt 操作系统的默认路径为 $oracle_home/database

3.2动态服务器参数文件(spfile)

在windows nt 操作系统的默认路径为 $oracle_home/database

spfile 文件的创建:

create spfile [=’spfile name’] from pfile [=’pfile name’]

create spfile from pfile

//在使用默认的pfile文件创建spfile文件,保存在默认的路径下.

想要修改spfile 文件中的参数,最好的方法是使用 alter system set 命令来完成.

3.3 数据库的启动

(1) 读取spfileSID文件启动实例

(2)没有找到,则用默认的spfile启动;

(3)没有默认的则使用initsid 文件启动

(4)没有找到,则使用默认的pfile来启动

startup 启动命令的格式:

   startup [force][restrict][pfile=文件名]

             [open[recover][database]

             |mount

             |nomount]

还可以使用下面的命令来更改oracle的几种状态:

startup nomount;

alter database mount;

alter database open;

将数据库的状态置为只读:

 alter database read only;

恢复正常状态:

alter database read write;

数据库运行在限制模式:

startup restrict; 或

alter system enablerestricted session;

第四章    数据字典和控制文件

4.1 显示对象中列的定义:

  sql>desc user_objects

  sql>desc dba_objects

4.2 格式化输出结果

   sql>set wrap off  //不换行

   Sql>set linesize 1000 //行大小

   sql>col owner for a8 // owner 列 8个字符的宽度

4.3 数据字典视图

获得某个用户下的表情况:

sql>select table_name from user_tables;

sql>select * from cat;

用户可以访问到的表的情况:

sql>select table_name,owner from all_tables where owner not like ‘%sys’;

4.4动态性能表(视图)

 获取数据库上的数据字典信息:

  sql>select *

from dictionary

where table_name like ‘%table%’;

或

sql>select *

      from v$fixed_table;

了解数据字典中的某一列的含义:

     sql> select *

            from dict_columns

            where table_name=’dba_tables’

            and column_name=’initial_extent’;

4.5 获取数据库的名字,创建日期等信息,v$database;

SQL>select name,created,log_mode,open_mode

      from v$database;

4.6 计算机的主机名,实例名和数据库的版本

   SQL>select host_name,instance_name,version

         from v$instance;

    Sql>select *

          from v$version;

4.7 获取控制文件的名字:

    sql>select * from v$controlfile;

4.8 获取数据库的重做日志文件的配置信息:

    sql>select group#,members,bytes,status,archived

          from v$log;

4.9 获取重做日志(成员)文件所存放的具体位置:

    SQL>select * from v$logfile;

4.10 了解数据库的备份模式:

   sql>archive log list;

4.11 了解数据库的表空间和状态

   sql>select tablespace_name,block_size,status

,contents,logging

from dba_tablespaces;

4.12 了解表空间存在于那个磁盘上和文件的信息等:

    sql>select file_id,file_name,tablespace_name,status

,bytes

from dba_data_files;

4.13 了解oracle数据库系统上有多少用户和数据库的创建日期:

   sql>select username,created

        from dba_users;

4.14 从控制文件中获取信息的数据字典:

 v$archived,v$archived_log

v$backup;

v$database;

v$datafile;

v$log;

v$logfile;

v$loghist;

v$tablespace;

v$tempfile;

4.15 添加和移动控制文件的步骤:

   (1) 利用v$controlfile 获取现有的控制文件的名字;

   (2) 关闭数据库;

   (3)将控制文件名添加到参数文件的control_files参数中;

   (4)使用操作系统命令把现有的控制文件复制到相应的位置;

   (5)重启数据库

   (6)利用v$controlfile 来验证操作是否正确;

(7)如果有误,重做上述操作;无误,删除无用的旧的控制文件.

          如果使用了服务器初始参数文件(spfile)步骤如下:

   (1) 利用v$controlfile 获取现有的控制文件的名字;

   (2) 使用alter system set control_file命令修改控制文件的位置;

   (3) 关闭数据库;

   (4)使用操作系统命令把现有的控制文件复制到相应的位置;

   (5)重启数据库

   (6)利用v$controlfile 来验证操作是否正确;

(7)如果有误,重做上述操作;无误,删除无用的旧的控制文件.

          sql>alter system set control_files

                ‘d:/disk3/control01.ctl’,

                ‘d:/disk6/control02.ctl’,

                ‘d:/disk9/control03.ctl’ scope=spfile;

         4.16 控制文件的备份

              sql>alter database backup controlfile

to ‘d:/oracle/control.bak’;

              sql>alter database backup controlfile to trace;

         4.17 由 trace文件重建控制文件

             

第五章    重做日志文件

5.1 强制产生日志切换的命令:

   sql>alter system switch logfile;

5.2 强制产生检查点:

   sql>alter system checkpoint;

可以通过设置fast_start_mttr_target=900参数值来强制产生检查点

5.5 获取重做日志的信息

   sql>select group#,sequence#,members,bytes,status

          ,archived

          from v$log;

status列的状态值的含义:

      inactive:表示实例恢复已不再需要这组联机重做日志组了;

      active: 不是当前组,但实例恢复时需要这组联机重做日志组;

      current: 表示为当前组;

      unused : 表示oracle服务器从来没有写过改组联机重做日志组,或刚被添加到数据库中的状态;

sql>select * from v$logfile;

  status 列的状态含义:

        空白:  表示正在使用;

        stale: 该文件中的内容时不完全的;

               invalid: 该文件不可以被访问;

               deleted:文件不再有用了

         5.6 添加和删除联机重做日志文件组

             创建新的重做日志组的命令格式:

             alter database [数据库名]

                   add logfile [group 正整数] 文件名

                   [,[group 正整数] 文件名…]

           sql>alter database

add logfile (‘j:/disk3/redo04a.log’

,’j:/disk6/redo04b.log’) size 15M;

sql>select group#,sequence#,members,bytes,status

      ,archived

      from v$log;

删除重做日志组:

atler database [数据库名]

     drop logfile {group 正整数|(‘文件名’[,文件名]…)}

      [,{group 正整数|(‘文件名’[,文件名]…)}]…

sql>alter database drop group 4;

sql>select group#,sequence#,members,bytes,status

      ,archived

      from v$log;

sql>select * from logfile;

当一组重做日志文件被删除后,它的操作系统文件依然存在,需要手工删除.

5.7 添加和删除联机重做日志成员

    语法结构:

alter database [数据库名]

     add logfile member

     [‘文件名’ [reuse]

     [,’文件名’[reuse]]…

          to {group 正整数

     |(‘文件名’[,’文件名’]…)

              }

     ]…

sql>alter database add logfile member

    ‘j:/disk3/redo01b.log’ to group 1,

    ‘j:/disk3/redo02b.log’ to group 2,

    ‘j:/disk3/redo03b.log’ to group 3;

sql> select group#,sequence#,members,bytes,status

      ,archived

      from v$log;

sql>select * from v$logfile;

 删除重做日志组成员格式:

alter database [数据库名]

   drop logfile member ‘文件名’[,’文件名’]…

sql>alter database drop logfile member

     ‘j:/disk3/redo03b.log’;

注意:日志文件需要手工删除.

所谓的重做日志维护或修复就是将有问题的重做日志组或成员删除掉,之后再重建它们.

5.8 清除联机重做日志文件

   如果无法使用’将有问题的重做日志文件组或成员删除掉,之后再重建它们’,可以用以下命令重新初始化联机重做日志文件:

sql>alter database clear logfile group 组号;

如果崩溃的重做日志文件已经不能归档:

sql>alter database clear unarchived logfile group 组号;

执行了以上这两个命令后,以前的备份将无用,sequence#将变为0.

 

第六章    表空间和数据文件的管理

6.1 非系统表空间的创建语法:

   create tablespace 表空间名

           [datafile 子句] (数据文件说明)

           [minimum extent 正整数[k|M]

           [blocksize 正整数[K]]

           [logging|nologging](nologging只影响DML语句和ddl命令)

           [default 存储子句] (所有再该表中所创建的对象的默认存储参数)

           [online|offline]

           [permanent|temporary]

           [区段管理子句]

           [段管理子句]

6.2 查询表空间的管理方法:

   sql>set line 120

   sql>col tablespace_name for a15

   sql>select talbespace_name,block_size,

extent_management,segment_space_management

from dba_tablespaces;

sql>select tablespace_name,initial_extent,next_extent,

     max_extents,pct_increase,min_extlen

     from dba_tablespaces;

6.3 查询表空间有那些文件组成

    sql>select file_id,file_name,tablespace_name

          from dba_data_files

          order by file_id;

6.4 回滚段

    sql>create  undo tablespace xx_undo

          datafile ‘xx:/xx.dbf’

          size 20M

          extent management local;(段管理关键字可以省略,因为9i默认是本地管理方式)

6.5 临时表空间

   创建临时表空间时,必须使用标准数据块,还推荐使用本地管理的表空间.

sql>create temporary tablespace xx_temp

        tempfile ‘d:/xx_temp.dbf’

         size 10m

         extent management local

         uniform size 2m (设置为sort_area_size 参数的整数倍.)

6.6 设置默认临时表空间

sql>select * from database_properties

      where property_name like ‘default%’;

sql>alter database default temporary tablespace xxx_temp;

使用旧的(默认)临时表空间的用户被自动地赋予新的默认临时表空间.

       6.7 设置表空间为脱机

           维护工作:

1)     在数据库处于打开状态下移动数据文件

2)     数据库处于打开状态下恢复一个表空间或一个数据文件

3)     执行对表空间的脱机备份(虽然对表空间可以进行联机备份)

4)     使数据库的一部分不可以被访问,而其他的部分可以被正常的访问.

以下的表空间不可以设置为脱机状态:

1)     系统表空间

2)     上面有活动的还原/回滚段的表空间;

3)     默认临时表空间.

sql>select tablespace_name,status,contents

from dba_tablespaces

where tablespace_name like ‘xx%’;

            sql>alter tablespace xxx offline;

            sql>alter tablespace xxx online;

            sql>select file#,name,status

                  from v$datafile

       6.8只读(read-only)表空间

           可以删除只读表空间中的对象如表,索引.因为ddl语句只修改数据字典.

           改变状态时,该表空间必须处于联机状态.

          sql>alter tablespace xxx read only;

          sql>select tablespace_name,status,contents from

                 dba_tablespaces

                 where tablespace_name like ‘xxx%’;

         sql>alter tablespace xxx read write;

       6.9 改变表空间的存储设置

          alter tablespace 表空间名           

          [minimum extent 正整数[k|M]

                  [default 存储子句]

          sql>alter tablespace xxx

                 minimum extent 100k

                 default storate(initial 100k next 100k

maxextents 200);

6.10 重置表空间的大小

     sql>alter database datafile ‘d:/xx.dbf’ autoextend on

           next 1M;

     sql> select file_id,tablespace_name,file_name

,autoextensible

from dba_data_file

6.11 手工重置数据文件的大小

     sql>select file_id,file_name,tablespace_name,

          bytes/(1024*1024) MB

         from dba_data_files

     sql>alter database datafile ‘d:/xxx.dbf’ resize 100M;

     sql> alter tablespace xxx

            add datafile ‘d:/xxx.dbf’

            size 80M;

6.15 移动数据文件的方法

     1)移动数据文件:

       alter tablespace 表空间名

            rename datafile ‘文件名’[,’文件名’]…

            to ‘文件名’[,’文件名’]…

      只适用于上面没有活动的还原数据或临时段的非系统表空间中的数据文件.

      表空间一定为脱机状态而且没表数据文件必须存在.因为该语句只修改控制文件的指向数据文件的指针.

     步骤:

(1)              查询相关表空间和数据文件的信息

(2)              将表空间设置为脱机

(3)              复制或移动数据文件

(4)              执行alter 命令

(5)              将表空间设置为联机

(6)              检查表空间和数据文件的位置.

2)alter database [数据库名]

               rename file  ‘文件名’[,’文件名’]…

               to ‘文件名’[,’文件名’]…

语句适用于系统表空间和不能置为脱机的表空间

步骤:

(1)              查询表空间和数据文件的信息

(2)              关闭数据库

(3)              复制或移动数据文件

(4)              将数据库加载到mount状态

(5)              执行alter database命令

(6)              打开数据库

(7)              查询表空间和数据文件的信息

6.16 迁移数据字典和本地管理的表空间.

字典管理à本地管理

sql>execute dbms_space_admin.tablespace_migrate_to_local(‘表空间名’);

6.17 删除表空间

drop tablespace 表空间名

  [including contents [and datafiles] [cascade constraints]]

including contents: 用来删除段;

and datafiles 用来删除数据文件;

cascade constraints 删除所有的引用完整性约束.

第七章    存储结构和他们的关系

7.1 获取段的磁盘管理信息的数据字典

   dba_tablespaces,dba_data_files,dba_extents,dba_segments,

dba_free_space

 

第八章    管理还原数据

8.1 了解数据库的还原段的信息

sql>col name for a30

sql>col value for a50

sql>select name value from v$parameter

where name like ‘%undo%’;

undo_management=auto 参数不是动态参数.

undo_tablespace =undotbs1 是动态参数,可以使用alter syetem set命令修改:

sql>alter system set undo_tablespace=undotbs2;

8.2 还原表空间的创建:

 sql>create undo tablespace xxx_undo

       datafile ‘d:/xxx.dbf’

       size 20m;

8.3 增大表空间:

 sql>alter tablespace xxx_undo

      add datafile ‘d:/xxx.dbf’

      size 25m;

sql>select file_id,file_name,tablespace_name

,bytes/1024/1024 MB

 from dba_data_files

where tablespace_name like ‘xxx’;

8.4修改undo tables 允许的命令:

   rename,add datafile,datafile[noline|offline]

,begin backup,end backup

8.5 配置表空间某个文件可以自动扩展

   sql>alter database

        datafile ‘d:/ddd.dbf’;

        autoextend on;

  sql>select file_id,file_name,tablespace_name

,autoextensible

from dba_data_files

where tablespace_name like ‘ddd’;

8.6 还原表空间的切换

   sql>alter system set undo_tablespace=undo_xxxx;

8.7 删除表空间

   sql>drop tablespace undo_xxxx;

8.8 自动还原数据管理的一些参数:

  alter session set undo_suppress_errors=true;

    出错不提示;

sql>alter system set undo_retention=900;

   还原数据在事务提交后至少保留900秒.

8.9 获得还原数据的信息

   sql>select to_char(begin_time,’hh:mm:ss’) begin_time,

        to_char(end_time,’hh:mm:ss’) end_time,

undoblks,txncount,maxquerylen

        from v$undostat;

说明:

     begin_time:标识时间间隔的开始

     end_time:标识时间间隔的结束;

     undoblks:标识所消耗的还原数据块的总数

     txncount:标识该时间段中事务的总数;

     maxquerylen: 标识该时间段中所执行的最长的查询(按秒数)

8.10 获得还原段的信息的数据字典和视图

    v$parameter_tablespaces,dba_data_files和v$undostat

    dba_rollback_segs

    动态视图

    v$rollname ,v$rollstat,v$session,v$transaction;

 

 

第九章    创建数据库

     

第十章    管理表

     10.1 创建临时表

       sql>create global temporary table

             emp_temp

             on commit preserve rows

             as

             select *

             from emp

             where …..;

 on commit preserve rows 说明数据行在整个会话中可见.

 on commit delete rows  说明数据行在事务中可见,默认值.

     10.2 了解某用户下的索引和表

        sql>select object_id,object_name,object_type,status

,created

 from dba_objects

 where owner=’scott’;

10.3 了解索引属于那个表的

   sql>select index_name,table_name,tablespace_name,status

        from dba_indexes

        where owner=’scott’;

10.3 更改表存储的表空间

   sql>alter table scott.emp

         move tablespace users;

  sql>select segment_name,tablespace_name,extents,blocks

       from dba_segments

       where owner=’scott’;

10.3 重建表索引并更改存储的表空间

    sql>alter index scott.pk_emp rebuild

          tablespace indx;

10.4 表列的重名:

    alter table 表名

     rename column 旧名

     to 新列名;

10.5 列的删除

     alter table 表名

     drop column 列名

     cascade constraints checkpoint 行数

表比较大的时候可以用checkpoint 行数来做检查,当系统崩溃的时候可以用下面的命令来继续删除:

alter tables 表名 drop column continue;

10.6 先把列标识为无用后再删除操作

   sql>alter table 表名

      set unused 列名 cascade constraints;

   sql>alter table 表名

     drop unused columns checkpoint 行数;

   sql> alter tables 表名 drop column continue;

 

 

第十一章              索引的管理与维护

           11.1 创建索引

              create (unique|bitmap) index [用户名.] 索引名

                 on [用户名.]表名

                 (columnname[asc|desc][,columnname[asc|desc]]…)

                 [tablespace 表空间名]

                 [pctfree 正整数]

                 [initrans 正整数]

                 [initrans 正整数]

                 [maxtrans 正整数]

                 [存储子句]

                 [logging|nologging]

                 [nosort]

   关键字说明:

          unique: 说明是唯一索引

          asc : 索引为升序

          pctfree: 每个块中预留的空间

          initrans:在每一个块中预分配的事务记录数

          存储子句:说明索引中extents怎样分配

         

第十二章              管理和维护数据完整性

第十三章              用户及系统资源的安全的管理

          13.1 创建用户

           create user 用户名

            identified { by 口令|externally|globally as

external name}

[default tablespace 默认表空间名]

[temporary tablespace 表空间名]

[quota {正整数[K|M]|unlimited} on 表空间名]

[quota {正整数[K|M]|unlimited} on 表空间名]….]

[password expire]该用户在第一次登陆口令旧失效.

[account {lock|unlock}]

[profile{ 概要文件名|default}]

13.2 删除用户

  SQL>drop user 用户名 [cascade]

13.3 修改用户的默认表空间

  sql>alter user default tablespace 新表空间名

 

第十四章              管理权限

           14.1 分配权限

             sql>grant 权限列表 to 用户名

          在grant 命令中使用了with admin option 子句,被授予权限的用户可以进一步将这些系统权限授予其他的用户.

             sql>grant 权限列表 to 用户名 with admin option;

              sql>select * from dba_sys_privs where grantee=’用户名’;

           14.2 回收权限

             sql>revoke 权限列表 from 用户名