Oracle笔记(九)-白红宇

Oracle笔记(九)

阅读量：6878 次

发布时间：2019-06-26

本文共 12752 字，大约阅读时间需要 42 分钟。

[第六章　Oracle权限与角色管理]

一、Oracle 权限管理

SQL> grant connect, resource, dba to acc01;

SQL> revoke connect, resource from acc01;

二、Oracle 角色管理（参考讲义）

SQL> Create Role <role_name>

Identified by password/ Not Identified;

SQL> Alter Role <role_name> ...

SQL> Grant <privs> to <role_name>;

SQL> Grant <role_name> to <user_name>

SQL> Set Role <role_name>

All Except <role_name2> / None // 使角色生效或失效

[第七章　Oracle 数据库备份与恢复]

一、Oracle归档方式

确认数据库归档方式：

SVRMGR> archive log list;

SQL> select log_mode from v$database;

二、Oracle归档方式配置步骤：

1、启动自动归档进程ARCH

log_archive_start=true

2、确定归档日志文件存储目录

log_archive_dest=目录名

3、确定归档日志文件存储格式

log_archive_format=%t_%s.arc

4、确定归档日志文件镜像目录：

log_archive_duplex_dest=镜像目录名

5、以MOUNT方式启动数据库，修改数据库归档方式：

SVRMGR> connect internal

SVRMGR> startup mount

SVRMGR> alter database archivelog;

SVRMGR> alter database open;

// 前面1、2、3、4四个参数都在参数文件中。

[*]在Oracle8i以后，可以使用多个镜像目录：[8i最多是5个，9i是10个，与duplex方式不能混用]

log_archive_dest_1='location=d:/oracle/archive1'

log_archive_dest_2='location=d:/oracle/archive2'

log_archive_dest_3='location=d:/oracle/archive3'

[*]归档文件的格式为二进制，可以没有后缀。

实际操作：

1、在d:/oracle目录下建立archive1, archive2两个目录

2、修改init.ora文件，增加下面四行：

log_archive_start=true

log_archive_format=%t_%s.arc

log_archive_dest=d:/oracle/archive1

log_archive_duplex_dest=d:/oracle/archive2

3、进入svrmgrl

SVRMGR> connect internal/oracle

SVRMGR> shutdown immediate

SVRMGR> startup mount

SVRMGR> alter database archivelog;

SVRMGR> alter database open;

4、进入SQL*Plus

SQL> alter system switch logfile;

//检查d:/oracle/archive1及archive2目录下是否有归档的日志文件。

[*]在线启动或关闭归档方式

SQL> Alter system archive log start;

SQL> Alter system archive log stop;

三、数据库物理备份

[*]为了最大限度地进行恢复，应选择最合理的备份方法来防止介质失败导致的数据丢失。

(1)非归档的操作系统备份

(2)归档的操作系统备份

(3)Export工具备份

注释:

。用归档的操作系统备份的方法来恢复到介质失败后的失败点。

。用其它的恢复方法恢复到介质失败后的最后一次备份点。

。用Export工具创建一个直接的数据库备份文件，它不能与归档log文件组合使用。

1、完全数据库脱机备份：可以在归档及非归档两种模式下使用。

操作步骤：

(1)编写一个要备份的最新的文件列表。

(2)用SHUTDOWN命令关闭Oracle例程。

(3)用操作系统的备份工具，备份所有的数据文件、Redo Log文件、控制文件、参数文件。

(4)重启Oracle例程。

2、部分数据库联机备份：

备份步骤如下：

(1) 确认数据库运行在ARCHIVELOG下。

(2) 对于某一个表空间进行备份：

SQL> Alter tablespace 表空间名 begin backup;

(3) 操作系统备份相应数据文件

(4) 设置表空间备份结束标识：

SQL> Alter tablespace 表空间名 end backup;

恢复数据文件：

SVRMGR> Alter database RECOVER datafile '数据库文件名及路径';

<=等价=>

SVRMGR> RECOVER datafile '数据库文件名及路径';

3、部分数据库脱机备份：

备份步骤如下：

(1) 确认数据库运行在ARCHIVELOG下。

(2)将某一个表空间脱机：

SQL> Alter tablespace 表空间名 offline;

(3) 操作系统备份相应数据文件

(4) 将表空间联机：

SQL> Alter tablespace 表空间名 online;

恢复时也需要进行恢复数据文件：

SVRMGR> Alter database RECOVER datafile '数据库文件名及路径';

<=等价=>

SVRMGR> RECOVER datafile '数据库文件名及路径';

四、完全数据库恢复应用实例

[*]完全数据库恢复的语法：

　　在数据库关闭时，数据库恢复语法(可以Mount):

SVRMGR> Alter database recover datafile 'filespec';

SVRMGR> Alter database recover database;

SVRMGR> Recover datafile 'filespec';

SVRMGR> recover database;

第1、3及第2、4条命令分别等价。

　　在数据库运行时，数据库恢复语法(OPEN):

SVRMGR> recover tablespace 表空间名;

SVRMGR>recover datafile 'filespec';

1、数据库文件被删除，数据库关闭时，数据库恢复方法：

A、复制数据文件的备份文件

B、以mount方式启动数据库

C、恢复数据文件

SVRMGR> alter database recover datafile '数据文件名';

D、打开数据库

SVRMGR> alter database open;

2、数据库在运行时，数据文件被删除的数据库恢复方法：

A、将数据文件设置为offline:

SVRMGR> alter database datafile '数据文件名' offline;

B、复制数据文件的备份文件

C、恢复数据文件

SVRMGR> alter database recover datafile '数据文件名';

D、将数据文件设置为online:

SVRMGR> alter database datafile '数据文件名' online;

3、数据文件被删除，该数据文件没有备份，数据库恢复方法：

A、以mount方式启动数据库

B、运行以下命令：

SVRMGR> Alter database create datafile '被删除数据文件名及路径'

as '被删除数据文件名及路径';

C、恢复数据文件

SVRMGR> alter database recover datafile '数据文件名';

D、打开数据库

SVRMGR> alter database open;

五、不完全数据库恢复实例：

1、恢复一个被Dropped的基表：

A、关闭数据库

B、拷贝全部的数据文件(不拷贝日志及控制文件)

注：这些拷贝的数据文件是以前做的数据文件的备份，现在拷贝回数据库数据文件所在目录，覆盖掉原来的。当然，最好在覆盖之前将现在的数据文件及归档日志做好备份。

C、实施基于时间的不完全数据库恢复

SVRMGR> Alter database recover database until time '2002-05-17:9:00:00';

D、打开数据库

SVRMGR> Alter database open RESETLOGS;

// 执行了resetlogs之后，文件同步号将从0开始，以前所有的归档日志全部失效。

2、恢复一个被Dropped的表空间：

A、查询跟踪文件，确定表空间删除时间

D:/oracle/admin/db_name/bdump/sidAlrt.log

B、关闭数据库，复制未删除表空间前的全部数据文件及控制文件，不复制日志文件。

C、实施基于时间的数据库恢复：

SVRMGR> Alter database recover database until time '2002-05-17:9:00:00' using backup controlfile;

D、打开数据库

SVRMGR> Alter database open RESETLOGS;

六、数据库逻辑备份：(Export/Import)

1、exp user01/user01 file=user01 // 用户方式：将user01用户的所有数据导出到user01.dmp文件中

2、exp user01/user01 file=user01_table tables=(emp,dept, pay, ...) // 表方式，将指定用户的指定表导出。

3、exp // 交互方式

4、卸出（导出）大表：

exp user/password file=filename direct=y ...

// 加上direct=y，表明用直接路径卸出数据，不通过内存，直接卸出到磁盘，效率高，节省内存。

4、全部数据库卸出：

exp system/manager file=fulldb full=y direct=y

// 加上full=y，表示将整个数据库全部卸出。

用户方式和表方式可以在NT 与 UNIX之间的Oracle进行数据传输，但整个数据库方式不能，因为包含了相关的数据文件信息。

七、卸出表空间：

1、检验表空间自包含性：检查表空间是不是与其它表空间有外键约束，有外键约束的不能卸出。

SQL> execute ...

2、将表空间设为只读

3、使用export卸出表空间

exp transport_tablespace=y tablespaces=mis file=expdat.dmp

4、使用Import装入表空间

imp transport_tablespace=y datafile='d:/oracle/oradata/x.dbf' tablespaces=mis file=expdat.dmp

八、Windows 计划任务备份数据文件

1、C:/> net start schedule //启动计划服务

net stop schedule //停止计划服务

2、AT命令安排计划：

C:/>at 22:00 /every:m,t,w,th, f, s, su d:/fullback.bat // 每周一到周日的晚上十点运行d:/fullback.bat批处理文件

C:/>at /delete /yes // 删除全部计划

3、fullback.bat

svrmgrl @d:/fullback.sql

4、fullback.sql

connect internal/oracle

shutdown immediate

host copy d:/oracle/oradata/ora8i/*.* e:/back

startup

还可以为数据库在线备份建立脚本通过AT调用执行，实现数据库的联机备份。

[第八章　Oracle 数据库的优化与调整]

一、下面三个参数属于SGA区的设置，主要看服务器内存是不是需要扩充。

1、数据缓冲区计算:(DB_BLOCK_BUFFERS)

SQL> select 1 - (phy.value /(blk.value+con.value))

" Hit Ratio"

from v$sysstat phy, v$sysstat blk, v$sysstat con

where phy.name = 'physical reads' and

blk.name = 'db block gets' and

con.name = 'consistent gets'

Hit Ratio最好是大于90%,需要在数据库在最繁忙的时候的命中率，否则需要增加内存。

2、日志缓冲区计算(log_buffer)

v$latch

sum(misses) 越接近于零越好。

3、共享池大小计算(shared_pool_size)

(1) v$librarycach: PINS(命中），Reloads(失败）

Reloads/(pins + reloads) * 100 -> 失败率 -> 应小于1%，否则需要扩内存。

(2) v$rowcache: GETS(取到), GETMISSES（失败）

二、排序区参数计算(SORT_AREA_SIZE):v$sysstat

sorts(disk) / sorts(memory)+sorts(disk) 失败率 < 10%

三、磁盘存储参数测算：

计算以下存储参数：

initial

minextents

maxextents

pctincrease

如果使用本地化管理，不需要计算以上参数。

四、数据库参数自动统计计算

1、修改参数文件：timed_statistics=true，重启数据库

2、在数据库最繁忙的时段运行下面两个SQL程序（在Oracle目录中搜索这两个文件）

SVRMGR>@d:/oracle/ora81/rdbms/admin/utlbstat.sql

SVRMGR>@d:/oracle/ora81/rdbms/admin/utlestat.sql

3、查询d:/report.txt来看统计结果。

五、回退段优化设计

1、对于大事务，设计大回退段，提高数据操作速度。

步骤如下：

A、创建一个专用的回退段表空间。例如：rb100

B、在回退段表空间rbs100上创建大的回退段。

SQL> create rollback segment rbs100

tablespace rbs100

storage(initial 50m);

C、使回退段表空间online

SQL> Alter rollback segment rbs100 online;

D、指定用户使用专用的回退段表空间：

SQL> set transaction use rollback segment rbs100; // 必须在事务开始前指定

E、事务结束后，删除回退段及表空间。

教师操作实例：

System用户操作：

SQL> create tablespace rbs1 datafile 'd:/oracle/oradata/ora8i/rbs1_01.dbf' size 10m

SQL> create rollback segment rbs100 tablespace rbs1;

SQL> alter rollback segment rbs100 online;

SCOTT用户操作：

CREATE table bb(s1 char(20));

insert into bb values('abcdefghi');

commit; // 必须在事务开始之前设置回退段

set transaction use rollback segment rbs100;

system用户操作：

alter rollback segment rbs100 offline;

drop rollback segment rbs100

drop tablespace rbs1;

2、回退段I/O调整，具体步骤：

A、创建多个回退段表空间，对就不同物理磁盘，以平衡磁盘I/O

B、在多个表空间上分别创建回退段。例如：

rbs1:rbs01, rbs02, rbs03, rbs04, rbs05

rbs2: rbs06, rbs07, rbs08, rbs09, rbs10

C、修改参数文件，使回退段交叉排放。例如：

rollback_segments=(rbs01,rbs06,rbs02,rbs07,rbs03,rbs08,...)

[Oracle Developer/2000 系统开发工具]

简介：

Form: 开发基于Form应用系统，是Developer/2000的主要开发工具。

Report: 报表开发工具，开发设计各种报表。

Graphics:图形设计工具，开发基于数据库的图形界面

Form 包括以下三个工具：

。Form Builder　设计与开发组件

。Form compiler 编译组件

。Form Runtime 运行组件

[Form Builder 设计]

一、创建一个简单的Form应用

表在Form Builder中称为数据块。

在新建的Form中的表格字段中可以输入查询条件，按F8按条件查询。

另外，可以在字段中输入变量，例如:在emp表中的sal(工资)字段输入变量:x，在执行查询时，输入:x> 2000 and :x < 3000，则表示查询工资在2000及3000之间的人员。如果要查询1981年的人员，则在date字段输入:x，条件为：To_Char(:x, 'yyyy) = '1981'

查询最大工资：:x = (select max(sal) from emp)

二、创建主从型Form应用：

1、使用主表创建主块

2、使用子表创建子块

3、建立主表与子表之间的关联

三、布局设计(Layout)

四、域属性表(Properties): 双击某字段即可调出属性表

格式掩码

五、创建文本项（Text Item):

文本项：用于显示数据库的统计结果

名称

数据类型

格式掩码

计算模式

汇总函数：总和

数据块：emp

数据项：fullsal

公式：水平计算

汇总：垂直计算

工资及奖金合计：:sal + nvl(:comm,0)

数据－＞用项同步化

记录－＞显示的项数：0 -> 1

六、创建值列表Lov（List of Value):

七、创建按钮(Push Button):

1、创建文本类型按钮：

数据查询，条件查询，工资总和，报表打印、退出系统

2、创建图标类型按钮：

（1）常用缺省图标：

save:数据提交

rt_rdel:数据删除

rt_radd: 数据插入

rt_rec4: >>

rt_rec3: >

rt_rec2: <

rt_rec1: <<

（2）更改按钮属性

图标化：是

图标文件名：<Icon File path>

鼠标导航：否

显示的项数：1

(3) 增加按钮提示

保存

删除

插入数据

第一条记录

下一条记录

上一条记录

最后一条记录

八、创建显示项(Display Item):

显示项：只读数据项，用于显示数据库的统计结果。

九、创建单选按钮(Radio Button):

(1) 增加了单选按钮后，先到“对象导航器”中修改“Radio Group”的属性

名称：

鼠标导航：否

数据类型：数值

初始值：10

财务部(10)、销售部(20)、开发部(30)、网络中心（40）

示// 数据－＞需要：如果选择“是”，则表明数据为非空，不允许为空，如果不在表中输入查询条件，则不允许移动光标焦点，应选“否”。

十、复选框(Check Box)

增加一个复选框，修改其属性：

名称：

标签文本：

复选时的值：y

未复选时的值：n

鼠标导航：否

显示的项数：1

数据库项：否

[Trigger 触发器设计]

一、按钮触发器设计：

触发器类型：WEHN-BUTTON-PRESSED

触发器代码：使用PL/SQL及函数编写程序代码。

Execute_Query; // 执行查询的函数，这是写在相应按钮上的PL/SQL执行语句的脚本

编译，关闭，执行。

Enter_Query; // 条件查询函数

Exit_Form; // 退出系统函数

常用的对于数据库操作的函数：

Create_Record; // 数据插入

Delete_Record; // 数据删除

Commit_Form; // 数据提交

Execute_Query; // 无条件数据查询

Enter_Query; // 条件查询

Next_Record; // 下一条记录

Previous_Record; // 上一条记录

First_Record; // 第一条记录

Last_Record; // 最后一条记录

Scroll_Up; // 上滚一屏

Scroll_Down; // 下滚一屏

Exit_Form; // 退出系统

这些函数名不区分大小写。

如果在执行查询时提示Oracle错误，不能查询，有可能就是因为Form中一个与数据库字段无关的对象的“数据库项”设置成了“是”，将其改成“否”即可。

工资总和按钮的WHEN-BUTTON-Pressed的pl/sql脚本：select sum(sal) into :Display_sumsal from emp;

二、创建Form级触发器完成数据库的自动查询与统计：

A. 在Form启动时，进行自动查询

触发器名称：WHEN-NEW-FROM-INSTANCE

对象导航器->表格->Form1->触发器->新增

脚本内容：Execute_Query;

B. 在Form启动时，进行自动统计计算

触发器名称：POST-QUERY

对象导航器->表格->Form1->触发器->新增

select sum(sal) into :Display_sumsal from emp;

C. 显示动态时间：

触发器名称：POST-QUERY

自动显示统计时间：yyyy年mm月DD日

增加一个显示项：DISPLAYTIME

数据类型：字符

立体：无

修改POST-QUERYR的脚本，增加一行：

:DISPLAYTIME := To_Char(sysdate, 'yyyy"年"mm"月"dd"日"'); --// 显示的是客户端时间

select To_Char(sysdate, 'yyyy"年"mm"月"dd"日"') into :DISPLAYTIME from dual; --// 显示的是服务器端时间

[C*]在编辑触发器脚本中汉字不能正确显示的解决办法：

先退出FormBuilder。然后：

regedit -> HKLM-> Software -> Oracle -> 新建字串de20_plain_edit，键值为1。

再重新进入FormBuilder即可解决所有的汉字显示问题。

D. Form启动时，MDI窗口最大化及标题设置，同样在FORM的POST-QUERY触发器中设置：

SET_Window_Property(FORMS_MDI_WINDOW, WINDOW_STATE, maximize);

Set_Window_property(FORMS_MDI_WINDOW, TITLE, '公司员工工资情况一览表');

设置MDI子窗口Window1:

SET_Window_Property('window1', WINDOW_STATE, maximize);

Set_Window_property('window1', TITLE, '员工管理信息系统 Version 200205 Build 1');

[*] 进入Form1模块的属性设置-> 菜单模块 -> default&smartbar ，default表示菜单，smartbar表示工具栏，去掉就表示没有这些东西。

[*] Window1属性-> 模式 -> 是。意味着这是mdi窗口中的最后一个窗口。还可以取消最大最小化等按钮，实现mdi子窗口看上去与主窗口好象一个窗口。

三、单选按钮触发器：

触发器类型：WHEN-RADIO-CHANGED

脚本：

Declare

string varchar2(50);

v_dname dept.dname%type;

Begin

select sum(sal), count(*) into :Display_item2, :Display_item3 from emp

where deptno=:select_deptno;

select dname into v_dname from dept where deptno = :select_deptno;

string := v_dname || '部门统计结果';

Message(string);

Exception

when NO_DATA_FOUND THEN

Message('数据库中没有编码为'||:select_deptno||'的部门。');

End;

四、时间触发器(Timer):

1、创建计时器，确定时间间隔：

触发器名称：WHEN-NEW-FORM-INSTANCE

增加脚本：

Declare

timer_id timer;

Begin

timer_id := Create_Timer('T1', 1000, REPEAT);

End;

-- T1:计时器名称（大写）；1000:时间间隔（毫秒）；REPEAT:重复

2、创建时间触发器的执行操作：

触发器名称：WHEN-TIMER-EXPIRED

Declare

t_name varchar2(30);

Begin

t_name := Get_Application_Property(TIMER_NAME);

if t_name = 'T1' then Begin

--显示Server时间

select To_Char(Sysdate,'yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"')

into :DisplayTime from dual;

--显示本机时间

:DisplayTime :=To_Char(Sysdate,'yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"') ;

--select To_Char(sysdate, 'yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒") into :DisplayTime from dual;

End;

End if;

End;

五、复选按钮触发器：

触发器类型：WHEN-CHECKBOX-CHANGED

脚本：

Begin

if :ifprint='y' then

Set_Item_Property('print_report', ENABLED, PROPERTY_TRUE); --// 使打印按钮生效

ELSE

Set_Item_Property('print_report', ENABLED, PROPERTY_FALSE); --// 使打印按钮无效

end if;

end;

六、菜单设计

在c:/orawin95目录下查找menudef.mmb，然后打开该文件，双击MENUDEF编辑原来的菜单（删除不要的，添加需要的），然后另存为自己的菜单menu1.mmb，然后编译(文件－＞管理－＞编译文件CTRL+T）。。然后在Form1的属性设置中，将菜单模块直接设置成菜单文件的名称及路径，例如：e:/xyf/menu1

七、基于数据库的图象字段存储：

1、在数据库中创建一个含有图象字段的基表。

SQL>Create table employee as select * from emp;

SQL> alter table employee add(photo long raw); --//加上long raw字段后，该表就不能再用select * 去查询

2、对图象文件进行唯一性编码

SQL> Spool e:/empno

SQL> select empno from emp;

SQL> Spool off;

生成e:/empno.lst文件，存储了所有雇员的empno列表作为图象文件的唯一性编码

如：7788.jpg...

3、创建Form，编写触发器调用图象文件:photo.fmb

photo属性－＞大小风格 -> 调整