Oracle数据库知识集锦
基本概念
- 专用|共享服务器模式：区别：专用模式下，一个用户连接进程对应一个服务器的进程
- 会话与连接的关系：
一个物理TCP/IP连接上可以有多个会话；
会话可以失去物理连接（可以重新关联到物理连接？）；
JDBC中的connection，对应了Oracle的会话，而非物理连接。默认情况下，断开连接后，会话的状态可能处理INACTIVE，此时还是占用资源，可以通过设置会话超时，避免长期的资源占用：SQLNET.EXPIRE_TIME=10（分钟）
Oracle SQL
关于ROWNUM
- 1先根据WHERE子句进行过滤
- 标注结果集的ROWNUM
- 进行排序（注意，MAX等聚合函数需要排序）
Oracle与JDBC
允许DBA登录JDBC |
1 2 |
Properties props = new Properties(); props.put( "internal_logon", "sysdba" ); |
JDBC提交模式
| 自动提交 | 每一个语句被放在单独的事务中执行 |
| 手动提交 | 所有语句放在整个事务中执行，直到commit/rollback被调用 |
提交发生在语句执行完后，以下几个时间点标志着语句的完成：
- 对于DML语句（即 insert update delete）,执行完毕语句即完成
- 对于查询语句，当结果集关闭时完成
- 返回多个结果的CallableStatement，当关联的结果集关闭，并且update counts和输出参数已经被retrieve的时候完成
Oracle Hints提示
| /*+ALL_ROWS*/ | 对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化 |
| /*+FIRST_ROWS*/ | 对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化 |
| /*+CHOOSE*/ | 如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法 |
| /*+RULE*/ | 对语句块选择基于规则的优化方法 |
| /*+FULL(TABLE)*/ | 对表选择全局扫描的方法 |
| /*+ROWID(TABLE)*/ | 提示明确表明对指定表根据ROWID进行访问 |
| /*+CLUSTER(TABLE)*/ | 明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效 |
| /*+INDEX(TABLE INDEX_NAME)*/ | 表明对表选择索引的扫描方法 |
| /*+INDEX_ASC(TABLE INDEX_NAME)*/ | 表明对表选择索引升序的扫描方法 |
Oracle 锁机制
事务隔离级别
Oracle 支持三种事务隔离级别：已提交读取，串行化，以及 SQL92 中没有包含的只读模式。Oracle通过多版本一致性模型（multiversion consistency model）来保证可重复读：
- 语句级的读一致性：自动实现，所有结果集数据来自同一时间点
- 事务级读一致性：确保单个事务中的数据来自同一时间点
Oracle锁
基本的锁分类：
共享锁（Share Locks，即S锁）：其他事务只能加S锁，不能加X锁
排他锁（Exclusive Locks，即X锁）：其他事务不能对目标数据再加任何类型的锁
Oracle多粒度锁：
根据保护对象的不同，Oracle数据库锁可以分为以下几大类：
- DML lock（data locks，数据锁）：用于保护数据的完整性
- DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）
- Internal locks 和latches（内部锁与闩）：保护内部数据库结构
- Distributed locks（分布式锁）：用于OPS（并行服务器）中
- PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中
TX锁（事务锁，行级锁）
Oracle行级锁只有X锁，没有S锁
当执行DML语句或者select from update的时候，自动获取TX锁，直到提交或者回滚
每一行上都有关于锁的标记位。对行加锁的时候，必须首先对所在表进行意向锁定
意向锁
表由行组成，对表加锁时，不但要检查与已经存在的表锁的相容性，还要检查与表里面全部数据的锁的相容性
如果逐条检查数据的锁，很耗时，这就引出意向锁——对行进行加锁时，同时要对其所在的表加意向锁
意向共享锁（Intent Share Lock ,IS锁）：如果要对行加S锁，首先需要对表加IS锁
意向排他锁（IX）：如果要对行加X锁，首先需要对表加IX锁
读操作默认不加锁
默认的Oracle读数据不加任何锁定，通过回滚段（Rollback segment）来防止脏读
Oracle Job机制
设置并发Job数： |
1 2 |
-- 0 < n <= 1000 alter system set job_queue_processes=n; |
Jobs字典：
|
1 2 3 4 |
SELECT * FROM USER_JOBS SELECT * FROM DBA_JOBS SELECT * FROM ALL_JOBS SELECT * FROM DBA_JOBS_RUNNING |
管理Jobs：dbms_job包
该包包含以下过程：
| Broken | 更改已提交的Job的状态，Broken状态的Job不会被尝试执行Oracle多次尝试均不能成功运行的Job，会置为Broken=true |
| Change | 改变Job的设置 |
| Interval | 显式设置运行间隔 |
| Isubmit | 使用指定的Job号来提交Job |
| Next_Date | 显式设定工作时间 |
| Remove | 移除一个Job |
| Run | 立即运行一个Job |
| Submit | 提交Job |
| What | 设置Job的内容 |
INTERVAL设置
INTERVAL：从Job运行结束的那一刻，判断下一次执行的时间
注意：Oracle日期函数中，1代表一天
| Interval => TRUNC(sysdate,'mi') + 1/ (24*60)Interval => sysdate+1/1440 | 每分钟执行 |
| Interval => TRUNC(sysdate) + 1 +1/ (24) | 每天的凌晨1点执行 |
| Interval => TRUNC(NEXT_DAY(sysdate,2))+1/24 | 每周一凌晨1点执行（NEXT_DAY返回下一个星期X。数字1-7，分别表示周日到周六 ） |
| Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24 | 每月1日凌晨1点执行（LAST_DAY返回某月份的最后一天） |
| Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24 | 每季度的第一天凌晨1点执行 |
| Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24 | 每年7月1日和1月1日凌晨1点 |
| Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24 | 每年1月1日凌晨1点执行 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE V_I INTEGER; BEGIN FOR IDX IN (SELECT JOB FROM USER_JOBS WHERE WHAT = 'PKG_SUP_INFO_AJ.USP_SUP_AJ_JOB;') LOOP DBMS_JOB.REMOVE(IDX.JOB); END LOOP; SYS.DBMS_JOB.SUBMIT(JOB => V_I, WHAT => 'PKG_SUP_INFO_AJ.USP_SUP_AJ_JOB;', NEXT_DATE => TO_DATE('08-01-2011 02:00:00', 'DD-MM-YYYY HH24:MI:SS'), INTERVAL => 'TRUNC(SYSDATE) +1 + 2/24'); COMMIT; END; |
数据字典
Oracle数据字典中，对象名称多以USER. ALL. DBA.为前缀：
| USER. | 视图中记录通常记录执行查询的帐户所拥有的对象的信息 |
| ALL. | 视图中记录包括"USER"记录和授权至PUBLIC或用户的对象的信息 |
| DBA. | 视图包含所有数据库对象 |
常用的数据字典如下：
| ALL_CATALOG | 所有用户可见的表、视图、同义词、序列等 |
| ALL_COL_COMMENTS | 所有可见表、视图的列注释 |
| ALL_COL_GRANTS_MADE | 所有者或者授权者的列授权信息 |
| ALL_COL_GRANTS_RECD | 用户或者PUBLIC是授权目标的列授权信息 |
| ALL_COL_PRIVS | Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| ALL_COL_PRIVS_MADE | Grants on columns for which the user is owner or grantor |
| ALL_COL_PRIVS_RECD | Grants on columns for which the user, PUBLIC or enabled role is the grantee |
| ALL_CONSTRAINTS | 表上的约束定义 |
| ALL_CONS_COLUMNS | 列上的约束定义 |
| ALL_DB_LINKS | DB Link定义 |
| ALL_DEF_AUDIT_OPTS | Auditing options for newly created objects |
| ALL_DEPENDENCIES | 依赖关系（从/到） |
| ALL_ERRORS | 存储对象上的错误 |
| ALL_INDEXES | 表上的索引描述 |
| ALL_IND_COLUMNS | 列上的索引描述 |
| ALL_OBJECTS | 全部对象 |
| ALL_REFRESH | All the refresh groups that the user can touch |
| ALL_REFRESH_CHILDREN | All the objects in refresh groups, where the user can touch the group |
| ALL_SEQUENCES | 全部序列 |
| ALL_SNAPSHOTS | Snapshots the user can look at |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | 所有用户可见的同义词 |
| ALL_TABLES | 所有表 |
| ALL_TAB_COLUMNS | 所有表、视图、cluster的列 |
| ALL_TAB_COMMENTS | 所有表、视图的注释 |
| ALL_TAB_GRANTS_MADE | User's grants and grants on user's objects |
| ALL_TAB_GRANTS_RECD | Grants on objects for which the user or PUBLIC is the grantee |
| ALL_TAB_PRIVS | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| ALL_TAB_PRIVS_MADE | User's grants and grants on user's objects |
| ALL_TAB_PRIVS_RECD | Grants on objects for which the user, PUBLIC or enabled role is the grantee |
| ALL_TRIGGERS | 所有触发器 |
| ALL_TRIGGER_COLS | 触发器用到的列、或者被列用到的触发器 |
| ALL_USERS | 用户信息 |
| ALL_VIEWS | 视图信息 |
| USER_AUDIT_CONNECT | 用户登陆/退出的审计记录 |
| USER_AUDIT_OBJECT | Audit trail records for statements concerning objects, specifically: table, cluster, view, index, sequence, [public] database link, [public] synonym, procedure, trigger,rollback segment, tablespace, role, user |
| USER_AUDIT_SESSION | |
| USER_AUDIT_STATEMENT | Audit trail records concerning grant, revoke, audit, noaudit and alter system |
| USER_AUDIT_TRAIL | Audit trail entries relevant to the user |
| USER_CATALOG | |
| USER_CLUSTERS | |
| USER_CLU_COLUMNS | 表列到cluster列的映射 |
| USER_JOBS | 任务定义 |
| USER_OBJECT_SIZE | 多种PL/SQL对象的长度（bytes） |
| USER_OBJ_AUDIT_OPTS | 用户表、视图的审计选项 |
| USER_RESOURCE_LIMITS | 用户的资源使用限制 |
| USER_ROLE_PRIVS | 当前用户的角色与特权 |
| USER_SEGMENTS | 分配给所有数据库段的存储 |
| USER_SYS_PRIVS | 授予当前用户的系统特权 |
| USER_TABLESPACES | 可见表空间 |
| USER_TS_QUOTAS | 表空间配额 |
| USER_USERS | 当前用户信息 |
| USER_VIEWS | 当前用户所有视图 |
| COLUMN_PRIVILEGES | Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| DICTIONARY | 数据字典本身的描述 |
| DICT_COLUMNS | 数据字典列的描述 |
| DUAL | 虚表 |
| GLOBAL_NAME | 数据库全局名称 |
| INDEX_HISTOGRAM | statistics on keys with repeat count |
| INDEX_STATS | statistics on the b-tree |
| RESOURCE_COST | Cost for each resource |
| ROLE_ROLE_PRIVS | Roles which are granted to roles |
| ROLE_SYS_PRIVS | System privileges granted to roles |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
用户管理
创建用户脚本示例|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Create the user create user NU identified by NU default tablespace TS temporary tablespace TST profile DEFAULT; -- Grant/Revoke role privileges grant connect to NU; -- Grant/Revoke system privileges grant alter session to NU; grant create any snapshot to NU; grant create any trigger to NU; grant create procedure to NU; grant create sequence to NU; grant create session to NU; grant create table to NU; grant create type to NU; grant create view to NU; grant select any dictionary to NU; grant unlimited tablespace to NU with admin option; |
删除用户：drop user 用户名 cascade; cascade //将用户连同其创建的东西全部删除
权限管理
系统权限：系统规定用户使用数据库的权限
系统权限分类
| 权限分类 | 说明 |
| DBA | 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构 |
| RESOURCE | 拥有Resource权限的用户只可以创建实体，不可以创建数据库结构 |
| CONNECT | 拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构 |
系统权限只能由DBA用户授出,普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收
系统权限授权命令
|
1 2 3 4 5 6 7 8 9 10 |
--授予权限 grant connect, resource, dba to user1 [,user2]...; --授予可传递权限（被授予用户可以授予其他用户） grant connect, resorce to user0 with admin option; --查询权限 select * from dba_role_privs; select * from dba_sys_privs; select * from role_sys_privs; --回收权限，只能由DBA执行，系统权限不会级联回收 Revoke connect, resource from user0; |
实体权限：某用户对其它用户的表或视图的存取权限
实体权限分类：select, update, insert, alter, index, delete, all , execute实体权限授权命令
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 把product的增改查权限授予user0 grant select, update, insert on product to user0; -- 把product的所有权限授予user0 grant all on product to user0; -- 把product的所有权限授予所有用户 grant all on product to public; -- 实体权限传递：with grant option，可以授予其他人 grant select, update on product to user02 with grant option; -- 实体权限回收，传递的权限全部收回 Revoke select, update on product from user02; -- 实体权限数据字典 select owner, table_name from all_tables; select table_name from user_tables; select grantor, table_schema, table_name, privilege from all_tab_privs; select grantee, owner, table_name, privilege from user_tab_privs; |
常用脚本
管理表空间
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--创建表空间 CREATE TABLESPACE "TS" LOGGING DATAFILE 'D:\ORACLE\ORADATA\CPDB\TS.ora' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO --创建临时表空间 CREATE TEMPORARY TABLESPACE "NETBAISTEMP" TEMPFILE 'D:\ORACLE\ORADATA\CPDB\NETBAISTEMP.ora' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M |
导入/导出表/库
导入时提示只有dba才能导入其他dba导出的dmp：使用system用户|
1 2 3 4 5 6 |
-- 导出一张表 exp user/passwd@dbname file=dmppath tables=(tab_name) query="where 1=1" -- 导出整个库 exp user/passwd@dbname BUFFER=1000 file=dmppath owner=user log=logpath -- 导入DMP文件 imp user/passwd@dbname file=dmppath fromuser=user touser=user ignore=y |
锁定对象查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--查具有锁定的用户 SELECT USERNAME, LOCKWAIT, STATUS, MACHINE, PROGRAM FROM V$SESSION WHERE SID IN ( SELECT SESSION_ID FROM V$LOCKED_OBJECT) --查锁定的SQL SELECT SQL_TEXT FROM V$SQL WHERE HASH_VALUE IN ( SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID IN ( SELECT SESSION_ID FROM V$LOCKED_OBJECT)) --查锁定的SID等 SELECT S.USERNAME, S.STATUS, L.OBJECT_ID, P.SPID, L.SESSION_ID, S.SERIAL#, L.ORACLE_USERNAME, L.OS_USER_NAME, L.PROCESS FROM V$LOCKED_OBJECT L, V$SESSION S, V$PROCESS P WHERE L.SESSION_ID = S.SID AND S.PADDR = P.ADDR --杀死锁定会话 ALTER SYSTEM KILL SESSION 'sid,serial' ; |
查询会话的信息
|
1 2 3 4 5 6 7 8 9 |
SELECT A.SID || ',' || A.SERIAL# AS "SID,SERIAL", A.USERNAME, A.TERMINAL, A.PROGRAM, S.SQL_TEXT FROM V$SESSION A, V$SQLAREA S WHERE A.SQL_ADDRESS = S.ADDRESS(+) AND A.SQL_HASH_VALUE = S.HASH_VALUE(+) ORDER BY A.USERNAME, A.SID |
修改系统日期时间格式
|
1 |
alter system set nls_date_format = 'YYYY-MM-DD hh24:mi:ss' scope=spfile; |
查执行缓慢的SQL语句
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT OSUSER, PROGRAM, USERNAME, SCHEMANAME, B.CPU_TIME, STATUS, B.SQL_TEXT FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY B.CPU_TIME DESC |
Leave a Reply