一、查看被鎖的表

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

二、解鎖表語句

alter system kill session '#SID#,#serial#';

三、被鎖的表生成批量解表語句

select DISTINCT 'alter system kill session ''' ||  sess.sid || ',' || sess.serial# || ''';' 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

四、查看用戶連接數

SELECT machine,terminal,osuser,program,process,logon_time,username,command,sql_hash_value FROM v$session 

v$session:

這個視圖主要提供的是一個數據庫connect的信息，
主要是client端的信息，比如以下字段：
machine 在哪臺機器上
terminal 使用什么終端
osuser 操作系統(tǒng)用戶是誰
program 通過什么客戶端程序，比如TOAD
process 操作系統(tǒng)分配給TOAD的進程號
ogon_time 在什么時間
username 以什么oracle的帳號登錄
command 執(zhí)行了什么類型的SQL命令
sql_hash_value SQL語句信息

五、檢驗數據庫是否存在此表

# TABLE_NAME（表名）
# OWNER（所屬用戶下）
SELECT COUNT(*) COUNT FROM ALL_TABLES WHERE 1=1 AND TABLE_NAME = UPPER('MS_TCJ_620546I01') AND OWNER = UPPER('JHYSC')

六、查詢當前月份

AND TO_CHAR(wt.DELEGATE_TIME, 'YYYY-MM')= TO_CHAR(SYSDATE, 'YYYY-MM')

七、Oracle 分頁

    /**
     * 將查詢sql轉換為分頁sql
     * @param sql {查詢sql}
     * @param offset {開始行數}
     * @param limit {截至行數}
     * @return {查詢中不可以有相同的字段名}
     */
    public static String limitSql(String sql,Integer offset,Integer limit) {
        if (isEmpty(sql)) return sql;
        String limitSql = "SELECT *  FROM ( SELECT row_.*, ROWNUM rownum_ FROM ( " +
                    sql +
                " ) row_ ) WHERE rownum_ > " + (offset!=null?offset:0) +
                " AND rownum_ <= " + (limit!=null?limit:10);
        return limitSql;
    }
    @Test
    public void test_029(){
        String sql = "SELECT * FROM AGJH.MS_TYLZP01";
        System.out.println(CPUtil.limitSql(sql, 0, 10));
    }

八、Oracle恢復表數據指定表指定時間

select * from MS_GRID_COLUMN  as of timestamp to_timestamp('20220926163700','YYYYMMDD HH24:MI:SS');
alter table MS_GRID_COLUMN enable row movement;--開啟
flashback table MS_GRID_COLUMN to timestamp to_timestamp('20220926163700','YYYYMMDD HH24:MI:SS');
ALTER TABLE MS_GRID_COLUMN DISABLE row movement ;--關閉

九、Oracle刪除表中重復的數據

SELECT * FROM XS_USER WHERE LOGIN_NAME IN(SELECT LOGIN_NAME FROM XS_USER GROUP BY LOGIN_NAME HAVING COUNT(LOGIN_NAME) > 1) AND ROWID NOT IN(SELECT MIN(ROWID) FROM XS_USER GROUP BY LOGIN_NAME HAVING COUNT(LOGIN_NAME)>1) FOR UPDATE  

十、Oracle刪除表中重復的數據

SELECT * FROM XS_USER WHERE LOGIN_NAME IN(SELECT LOGIN_NAME FROM XS_USER GROUP BY LOGIN_NAME HAVING COUNT(LOGIN_NAME) > 1) AND ROWID NOT IN(SELECT MIN(ROWID) FROM XS_USER GROUP BY LOGIN_NAME HAVING COUNT(LOGIN_NAME)>1) FOR UPDATE