Oracle学习笔记（2）

事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的语句要么全部成功，要么全部失败。

当执行事务的时候，会在被作用的表上加锁，防止其他用户改表表的结构。

只读事务

指只允许执行查询的操作，而不允许执行任何其他dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。

set transaction ready only;

====================

函数

字符函数

lower(char)、upper(char)、length(char)、substr(char,m,n)

Replace( )、Instr( )

实例：首字母大写显示名字的信息

1、完成首字母大写

Select upper(substr(ename,1,1))  from scott.emp;

2、完成后面字母小写

Select lower(substr(ename,2,length(ename)-1)) from scott.emp;

3、合并

Select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from scott.emp;

数学函数

Round(n[,m]) 四舍五入

Trunc(n[,m]) 截取数字

Mod(m,n) 取模

Floor(n) 返回小于或是等于n的最大整数

Ceil(n) 返回大于或是等于n的最小整数

日期函数

Oracle默认日期格式: dd-mon-yy 即如12-7月-78

sysdate 返回系统时间   如：select sysdate from dual;

add_months(d,n) 

last_day(d) 返回指定日期所在月份的最后一天

格式化函数

to_char

格式化函数提供一套有效的工具用于把各种数据类型（日期/时间，int，float，numeric）转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。

系统函数

sys_context

====================

数据库管理

管理数据库的用户主要是：sys、system。

oracle的sys和system用户有啥区别？

1 )   最重要的区别，存储的数据的重要性不同 

【 sys 】 所有 oracle 的数据字典的基表和视图都存放在 sys 用户中，这些基表和视图对于 oracle 的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。 sys 用户拥有 dba ， sysdba ， sysoper 等角色或权限，是 oracle 权限最高的用户。 

【 system 】 用户用于存放次一级的内部数据，如 oracle 的一些特性或工具的管理信息。 system 用户拥有普通 dba 角色权限。 

2)   其次的区别，权限的不同。 

 【 sys 】用户具有 “SYSDBA” 或者 “SYSOPER” 系统权限，登陆 em 也只能用这两个身份，不能用 normal 。 

【 system 】如果正常登陆，它其实就是一个普通的dba用户，但如果以as sysdba登陆，其结果实际上它是作为sys用户登陆的，这一点类似 Linux 里面的 sudo 的感觉，从登录信息里面我们可以看出来（再者用show user;）。 

normal 、 sysdba 、 sysoper 有什么区别 

normal 是普通用户  

sysdba 拥有最高的系统权限，登陆后是 sys

sysoper 主要用来启动、关闭数据库， sysoper 登陆后用户是 public

sysdba 和 sysoper 属于 system privilege ，也称为 administrative privilege ，拥有例如数据库开启关闭之类一些系统管理级别的权限 sysdba 和 sysoper 具体的权限可以看下表： 

系统权限sysdba sysoper  

区别 Startup( 启动数据库 )   startup  

Shutdown( 关闭数据库 )  shutdown  

alter database open/mount/backup  alter database open/mount/backup  

改变字符集 none  

create database( 创建数据库 )  None 不能创建数据库  

drop database( 删除数据库 )  none  

create spfile create spfile  

alter database archivelog( 归档日志 )  alter database archivelog  

alter database recover( 恢复数据库 )  只能完全恢复，不能执行不完全恢复  

拥有restricted session权限   拥有restricted session ( 会话限制 ) 权限  

可以让用户作为sys 用户连接  可以进行一些基本的操作，但不能查看用户数据  

登录之后用户是sys   登录之后用户是public  

dba 和 sysdba 的区别 

dba 、 sysdba 这两个系统角色有什么区别呢 

在说明这一点之前我需要说一下 oracle 服务的创建过程 

· 创建实例→ · 启动实例→ · 创建数据库 (system 表空间是必须的 )

启动过程 

· 实例启动→ · 装载数据库→ · 打开数据库 

sysdba ，是管理 oracle 实例的，它的存在不依赖于整个数据库完全启动，只要实例启动了，他就已经存在，以 sysdba 身份登陆，装载数据库、打开数据库。只有数据库打开了，或者说整个数据库完全启动后， dba 角色才有了存在的基础！

以 sys 用户登陆 Oracle ，执行 select * from V_$PWFILE_USERS; 可查询到具有 sysdba 权限的用户。

初始化参数管理（略）

show paramete;显示参数。

数据库导出/导入（备份/恢复）

oracle 的exp/imp命令用于实现对数据库的导出/导入操作;
exp命令用于把数据从远程数据库服务器导出至本地,生成dmp文件;
imp命令用于把本地的数据库dmp文件从本地导入到远程的Oracle数据库中。

oracle主目录的bin目录下（exp、imp执行程序），环境变量里加就不用到该目录下再用命令。比如：D:\softwares\oracle\product\10.2.0\db_1\bin

可以通过在命令行输入 imp help=y 获取imp的语法信息。

同样可以通过输入 exp help=y 获取exp的语法信息。

imp使用的基本格式：imp[username[/password[@service]]]

导出/入：导出/入表、导出/入方案、导出/入数据库。

使用工具pl/sql developer可以按照以下操作：
1、点击Tools--Export User Objects
这种方式可以导出当前用户拥有的所有对象，包括表、视图、触发器、同义词等等，对于表，只能导出表结构，不能导出数据，选中要导出的对象，设置好导出的路径和各个参数后，点击Export就OK
    PS：这种方式还可以导出其他用户的对象，在下面user下拉框中选择要用户就可以，默认是系统当前登录用户
2、如果想将表中数据一起导出，可用Tools--Export Tables，这种方式导出的文件，本身就带着创建表结构的sql语句选中要导出的表（可选多个），导出方式有三种：
    Oracle Export，Sql Insert，pl/sql developer
第一种是导出为.dmp的文件格式，.dmp文件是二进制的，可以跨平台，还能包含权限，效率也很不错，用得最广
第二种是导出为.sql文件的，可用文本编辑器查看，通用性比较好，但效率不如第一种，适合小数据量导入导出。尤其注意的是表中不能有大字段（blob,clob），如果有，会提示不能导出，可以用第一种和第三种方式导出。
第三种是导出为.pde格式的，.pde为Pl/sql developer自有的文件格式，只能用Pl/sql developer自己导入导出；不能用编辑器查看，效率不知道怎么样
PS:只有“Oracle Export”方式导出再导入后，表结构和索引结构没有发生变化，另两种方式都改变了索引类型。

工具最终还是调用命令，oracle导入导出的命令是imp/exp。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把 dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。

数据字典   基表、动态性能视图

动态性能视图记载了例程启动后的相关信息。

数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。它是只读表和视图的集合，数据字典的所有者是sys。数据字典包括数据字典基表和数据字典视图。

用户只能在数据字典上执行查询操作，而其维护和修改是由系统自动完成的。

数据字典三种视图：user_xxx、all_xxx、dba_xxx

user_xxx  用于显示当前用户所拥有的xxx对象

all_xxx   用于显示当前用户可以访问的所有xxx对象

dba_xxx  显示所有方案拥有的数据库xxx对象。但是查询这种数据库字典视图，要求用户必须是dba角色或是拥有select any table系统权限。

如：xxx是tables

Select table_name from user_tables;

Select table_name from all_tables;

用户、权限、角色

Oracle会将权限和角色信息存放到数据字典，

通过查询dba_users可以显示所有数据库用户信息；

如：Select username,password from dba_users;

通过查询数据字典视图dba_sys_privs，可以显示用户所具有的系统权限

通过查询数据字典视图dba_tab_privs，可以显示用户所具有的对象权限

通过查询数据字典dba_col_privs，可以显示用户所具有的列权限

通过查询数据字典视图dba_role_privs，可以显示用户所具有的角色

如：select * from dba_role_privs where GRANTEE='SCOTT';

查看oracle中所有的角色，一般是dba

Select * from dab_roles;  

查看oracle中所有的系统权限，一般是dba

Select * from  system_privilege_map order by name; 

查看oracle中所有的对象权限，一般是dba

Select distinct privilege from dba_tab_privs；

显示当前用户可以访问的所有数据字典视图

Select * from dict where comments like '%grant%';

显示当前数据库的名称

Select * from global_name;

====================

约束、索引

约束用于确保数据库数据满足特定的商业规则。

在oracle中，约束包括：not mull、unique、primary key、foreign和check五种。

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是要注意：增加not null约束时，需要使用modify选项，而增加其他四种约束使用add constraint，删除用drop constraint。

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。