一、查看被锁的表

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 操作系统用户是谁
program 通过什么客户端程序，比如TOAD
process 操作系统分配给TOAD的进程号
ogon_time 在什么时间
username 以什么oracle的帐号登录
command 执行了什么类型的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