一. 数据库基础部分
1、Oracle9i数据库系统结构与产品组成
67% Oralce
/
Internet
\
9% sql server
中大型网站： Unix + Oracle + jsp
小型网站： NT + sql server + asp
50% Oralce
/
DBMS -- 17% DB2(针对IBM机器)
\
5% sql server
用户数 不会影响并发数，没有限制 ，只有企业版才有用户数概念。
会话数
进程数 操作系统的进程数应该是Oracle进程数的两倍
登陆sql*plus
用户：/as sysdba
查询数据库选件产品：
sql > select * from v$option;
sql > set linesize 1000 --设置行宽
sql > set pagesize 100 --设置页宽
sql > Clear scr --清屏
sql > / --重复执行sql
sql > show parameter processes 进程数
1）关于Oracle9i数据库的数据库管理员用户
Sys: 在Oracle9i9.0.1之前的数据库中，默认口令字为：change_on_install; 在9.2.0中无默认口令字
System: 在Oracle9i9.0.1之前的数据库中，默认口令字为manager;在9.2.0中无默认口令字
安装结束后，可以更改：
SQL>grant connect to system identified by "1234";
SQL>password --需要原口令
Sys用户登陆数据库方法：
$sqlplus "/as sysdba" --sys用户无口令字要求（缺省）
$sqlplus "sys/xx@cc as sysdba" --sys用户有口令要求
操作系统认证 (进入oracle不许要口令字)只限于本地（8i/9i/10g）
sys用户口令认证方式： /
\
oracle认证 (7i/8)
unix: oinstall (oracle8i/9i)
dba
修改sys用户口令字的方法：
A 修改认证方法。
D:\oracle\ora92\network\admin\sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (NTS)
unix 中要加上这一行。
NTS - 操作系统认证
none -oracle 认证
B 修改口令字
c:\>orapwd file=d:\oracle\oracle\roa92\database\pwdSID.ora password=abcd entries=30 --Windows
$orapwd file=/u01/app/oracle/product/9.2.0/dbs orapwSID password=abcd entries=30 --Unix
C 重新启动oracle服务（Windows）
重新启动数据库（Unix）
tkprof:跟踪文件整理命令，将跟踪文件转换为文本文件
语法：
D:\>tkprof 跟踪文件 文本文件
跟踪文件存贮目录：
D:\>oracle\admin\db_name\udump\*.trc (trace)
跟踪文件需要定期清理。（bdump 和 udump 目录）
跟踪每一条命令：
sql>alter session set sql_trace=true;|false 立刻生效，当前session生效。
sql>exec dbms_session.set_sql_trace(true);|false 以包的形式执行。
sql>alter system set sql_trace=true scope=spfile; 永久修改，重起生效。
2）分布式数据库 （必须是企业版）
分布式数据库的组成：
全局数据库管理系统：GDBMS
局部数据库管理系统：LDBMS
通讯驱动程序：CM
全局数据库字典：DICITIONARY
SQL*NET 网络驱动程序
sql*net v1.0 oracle7.3.4
sql*net v2.0 oracle 7.3
net8 oracle 8 \
net8i oracle 8i - connect pool
oracle net services oracle 9i / 断掉后自动重新连接
Oracle 运行的过程
http://www.oracle.com
http://www.oracle.com:234/cgi-hie/gun.go/
/虚拟路径/包名.过程/
3）Oracle的网络配置
协议
/
主机字符串 -- IP地址或服务器
\
\ 数据库实例名(oracle7/8)
- /
\
数据库服务名(oracle8i/9i/10g)
8i以上尽可能用数据库服务名。
数据库标准参数
数据库名：db_name
数据库实例名：instance_name
数据库域名：db_domain 区分同名数据库
数据库服务名：service_names
全局数据库名：global_db_name 查询远程表，用在链路上
实例名和数据库名是一一对应关系（大部分情况；双机热备系统是一对二关系，共享磁盘阵列）
两个同名数据库不能远程传数据。
数据库名相同时，可以加上域名，来区分数据库。
当数据库名加上域名不相同时，可以进行远程数据传输。
cats.hn.gov.cn
---- --------
| |
| |-------db_domain
|
|------db_name
题目：
一个数据库全局数据库名是：prod.us.oracle.com
问：该数据库名：prod
域名：无
服务名：prod
数据字典
查询数据库名：
sql>select name from v$database;
sql>show parameter db_name
查询数据库实例名：
sql>select instance_name from v$instance;
sql>show parameter instance_name
查询数据库域名：
sql>select value from v$parameter where name='db_domain';
sql>show parameter db_domain
查询数据库服务名：
sql>select value from v$parameter where name='service_anmes';
sql>show parameter service_names
查询全局数据库名：
sql>select * from global_name;
v打头的是动态数据字典
Unix 下网络配置工具
$netlasst(Orale8i)
$netca(Oracle9i/10g)
以sys用户连接远程数据库：
$sqlplus "sys/xx@unix01 as sysdba"
启动Net configuration Assistant
选择版本，远端服务名，ip地址，重新设置连接名（在原名后加01等），重新登陆sql*plus. 使用用户名远程登陆。
从sql*plus 进入命令行：host
手工配置：
Oracle网络连接解析文件：tnsnames.ora
D:\oracle\ora92\network\admin\tnsnames.ora -windows
/u01/app/oracle/product/9.2.0/network/admin/tnsnames.ora -unix
Oracle检听进程配置文件：listener.ora
D:\oracle\ora92\network\admin\listener.ora -windows
/u01/app/oracle/product/9.2.0/network/admin/listener.ora -unix
$lsnrctl start -启动监听进程
$lsnrctl status -显示监听进程状态
$lsnrctl stop -停止监听进程
查询当前在先用户
sql>select username,sid,serial#,machine from v$session;
删除用户会话：
sql>alter system kill session 'sid,serial#';
查询用户所使用的sql:
sql>select user_name,sql_text from v$open_cursor;
查询sql区的sql语句:
sql>select sql_text from v$sqlarea;
方便客户端连接。
将主机字符串写入注册表：
regedit.exe->Local Machine->Software->ORACLE->HOMEO
增加关键字：local
键 值：主机字符串
3)、Oracle9i 相关的数据库语言
sql 标准化数据库语言
sql*plus 数据库接口语言
pl/sql 过程化语言
4)、数据分区技术（相对于数据集中）
list分区表-不能比大小
range -可以比大小
LOB数据类型
BLOB:存储二进制数据。例如：图象、声音、视频等、以代替LONG RAW类型（oracle里面已经淘汰）。
CLOB:存储大字符类型数据。例如：网上论坛，个人简历，说明信息等等以代替LONG 类型。
NCLOB：
nls(National Language Support)字符集，oracle中n打头的基本上和国家有关。
A、数据库核心字符集设置参数：
nls_characterset:zhs16cgb231280 (支持版本：oracle7.3以上，不支持繁体字)
zhs16gbk (支持版本：oracle8.0以上，包括韩文、日文)
zhs32gb18030 (支持版本：oracle9i以上，在上一个上支持蒙文、维吾而文等）
查询核心字符集:
sql>select * from nls_database_parameters;
B、客户端字符集设置参数：
nls_lang:simplified chinese_china.zhs16gbk
Windows下注册表中的NLS_LANG项的键值
Unix下设置字符集方法：
$NLS_LANG="simplified chinese"_china.zhs16gbk
$export NLS_LANG
两个机器的ORACLE核心字符集不同，ORACLE拒绝迁移。
如果本机的ORACLE不是中文，需要设置字符集，再进行迁移。
C、修改数据库核心字符集的方法。
sql>update props$
set value$='ZHS16GBK'
where name='NLS_CHARACTERSET';
sql>commit;
修改后，重新启动数据库即可。
核心字符集和客户端一致的话，也可以显示中文。
BFILE：外部存储，将数据存储在服务器外部磁盘。
只存路径，不管格式。
5）、Oracle9i数据库改变及新增功能。
sql>set com v7 --设置数据库为oracle7.0,临时型设置。不支持V6。
OLTP 数据库和前端交互较多，回退空间较大。
数据仓库，改动较少，回退空间较小。
6）、Oracle9i数据库语言设计:
sql*plus => sql 、 sql*plus 、 pl/sql
SQL语言：
查询语言(QL): Query Language
数据操纵语言（DML）：Data Manpulation Language
数据控制语言（DCL）：Data Control Language
数据定义语言（DDL）：Data Definition Language
第一章 数据查询语言(QL)
2006-4-5
一、数据字典：
数据字典是一组存储系统信息的表、视图、同义词等，由系统自动维护，用户可以查询。
数据字典分类：
user_xxx: 存储用户创建的数据库实体，例如：user_tables,user_indexes,user_db_links等。
all_xxx: 存储用户可以存取的数据库实体，例如：all_tables,all_indexes,all_links等。
sql>select table_name from all_tables;
dba_xxx: 数据库管理员数据字典。例如：dba_tables,dba_indexes,dba_links等。
sql>select owner,table_name,tablespace_name from dba_tables;
v$xxx: 动态数据库字典。例如：v$database,v$session,v$sga（内存数）,v$license（最大会话数）,v$sgastat（内存使用状况）等。
sql>set timing on --设置跟踪，显示执行时间。
sql>select * from all_objects;所有的数据字典，大概有3万多个。
二、日历及日期显示格式的设置
1、设置日历显示格式参数：nls_calendar
sql>alter session set nls_calendar="Japaness Imperial";
sql>alter session set nls_calendar="ROC Official";
sql>alter session set nls_calendar="Gregorian";
2、设置日期显示格式参数：nls_date_format
sql>alter session set nls_date_format='格式';
常用日期格式组合方法：
yyyy.mm.dd yy.mm.dd yyyy/mm/dd yy/mm/dd yyyy-mm-dd yy-mm-dd
yyyy"年"mm"月"dd"日dy hh24:mi:ss AM" 加AM时24要去掉
周的话加dy --星期
sql>select sysdate+2+2/24+20/1440 from dual; --过两天两小时20分钟
查询5年11个月之后的时间：
sql>select sysdate+to_yminterval('05-11') from dual;
查询5年11个月之前的时间：
sql>select sysdate+to_yminterval('-05-11') from dual;
查询年度：
sql>select extract(year from sysdate) from dual;
查询月度：
sql>select extract(month from sysdate) from dual;
查询日期：
sql>select extract(day from sysdate) from dual;
3、将日期显示格式写入注册表
Local Machine->Software->ORACLE->HOMEO
增加关键字：nls_date_format
键 值 ：日期格式（注意：无单引号）
unix下设置：
$NLS_DATE_FORMAT=“格式”
$export NLS_DATE_FORMAT
也可以将日期格式设置写入ORACLE的登陆自动执行文件：glogin.sql
D:\oracle\ora92\sqlplus\admin\glogin.sql (windows)
/U01/app/oracle/product/9.2.0/sqlplus/admin/glogin.sql (nuix)
4、时区（Time Zones）
查询当前时区：
sql>select sessiontimezone from dual;
设置时区：
sql>alter session set time_zone='-7:00';
查询当前时间：
sql>select to_char(sysdate,’hh24:mm:ss’),to_char(current_date,’hh24:mm:ss’) from dual;
2006-04-052006-04-04
current_date:当地时间。
sysdate:当前时间。
三、谓词
A、 IN（NOT IN）：等于或不等于列表中的任意表达式
sql>select * from emp
where deptno in(20,30,40)
B、 （NOT）BETWEEN AND：表示从小到大的一个范围
sql>select * from emp
where sal between 1000 and 2000;
C、（NOT）LIKE ：字符型数据匹配查询
sql>select * from emp
where ename like 'S%';
-:表示一个字符；
%：表示任意字符串；
D、伪例
rowid:唯一行标识
rownum:行号
问题：
试查询EMP表中工资最高的5名员工及工资值及序号。
sql>select ename,sal,rownum from(select ename,sal from emp order by sal desc)
where rownum<=5;
http://fh_sw:7778/isqlplus
E、空值（NULL）
判断以下语句是否正确：
sql>select ename,sal,comm,sal+nvl(comm,0) from emp;
nvl(c1,c2)=c1 c1 不是空值，
=c2 c1 是空值。
类似函数：
nvl2(c1,c2,c3)=c2 c1不是空值
=c3 c1是空值
有奖金的加100，没奖金的直接给500
sql>select ename,nvl2(comm,comm+100,500) from emp;
coalesce(c1,c2,c3,,,)=c1 c1不是空值
=coalesce(c2,c3,c4,,,) c1是空值。返回第一个不为空的值。
sql>select * from emp where comm is null;
case:
sql>select ename,(case when sal<1500 then '低收入'
when sal>=1500 and sal<=3000 then '中等收入'
when sal>3000 then '高收入'
else '收入状况不详'
end) "收入状态",sal
from emp;
decode:
sql>select ename,job,
decode(job,'MANAGER',sal+300,'SALESMAN',sal+200,'CLERK',sal+100,sal) R_sal
from emp;
违法字符用双引号，字符型和日期型用单引号。
四、汇总与统计
常用聚组函数：
sum,max,min,avg,count,count distinct,stddev（标准差）
sql>select count(*),count(comm)
from emp; 空不记数。
sql>select count(distinct job)
from emp; 工作岗位有几个。
分组统计：服务对职工工资进行合计。
分部门、
sql>select deptno,job,sum(sal)
from emp
group by deptno,job;
分部门、职务对工资进行合计，并计算工资总合。
sql>select deptno,job,sum(sal)
from emp
group by rollup(deptno,job);
分别按部门、职务对工资进行合计
sql>select deptno,job,sum(sal)
from emp
group by grouping sets(deptno,job);
题目：
分别按年度、部门、职务对工资进行合计。同一个年度的在一起，同一部门的在一起，同一个职务的在一起。
sql>select to_char(hiredate,'yyyy'),deptno,job,sum(sal)
from emp
group by grouping sets(to_char(hiredate,'yyyy'),deptno,job);
五、连接查询（Join）
1、等值连接
问：SMITH在哪里工作？
sql>select ename,loc
from emp,dept
where emp.deptno = dept.deptno and ename='SMITH';
查询出员工姓名、工作地点、所在部门（要求列出公司所有部门）
sql>select ename,loc,dept.deptno
from dept left outer join emp on dept.deptno=emp.deptno;
sql>select ename,loc,dept.deptno
from emp left join dept on dept.deptno=emp.deptno;
sql>select ename,loc,dept.deptno
from emp,dept
where emp.deptno(+)=dept.deptno;
六、子查询（Subqueries)
1、多级子查询
哪些员工的工作与SMITH相同，或者工资比他高？
select * from emp
where ename<>'TURNER'
and (sal > (select sal from emp where ename='TURNER' )
or deptno=(select deptno from emp where ename='TURNER'));
2、哪个员工最早受雇？
sql>select ename
from emp
where hiredate=(select min(hiredate) from emp);
3、当查询结果返回值不止一个时，可以使用all,any,in,not in 等
题目：
查询出其他部门中比部门30中某一个员工工资高员工？
sql>select ename,sal
from emp
where deptno !=30
and sal > any (select sal from emp
where deptno=30); 相当于min(sal)
4、当查询返回结果多个一列时，主查询语句的where可以使用括号括起来。
题目：
查询与FORD具有相同职务与工资的员工？
sql>select ename,job,sal
from emp
where (job,sal)=(select job,sal from emp where ename='FORD');
dml 和 dcl部分暂时不讲。
第二章 数据定义语言（DDL)
一、创建基表
语法：
sql>Create table 表名（
列名1 数据类型 约束条件，
列名2 数据类型 约束条件，
，，，，）
sql>Create table product(
p_name varchar2(20) unique,
p_id number(5) primary key,
p_date date not null,
p_loc char(30) default '郑州',
constraint uk_p_name unique(p_name),
constraint pk_i_id primary key(p_id));
/*(constraint nn_p_date not null,)*/
check(p_id>11110 and p_id<99999)
以上sql有点问题
查询表约束：
sql>select * from user_constraints
where table_name='PRODUCT';
查询列的默认值：
sql>select column_name,data_default
from user_tab_columns
where table_name='PRODUCT';
save c:\p1;
get c:\p1;
sql>insert into product(p_name,p_id,p_loc)
values('计算机',12221,sysdate,default)
约束条件的修改：
删除约束：
sql>alter table product drop primary key; --删除主键约束
sql>alter table product drop unique(p_name); --删除唯一性约束
sql>alter table product modify(p_date null) ; --取消非空约束
sql>alter table product modify(p_loc default null); --取消默认值
增加约束条件：
sql>alter table product add primary key(p_id);
sql>alter table product add unique(p_name);
sql>alter table product add modify(p_date not null);
sql>alter table product add modify(p_loc default '河南');
二、数据类型
字符型 varchar2(n) n<=4000 --空间不满的时候回退空间
char(n) n<=2000 --占满空间
数字型 number(n) 0-9.99(e125) 最大38位
number(n,d)
int 最大38位数
日期型 date
二进制 long raw
大对象 LOB
BLOB：存储二进制数据
CLOB：存储大字符数据
NCLOB：外部数据
创建含有LOB列的基表：
sql>Create table employee(
name varchar(30),
id int primary key,
photo blob,
resume clob) lob(photo,resume)
store as(tablespace users chunk 2048 cache);
说明：
Chunk 表示读取LOB列时的数据量。默认2K
经常查询LOB列时可以增加Cache，默认NoCache
2006-4-6
BFILE数据类型的使用方法：
A：创建相应的物理存储路径
B：使用Create directory创建目录与物理路径连接。
C：为用户授予Read on directory 权限
例如：D:\video
sql>Create or Replace directory video as 'd:\video';
sql>grant read on directory video to stud20;
sql>Create table b_video(
bname varchar2(20),
bid int primary key,
bvideo bfile);
sql>Insert into b_video
values('影象资料',1001,bfilename('video','2.bmp'));
查询基表原代码：
sql>select dbms_metadata.get_ddl('TABLE','B_VIDEO') from dual; --输出SQL；
sql>select dbms_metadata.get_xml('TABLE','PRODUCT') from dual; --输出SQL;
sql>set long 1000
三、创建临时表（Temporary Table)
sql>Create global temporary table temp_data(
name varchar2(20),
id int,
hdate date)
on commit delete rows 或
on commit preserve rows
其中，on commit delete rows:表示提交后数据被删除
on commit preserve rows:表示会话中断后被删除
sql>set autocommit on 自动提交
sql>set autocommit 5 5次事务
查询是否创建了临时表：
sql>select table_name,temporary from user_tables;
四、创建聚集(Clusters)
几个表有关连，经常做联合查询时，使用Clusters，比较节省空间，增加查询速度。
创建Cluster
sql>Create cluster prod_sales(p_id int);
创建主表，指定Cluster关键字
sql>Create table prod(
p_name varchar2(20),
p_id int primary key) cluster prod_sales(p_id);
创建子表，指定外键及Cluster关键字
sql>Create table sales(
sname varchar2(20),
p_id int,
constraint fk_p_id foreign key(p_id) references
prod(p_id)) cluster prod_sales(p_id);
sql>select extent_id,bytes,blocks,segment_name from user_extents; 查看表的存储空间
五、创建对象类型表（Objects)
创建对象类型：
sql>Create or Replace type emp_other as object(
emp_id number(7),
emp_name varchar2(20),
emp_sal number(7,2));
需要使用'/'来执行。
创建含有对象类型的基表：
sql>Create table e_info(
job varchar2(20),
hdate date,
other emp_other);
数据插入：
sql>Insert into e_info
values('工程师',sysdate,emp_other(1234,'李大力',1500));
数据查询：
sql>select job,hdate,p1.other.emp_name,p1.other.emp_sal
from e_info p1;
六、修改表结构
1、增加新列
sql>alter table product add(
p_list number(7) constraint uk_p_list unique,
p_no int);
2、删除列 （支持版本：Oracle8i以上版本）
sql>alter table product drop(p_list,p_no);
3、修改表名
sql>rename 原名 to 新名;
4、修改列名 （支持版本：Oracle9i9.2.0以上）
sql>alter table prodctuct rename column p_name
to product_name;
5、修改列宽
sql>alter table product
modify(p_name varchar2(40));
要求整列为空，才可以从大到小修改列宽。
6、回收表中未使用空间
sql>alter table product deallocate unused keep 80k; ----回收表中部分未使用空间。
sql>alter table product deallocate unused; ----回收表中全部未使用空间。
表中未使用空间：delete from emp; 删除后不释放空间。
数据删除后产生未使用空间。
truncate table emp;倾空表，回收空间。
HWM(high water mark 水印）
查询表所占用空间大小：
sql>select sum(bytes),sum(blocks)
from user_extents
where segment_name='EMP';
sql>Create table payment as select ename,sal from emp;
sql>Insert into payment select * from payment;
七、创建序列(Sequences)
创建序列的语法：
sql>Create sequence 序列名
start with 起始编码
increment by 步长
maxvalue 终止编码；
sql>Create sequence id_code
start with 11111
increment by 1
maxvalue 9999999 cycle cache;
cycle 循环，Nocycle不循环
Nocache.
使用序列的方法：
id_code.currval --取当前值
id_code.nextval --取下一个值
首次使用时，应使用nextval.
sql>Create table icode(
code int primary key);
sql>Insert into icode values(id_code.nextval);
查询数据库中的序列：
sql>select sequence_name,increment_by,max_value
from user_sequences;
删除序列：
sql>drop sequence id_code;
八、数据库链路(Database Link)
数据库链路是分布式数据库中的一种数据库实体，通过链路可以实现数据的远程传输。
创建数据库链路的语法：
sql>Create database link 链路名 对方数据库的全局数据库名。（同名数据库不能建链路，同名数据库加域名可以建链路）
connect to 用户名 identified by 口令字
using '主机字符串';
sql>Create database link swora.us.oracle.com
connect to stud20 identified by stud20
using 'swora01';
数据库链路的使用：
sql>select * from tab@swora.us.oracle.com;
删除链路：
sql>drop database link swora.us.oracle.com;
查询数据链路：
sql>select db_link,username,password,host from user_db_links;
sql>column db_link format a20
sql>column host format a10
DDL操作不允许使用链路
使用数据库链路实现数据实时传输：
数据插入触发器：
sql>Create or Replace trigger Insert_dept
before insert on dept
for each row
Begin
insert into dept@swora.us.oracle.com
values(:new.deptno,:new.dname,:new.loc);
End Insert_dept;
查询错误:
sql>show error
查询触发器：
sql>select trigger_name from user_triggers;
查询触发器原代码：
sql>select trigger_body from user_triggers
where trigger_name='INSERT_DEPT';
sql>drop trigger 触发器名;
数据更新触发器：
sql>Create or Replace trigger Update_dept
before update on dept
for each row
Begin
update dept@swora.us.oracle.com
set deptno=:new.deptno,dname=:new.dname,loc=:new.loc
where deptno=:old.deptno and
dname=:old.dname and
loc =:old.loc;
End Update_dept;
数据删除触发器：
sql>Create or Replace trigger Delete_dept
before delete on dept
for each row
Begin
delete from dept@swora.us.oracle.com
where deptno=:old.deptno;
End Delete_dept;
使用数据库链路实现数据定时传输：
快照是从主节点传向辅节点，两个数据库要分主节点和辅节点。
创建快照(Snapshots)语法：
sql>Create snapshots 快照名
refresh 刷新方式
next 时间间隔
with parmary key
as select 子句：
语法说明：
刷新方式： complete ---完全刷新 比较主/副节点的表，不同的话，刷新
fast ---快速刷新
force ---强制刷新
时间间隔： 以天为单位
sysdate + 1/24 一小时加一次
Next_Day(trun(sysdate),'星期日')+22/24 每周日的22点
with parmary key: 要求传输的表要有主键
创建快照步骤：
A、授予用户创建快照权限（辅节点）
sql>grant create snapshot to 用户名; 需要系统管理员授权。
B、创建快照（辅节点）
C、创建快照日志（主节点）
sql>Create snapshot log on 表名;
sql>Create snapshot c_dept
refresh force
next sysdate +1/1440
with primary key
as select * from dept@swora.us.oracle.com;
sql>drop snapshot c_dept;
建立、删除日志要单独进行。
查找快照信息(辅节点查询)：
sql>select name,master_owner,master,type,next,master_link
from user_snapshots;
其中：name --快照名
master_owner ---主节点用户名
master ---主节点表名
type: --刷新方式
next: --时间间隔
master_link ---数据库链路名
创建公共数据库链路：
同名用户的对联。管理员创建，大家使用。
sql>create public database link swora.us.oracle.com
using 'swora01';
查询公共链路：
sql>select owner,db_link,username,host
from all_db_links;
九、创建视图(Views)
1、视图的特点
不存储数据
数据来源于基表
不能数据复制
在同一个表上可以建多个视图
2、创建视图的语法
sql>Create or Replace view 视图名 as select 子句
sql>Create or Replace view manager
as select * from emp
where job='MANAGER';
sql>select text from user_views
where view_name='MANAGER';
3、创建视图时的选项：
with read only ---创建只读视图
sql>Create or Replace view manager
as select * from emp
where job='MANAGER' with read only;
with check option ---在通过视图修改数据时，必须满足创建视图时where指出的条件。
sql>Create or Replace view part20
as select empno,ename,hiredate,deptno
from emp
where deptno=20 with check option;
sql>Insert into part20
values(1235,'李大力',sysdate,30);
此语句出错（视图 WITH CHECK OPTION 违反 where 子句）
sql>select * from part20;
4、为视图列取别名
试判断以下语句是否有错误？
sql>create or Replace view pay as
select ename,sal,sal*12 sal_year,sal+nvl(comm,0) sal_month
from emp;
sql>create or Replace view pay(c1,c2,c3,c4) as
select ename,sal,sal*12 ,sal+nvl(comm,0)
from emp;
%,#,$,\,/ 非法字符
第三章 Oracle数据分区表
一、什么是数据分区表
数据分区就是把表划分为多个存储区域。在创建表结构时考虑分区方案，选取表中一个或多个列作为分区关键字，该关键字决定在哪些数据存储在哪些分区，所插入数据自动存储到相应分区。
二、创建分区表
sql>Create table c_employee(
name varchar2(20),
id number(7),
sal number(7,2)) partition by range(sal)
(partition p1 values less than(500) tablespace users,
partition p2 values less than(800) tablespace indx,
partition p3 values less than(1500) tablespace odm,
partition p4 values less than(maxvalue) tablespace tools);
不确定的数据存入p4。
查询表空间信息：
sql>select tablespace_name from dba_tablespaces;
其中，temp,undotbs1不得使用。
使用sql>c/100/200可以替换
三、分区表查询
sql>select * from c_employee;
sql>select * from c_employee partition(p1);
sql>Create table c_part2
as select * from c_employee partition(p2);
查询是否为分区表：
sql>select table_name,partitioned from user_tables;
查看分区表结构：
sql>select partition_name,high_value,tablespace_name
from user_tab_partitions
where table_name='C_EMPLOYEE';
2006-4-7
四、分区表结构的修改
1、删除分区
sql>alter table c_employee drop partition p3;
2、增加分区
如果有maxvalue区，则不能增加区，需要先删除，如果有数据就比较麻烦，所以一般不设置maxvalue区。
必须增加在最后一个区后面
sql>alter table c_employee add partition p3
values less than(1500) tablespace users;
3、分区数据移动：将分区数据从一个表空间移动到另外一个表空间,将结构和数据全部移动。
sql>alter table c_employee move partition p1
tablespace tools;
4、修改分区名称
sql>alter table c_employee rename partition p1 to part1;
5、分区的拆分(Split)在两个区之间增加区。
sql>alter table c_employee split partition p3
at(1000) into (partition p21,partition p31);
6、合并（Merge）必须向高的一方合并。
sql>alter table c_employee merge partitions p21,p31
into partition p3;
五、创建List分区表
用于数据不能比较大小的时候
sql>Create table list_prod(
pname varchar2(20),
pid int,
ploc varchar2(20))partition by list(ploc)
(partition p1 values('广东') tablespace users,
partition p2 values('河南') tablespace tools,
partition p3 values('陕西','河北') tablespace odm,
partition p4 values(null) tablespace indx,
partition p5 values(default) tablespace example);
null 没有产地
default 其它情况
List分区表的使用和上面的一样。
工具介绍
Oracle Management Server(Oracle管理服务器配置） 可以把网上的多个数据库放在Oracle上使用。
服务：机器名：1521：SID
SID实例名
Oracle Enterprise Manager Console
管理员：sysman
口令字：oem_temp (default)
第四章 Oracle权限设置
一、Oralce权限分类
系统权限：系统限制用户在数据库中的操作权限（用户）
实体权限：某种权限用户对于其他用户实体的存取权限（实体）
二、系统权限的管理
1、系统权限的分类
DBA:权限组合，拥有全部特权，是系统最高权限，只有DBA才可以创建数据库
RESOURCE：拥有RESOURCE权限的用户可以登陆数据库。可以创建数据库实体，不可以创建数据库结构。
CONNECT：拥有CONNECT权限的用户可以登陆数据库。（会话权），不可以创建数据库实体。
2、系统权限的授权命令
sys/system:
sql>Create user acc01 identified by acc01;
sql>grant connect,resource to acc01;
查询用户拥有的权限：
sql>select * from user_role_privs;
ADM:是否允许权限传递
sql>select * from role_sys_privs;
sql>select * from dba_role_privs;
3、系统权限传递
sys/system:
sql>grant connect,resource to acc01 with admin option;
4、系统权限回收
sql>revoke connect,resource from acc01;
DBA权限不要轻易授权
系统权限之间无级联：授出权限可以跨级收回
实体权限之间有级联：收回一个全部收回，不能跨用户收取权限。
三、实体权限管理
1、实体权限分类
select,update,insert,delete,alter,index,execute,references,all.
2、实体权限授权命令
scott:
sql>grant select,update,delete on product to acc01;
sql>grant select,update,delete on product to public; --授权给public
acc01:
sql>select * from scott.product;
查询用户创建的基表：
sql>select table_name from user_tables;
查询用户可以存取的基表：
sql>select owner,table_name from user_tables;
查询授出权限的表：
sql>select grantee,owner,table_name,privilege
from user_tab_privs;
3、实体权限传递：with grant option
scott:
sql>grant select,update,delete on emp
to acc01 with grant option;
acc01:
sql>grant select on scott.emp to acc02;
3、实体权限的回收：
scott:
sql>revoke select,update on emp from acc01;
DBA可以管理实体用户
四、同义词(Synonyms)
1、创建私有同义词：
acc01:
sql>select * from scott.emp;
sql>Create synonym emp
for scott.emp;
sql>select * from emp;
创建同义词代替数据链路
sql>select * from dept@swora.us.oracle.com;
sql>Create synonym y_dept
for dept@swora.us.oracle.com;
2、创建公共同义词：
由DBA创建，所有用户共同使用
语法：sql>Create public synonym 公共同义词名 for 代替项;
scott:
sql>Create table e_sal as select ename,sal from emp;
sql>grant select on e_sal to public;
system:
sql>Create public synonym e_sal for scott.e_sal;
查询私有同义词：
sql>select synonym_name ,table_owner,table_name,db_link
from user_synonyms;
sql>column synonym_name format a10;
sql>column db_link format a10;
查询公共同义词：
sql>select synonym_name ,table_owner,table_name,db_link
from dba_synonyms;
sql>column synonym_name format a10;
sql>column db_link format a10;
db_link:远程表
删除公共同义词：
sql>drop public synonym e_sal; --管理员删除
删除私有同义词：
sql>drop synonym e_sal; --用户删除
第五章 PL/SQL程序设计(Procedual Language)
一、PL/SQL概述
ROWTYPE %rowtype 行类型
用于存储表中的一行记录
Declare
v_empno emp.empno%type:=&empno;
r emp%rowtype;
Begin
select * into r from emp where empno=v_empno;
dbms_output.put_line('姓名：'||r.ename||' |工资：'||r.sal||' |工作时间：'||r.hiredate);
End;
&:表示接收键盘输入。
打开屏幕打印开关：
SQL> set serveroutput on
Rocode：记录类型
Declare
v1 emp.empno%type:=&empno;
type r_record is record(
v1 varchar2(20),
v2 number(7,2),
v3 date);
r r_record;
Begin
select ename,sal,hiredate into r from emp
where empno=v1;
dbms_output.put_line('姓名：'||r.v1||' |工资：'||r.v2||' |工作时间：'||r.v3);
End;
Type: 表类型
Declare
v1 emp.empno%type:=&v1;
type t_emp is table of varchar2(20) index by binary_integer;
t1 t_emp;
v_i integer;
Begin
select 1 into v_i from dual;
select ename,sal into t1(v_i),t1(v_i+1) from emp where empno=v1;
dbms_output.put_line('编码：'||v1||' |姓名：'||t1(v_i)||' |工资:'||t1(v_i+1));
End;
二、条件判断语句
Declare
v1 emp.empno%type:=&empno;
v2 number(7,2);
v3 varchar2(20);
Begin
select sal,ename into v2,v3 from emp
where empno=v1;
if v2<2000 then
Begin
update emp set sal=sal+100
where empno=v1;
dbms_output.put_line('员工：'||v3||' 工资已经增加!');
End;
else dbms_output.put_line('员工：'||v3||' 工资已经超过规定值,不增加!');
end if;
End;
三、循环语句
Declare
i int :=0;
Begin
loop
i:=i+1;
dbms_output.put_line('i的当前值为i='||i);
exit when i=10;
end loop;
End;
Declare
i int :=0;
Begin
while i<10 loop
i:=i+1;
dbms_output.put_line('i的当前值为i='||i);
end loop;
End;
Declare
i int :=0;
Begin
for i in 1..10 loop
dbms_output.put_line('i的当前值为i='||i);
end loop;
End;
四、光标
查询结果一对多（多条记录）的情况，需要光标。
Declare
v1 number(4);
v2 varchar2(20);
v3 number(7,2);
cursor c is select empno,ename,sal from emp;
Begin
open c;
loop
fetch c into v1,v2,v3;
if v3<3000 then
Begin
update emp set sal=sal+100 where empno=v1;
dbms_output.put_line(v2||'工资已经增加!');
End;
end if;
exit when c%notfound;
end loop;
close c;
End;
题目：
请将EMP表中工资最高的前几名员工姓名及工资值查询出来，存储到基表topsal中。要求：
1、基表topsal结构如下
sql>Create table topsal(
name varchar2(20),
sal number(7,2));
2、员工个数由键盘输入确定。
Declare
i int:=&i;
j int :=0;
v2 varchar2(20);
v3 number(7,2);
cursor c is select ename,sal from emp order by nvl(sal,0) DESC;
Begin
open c;
loop
j:=j+1;
fetch c into v2,v3;
insert into topsal
values (v2,v3);
exit when (i=j or c%notfound);
end loop;
close c;
End;
空值永远在前面排。
光标变量使用方法：
Declare
type row_cursor is ref cursor;
c row_cursor;
r emp%rowtype;
Begin
open c for select * from emp;
loop
fetch c into r;
dbms_output.put_line(r.ename||'--'||r.sal||'--'||r.hiredate);
if c%notfound then exit;
end if;
end loop;
close c;
End;
用于光标的for循环语句：
Declare
cursor c is select empno,ename,sal from emp;
Begin
for i in c loop
dbms_output.put_line(i.empno||'--'||i.ename||'--'||i.sal);
end loop;
End;
Declare
cursor c is select ename,sal from emp
for update of sal nowait; --锁住
Begin
for i IN c loop
if i.sal<2000 then
Begin
update emp set sal=i.sal +100
where current of c; --光标当前行
dbms_output.put_line('员工：'||i.ename||'已经增加工资!');
End;
end if;
end loop;
End;
Declare
v_empno emp.empno%type=:=&empno;
Begin
delete from emp
where empno=v_empno;
if sql%NOTFOUND then
dbms_output.put_line('删除失败，无编码为'||v_empno||'的员工!');
else
dbms_output.putline('成功删除编码为'||v_empno||'的员工!');
end if;
End;
2006-4-8
五、例外处理(Exception)
Exception
when NO_DATA_FOUND then
dbms_output.put_line('无这样的员工');
when TOO_MANY_ROWS then
dbms_output.put_line('查询结果出现多行，请定义光标!');
when VALUE_ERRORS then
dbms_output.put_line('员工编码为4位数!');
when INVALID_NUMBER then
dbms_output.put_line('员工位数!');
when OTHERS then
dbms_output.put_line('程序运行出错!');
---- 有例外时，附值要在Begin后。
Declare
v1 emp.empno%type;
v2 number(7,2);
v3 varchar2(20);
Begin
v1:=&empno;
select sal,ename into v2,v3 from emp
where empno=v1;
if v2<2000 then
Begin
update emp set sal=sal+100
where empno=v1;
dbms_output.put_line('员工：'||v3||' 工资已经增加!');
End;
else dbms_output.put_line('员工：'||v3||' 工资已经超过规定值,不增加!');
end if;
Exception
when NO_DATA_FOUND then
dbms_output.put_line('无这样的员工');
when TOO_MANY_ROWS then
dbms_output.put_line('查询结果出现多行，请定义光标!');
when VALUE_ERROR then
dbms_output.put_line('员工编码为4位数!');
when INVALID_NUMBER then
dbms_output.put_line('NUMBER型非法!');
when OTHERS then
dbms_output.put_line('程序运行出错!');
End;
用户自定义例外的使用方法：
A、定义例外
B、使用raise引起
C、作为Exception统一使用
试指出以下语句是否有错误？
错误用法：
Declare
v1 emp.empno%type :=&empno;
Begin
delete from emp where empno=v1;
Exception
when SQL%NOTFOUND then
--SQL%NOTFOUND是属性，不能作为例外，但开始使用用户自定义例外。
dbms_output.put_line('为无编码'||v1||'的员工!');
End;
正确用法：
Declare
v1 emp.empno%type :=&empno;
if_delete exception; -----自定义例外
Begin
delete from emp where empno=v1;
if SQL%NOTFOUND then raise if_delete;
end if;
Exception
when if_delete then
dbms_output.put_line('为无编码'||v1||'的员工!');
when VALUE_ERROR then
dbms_output.put_line('员工编码为4位数!');
when others then
dbms_output.put_line('为无编码'||v1||'的员工!');
End;
第六章 存储过程与函数
一、什么是存储过程（函数）
存储过程（函数）就是把一个PL/SQL程序块存到数据库中，作为一个数据库实体，可以在其他PL/SQL程序、过程中调用。
二、存储过程的调用：
在SQL*PLUS中调用
sql>exec 过程名;
在PL/SQL及过程中调用
过程名;
三、创建存储过程的语法：
IN
Create or Replace procedure DelEmp(v1 number) is
Begin
delete from emp where empno=v1;
if sql%found then
dbms_output.put_line('编码：'||v1||'的员工成功删除!');
else
dbms_output.put_line('无编码为：'||v1||'的员工!');
end if;
End DelEmp;
在SQL*PLUS中调用存储过程：
sql>exec EdlEmp(7788);
在PL/SQL中调用：
Begin
DelEmp(7923);
DelEmp(7369);
End;
查询存储过程：
sql>select text from user_source
where name='DELEMP';
OUT
Create or Replace Procedure QueryEmp(v1 number,v2 out varchar2,v3 out number)
is
Begin
select ename,sal into v2,v3 from emp
where empno=v1;
Exception
when NO_DATA_FOUND then
dbms_output.put_line('无编码为：'||v1||'的员工!');
End QueryEmp;
调用方法：
Declare
v1 varchar2(20);
v2 number;
Begin
QueryEmp(7369,v1,v2);
dbms_output.put_line(v1||'=='||v2);
End;
IN OUT
Create or Replace Procedure ChangeName(
v_name in out varchar2) is
Begin
v_name:=SubStr(v_name,1,3);
End ChangeName;
调用方法：
Declare
v_name varchar2(20);
Begin
v_name:='SMITH';
ChangeName(v_name);
dbms_output.put_line(v_name);
End;
存储过程权限管理：
sql>grant execute on DelEmp to 用户名; ---使用的时候要加用户名前缀。
删除存储过程：
sql>drop procedure 过程名;
收回权限：
sql>revoke execute on DelEmp from 用户名;
查询存储过程权限：
sql>select owner,name,type
from all_source
where owner='SCOTT'
AND type='PROCEDURE';
四、函数(Function)
Create or Replace Function GetSal(v1 number)
return number
is
v_sal emp.sal%type;
Begin
select sal into v_sal from emp where empno=v1;
return v_sal;
End GetSal;
函数的调用：
sql>select GetSal(7369) from dual;
sql>exec dbms_output.put_line(GetSal(7369));
题目： 试编写一个n!的函数。
Create or Replace Function fn(n number)
return number
is
v_h number;
v_i number;
Begin
v_i:=1;
v_h:=1;
for v_i in 1..n loop
v_h:=v_h*v_i;
end loop;
return v_h;
End A1;
Create or Replace Function fn(n number)
return number
is
Begin
if n=1 then
return 1;
else
return n*fn(n -1);
end if;
End A2;
五,包(package)
1,什么是包?
把一些相关的存储过程,函数,光标,例外等组合在一起形成包,包由包的说明及包体两个部分组成,定义在包的说明部分的元素叫公共元素,定义在包体部分的元素叫私有元素.
2、包的优点
规范化程序设计
便于过程及函数的管理：整个包的授权只需一次完成
包作为一个整体一次性调入内存，减少I/O
3、创建包的语法
删除包：
sql>drop package 包名;
sql>drop package body 包体;
第七章 动态SQL
一、什么是动态SQL
在应用程序中动态创建基表、视图、索引等
动态生成SQL、DML语句
在PL/SQL中使用DDL语句
二、动态SQL应用实例
1、动态创建基表：
Declare
v_table varchar2(20):='&v_table';
stm varchar2(200);
Begin
stm:='Create table '||v_table||'(
id number(7),
name varchar2(20),
sal number(7,2))';
execute immediate stm;
End;
2,动态删除基表
Declare
v_table varchar2(20):='&v_table';
stm varchar2(200);
Begin
stm:='Drop table '||v_table;
execute immediate stm;
dbms_output.put_line('表'||v_table||'已经被删除!');
End;
3,动态sql应用实例
Create or Replace procedure TableRows(
table_name varchar2,
wherestr varchar2:=null) is
rownums number;
stm varchar2(200);
Begin
stm:='select count(*) from '||table_name||' where '||nvl(wherestr,'2=2');
execute immediate stm into rownums;
dbms_output.put_line('表'||table_name||'有'||rownums||'行符合要求');
End TableRows;
sql>exec TableRows('EMP')
sql>exec TableRows('EMP','sal>1500')
sql>exec TableRows('EMP','deptno=10')
4、动态生成QL语句
多行查询语句：使用光标
Create or Replace procedure DisEmp(
wherestr varchar2:=null)
is
type c is ref cursor;
r_c c;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
stm varchar2(200);
Begin
stm:='select empno,ename,job from emp where '||nvl(wherestr,'2=2');
open r_c for stm;
loop
fetch r_c into v_empno,v_ename,v_job;
exit when r_c%notfound;
dbms_output.put_line(v_empno||'\'||v_ename||'\'||v_job);
end loop;
close r_c;
End;
或者用rowtype类型不用定义多的变量
Create or Replace procedure DisEmp(
wherestr varchar2:=null)
is
type c is ref cursor;
r_c c;
r1 emp%rowtype;
stm varchar2(200);
Begin
stm:='select * from emp where '||nvl(wherestr,'2=2');
open r_c for stm;
loop
fetch r_c into r1;
exit when r_c%notfound;
dbms_output.put_line(r1.empno||'\'||r1.ename||'\'||r1.job);
end loop;
close r_c;
End;
sql>exec DisEmp('sal>1500')
sql>exec DisEmp
5,动态生成DML语句
Create or replace Procedure UpdateTable(
Table_name in varchar2,column_name in varchar2,column_value in varchar2,
Wherestr in varchar2:=null) is
stm varchar2(200);
Begin
stm:='update '||table_name||
' set '||column_name||'='||column_value||' where '||nvl(wherestr,'1=1');
execute immediate stm;
dbms_output.put_line('更新行数:'||sql%rowcount);
end UpdateTable;
sql>exec UpdateTable(‘EMP’,’sal’,2000,’sal<2000’);
sql> exec UpdateTable(‘EMP’,’sal’,100);
触发器：补充
语句级触发器：只执行一次。
Create or Replace trigger ChangeEMP
before insert or update or delete on emp
--for each row --行级
Begin
If(to_char(sysdate,'dy') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 8 and 18)
Then Raise_Application_Error(-20500,'非工作时间,不得该数据!');
End if;
End ChangeEMP;
--Oracle错误代码范围：ORA-20000 至 ORA-2009999；
系统事件触发器:(DBA)
系统事件: after logon,before logoff,after startup,before shutdown
sql>create table log_tab(user_name varchar2(20),log date,log_into varchar2(20));
sql>create or replace trigger logon_trigger after logon on database
begin
insert into log_tab values(user,sysdate,’成功登陆’);
end;
sql>create or teplace trigger logoff_trigger
before logoff on database
begin
insert into log_tab values(user,sysdate,’退出系统 ‘);
end;
DDL触发器:(DBA)
触发器事件:create,alter,drop
sql> create table ddl_tab(user_name varchar2(20),ddl_date date,ddl_info varchar2(20));
sql>create or replace trigger Create_trigger
before create on database
begin
insert into ddl_tab values(user,sysdate,’创建基表’);
end;
sql>create or replace trigger Alter_trigger before alter on database
begin
insert into ddl_tab values(user,sysdate,’修改基表结构’);
end;
sql>create or replace trigger Drop_trigger before drop on database
begin
insert into ddl_tab values(user,sysdate,’删除基表’);
end;
（DBA）(Database Administrator)
Oracle9i 数据库体系结构
物理结构
逻辑结构
内存结构
进程结构
第一章 Oracle9i 体系结构与物理结构管理
一、数据库物理结构
数据库是由构成数据库的操作文件所组成。
c:\oracle\oradata\db_name\*.* --windows 安装时的选择
/u01/app/oracle/oradata/db_name/*.* --unix
每一个数据库物理结构文件包括：数据文件、日志文件、控制文件及参数文件。
二、数据文件管理(Data Files)
存储数据库数据的文件
查询数据文件信息：
sql>select bytes,blocks,file_name
from dba_data_files;
查看临时文件信息：
sql>select bytes,blocks,file_name
from dba_temp_files;
Oracle数据文件不显示数据大小，只显示空间大小。
查看空闲空间大小：
sql>select tablespace_name,sum(bytes)
from dba_free_space
group by rollup(tablespace_name);
查看临时表空间空闲空间
sql>select * from v$temp_space_header; ---Oracle9i
数据块（Data Blocks）: db_block_size=2k~32k (2k,4k,8k,16k,32k)
默认值如下:
oracle7,oracle8,oracle8i8.1.5 2k 2048
oracle8i 8.1.6/8.1.7 8k 8192
oracle9i 9.0.1 4k 4096
oracle9i 9.2.0 8k 8192
oracle10g 8k 8192
查询数据块大小：
sql>show parameter db_block_size;
A、修改数据文件大小
sql>alter database datafile '数据文件名及路径'
resize xxM;
$df -k --unix下看空间大小
sql>alter database datafile 'D:\ORACLE\ORADATA\HF\SYSTEM01.DBF'
resize500M;
B、数据文件自动扩展属性(Autoextend)
查询数据文件自动扩展属性
sql>select bytes,autoextensible,maxbytes,file_name
from dba_data_files;
修改数据库自动扩展属性
sql>alter database datafile '数据文件名及路径'
autoextend on; ---打开数据文件自动扩展属性。
sql>alter database datafile '数据文件名及路径'
autoextend off; ---关闭数据文件自动扩展属性。
设置数据文件每次扩展大小：
sql>alter database datafile "数据文件名及路径'
autoextend on next100Mmaxsize2000m; --unlimited 无限
sql>select bytes,increment_by,file_name
from dba_data_files;
create table sa(
s1 char(20)) storage(initial80m) tablespace users; ---空表初始80m.
三、日志文件管理(Redo Log Files)
存储数据库修改前后信息。用于数据库恢复。--必须要有备份和日志。
每个数据库实例至少有两个日志文件组，每组有一个或多个日志成员。
sql>select log_mode from v$database;
sql>archive log list;
查询在线日志信息：
sql>select * from v$log;
查询日志文件信息：
sql>select * from v$logfile;
查询归档日志：
sql>select * from v$archived_log; 在日志切换时，写到磁盘或磁带上的日志。
查询历史日志：
sql>select * from v$log_history;
sql>alter session set nls_date_format='yyyy.mm.dd h24:mi:ss';
日志文件不宜太小，一般几十兆即可。
日志成员最少要有2个，9i里面缺省5个。
DBA强制日志切换命令：
alter system switch logfile;
A、增加日志文件组命令：
sql>alter database add logfile group n ('第一个日志成员文件名及路径',
'第二个日志成员文件名及路径',,,) size xxM; ―n为组号
sql>alter database add logfile group 4(
' c:\oracle\oradata\sunny \redo41.log',
' c:\oracle\oradata\sunny \redo42.log')
size10m;
sql>alter database add logfile group 2 'c:\oracle\oradata\sunny\redo21.log' size1m;
―――可以在后面加reuse；
sql>alter database add logfile group n ('第一个日志成员文件名及路径',
'第二个日志成员文件名及路径',,,) reuse; ―n为组号
B、删除日志文件组：
sql>alter database drop logfile group n;
注意：当前组Current,Active组不得删除
数据库只有两个组时，不得删除
先逻辑删除，后物理删除
sql>select * from v$logfile;
C、增加日志成员文件：
sql>alter database add logfile member
'日志成员文件名及路径','日志成员文件名及路径' to group n;
―――可以在后面加reuse；
sql>alter database add logfile member
'日志成员文件名及路径','日志成员文件名及路径' reuse to group n;
D、删除日志成员文件：
Sql>alter data base drop logfile member
‘日志成员文件名及路径’）；
注意： 。Current组中的成员不得删除，
。组中只有一个成员时，不得删除成员
。先逻辑删除，后物理删除
题目：请将系统默认安装产生的3个日志文件的大小全部由100M改为1M。
用到：alter database drop logfile group N;
alter database add logfile group N…..
select * from v$log;
alter system switch logfile;
四、参数文件
在Oracle9i数据库中，可以使用两个不同类型的参数文件，一个为文本类型Pfile,一个为服务器类型Spfile,
Spfile 默认存储路径：
C:\oracle\ora92\database\spfileSID.ora
--win
/u01/app/oracle/product/9.2.0/dbs/spfileSID.ora
--unix
Spfile修改方法：
Sql>alter system set db_cache_size =8mcomment=’Changed By TYC,2006,04.09’);
参数 注释
Pfile默认存储路径
C:\oracle\admin\db_name\pfile\init.ora….. --win
/u01/app/oracle/admin/db_name/pfile/initSID.ora --unix
建立链接关系：
c:\oracle\ora92\database\initSID.ora=> --在database目录中建立initsid.ora
ifile=’d:/oracle/admin/db_name/pfile/init.ora’ --内容
--win
/u01/app/oracle/produce/9.2.0/dbs/initSID.ora
in:
/u01/app/oracle/admin/db_name/pfile/initSID.ora
--unix
。当pfile和spfile同时存在时，系统启动时优先使用Spfile，如果未找到spfile，则以pfile启动。
查询数据库所使用得参数文件
sql>show parameter spfile
。两个参数文件可以相互转换
sql>create spfile from pfile;
sql> create pfile from spfile;
c:\oradim -startup -sid sunny ---在操作系统中启动数据库
五、控制文件(Control Files)
C:\oracle\oradata\sunny\*.ctl
控制文件用于存储数据结构。
查询警告文件信息。了解修改数据库结构得操作：
C:\oracle\admin\sunny\bdump\alert_sunny.log
控制文件得主要信息如下：
。数据库名称
。数据库创建时间
。数据库中全部日志文件名及路径、全部数据文件及路径
。数据库恢复时所需同步信息
*.dbf, dba_data_files
*.log, v$logfile
*.ctl, v$
查询数据文件同步号：
sql>select file#,checkpoint_change#,name from v$datafile;
查询控制文件的同步号：
sql>select checkpoint_change#,name from v$database;
控制文件时恢复数据的基准！
控制文件镜像方法：
A．关闭数据库
B． 复制控制文件
C． 修改参数文件
D．重启数据库
查看控制文件：
select * from v$controlfile;
第二章 Oracle9i逻辑结构管理
数据库逻辑结构组成：
表空间(Tablespaces)
段(Segments)＝＝》数据段：存储基表数据的段 ----私有
索引段：存储索引数据的段 ――私有
临时段：用于排序 ――公有
回退段：用于事务的回退 ――公有
区(Extents)
数据块(Data Blocks)
块－》区－》段－》表空间
一、表空间（Tablespaces）
。控制数据库数据磁盘分配
。表空间与数据文件相对应，表空间大小等于构成该表空间的所有数据文件大小之和
查询表空间与数据文件对应关系：
sql> select tablespace_name,bytes,file_name from dba_data_files;
。表空间属性：read only, read write, online,offline
修改表空间属性：
sql> alter tablespace table_name read only;
查询表空间状态：
sql> select tablespace_name,status from dba_data_files;
其中:system,undotabs1,temp 不得设置为：read only,offline;
二、段（Segments）
A、数据段：存储基表数据的段。Drop删除段空间，delete
查询表与存储表的表空间对应关系：
sql>select table_name,tablespace_name from user_tables;
将表从一个表空间移动到另外一个表空间：
sql> alter table dept move tablespace users;
创建基表时，可以指定表空间：
sql>create table t_data(tname varchar2(20),tdate date) tablespace users;
查询用户缺省表空间：
sql>select username,default_tablespace from dba_users;
B、 索引段：存储索引数据的段。
查询索引与存储该索引表空间对应关系：
sql> select index_name,table_name,tablespace_name from user_indexes;
在创建索引时，可以指定表空间：
sql>create index d_dname on dept (dname) tablespace indx;
C、 临时段：用于排序。
查询临时表空间：
sql>select tablespace_name,contents from dba_tablespaces;
查询用户所使用的临时表空间：
sql> select username,temporary_tablespace from dba_users;
D、回退段：用于事务的回退。8i(tbs)9i(undotabs1)
sql>select * from dba_rollback_segs;
查询回退段信息：
sql>select * from v$rollstat;
sql> select * from v$rollname;
三、区（Extents）
区是磁盘空间分配最小单位。一个段包含多个区。
查询段与区的关系：
sql>select extent_id,bytes,blocks from user_extents where segment_name=’EMP’;
区大小设置参数（磁盘存储参数）：
Initial:初始区大小 8.0(2k) 8i(5*db_block_size) 9i(64k)
Next:增长区大小 8.0(2k) 8i(5*db_block_size) 9i(64k)
Minextents:区最小个数 1
Maxextents:区最大个数 8.0(121) 8i(505) 9i(unlimit)
Pctincrease:区增长百分比 8.0(50) 8i(50) 9i(0)
sql>Create table sales(
s1 char(20),
s2 char(20));
如果磁盘无限大，这张表能存多少数据？
这个表的缺省参数：Storage(initial 64k,
next 64k,
minextents 1,
maxextents unlimit,
pctincrease 0);
sql>Create table sales(
s1 char(20),
s2 char(20))storage(initial 200k next 400k minextents 2 maxextents 20000 prtincrrease 0);
查询可以用于以下对象
表空间
回退段
表
索引
存储参数优先原则：实体级参数>表空间级参数
四、数据块(Data Blocks)
数据块是数据文件存储空间单位，是I/O最小单位
在Oracle9i中，可以使用标准数据块及非标准数据块，其大小为 2k,4k,8k,16k,32k。
标准块大小由参数db_block_size设置，非标准块在创建表空间时，由参数blocksize设置。
标准块用于系统表空间及临时表空间、非标准块数据块用于用户数据表空间。
查询表空间大小：
sql>select tablespace_name,block_size
from dba_tablespaces;
第三章 Oracle9i 内存结构管理
一、SGA(System Global Area)系统全局区
1、数据缓冲区(Database Buffer Cache):存储由磁盘数据文件读入的数据，全体用户共同使用。
数据缓冲区的参数：
db_block_buffers (Oracle8i/8/7)
db_cache_size (Oracle9i/10g)
非标准块缓冲区设置参数：db_nk_cache_size(其中：n=2,4,8,16,32)
标准块不得重复设置
在数据库中，可以同时设置多个数据缓冲区。例如：
db_cache_size=128m
db_2k_cache_size=64m
db_4k_cache_size=32m
以下设置是否正确：
...
db_block_size=8k --标准块设置
db_4k_cache_size=8m
db_cache_size=128m
db_8k_cache_size=16m(x) 和标准块设置重复
db_16k_cache_size=64m
...
2、共享池（shared pool）:用于对于sql、pl/sql程序进行语法分析及编译。
共享池大小设置参数：shared_pool_size=xxx
3、日志缓冲区(redo log buffer)：存储数据库修改信息。在日志缓冲区写满时，由LGWR（日志写入进程）写入磁盘日志文件组。
日志缓冲区大小设置参数：log_buffer
查询日志缓冲区大小：
sql>show parameter log_buffer
显示SGA区总大小：
SQL>show sga
Sql>show parameter sga_max_size
Sql>show parameter db
sql>show parameter log
sql>show parameter
sql>select * from v$sga;
sql>select * from v$sgastat;
二、PGA(rogram Global Area)
程序全局区用于变量及程序设计
程序全局区设置参数：pga_aggregate_target
三、SORT AREA:排序区：
用于order by,group by ,distinct等排序
排序区大小设置参数：sort_area_size
内存使用完后，ORALCE使用磁盘临时空间，会造成临时空间的增长。
四、LARGE POOL大池：
用于Rman数据库备份工具。
大池设置参数：large_pool_size
五、JAVA POOL：
用于Java语言
设置参数：java_pool_size
--------------------------第四章 Oralce9i实例及进程结构--------------------------------------------
一、什么是Oralce实例
每一个运行的数据库都与一个实例相联系。数据库启动时将分配SGA并启动后台数据库进程。该SGA与后台进程合成Instance.
Unix 下设置实例方法：
$CRACLE_SID=sunny
$export ORACLE_SID
Windows下设置实例方法：
c:\>set oracle_sid=sunny
二、Oracle进程结构
用户进程
/
多进程 - 服务器进程
/ \
Oracle 主要进程 后台进程
\
单进程
查询后台进程信息：
sql>select name,description
from v$bgprocess;
三、用户进程和服务器进程
CBO (Cost_Based optionmizer)基于成本的规划器
/
执行计划
\
RBO (rule_Based optionmizer)基于规则的规划器
Oralce 的优化 -- 减少分析和编译
四、后台进程
A、DBWR 数据写入进程(Database Writer)
将数据由数据缓冲区写入磁盘数据文件。
采取LRU(least Recently Used)最近最少使用原则，将最近数据保留在内存，以减少I/O操作。
DBWR进程设置参数:
db_writer_processes=1~10 (Oracle8i)
=1~20 (Oracle9i/10g)
dbwr_io_salvers
B、LGWR
C、ARCH:归档进程。在日志切换时，将日志信息写入磁盘或磁带，用于数据库恢复。
ARCH进程设置参数：log_archive_start=ture|false
默认：false
sql>show log_archive_start
D、CKPT:检验点进程(checkpoint)
DBA强制产生检验点命令：
sql>alter system checkpoint;
sql>show parameter log_checkpoint_timeout
sql>show parameter log_checkpoint_interval
--------------第五章 Oracle9i表空间管理------------------------------------------------------------
一、Oracle9i表空间概述
查询数据库中的碎片：
sql>select * from dba_free_space;
二、表空间分类
按照表空间中区的管理方式不同，表空间分为数据字典管理类型及本地化管理类型。
查询表空间中区的管理方式：
sql>select tablespace_name,extent_management,allocation_type
from dba_tablespaces;
其中： UNIFORM --每个区的大小一样。
SYSTEM --每个区有数据库管理，动态分配。
2、在数据字典管理表空间中，区的大小由参数initial,next,minextents,maxextents,pctincrease设置。
在本地化管理表空间中区的管理分为uniform及autoallocate两种。
3、数据字典管理表空间主要用于Oracle8i及之前的数据库；本地化管理表空间主要用于Oracle9i及以后版本数据库中。
三、创建数据字典管理表空间(Oracle7/8/8i)
sql>Create tablespace s_date datafile
'd:\oracle\oradata\ora8i\s_data01.dbf' size100m
default storage (initial 100k
next 100k
minextents 1
maxextens 2000
pctincrease 0);
四、创建本地化管理表空间(Oracle8i/9i/10g)
1、Uniform:区大小一律相同
sql>Create tablespace product datafile
'c:\oracle\oradata\sunny\prod01.dbf' size100m
autoextend on next100mmaxsize2000m,
'c:\oracle\oradata\sunny\prod02.dbf' size100m
autoextend on next100mmaxsize2000m
extent management local uniform size 100k;
sql>select tablespace_name,extent_management,allocation_type
from dba_tablespaces;
sql>select tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase
from dba_tablespaces;
删除表空间：
sql>drop tablespace product including contents and datafiles;
如果表是空的可以直接删除：
sql>drop tablespace product including contents; (Oracle8i只能这样写，并手工删除文件)
2、Autoallocate:表空间中区的大小由系统自动动态分配
sql>Create tablespace account datafile
'c:\oracle\oradata\sunny\acc01dbf' size100m,
'c:\oracle\oradata\sunny\acc02dbf' size100m
extent management local autoallocate;
sql>select maxbytes,file_name
from dba_data_files;
五、修改表空间：
为表空间增加数据文件
sql>alter tablespace account add datafile
'c:\oracle\oradata\sunny\acc03.dbf' size100m
autoextend on next100mmaxsize2000m;
数据文件移动：将数据文件从一个磁盘移动到另一个磁盘；
移动步骤：
A、将表空间设置为offline
sql>alter tablespace account offline;
B、物理移动
C、逻辑移动
sql>alter tablespace account rename datafile
'原数据文件名及路径' to '目标数据文件名及路径';
D、将表空间设置为online
sql>alter tablespace account online;
sql>startup restrict --数据库以限制方式运行
sql>grant restricted session to 指定用户;
注意：System,undotbs1,temp数据文件不得移动
六、创建Undo表空间(撤消表空间)
sql>Create undo tablespace undotbs datafile
'c:\oracle\oradata\sunny\undo01.dbf' size100m
autoextend on next100mmaxsize unlimited
extent management local autoallocate;
设置参数：undo_tablespace=undotbs
重新启动数据库。
-----------------------第六章 Oralce9i数据库用户管理--------------------------------------------
二、创建资源限制文件(Profile)
sql>alter system set resource_limit=true;
创建资源限制文件：
sql>Create profile acc limit
failed_login_attempts 2
password_lock_time 1
sessions_per_user 2
idle_time 2;
三、创建用户：
sql>Create user acc03 identified by acc03
default tablespace users
temporary tablespace temp
profile acc
quota50mon users; --如果不限制，这行不要
sql>grant connect,resource to acc03;
为用户解锁：
sql>alter user acc03 account unlock;
四、修改用户：
修改口令字：
sql>alter user acc03 indentified by '123';
修改用户缺省表空间
sql>alter user acc03 default tablespace example;
取消资源限制：
sql>alter user acc03 profile default;
取消空间使用限制
sql>alter user acc03 quota unlimited on users;
五、删除用户
sql>drop user acc03 | cascade; --cascade用在此用户有实体。
------------------------第七章 Oracle9i数据库备份与恢复----------------------------------------------
一、数据库归档模式
二、数据库归档模式的配置
设置参数：
log_archive_start=true
log_archive_dest=目录名
log_archive_format=%t-%s.arc --日志存储格式%t线程号 %s日志号
log_archive_duplex_dest=镜像目录名
sql>shutodwn immediate
sql>startup mount --mount只打开控制文件，不打开数据库文件。
sql>alter database archivelog;
sql>alter database open;
sql>select log_mode from v$database;
sql>archive log list
sql>alter system switch logfile
三、物理备份
1、完全数据库脱机备份(ARCHIVELOG/NOARCHIVELOG)
2、联机部分数据库备份(ARCHIVELOG)
A、确认数据库运行在ARCHIVELOG模式
B、设置要备份的表空间为备份状态
sql>alter tablespace 表空间 begin backup;
C、操作系统备份相应的数据文件
D、设表空间结束标志：
sql>alter tablespace 表空间名 end backup;
begin 和 end 为了下次从哪里恢复而做的标记。期间的数据不保存。
sql>Create tablespace account datafile
'd:\oracle\oradata\hf\acc01.dbf' size10m;
sql>alter tablespace account begin backup;
sql>alter tablespace account end backup;
sql>Create table account(
s1 char(20)) tablespace account;
sql>insert into account values('计算技术');
sql>insert into account select * from account;
数据文件恢复命令：
sql>alter database datafile '数据文件名及路径'; --日志自动读取
或
sql>recover datafile '数据文件名及路径'; --交互式，可以输路径
sql>alter database open;
基于时间的数据库恢复：
sql>alter database recover database until time '2006-04-11 13:00:00';
将数据库向回倒。
四、逻辑备份
c:\>exp scott/tiger tables=(dept,emp) file=d:1 --表方式卸出
c:\>exp scott/tiger file=d:\f2 --用户方式
c:\>exp system/manager full=y file =d:\f3 --全部数据库方式
其他关键字：
C:\exp scott/tiger direct=y file =d:\f2 --直接路径卸出，不经过内存，直接从文件卸出，适合大表。
C:\exp scoot/tiger tables=(emp) query=\"where sal>1500\" file =d:\f2 --条件卸出
C:\>exp system/manager full=y filesize=100m ---限制卸出文件大小
C:\>exp system/manager full=y inctype=complete --为增量设基准
C:\>exp system/manager full=y inctype=incremental ----增量卸出
实际中以物理备份为好，当需要数据迁移时，用逻辑备份。逻辑备份不许要日志，但速度较慢。
五、Rman恢复管理器
Unix中创建数据库的图形界面工具：
$dbca (Oracle9i/10g)
1、创建恢复目录
创建Rman数据库
创建Rman用户
sql>Create user rman identified by rman default tablespace users
temporary tablespace temp;
sql>grant connect,resource,recovery_catalog_owner to rman;
c:\>set oracle_sid=rman --确定进rman库
因为oracle为rman库默认建立的rman用户，限制太多。
所以删除默认rman用户，自己建立用户。
sql>drop user rman cascade;
创建用户，授权给新用户。
创建恢复目录
c:\>set oracle_sid=rman
c:\>rman catalog rman/rman
RMAN>Create catalog; (删除命令：drop catalog) 在rman中建表，
c:\rman catalog rman/rman
2、将Rman连接到目标数据库
c:\>set oracle_sid=sunny
c:\>rman target sys/zy123 catalog rman/rman@rman --target表示目标库,catalog表示rman库,@表示连接到rman上
注册数据库：在Rman恢复目录中写入目标库信息。
RMAN>register database;
>report schema; 应该显示目标数据库中的表空间和数据文件，Rman配置成功。
3、使用Rman备份数据库
COPY:复制文件(数据文件和控制文件)只能copy到磁盘
RMAN>copy datafile 9 to 'c:\oracle\back\9.cf';
显示备份文件信息：
RMAN>list copy of datafile 9;
备份控制文件：
RMAN>copy current controlfile to 'c:\oracle\back\control.cf';
RMAN>list copy of controlfile;
删除备份文件：
RMAN>delete copy of datafile 9;
RMAN>delete copy of controlfile;
复制多个文件：
RMAN>run {
copy
datafile 9 to 'c:\oralce\back\9.cf,
datafile 8 to 'c:\oracle\back\8.cf,
current controlfile to 'c:\oracle\back\control.cf';
}
RMAN>host "cls";
BACKUP：可以备份数据库、表空间、数据文件，可以备份到磁盘和磁带，并对备份文件进行压缩。
RMAN>backup datafile 9,8,7
format='c:\oracle\back\%T_%U'; %T系统当前时间，%U系统唯一型，备份文件不会重复。
显示：
RMAN>list backup of datafile 9;
删除：
RMAN>delete backup of datafile 9;
删除所有文件：
RMAN>delete backup;
备份表空间：
RMAN>backup tablespace users,indx,odm
format='c:\oracle\back\'%T_%U';
全部备份：
RMAN>backup tablespace database
format='c:\oracle\back\'%T_%U';
format='c:\oracle\back\'%T_%U';
4、使用RMAN恢复数据库
RMAN>restore |datafile,tablespace,database --向回考，物理性恢复
RMAN>recover |datafile,tablespace,database --同步恢复。
5、Rman恢复实例
A、恢复数据文件
RMAN>startup mount
>restore datafile 9;
>recover datafile 9;
>alter database open;
B、恢复表空间
RMAN>startup mount
>restore tablespace users,
>recover tablespace users;
>alter database open;
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
清洗数据
。数据包含有不可显示的符号：
。通常：linefeed、tab、escape
。使用清洗工具
。set cleanchar
。clean “^9”,”^10”
内部数据类型（internal datatypes）
。number、char、vchar2、date
number是可变长格式，1位存放指数和20位存放尾数，在fortran，cobol与c不能直接处理。
Oracle的number一般为38位数
外部数据类型（external datatypes）
Oracle也有外部数据类型，它们可转换成fortran，c，java等能识别的数据
Oracle如何传递数据到程序：
。integer，char，floating－point，packed－decimal
二.程序设计
2、存储过程，函数
3、包（把存储过程和函数打成包），数据库触发器
三.系统管理与维护（DBA）
1、核心软件的安装与产品升级
2、存储空间管理
oracle资源占用以库为单位，一个库要占用资源50-57%，所以一台机子最好只装一个数据库。
解决方法：同一个数据库，多个表空间。 一个实例相当于一个数据库，oracle的表空间级相当于sqlserver的数据库级。
可以在表空间级定义用户，密码。
3、权限与角色管理
4、数据库的备份与恢复
增量备份比较麻烦。Rman。
5、优化与调整
四.应用系统开发
1、工具
form -> *.form
/
Develop200 -- report -> *.rdf
\
graphics -> *.ogd
oralce application server
其它：
在Windows下清除Oracle8i/9i/10g运行环境
1、删除Oracle注册表
regedit.exe->Local Machine->Software->ORACLE
2、删除Oracle服务
regedit.exe->Local Machine->System->CurrentControlSet->Services->ORA*
3、删除Oracle事件日志
regedit.exe->Local Machine->System->CurrentControlSet->Services->Eventlog->Application->ORA*
4、删除NT安装磁盘\Program Files\Oracle目录
5、删除Oracle环境变量
控制面板->系统->高级->环境变量
6、删除Oracle菜单
7、重新启动操作系统
8、删除Oracle主目录
oracle用于unix时，长时间不重起，重启一次时间非常长。
《Oracle数据库系统管理》（上、下册） --清华大学出版社
《Oracle Developer/2000开发实例与应用技术》 --清华大学出版社
《Oracle9i数据库系统管理大全》 --清华大学出版社
《Oracle10g数据库系统管理》 --机械工业出版社