乐趣区

oracle事物和常用数据库对象笔记和实验

一:事物
1:什么是事物
是业务上的一个逻辑单元,它能够保证其中对数据所有的操作,要么全部成功,要么全部失败

2:事物的特性
原子性、一致性、隔离性、持久性

3:事物的控制
使用 COMMIT 和 ROLLBACK 实现事务控制
    COMMIT:提交事务,即把事务中对数据库的修改进行永久保存
    ROLLBACK:回滚事务,即取消对数据库所做的任何修改

使用 AUTOCOMMIT 实现事务的自动提交
    设置 AUTOCOMMIT 为 ON

二:索引
1:索引的含义
索引是 oracel 的一个对象,是与表关联的可选结构,提供了一种快速访问数据的途径,提高了数据库检索性能

2:索引的特性
提高查询速度
可以对表的一列或多列建立索引
建立索引的数量没有限制
索引需要磁盘存储
索引的引用由 oracle 决定

3:索引的分类
B 树索引
唯一索引和非唯一索引
反向键索引
位图索引
组合索引
基于函数的索引

三:视图
1:视图的含义
视图:视图是一个虚表,不占用物理空间,视图中的数据是从一个或多个实际表中获得的。
物化视图:物化视图也称为实体化视图,含有数据,占用空间。

2:视图的作用
提供了另外一种级别的表安全性
隐藏的数据的复杂性
简化的用户的 SQL 命令
隔离基表结构的改变
通过重命名列,从另一个角度提供数据

四:序列
1:序列的含义
序列是用于生成一系列唯一数字的对象,通常用来自动生成主键或唯一键的值。

2:访问序列
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值

五:同义词
1:同义词含义
同义词是现有对象的一个别名

2:同义词的好处
简化 SQL 语句
隐藏对象的名称和所有者
提供对对象的公共访问

3:同义词的类型
私有同义词:只能在其模式内访问,且不能与当前模式的对象同名
公有同义词:可被所有的数据库用户访问

六:分区表
1:分区表的含义
允许用户将一个表分成多个分区,这个分划分过区的表就是 oracle 的分区表

2:分区表的特性
用户可以执行查询,只访问表中的特定分区
将不同的分区存储在不同的磁盘,提高访问性能和安全性
可以独立地备份和恢复每个分区

3:分区表优点
改善表的查询性能
表更容易管理
便于备份和恢复
提高数据安全性

4:分区表的条件
数据量大于 2GB
已有的数据和新添加的数据有明显的界限划分

实验部分:

添加 c##scott 账号,为后续试验做准备

1:sys 身份登录 oracle

2:创建测试账号 scott

SQL> create user c##scott identified by scott default tablespace users temporary tablespace temp quota unlimited on users;

3:授权

SQL> grant connect,resource to c##scott;

4:导入脚本文件(提前将脚本拷贝到 /opt 的目录下)

SQL> @/opt/test.sql

5:切换到 c##scott 用户

SQL> conn c##scott/scott

一:事务

1:使用 commit 和 rollback 实现事务控制

1):向 scott 数据库中的 dept 表插入数据

SQL> conn c##scott

SQL> col loc for a10;

SQL> select * from dept;

    DEPTNO DNAME       LOC

———- —————————————— ———-

10 ACCOUNTING       NEW YORK

20 RESEARCH       DALLAS

30 SALES       CHICAGO

40 OPERATIONS       BOSTON

SQL> insert into dept values(50,’a’,null);

SQL> insert into dept values(60,’b’,null);

SQL> commit;

SQL> select * from dept;

2):再次插入数据,不提交,执行回滚操作

SQL> insert into dept values(70,’c’,null);

SQL> select * from dept;

SQL> rollback;

SQL> select * from dept;

2:实现事物的自动提交

SQL> set autocommit on;       \\ 实现事物自动提交

SQL> insert into dept values(90,’b’,null);

SQL> rollback;

SQL> select * from dept;

二:索引

1:索引的创建

SQL> conn c##scott

(1)创建 B 树索引

SQL> create index emp_ename_idx on emp(ename);   \\ 在 emp 表中,为雇员名称列创建 B 树索引

(2)创建唯一索引

SQL> create unique index emp_grade_unique_idx on salgrade(grade);  \\ 在 salgrade 表中,为级别编号 grade 创建唯一索引

(3)反向键索引

在 emp 中,为雇员编号 empno 创建反向键索引

SQL> create index emp_deptno_reverse_idx on emp(deptno) reverse;

(4)位图索引

SQL> create bitmap index emp_job_bit_idx on emp(job);  \\ 为工种列创建位图索引

(5)基于函数的索引

SQL> create index emp_ename_upper_idx on emp(upper(ename));  \\ 为雇员名称创建大写函数索引

2:查看索引

(1)查看索引名,索引类型,所属表名,表空间名称

SQL> SQL> select index_name,index_type,table_name,tablespace_name from user_indexes;

(2)查看索引名,表名,索引列

SQL> select index_name,table_name,column_name

from user_ind_columns

where index_name like ‘EMP%’;

3:维护索引

1):SQL> alter index emp_job_bit_idx rebuild;  \\ 重建索引

2):SQL> alter index emp_job_bit_idx coalesce;  \\ 合并索引碎片

3):SQL> drop index emp_job_bit_idx;  \\ 删除索引

三:视图

普通视图

1:创建视图

(1)创建表

[oracle@oracle admin]$ sqlplus sys/aptech as sysdba

SQL> create table order_master (

orderno number(5) constraint p_ord primary key,

odate date,

vencode number(5),            

o_status char(1));

(2)插入数据

SQL> insert into order_master values (1,to_date(‘2006-01-01′,’yyyy-mm-dd’),1,’a’);

SQL> insert into order_master values (2,to_date(‘2007-01-01′,’yyyy-mm-dd’),2,’p’);

(3)创建视图

SQL> create view pen_view as

  select * from order_master

  where o_status =’p’;        \\ 该列作为创建视图的条件,不能被修改

(4)查询视图

SQL> select * from pen_view;

2:由于视图创建有缺陷,导致错误的修改后,无法查询视图,可以为视图添加约束,

(1)通过视图修改数据(错误的修改后,无法查询视图,修改了 o_status 列的值)

SQL> update pen_view set o_status=’d’ where o_status=’p’;

SQL> select * from pen_view;

(2)为了避免修改视图错误,使用 with check option 创建检查约束

SQL> create or replace view pen_view as select * from order_master where o_status=’p’

with check option constraint penv;

(3)插入数据

SQL> insert into order_master values (3,to_date(‘2007-01-01′,’yyyy-mm-dd’),3,’p’);   \\ 插入数据

(4)修改视图,会报错,因为违反检查约束

SQL> update pen_view set o_status=’d’ where o_status=’p’;

3:使用 read only 创建只读视图,阻止对视图的修改

SQL> create or replace view pen_view as select * from order_master with read only;

4:创建视图时,基表不存在

(1)创建错误的视图(venmaster 表不存在)

SQL> create force view ven as select * from venmast;

会报错,提示表不存在,此时可以创建出这个表,如果针对该表执行了 sql 语句,系统会自动重新编译该视图,也可以手动重新编译此视图

(2)创建 venmast 表

SQL> create table venmast (

venno number(5) constraint p_ven primary key,

vendate date,

vencode number(5),            

ven_status char(1));

(3)手动编译视图,

SQL> alter view ven compile;

4:创建带有 order by 字句的视图

SQL> create or replace view pen_view as select * from order_master order by orderno;

5:DML 语句和复杂视图

(1)通过数据字典查询视图

SQL> select view_name from user_views;

(2)删除视图

SQL> drop view pen_view;

物化视图

1:查看物化视图的查询重写功能是否开启

SQL> show parameter query_rewrite_enabled;

2:创建物化视图

(1)位 c##scott 账户授权

SQL> conn system   \\ 先以 system 的身份链接 oracle

SQL> grant create view to c##scott;   \\ 赋予 scott 创建视图的权限

SQL> grant create materialized view to c##scott;   \\ 赋予 scott 创建物化视图的权限

SQL> grant query rewrite to c##scott;   \\ 赋予 scottquery rewrite 的权限

SQL> grant create any table to c##scott;   \\ 赋予 scott 创建表的权限

SQL> grant select any table to c##scott;   \\ 赋予 scott 查询任何表的权限 

(2)创建物化视图日志

SQL> conn c##scott   \\ 用 scott 账户登录

创建物化视图日志

SQL> create materialized view log on dept with rowid;

SQL> create materialized view log on emp with rowid;

(3)创建物化视图

SQL> create materialized view mtrlview_test

build immediate

refresh fast

on commit

enable query rewrite

as

select d.dname,d.loc,e.ename,e.job,e.mgr,

e.hiredate,e.sal,d.rowid d_rowid,e.rowid e_rowid

from dept d,emp e

where d.deptno=e.deptno;

(4)删除物化视图

SQL> drop materialized view mtrlview_test;

四:序列

1:创建序列

SQL> create sequence toy_seq

  start with 10

  increment by 1

  maxvalue 2000

  nocycle

  cache 30;

2:序列的使用

(1)创建表

SQL> create table toys (

  toyid number not null,

  toyname varchar2(20),

  toyprice number);

(2)插入数据

SQL> insert into toys (toyid,toyname,toyprice)

  values (toy_seq.nextval,’twenty’,25);

(3)查看数据

SQL> col toyname for a10;

SQL> select * from toys;

(4)查看序列当前值,和下一个值

SQL> select toy_seq.currval from dual;

SQL> select toy_seq.nextval from dual;

3:更改序列

SQL> alter sequence toy_seq

  maxvalue 5000

  cycle;

4:查看序列

SQL> select sequence_name,increment_by,cache_size

  from user_sequences;

5:删除序列

SQL> drop sequence toy_seq;

五:同义词

1:在 system 模式下创建私有同义词访问 scott 模式下的 emp 表

(1)以 system 身份登陆,并查询 scott 中 emp 表

SQL> conn system

SQL> select * from c##scott.emp ;

(2)创建同义词 emp

SQL> create synonym sy_emp for c##scott.emp;

(3)访问同义词 sy_emp

SQL> select * from sy_emp;

2:案例:访问网络服务名为 orcl 的远程数据库中的表 T

(1)system 身份登录

SQL> conn system/aptech@orcl

(2)下面语句是要创建一个表 T,并添加两条记录(书上没有这个过程,表 T 也就不存在了,所以要创建一个表 T)

SQL> create table T (

   orderno number(5) constraint p_ord primary key,

   odate date,

   vencode number(5),            

   o_status char(1));

(3)添加数据

SQL> insert into T values (1,to_date(‘2006-01-01′,’yyyy-mm-dd’),1,’a’);

SQL> insert into T values (2,to_date(‘2007-01-01′,’yyyy-mm-dd’),2,’p’);

(4)创建数据库链接

SQL> create database link dblink_sw_orcl connect to system identified by aptech using ‘orcl ‘;

(5)用刚创建的链接名查询

SQL> select * from T@dblink_sw_orcl;

(6)创建私有同义词,orcl 为实例名

SQL> create synonym sy_T for T@orcl;

(7)用私有同义词查询

SQL> select * from sy_T;

3:在 scott 模式下对部门表 dept 创建共有同义词 public_sy_dept,让其他用户也能使用这个共有同义词

SQL> conn system

SQL> create user c##user1 identified by aptech;

SQL> grant connect,resource to c##user1;

SQL> grant create public synonym to c##scott;

SQL> conn c##scott

SQL> grant select on dept to c##user1;

SQL> create public synonym public_sy_dept for dept;

SQL> conn c##user1

SQL> select * from public_sy_dept;

4:删除同义词

SQL> drop synonym system.sy_emp;

SQL> drop public synonym public_sy_dept;

六:分区表

1:以分区的方式建立季度销售信息

SQL> conn sys as sysdba

create table sales

(

sales_id number,

product_id varchar2(5),

sales_date date not null

)

partition by range (sales_date)

(

partition p1 values less than (to_date(‘2013-04-1′,’yyyy-mm-dd’)),

partition p2 values less than (to_date(‘2013-07-1′,’yyyy-mm-dd’)),

partition p3 values less than (to_date(‘2013-10-1′,’yyyy-mm-dd’)),

partition p4 values less than (to_date(‘2014-01-1′,’yyyy-mm-dd’)),

partition p5 values less than (maxvalue)

);

2:插入数据

SQL> alter session set nls_date_format = ‘yy-mm-dd’;       \\ 设置日期格式

SQL> INSERT INTO SALES VALUES (1,’P001′,’2013-02-3′);

3:查看某季度的数据

SQL> select * from sales partition(p1);

4:删除某季度的数据(如果表中无数据会提示 0 rows deleted.)

SQL> delete from sales partition(p1);

5:查看分区情况

SQL> select * from user_tab_partitions;

退出移动版