Oracle知识综合整理

1. SQL语言分类 种类 缩写 全称 常用操作 数据定义语言 DDL Data define language create, alter, drop 数据操纵语言 DML Data Manipulation Language select, insert, delete, update 事务操纵语言 TCL Transaction Control Language commit, savapoint, rollback 数据操纵语言 DCL Data Control Language grant, revoke

2. SQL常用数据类型

分类 关键字 表示范围 备注 字符 char 1~2000字节 固定长度 varchar2 1~4000字节 可自增长长度 long 2GB 可自增长长度 数值 number 定义方式 number(P,[S]) P:长度 S:精度 日期 date 日期和时刻部分，精确到整个的秒 timestamp 存储日期、时刻和时区信息，秒值精确到小数点后6位 RAW raw 1~2000字节 存储二进制数据 long raw 2GB 存储二进制数据 LOB clob 4GB 能够存储大量字符数据 blob 4GB 能够存储较大的二进制对象，如图形、视频剪辑和声音文件 bfile 4GB 用于将二进制数据存储在数据库外部的操作系统文件中

3. Oracle中的伪列

 Oracle 中伪列就像一个表列，然而它并没有存储在表中  伪列能够从表中查询，但不能插入、更新和删除它们的值  常用的伪列有 ROWID 和ROWNUM 3.1. ROWID ROWID是表中行的存储地址，该地址能够唯一地标识数据库中的一行，能够使用ROWID伪列快速地定位表中的一行。 3.2. ROWNUM ROWNUM是查询返回的结果集中行的序号，能够使用它来限制查询返回的行数。 4. 命名规则 Oracle中的各种数据对象，包括表名称，视图等等名称的命名都需要遵循Oracle的命名规则。Oracle的命名规则分为标准命名方式和非标准命名方式。  标准命名方式 以字符打头30个字符以内只能包含A-Z,a-z,0-9,_,$和#。不能和同一个用户下的其他对象重名，不能是oracle服务器的保留字。  非标准命名方式 你能够使用你想使用的任何字符，包括中文，oracle中的保留字，空格等等，然而需要将对象名用双引号引起来。例如： create table “table” (test1 varchar2(10)); 5. 创建表空间 CREATE TABLESPACE tablespacename DATAFILE ‘filename’ [SIZE integer [K|M]] [AUTOEXTEND [OFF|ON]]; 注：大写字母为关键字，小写部分为用户自定义部分；[]中的内容为可选择部分。 例：create tablespace my_tablespace datafile ‘c:\myspace’ size 100K autoextend on; 6. Oracle用户操作 6.1. 创建用户 CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace]; 例：create user scott identified by tiger; 6.2. 密码修改 ALTER USER username IDENTIFIED BY newpassword 6.3. 锁定用户 ALTER USER username ACCOUNT LOCK; 6.4. 撤销锁定 ALTER USER username ACCOUNT UNLOCK; 6.5. 删除用户 DROP USER username; 7. 权限治理  Oracle权限有两种类型，系统权限和对象权限。  系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言）。  实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。 7.1. 系统权限治理 7.1.1. 系统权限分类 DBA：拥有全部特权，是系统最高权限，只有DBA才能够创建数据库结构。 RESOURCE：拥有Resource权限的用户只能够创建实体，不能够创建数据库结构。 CONNECT：拥有Connect权限的用户只能够登录Oracle，不能够创建实体，不能够创建数据库结构。 关于一般用户：授予connect, resource权限。 关于DBA治理用户：授予connect, resource, dba权限。 7.1.2. 系统权限授权 系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户) 授权命令：GRANT connect, resource, dba TO username; 一般用户通过授权能够具有与system相同的用户权限，但永久不能达到与sys用户相同的权限，system用户的权限也能够被回收。 例：grant connect, resource to scott; 7.1.3. 系统权限传递 增加WITH ADMIN OPTION选项，则得到的权限能够传递。 例：grant connect, resorce to user01 with admin option; 7.1.4. 系统权限回收 REVOKE connect, resource FROM username; 7.2. 实体权限治理 7.2.1. 实体权限分类 select, update, insert, alter, index, delete, all等 7.2.2. 实体权限授权 GRANT select, update, insert ON tablename TO username; 例：grant all on emp to scott; 7.2.3. 实体权限传递 例：user01:grant select, update on product to user02 with grant option; 7.2.4. 实体权限回收 REVOKE select, update ON tablename FROM username; 8. 角色治理  角色是一组权限的集合，将角色赋给一个用户，那个用户就拥有了那个角色中的所有权限。 8.1. 系统预定义角色 预定义角色是在数据库安装后，系统自动创建的一些常用的角色。 角色所包含的权限能够用以下语句查询： select * from role_sys_privs where role=’角色名’; CONNECT, RESOURCE, DBA 这些预定义角色要紧是为了向后兼容。其要紧是用于数据库治理。oracle建议用户自己设计数据库治理和安全的权限规划，而不要简单的使用这些预定角色。 DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE 这些角色要紧用于访问数据字典视图和包 SNMPAGENT 用于oracle enterprise manager和Intelligent Agent RECOVERY_CATALOG_OWNER 用于创建拥有恢复库的用户。关于恢复库的信息，参考oracle文档《Oracle9i User-Managed Backup and Recovery Guide》 HS_ADMIN_ROLE A DBA using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary. 8.2. 治理角色  建一个角色 create role role1;  授权给角色 grant create any table, create procedure to role1;  授予角色给用户 grant role1 to user1;  查看角色所包含的权限 select * from role_sys_privs;  scott的权限如图：

--查看scott用户具有什么系统权限 select * from role_sys_privs; --查看scott用户自己拥有什么角色 select * from user_role_privs; --查看scott用户自己具有什么的权限 select * from SESSION_ROLES; --查scott用户的创建时刻、用户状态、使用的默认表空间、临时表空间等信息 select * from user_users; --查看scott用户中，都哪些用户把对象授予给scott用户 select * from user_tab_privs; --查看scott用户中拥有的resource角色都具有什么权限