数据库培训 第一部分基础知识
1. 软件安装
1.1 数据库软件安装
安装目录不能包含中文、空格
1.2 数据库实例安装
SGA 和PGA 的大小设置
2. 数据库TNS 的配置、检查
ORACLE_HOME
D:\app\hegel\product\12.1.0\dbhome_1
方法一：tnsnames.ora
TNS 配置文件
%ORACLE_HOME %\NETWORK\ADMIN\tnsnames.ora
CARD_185 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.100.185)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = oracledb)
)
)
方法二：Net Configuration Assistant
方法三：Oracle Net Manager
使用tnsping 测试
tnsping card_185
3. 表空间的创建
一般情况下是根据表空间来备份的，所以要把表中的数据和索引，存放在不同的表空间中。备份时只备份数据，索引只是重建。
数据表空间 CREATETABLESPACEKAYOU_DATA DATAFILE
'E:\APP\HEGEL\ORADATA\MOMA\KAYOU_DATA01.DBF'SIZE 2GAUTOEXTENDOFF 索引表空间 CREATETABLESPACEKAYOU_INDEX DATAFILE
'E:\APP\HEGEL\ORADATA\MOMA\KAYOU_INDEX01.DBF'SIZE 512MAUTOEXTENDOFF
4. 数据库用户的创建、赋权
创建用户 --创建用户
--DROP USER KAYOU CASCADE;
CREATEUSER KAYOU
IDENTIFIEDBY KAYOU DEFAULTTABLESPACE KAYOU_DATA
TEMPORARYTABLESPACE TEMP;
赋权 -- Grant/Revoke role privileges
grantconnectto KAYOU ;
grantresourceto KAYOU ;
grantdbato KAYOU ;
查询权限 --系统权限
select *from user_sys_privs;
--角色权限
select *from user_role_privs; --对象权限
select *from user_tab_privs;
--当前session 有哪些权限
select *from session_privs;
5. 数据库对象：表、视图
一般情况下是根据表空间来备份的，所以要把表中的数据和索引，存放在不同的表空间中。备份时只备份数据，索引只是重建。
创建表
我们示例账号scott 里的表
建表DEPT
CREATETABLE DEPT
(
DEPTNO NUMBER (2),
DNAME VARCHAR2(14BYTE ),
LOC VARCHAR2(13BYTE )
)
TABLESPACE TBS_DATA--创建表时，使用数据表空间
RESULT_CACHE (MODEDEFAULT );
CREATEUNIQUEINDEX PK_DEPT ON DEPT
(DEPTNO )
TABLESPACE TBS_INDEX; --创建索引时，使用索引表空间
ALTERTABLE DEPT ADD (
CONSTRAINT PK_DEPT
PRIMARYKEY
(DEPTNO )
USINGINDEX PK_DEPT
);
建表EMP CREATETABLE EMP
(
EMPNO NUMBER (4),
ENAME VARCHAR2(10BYTE ),
JOB VARCHAR2(9BYTE ),
MGR NUMBER (4),
HIREDATE DATE ,
SAL NUMBER (7, 2),
COMM NUMBER (7, 2),
DEPTNO NUMBER (2)
)
TABLESPACE TBS_DATA
RESULT_CACHE (MODEDEFAULT );
CREATEUNIQUEINDEX PK_EMP ON EMP
(EMPNO )
TABLESPACE TBS_INDEX;
ALTERTABLE EMP ADD (
CONSTRAINT PK_EMP
PRIMARYKEY
(EMPNO )
USINGINDEX PK_EMP
);
复制表结构
createtable emp_copy asselect *from emp where 1=0;
注意：ctas 语法创建的表，只会复制非空约束，其它的比如主键、外键等不会复制。
创建了哪些对象 --查询当前用户有哪些表
SELECT *FROM user_tables t;
--查询当前用户有哪些视图
SELECT *FROM user_views t;
--查询当前用户有哪些索引
select *from user_indexes t;
--查询当前用户有哪些约束
select *from user_constraints t;
6. 约束
--约束 SELECT *FROM user_constraints t;
ALTERTABLE emp DISABLECONSTRAINT fk_deptno;
ALTERTABLE emp ENABLECONSTRAINT fk_deptno;
7. 数据库查询
示例SQL 中使用的表和数据
图6.1 DEPT 表
图6.2 EMP表
7.1 内联连 SELECT *FROM dept INNERJOIN emp ON dept.deptno = emp.deptno; SELECT *FROM dept, emp WHERE dept.deptno = emp.deptno;
7.2 左连接 SELECT *FROM dept LEFTJOIN emp ON dept.deptno = emp.deptno;
SELECT
*
FROM dept, emp WHERE dept.deptno = emp.deptno(+);
7.3 右连接 SELECT *FROM dept RIGHTJOIN emp ON dept.deptno = emp.deptno; SELECT *FROM dept, emp WHERE dept.deptno(+)= emp.deptno;
7.4 全连接
select *from dept fullJOIN emp ON dept.deptno = emp.deptno;
8. 索引
8.1
B 树索引
Normal indexes. (By default, Oracle Database creates B-tree indexes.)
在基数高，特征值强的列使用B 树索引，比如身份证、主键等。
8.2 位图索引 Bitmap indexes, which store rowids associated with a key value as a bitmap
在使用字典值的列使用函数索引，比如性别、城市、省份等。
8.3 分区索引 Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table。
Global Index: A global index is a one-to-many relationship, allowing one index
partition to map to many table partitions. The docs says that a"global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".
Local Index: A local index is a one-to-one mapping between a index partition and a
table partition. In general, local indexes allow for a cleaner "divide and conquer" approach for generating fast SQL execution plans with .
8.4 函数索引
Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
例如，使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引（function-based index）有助于与大小写无关（case-insensitive ）的查询。例如创建以下函数索引：
CREATEINDEX idx_fn_empn ON emp(upper (ename ));
有助于提高以下查询的性能：
SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD'; 8.5 查看索引
查询哪个用户的哪个表哪些列创建了什么索引，状态是否有效。 SELECT a.table_owner,
b.table_name,
b.column_name,
a.index_name,
a.index_type,
a.status
FROM user_indexes a
JOIN user_ind_columns b
ON a.index_name = b.index_name;
9. 执行计划
9.1 查看执行计划
在plsql 中选择SQL ，按F5
SELECT *FROM dept leftJOIN emp ON dept.deptno = emp.deptno;
9.2 SQL 优化的常见思路
1. 查看执行计划
2. 找出cost 列值最大的执行计划行，该行对应的表
3. 查看表的行数
4. 查看表列有没有索引，如果没有索引就创建；如果有就做表分析。
5. 重新查看执行计划，看cost 值有没有下降。
9.3 索引失效的常见情况
1.
2. 单独的>,
)
3. like "%_" 百分号在前.
4. 表没分析.
5. 单独引用复合索引里非第一位置的索引列.
6. 字符型字段为数字时在where 条件里不添加引号.
7. 对索引列进行运算. 需要建立函数索引.
8. not in ,not exist.
9. 当变量采用的是times 变量，而表的字段采用的是date 变量时. 或相反情况。
10. 索引失效。
11. 基于cost 成本分析(oracle因为走全表成本会更小) ：查询小表, 或者返回值大概在10%以上
12. 有时都考虑到了但就是不走索引,drop 了从建试试在
13. B-tree索引 is null不会走,is not null会走, 位图索引 is null,is not null 都会走
14. 联合索引 is not null 只要在建立的索引列（不分先后）都会走,
in null时 必须要和建立索引第一列一起使用, 当建立索引第一位置条件是is null 时, 其他建立索引的列可以是is null（但必须在所有列都满足is null的时候）, 或者=一个值；
当建立索引的第一位置是=一个值时, 其他索引列可以是任何情况（包括is null =一个值）, 以上两种情况索引都会走。其他情况不会走。
10. 常用SQL 优化hint 语句
在SQL 语句优化过程中，我们经常会用到hint, 现总结一下在SQL 优化过程中常见Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法, 并获得最佳吞吐量, 使资源消耗最小化. 例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法, 并获得最佳响应时间, 使资源消耗最小化. 例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
3. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息, 将基于开销的优化方法, 并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息, 将基于规则开销的优化方法; 例如:
SEL ECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
4. /*+RULE*/
表明对语句块选择基于规则的优化方法. 例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
5. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法. 例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;
6. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID 进行访问. 例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’ AND EMP_NO=’SCOTT’;
7. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法, 它只对簇对象有效. 例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法. 例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法. 例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;
10. /*+INDEX_COMBINE*/
为指定表选择位图访问路经, 如果INDEX_COMBINE中没有提供作为参数的索引, 将选择出位图索引的布尔组合方式. 例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径. 例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法. 例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描, 而不是全表扫描的办法. 例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择, 将几个单列索引的扫描合起来. 例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';
15. /*+USE_CONCAT*/
对查询中的WHERE 后面的OR 条件进行转换为UNION ALL的组合查询. 例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /*+NO_EXPAND*/
对于WHERE 后面的OR 或者IN-LIST 的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展. 例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /*+NOWRITE*/
禁止对查询块的查询重写操作.
18. /*+REWRITE*/
可以将视图作为参数.
19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并. 例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并. 例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根据表出现在FROM 中的顺序,ORDERED 使ORACLE 依此顺序对其连接. 例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE
A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接, 并把指定表作为内部表. 例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来. 例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
将指定的表与其他行源通过哈希连接方式连接起来. 例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/ BSEMPMS,BSDPTMS WHERE
强制与ORACLE 所选择的位置不同的表进行查询执行. 例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS
BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
将指定的表作为连接次序中的首表.
27. /*+CACHE(TABLE)*/ WHERE
当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU 的最近使用端。例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU 的最近使用端。例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最后, 可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;
第二部分进阶
11. 监控索引的使用
索引的维护需要一定的成本，为了找出有用的索引，安全的删除无用的索引，可以考虑索引监控。
The SQL engine maintains all indexes defined against a table regardless of their usage. Index
maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources. Index monitoring allows unused indexes to be identified accurately, removing the risks associated with dropping useful indexes.
It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. In a Decision Support System (DSS) this may involve a period of weeks or months, while OLTP systems typically have shorter cycles.
--查看有哪些表创建了索引
SELECT *FROM user_indexes t;
11.1 开启停止索引监控
--开启停用索引监控 ALTERINDEX pk_dept MONITORINGUSAGE ;
ALTERINDEX pk_dept NOMONITORINGUSAGE ;
11.2 查看索引监控情况
--查看索引监控情况 SELECT index_name,
table_name,
monitoring ,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE index_name ='PK_DEPT'
ORDERBY index_name;
12. 查询最耗资源的SQL
12.1 通过top 命令获取spid 号
先通过top 命令查看产用资源较多的spid 号
12.2 查询spid 对应的SQL SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECTdecode (sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode (sql_address,
utl_raw.cast_to_raw('00' ), prev_sql_addr,
sql_address)
FROM v$session b
WHERE b.paddr =
(SELECT addr FROM v$process c WHERE c.spid ='&pid')) ORDERBY piece ASC ;
12.3 查询最耗资源的SQL
--最占用资源的SQL
SELECT b.username,
a.disk_reads,
a.executions,
a.disk_reads /decode (a.executions , 0, 1, a.executions) rds_exec_ratio,
a.sql_text
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND a.disk_reads >1000
ORDERBY a.disk_reads DESC ;
--占用最多内存的SQL
SELECT b.username,
a.buffer_gets,
a.executions,
round (a.buffer_gets /decode (a.executions , 0, 1, a.executions), 2) rds_exec_ratio,
a.sql_text
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND a.disk_reads >1000
ORDERBY a.disk_reads DESC ;
--通过v$sql查询最占资源的SQL
SELECT *
FROM (SELECT a.sql_text,
rank ()over (ORDERBY buffer_gets DESC ) AS rank_bufgets,
to_char(100*
ratio_to_report(buffer_gets) over (),'999.99' ) pct_bufgets FROM v$sql a)
WHERE rank_bufgets
13. 杀会话kill session
kill session 是DBA 经常碰到的事情之一。如果kill 掉了不该kill 的session ，则具有破坏性，因此尽可能的避免这样的错误发生。注意，如果kill 的session 属于Oracle 后台进程，则容易导致数据库实例宕机。 13.1 获得Oracle 会话信息
--获得当前会话的SID
SELECTsidFROM v$mystat WHERErownum =1;
--根据username 、machine 、osuser 等获取会话信息
SELECT *
FROM v$session t
WHERE t.username ='ZHOUZH3' --oracle 用户名 AND t.machine ='WORKGROUP\DESKTOP-1LI6S5N' --机器名
AND t.osuser ='DESKTOP-1LI6S5N\Hegel'; --操作系统用户名
13.2 获得需要kill session的信息 SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.paddr,
s.status
FROM gv$session s
JOIN gv$process p
ON p.addr = s.paddr
AND p.inst_id
= s.inst_id
WHERE s.type !='BACKGROUND' ;
13.3 使用ALTER SYSTEM KILL SESSION 命令实现
语法：
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
13.4 在操作系统级别杀掉会话
killsession -9 spid
14. 绑定变量
14.1 SQL 的解析过程
①解析：安全性检查，语法检查；
②创建：评估多个执行计划，并选择一个最优的执行计划
③执行：捆绑变量，执行已经创建的执行计划
④获取：获取结果集，进行转换，排序等。
14.2 不同SQL 的写法 未使用绑定变量
SELECT *FROM emp t WHERE t.ename ='SMITH' ;
1->2->3->4
使用绑定变量
SELECT *FROM emp t WHERE t.ename =:v_name;
3->4
在JAVA 的角度，就是多使用PreparedStatement 。
问题：所有的SQL 都应该使用绑定变量吗？什么时候不该使用绑定变量？
15. 高水位线（High-Water Mark, HWM） 15.1 什么叫高水位线
定位：High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations. 高水位线就是某个数据段至今为止使用过的数据块最大值。删除操作不会重置这个值。 he database doesn't know what is on a block unless and until...... It goes to the block. So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does.
And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now. We won't know what is there until we read it.
Suppose you deleted all but ONE row - we have to look in every block (unless of course we use an index) to see if that row is on a given block.
This is a term used with table segments stored in the database. If you envision a table, for example, as a 'flat' structure or as a series of blocks laid one after the other in a line from left to right, the high-water mark (HWM) would be the rightmost block that ever contained data, as illustrated in Figure 12-1.
图12-1 HWM描述
15.2 如何判断高水位线
--1创建一个大表 createtable big_table asselect *from all_objects t; --2每执行一次，数据翻倍 insertinto big_table select *from big_table t; --3删除表中的数据 deletefrom big_table;
--统计数据有多少行 selectcount (*) cnt from big_table;
--3
使用完全计算法分析表，查看表统计信息前要先分析
ANALYZETABLE BIG_TABLE COMPUTESTATISTICS ;
--5查看表占用多少个块 SELECT t.table_name, blocks, empty_blocks, num_rows FROM user_tables t where t.TABLE_NAME ='BIG_TABLE';
SELECT t.blocks FROM user_segments t WHERE t.segment_name ='BIG_TABLE';
视图列含义：
user_tables.blocks : Number of used data blocks in
the table
user_tables.empty_blocks: Number of empty (never used) data blocks in the table
user_segments.blocks：Size, in Oracle blocks, of the segment
说明：SQL5中的blocks+empty_blocks = SQL6的blocks
其它列的含义详见：
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm#REFRN23243
--6实际使用了多少块
SELECTCOUNT (DISTINCT
dbms_rowid.rowid_relative_fno(ROWID )||
dbms_rowid.rowid_block_number(ROWID )) "Used" FROM big_table;
如果user_segments.blocks远大于 SQL6中的数据，那说明就是表处于高水平位线了。
15.3 如何消除高水位线（5种方法）
altertable big_table move ;
altertable big_table enablerowmovement ;
altertable big_table shrinkspace ;
rename big_table to big_table_copy;
createtable big_table ... --重新创建表
insertinto big_table select *from big_table_copy;
altertable big_table deallocateunused ;
truncatetable big_table;
16. 闪回
16.1 闪回的前提
数据库使用的是自动回滚段管理。 Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments.
数据可以被闪回的最长时间，是通过参数UNDO_RETENTION定义的。 --默认是900秒
select *from v$parameter t where t.NAME ='undo_retention';
16.2 闪回查询Flashback Query Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. --创建闪回测试表
createtable tt asselect *from all_objects whererownum
SELECT current_scn, to_char(systimestamp , 'YYYY-MM-DD
HH24:MI:SS')
FROM v$database;
--先查询，再删除部分数据
select tt.*,rowidfrom tt;
--根据时间或者scn 闪回查询
SELECT *FROM tt ASOFTIMESTAMPCAST (SYSDATE -1/24/60ASTIMESTAMP );
SELECT *FROM tt ASOFscn 2701940;
16.3 闪回删除的表Flashback Drop (Recycle Bin) In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The DROP TABLE ... PURGE option can be used to permanently drop a table.
DROPTABLE my_table PURGE ;
The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. This feature does not use flashback logs or undo, so it is independent of the other flashback technologies. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.
--清空加收站
PURGERECYCLEBIN ;
--创建测试表
CREATETABLE FLASHBACK_DROP
AS
SELECT *FROM emp;
--先删除表
DROPTABLE FLASHBACK_DROP;
--查询回收站
SELECT t.object_name
FROMrecyclebin t
WHERE t.original_name ='FLASHBACK_DROP';
--根据回收站的对象名查询
SELECT *FROM "BIN$ngyZBUtETe2wiG2mpCrKXA==$0";
--闪回删除的表
FLASHBACKTABLE FLASHBACK_DROP TOBEFOREDROP ;
--闪回删除的表，并重命名。（在同名表被多次删除时非常有用）
FLASHBACKTABLE FLASHBACK_DROP TOBEFOREDROPRENAMETO
FLASHBACK_DROP_OLD;
第三部分软件的使用
17. PLSQL 的使用 17.1 关键字自动替换
文件的AutoReplace.txt 的内容 se=select
de=delete
fr=from
sf=select * from
up=update
sr=select t.*, rowid from w=where
17.2 自动选择语句
18. PowerDesigner 的使用 18.1 逆向工程
很多项目有数据库，但是没有ER 图，可以通过逆向工程生成。
DCP 文件的配置
18.2 生成测试数据
我们创建数据库之后，为了开发需求生成一些测试数据。
18.3 生成表结构报告
19. 附录
推荐网址
数据库培训 第一部分基础知识
1. 软件安装
1.1 数据库软件安装
安装目录不能包含中文、空格
1.2 数据库实例安装
SGA 和PGA 的大小设置
2. 数据库TNS 的配置、检查
ORACLE_HOME
D:\app\hegel\product\12.1.0\dbhome_1
方法一：tnsnames.ora
TNS 配置文件
%ORACLE_HOME %\NETWORK\ADMIN\tnsnames.ora
CARD_185 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.100.185)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = oracledb)
)
)
方法二：Net Configuration Assistant
方法三：Oracle Net Manager
使用tnsping 测试
tnsping card_185
3. 表空间的创建
一般情况下是根据表空间来备份的，所以要把表中的数据和索引，存放在不同的表空间中。备份时只备份数据，索引只是重建。
数据表空间 CREATETABLESPACEKAYOU_DATA DATAFILE
'E:\APP\HEGEL\ORADATA\MOMA\KAYOU_DATA01.DBF'SIZE 2GAUTOEXTENDOFF 索引表空间 CREATETABLESPACEKAYOU_INDEX DATAFILE
'E:\APP\HEGEL\ORADATA\MOMA\KAYOU_INDEX01.DBF'SIZE 512MAUTOEXTENDOFF
4. 数据库用户的创建、赋权
创建用户 --创建用户
--DROP USER KAYOU CASCADE;
CREATEUSER KAYOU
IDENTIFIEDBY KAYOU DEFAULTTABLESPACE KAYOU_DATA
TEMPORARYTABLESPACE TEMP;
赋权 -- Grant/Revoke role privileges
grantconnectto KAYOU ;
grantresourceto KAYOU ;
grantdbato KAYOU ;
查询权限 --系统权限
select *from user_sys_privs;
--角色权限
select *from user_role_privs; --对象权限
select *from user_tab_privs;
--当前session 有哪些权限
select *from session_privs;
5. 数据库对象：表、视图
一般情况下是根据表空间来备份的，所以要把表中的数据和索引，存放在不同的表空间中。备份时只备份数据，索引只是重建。
创建表
我们示例账号scott 里的表
建表DEPT
CREATETABLE DEPT
(
DEPTNO NUMBER (2),
DNAME VARCHAR2(14BYTE ),
LOC VARCHAR2(13BYTE )
)
TABLESPACE TBS_DATA--创建表时，使用数据表空间
RESULT_CACHE (MODEDEFAULT );
CREATEUNIQUEINDEX PK_DEPT ON DEPT
(DEPTNO )
TABLESPACE TBS_INDEX; --创建索引时，使用索引表空间
ALTERTABLE DEPT ADD (
CONSTRAINT PK_DEPT
PRIMARYKEY
(DEPTNO )
USINGINDEX PK_DEPT
);
建表EMP CREATETABLE EMP
(
EMPNO NUMBER (4),
ENAME VARCHAR2(10BYTE ),
JOB VARCHAR2(9BYTE ),
MGR NUMBER (4),
HIREDATE DATE ,
SAL NUMBER (7, 2),
COMM NUMBER (7, 2),
DEPTNO NUMBER (2)
)
TABLESPACE TBS_DATA
RESULT_CACHE (MODEDEFAULT );
CREATEUNIQUEINDEX PK_EMP ON EMP
(EMPNO )
TABLESPACE TBS_INDEX;
ALTERTABLE EMP ADD (
CONSTRAINT PK_EMP
PRIMARYKEY
(EMPNO )
USINGINDEX PK_EMP
);
复制表结构
createtable emp_copy asselect *from emp where 1=0;
注意：ctas 语法创建的表，只会复制非空约束，其它的比如主键、外键等不会复制。
创建了哪些对象 --查询当前用户有哪些表
SELECT *FROM user_tables t;
--查询当前用户有哪些视图
SELECT *FROM user_views t;
--查询当前用户有哪些索引
select *from user_indexes t;
--查询当前用户有哪些约束
select *from user_constraints t;
6. 约束
--约束 SELECT *FROM user_constraints t;
ALTERTABLE emp DISABLECONSTRAINT fk_deptno;
ALTERTABLE emp ENABLECONSTRAINT fk_deptno;
7. 数据库查询
示例SQL 中使用的表和数据
图6.1 DEPT 表
图6.2 EMP表
7.1 内联连 SELECT *FROM dept INNERJOIN emp ON dept.deptno = emp.deptno; SELECT *FROM dept, emp WHERE dept.deptno = emp.deptno;
7.2 左连接 SELECT *FROM dept LEFTJOIN emp ON dept.deptno = emp.deptno;
SELECT
*
FROM dept, emp WHERE dept.deptno = emp.deptno(+);
7.3 右连接 SELECT *FROM dept RIGHTJOIN emp ON dept.deptno = emp.deptno; SELECT *FROM dept, emp WHERE dept.deptno(+)= emp.deptno;
7.4 全连接
select *from dept fullJOIN emp ON dept.deptno = emp.deptno;
8. 索引
8.1
B 树索引
Normal indexes. (By default, Oracle Database creates B-tree indexes.)
在基数高，特征值强的列使用B 树索引，比如身份证、主键等。
8.2 位图索引 Bitmap indexes, which store rowids associated with a key value as a bitmap
在使用字典值的列使用函数索引，比如性别、城市、省份等。
8.3 分区索引 Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table。
Global Index: A global index is a one-to-many relationship, allowing one index
partition to map to many table partitions. The docs says that a"global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table".
Local Index: A local index is a one-to-one mapping between a index partition and a
table partition. In general, local indexes allow for a cleaner "divide and conquer" approach for generating fast SQL execution plans with .
8.4 函数索引
Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
例如，使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引（function-based index）有助于与大小写无关（case-insensitive ）的查询。例如创建以下函数索引：
CREATEINDEX idx_fn_empn ON emp(upper (ename ));
有助于提高以下查询的性能：
SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD'; 8.5 查看索引
查询哪个用户的哪个表哪些列创建了什么索引，状态是否有效。 SELECT a.table_owner,
b.table_name,
b.column_name,
a.index_name,
a.index_type,
a.status
FROM user_indexes a
JOIN user_ind_columns b
ON a.index_name = b.index_name;
9. 执行计划
9.1 查看执行计划
在plsql 中选择SQL ，按F5
SELECT *FROM dept leftJOIN emp ON dept.deptno = emp.deptno;
9.2 SQL 优化的常见思路
1. 查看执行计划
2. 找出cost 列值最大的执行计划行，该行对应的表
3. 查看表的行数
4. 查看表列有没有索引，如果没有索引就创建；如果有就做表分析。
5. 重新查看执行计划，看cost 值有没有下降。
9.3 索引失效的常见情况
1.
2. 单独的>,
)
3. like "%_" 百分号在前.
4. 表没分析.
5. 单独引用复合索引里非第一位置的索引列.
6. 字符型字段为数字时在where 条件里不添加引号.
7. 对索引列进行运算. 需要建立函数索引.
8. not in ,not exist.
9. 当变量采用的是times 变量，而表的字段采用的是date 变量时. 或相反情况。
10. 索引失效。
11. 基于cost 成本分析(oracle因为走全表成本会更小) ：查询小表, 或者返回值大概在10%以上
12. 有时都考虑到了但就是不走索引,drop 了从建试试在
13. B-tree索引 is null不会走,is not null会走, 位图索引 is null,is not null 都会走
14. 联合索引 is not null 只要在建立的索引列（不分先后）都会走,
in null时 必须要和建立索引第一列一起使用, 当建立索引第一位置条件是is null 时, 其他建立索引的列可以是is null（但必须在所有列都满足is null的时候）, 或者=一个值；
当建立索引的第一位置是=一个值时, 其他索引列可以是任何情况（包括is null =一个值）, 以上两种情况索引都会走。其他情况不会走。
10. 常用SQL 优化hint 语句
在SQL 语句优化过程中，我们经常会用到hint, 现总结一下在SQL 优化过程中常见Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法, 并获得最佳吞吐量, 使资源消耗最小化. 例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法, 并获得最佳响应时间, 使资源消耗最小化. 例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
3. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息, 将基于开销的优化方法, 并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息, 将基于规则开销的优化方法; 例如:
SEL ECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
4. /*+RULE*/
表明对语句块选择基于规则的优化方法. 例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
5. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法. 例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;
6. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID 进行访问. 例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’ AND EMP_NO=’SCOTT’;
7. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法, 它只对簇对象有效. 例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法. 例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法. 例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;
10. /*+INDEX_COMBINE*/
为指定表选择位图访问路经, 如果INDEX_COMBINE中没有提供作为参数的索引, 将选择出位图索引的布尔组合方式. 例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径. 例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法. 例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描, 而不是全表扫描的办法. 例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择, 将几个单列索引的扫描合起来. 例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';
15. /*+USE_CONCAT*/
对查询中的WHERE 后面的OR 条件进行转换为UNION ALL的组合查询. 例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /*+NO_EXPAND*/
对于WHERE 后面的OR 或者IN-LIST 的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展. 例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /*+NOWRITE*/
禁止对查询块的查询重写操作.
18. /*+REWRITE*/
可以将视图作为参数.
19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并. 例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并. 例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根据表出现在FROM 中的顺序,ORDERED 使ORACLE 依此顺序对其连接. 例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE
A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接, 并把指定表作为内部表. 例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来. 例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
将指定的表与其他行源通过哈希连接方式连接起来. 例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/ BSEMPMS,BSDPTMS WHERE
强制与ORACLE 所选择的位置不同的表进行查询执行. 例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS
BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
将指定的表作为连接次序中的首表.
27. /*+CACHE(TABLE)*/ WHERE
当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU 的最近使用端。例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE 提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU 的最近使用端。例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最后, 可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4 ;
第二部分进阶
11. 监控索引的使用
索引的维护需要一定的成本，为了找出有用的索引，安全的删除无用的索引，可以考虑索引监控。
The SQL engine maintains all indexes defined against a table regardless of their usage. Index
maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources. Index monitoring allows unused indexes to be identified accurately, removing the risks associated with dropping useful indexes.
It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations. In a Decision Support System (DSS) this may involve a period of weeks or months, while OLTP systems typically have shorter cycles.
--查看有哪些表创建了索引
SELECT *FROM user_indexes t;
11.1 开启停止索引监控
--开启停用索引监控 ALTERINDEX pk_dept MONITORINGUSAGE ;
ALTERINDEX pk_dept NOMONITORINGUSAGE ;
11.2 查看索引监控情况
--查看索引监控情况 SELECT index_name,
table_name,
monitoring ,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE index_name ='PK_DEPT'
ORDERBY index_name;
12. 查询最耗资源的SQL
12.1 通过top 命令获取spid 号
先通过top 命令查看产用资源较多的spid 号
12.2 查询spid 对应的SQL SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECTdecode (sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode (sql_address,
utl_raw.cast_to_raw('00' ), prev_sql_addr,
sql_address)
FROM v$session b
WHERE b.paddr =
(SELECT addr FROM v$process c WHERE c.spid ='&pid')) ORDERBY piece ASC ;
12.3 查询最耗资源的SQL
--最占用资源的SQL
SELECT b.username,
a.disk_reads,
a.executions,
a.disk_reads /decode (a.executions , 0, 1, a.executions) rds_exec_ratio,
a.sql_text
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND a.disk_reads >1000
ORDERBY a.disk_reads DESC ;
--占用最多内存的SQL
SELECT b.username,
a.buffer_gets,
a.executions,
round (a.buffer_gets /decode (a.executions , 0, 1, a.executions), 2) rds_exec_ratio,
a.sql_text
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND a.disk_reads >1000
ORDERBY a.disk_reads DESC ;
--通过v$sql查询最占资源的SQL
SELECT *
FROM (SELECT a.sql_text,
rank ()over (ORDERBY buffer_gets DESC ) AS rank_bufgets,
to_char(100*
ratio_to_report(buffer_gets) over (),'999.99' ) pct_bufgets FROM v$sql a)
WHERE rank_bufgets
13. 杀会话kill session
kill session 是DBA 经常碰到的事情之一。如果kill 掉了不该kill 的session ，则具有破坏性，因此尽可能的避免这样的错误发生。注意，如果kill 的session 属于Oracle 后台进程，则容易导致数据库实例宕机。 13.1 获得Oracle 会话信息
--获得当前会话的SID
SELECTsidFROM v$mystat WHERErownum =1;
--根据username 、machine 、osuser 等获取会话信息
SELECT *
FROM v$session t
WHERE t.username ='ZHOUZH3' --oracle 用户名 AND t.machine ='WORKGROUP\DESKTOP-1LI6S5N' --机器名
AND t.osuser ='DESKTOP-1LI6S5N\Hegel'; --操作系统用户名
13.2 获得需要kill session的信息 SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.paddr,
s.status
FROM gv$session s
JOIN gv$process p
ON p.addr = s.paddr
AND p.inst_id
= s.inst_id
WHERE s.type !='BACKGROUND' ;
13.3 使用ALTER SYSTEM KILL SESSION 命令实现
语法：
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
13.4 在操作系统级别杀掉会话
killsession -9 spid
14. 绑定变量
14.1 SQL 的解析过程
①解析：安全性检查，语法检查；
②创建：评估多个执行计划，并选择一个最优的执行计划
③执行：捆绑变量，执行已经创建的执行计划
④获取：获取结果集，进行转换，排序等。
14.2 不同SQL 的写法 未使用绑定变量
SELECT *FROM emp t WHERE t.ename ='SMITH' ;
1->2->3->4
使用绑定变量
SELECT *FROM emp t WHERE t.ename =:v_name;
3->4
在JAVA 的角度，就是多使用PreparedStatement 。
问题：所有的SQL 都应该使用绑定变量吗？什么时候不该使用绑定变量？
15. 高水位线（High-Water Mark, HWM） 15.1 什么叫高水位线
定位：High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations. 高水位线就是某个数据段至今为止使用过的数据块最大值。删除操作不会重置这个值。 he database doesn't know what is on a block unless and until...... It goes to the block. So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does.
And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now. We won't know what is there until we read it.
Suppose you deleted all but ONE row - we have to look in every block (unless of course we use an index) to see if that row is on a given block.
This is a term used with table segments stored in the database. If you envision a table, for example, as a 'flat' structure or as a series of blocks laid one after the other in a line from left to right, the high-water mark (HWM) would be the rightmost block that ever contained data, as illustrated in Figure 12-1.
图12-1 HWM描述
15.2 如何判断高水位线
--1创建一个大表 createtable big_table asselect *from all_objects t; --2每执行一次，数据翻倍 insertinto big_table select *from big_table t; --3删除表中的数据 deletefrom big_table;
--统计数据有多少行 selectcount (*) cnt from big_table;
--3
使用完全计算法分析表，查看表统计信息前要先分析
ANALYZETABLE BIG_TABLE COMPUTESTATISTICS ;
--5查看表占用多少个块 SELECT t.table_name, blocks, empty_blocks, num_rows FROM user_tables t where t.TABLE_NAME ='BIG_TABLE';
SELECT t.blocks FROM user_segments t WHERE t.segment_name ='BIG_TABLE';
视图列含义：
user_tables.blocks : Number of used data blocks in
the table
user_tables.empty_blocks: Number of empty (never used) data blocks in the table
user_segments.blocks：Size, in Oracle blocks, of the segment
说明：SQL5中的blocks+empty_blocks = SQL6的blocks
其它列的含义详见：
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4097.htm#REFRN23243
--6实际使用了多少块
SELECTCOUNT (DISTINCT
dbms_rowid.rowid_relative_fno(ROWID )||
dbms_rowid.rowid_block_number(ROWID )) "Used" FROM big_table;
如果user_segments.blocks远大于 SQL6中的数据，那说明就是表处于高水平位线了。
15.3 如何消除高水位线（5种方法）
altertable big_table move ;
altertable big_table enablerowmovement ;
altertable big_table shrinkspace ;
rename big_table to big_table_copy;
createtable big_table ... --重新创建表
insertinto big_table select *from big_table_copy;
altertable big_table deallocateunused ;
truncatetable big_table;
16. 闪回
16.1 闪回的前提
数据库使用的是自动回滚段管理。 Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments.
数据可以被闪回的最长时间，是通过参数UNDO_RETENTION定义的。 --默认是900秒
select *from v$parameter t where t.NAME ='undo_retention';
16.2 闪回查询Flashback Query Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. --创建闪回测试表
createtable tt asselect *from all_objects whererownum
SELECT current_scn, to_char(systimestamp , 'YYYY-MM-DD
HH24:MI:SS')
FROM v$database;
--先查询，再删除部分数据
select tt.*,rowidfrom tt;
--根据时间或者scn 闪回查询
SELECT *FROM tt ASOFTIMESTAMPCAST (SYSDATE -1/24/60ASTIMESTAMP );
SELECT *FROM tt ASOFscn 2701940;
16.3 闪回删除的表Flashback Drop (Recycle Bin) In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The DROP TABLE ... PURGE option can be used to permanently drop a table.
DROPTABLE my_table PURGE ;
The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. This feature does not use flashback logs or undo, so it is independent of the other flashback technologies. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.
--清空加收站
PURGERECYCLEBIN ;
--创建测试表
CREATETABLE FLASHBACK_DROP
AS
SELECT *FROM emp;
--先删除表
DROPTABLE FLASHBACK_DROP;
--查询回收站
SELECT t.object_name
FROMrecyclebin t
WHERE t.original_name ='FLASHBACK_DROP';
--根据回收站的对象名查询
SELECT *FROM "BIN$ngyZBUtETe2wiG2mpCrKXA==$0";
--闪回删除的表
FLASHBACKTABLE FLASHBACK_DROP TOBEFOREDROP ;
--闪回删除的表，并重命名。（在同名表被多次删除时非常有用）
FLASHBACKTABLE FLASHBACK_DROP TOBEFOREDROPRENAMETO
FLASHBACK_DROP_OLD;
第三部分软件的使用
17. PLSQL 的使用 17.1 关键字自动替换
文件的AutoReplace.txt 的内容 se=select
de=delete
fr=from
sf=select * from
up=update
sr=select t.*, rowid from w=where
17.2 自动选择语句
18. PowerDesigner 的使用 18.1 逆向工程
很多项目有数据库，但是没有ER 图，可以通过逆向工程生成。
DCP 文件的配置
18.2 生成测试数据
我们创建数据库之后，为了开发需求生成一些测试数据。
18.3 生成表结构报告
19. 附录
推荐网址