OracleDatabase

结构

log文件

分区

回滚段：保留历史数据，一次commit一个版本（一致性读）
数据文件：所有数据随机存储（数据入库时：磁头反复横跳，速度慢）
Redo.log：所有改动日志有序存储（速度快）

xx

数据文件中的数据是分散储存的，Redo.log中的数据时顺序存储的，这点很重要。

• Redo Buffer：数据入库频繁，保证数据安全
• Commit之后：Redo Buffer中的数据先入库
• Rollback：从回滚段读取旧数据进行数据覆盖
• 数据恢复：先读取Redo.log中的文件进行恢复。再读取Undo进行恢复。因为Redo保存了Undo的变更记录，这也是Redo入库频繁的原因
• 读写不冲突：因为有Undo。当数据被更新时可以从Undo中读取某个版本的数据

---

SQL

mybatis-xml文件sql末尾不要分号“;”

闪回查询

历史上某个时间点的数据

-- 闪回查询
UPDATE SCOTT.EMP SET SAL = 801.00 WHERE EMPNO = 7369;
COMMIT;
SELECT * FROM SCOTT.EMP WHERE EMPNO = '7369';
-- 闪回查询(历史上某个时间点对应的数据状态)
SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2022-08-06 20:12:09', 'YYYY-MM-DD HH24:MI:SS');

闪回表

把表中的数据恢复到历史上某个时间点对应的数据状态

-- 闪回表
UPDATE SCOTT.EMP SET SAL = 805.00 WHERE EMPNO = 7369;
COMMIT;
SELECT * FROM SCOTT.EMP WHERE EMPNO = 7369;
ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMENT;
FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP TO_TIMESTAMP('2022-08-06 21:03:01', 'YYYY-MM-DD HH24:MI:SS');
COMMIT;
SELECT * FROM SCOTT.EMP WHERE EMPNO = 7369;

MERGE INTO

使用scott用户下的DEPT表，如果部门编号DEPTNO存在则更新部门信息LOC。

MERGE INTO DEPT mainTable
USING (SELECT 10 AS temp_id, '北京' AS temp_loc
       FROM DUAL) temp
ON (mainTable.DEPTNO = temp.temp_id)
WHEN MATCHED THEN
    UPDATE
    SET mainTable.LOC = temp.temp_loc
WHEN NOT MATCHED THEN
    INSERT (mainTable.DEPTNO, mainTable.LOC)
    VALUES (10, '北京');

锁

UPDATE SCOTT.DEPT SET LOC = '北京' WHERE DEPTNO = 10;
-- 不提交 COMMIT;

-- 查看被锁的object, 用户session
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE B.OBJECT_ID = B.OBJECT_ID;

-- 查看那个用户 那个进程造成死锁
SELECT B.USERNAME, B.SID, B.SERIAL#, B.LOGON_TIME FROM V$LOCKED_OBJECT A, V$SESSION  B
WHERE A.SESSION_ID = B.SID ORDER BY B.LOGON_TIME;
COMMIT;

-- kill session
ALTER SYSTEM KILL SESSION '242,179';

两条常用SQL

scott用户下的EMP表：

public interface EmpMapper extends BaseMapper<Emp> {

    /**
     * 批量插入
     * @param entities
     */
    void insertList(@Param("entities") List<Emp> entities);

    /**
     * 插入或更新
     * @param entity
     */
    void mergeInto(@Param("entity") Emp entity);
}

-- 创建一个序列，下面会使用
CREATE SEQUENCE TABLE_SEQUENCE_EMP INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10;

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="aa.slkIE.datas.oracle.webs.mapper.EmpMapper">

    <resultMap id="BaseResultMap" type="aa.slkIE.datas.oracle.webs.domain.Emp">
        <id property="empno" column="EMPNO" jdbcType="DECIMAL"/>
        <result property="ename" column="ENAME" jdbcType="VARCHAR"/>
        <result property="job" column="JOB" jdbcType="VARCHAR"/>
        <result property="mgr" column="MGR" jdbcType="DECIMAL"/>
        <result property="hiredate" column="HIREDATE" jdbcType="TIMESTAMP"/>
        <result property="sal" column="SAL" jdbcType="DECIMAL"/>
        <result property="comm" column="COMM" jdbcType="DECIMAL"/>
        <result property="deptno" column="DEPTNO" jdbcType="DECIMAL"/>
    </resultMap>

    <sql id="Base_Column_List">
        EMPNO,ENAME,JOB,
        MGR,HIREDATE,SAL,
        COMM,DEPTNO
    </sql>

    <!--@formatter:off-->
    <!--批量插入-->
    <insert id="insertList" parameterType="java.util.List">
        insert into scott.EMP (<include refid="Base_Column_List"/>)
        select TABLE_SEQUENCE_EMP.nextval SEQ_NO, T.* from (
        <foreach collection="entities" item="entity" separator="union all">
            SELECT
            #{entity.ename, jdbcType=VARCHAR},
            #{entity.job, jdbcType=VARCHAR},
            #{entity.mgr, jdbcType=DECIMAL},
            #{entity.hiredate, jdbcType=TIMESTAMP},
            #{entity.sal, jdbcType=DECIMAL},
            #{entity.comm, jdbcType=DECIMAL},
            #{entity.deptno, jdbcType=DECIMAL}
            FROM DUAL
        </foreach>
        ) T
    </insert>

    <!--插入或更新-->
    <update id="mergeInto" parameterType="aa.slkIE.datas.oracle.webs.domain.Emp">
        merge into scott.EMP T
        USING (SELECT #{entity.empno} AS id  FROM DUAL ) S ON
        (T.EMPNO=S.id)
        WHEN MATCHED THEN
        UPDATE
        <set>/*去除多余的 逗号*/
        <if test=" entity.ename != null and entity.ename != '' ">T.ENAME=#{entity.ename, jdbcType=VARCHAR},</if>
        <if test=" entity.job != null and entity.job != '' ">T.JOB=#{entity.job, jdbcType=VARCHAR},</if>
        <if test=" entity.mgr != null and entity.mgr != '' ">T.MGR=#{entity.mgr, jdbcType=DECIMAL},</if>
        <if test=" entity.hiredate != null and entity.hiredate != '' ">T.HIREDATE=#{entity.hiredate, jdbcType=TIMESTAMP},</if>
        <if test=" entity.sal != null and entity.sal != '' ">T.SAL=#{entity.sal, jdbcType=DECIMAL},</if>
        <if test=" entity.comm != null and entity.comm != '' ">T.COMM=#{entity.comm, jdbcType=DECIMAL},</if>
        <if test=" entity.deptno != null and entity.deptno != '' ">T.DEPTNO=#{entity.deptno, jdbcType=DECIMAL},</if>
        </set>
        WHEN NOT MATCHED THEN
        insert (<include refid="Base_Column_List"/>)
        values (
        TABLE_SEQUENCE_EMP.nextval,
        #{entity.ename, jdbcType=VARCHAR},
        #{entity.job, jdbcType=VARCHAR},
        #{entity.mgr, jdbcType=DECIMAL},
        #{entity.hiredate, jdbcType=TIMESTAMP},
        #{entity.sal, jdbcType=DECIMAL},
        #{entity.comm, jdbcType=DECIMAL},
        #{entity.deptno, jdbcType=DECIMAL}
        )

    </update>
    <!--@formatter:on-->
</mapper>