常用SQL

批量处理带有层级关系的表数据

--根据编码中-字符更新lngparentid
UPDATE DEPARTMENT T
   SET T.LNGPARENTID =
       (SELECT NVL(MIN(P.LNGDEPARTMENTID), 0)
          FROM DEPARTMENT P
         WHERE P.BLNISVOID = 0
           AND P.STRDEPARTMENTCODE =
               SUBSTR(T.STRDEPARTMENTCODE, 0,
                      INSTR(T.STRDEPARTMENTCODE, '-', -1, 1) - 1))
 WHERE T.BLNISVOID = 0;

--更新层级、是否末级
UPDATE DEPARTMENT T
   SET T.INTLEVEL   =
       (SELECT DECODE(COUNT(P.LNGDEPARTMENTID), 0, 1, COUNT(P.LNGDEPARTMENTID))
          FROM DEPARTMENT P
         START WITH P.LNGDEPARTMENTID = T.LNGDEPARTMENTID AND P.BLNISVOID = 0
        CONNECT BY PRIOR P.LNGPARENTID = P.LNGDEPARTMENTID),
       T.BLNISDETAIL =
       (SELECT DECODE(COUNT(P.LNGDEPARTMENTID), 0, 1, 0)
          FROM DEPARTMENT P
         START WITH P.LNGPARENTID = T.LNGDEPARTMENTID AND P.BLNISVOID = 0
        CONNECT BY PRIOR P.LNGPARENTID = P.LNGDEPARTMENTID)
 WHERE T.BLNISVOID = 0;

--更新物化路径和全名称
UPDATE DEPARTMENT T
   SET T.PATH =
       (SELECT LISTAGG(P.LNGDEPARTMENTID, '/') WITHIN GROUP(ORDER BY P.INTLEVEL) || '/'
          FROM DEPARTMENT P
         START WITH P.LNGDEPARTMENTID = T.LNGDEPARTMENTID AND P.BLNISVOID = 0
        CONNECT BY PRIOR P.LNGPARENTID = P.LNGDEPARTMENTID),
       T.STRFULLNAME =
       (SELECT LISTAGG(P.STRDEPARTMENTNAME, '/') WITHIN GROUP(ORDER BY P.INTLEVEL) || '/'
          FROM DEPARTMENT P
         START WITH P.LNGDEPARTMENTID = T.LNGDEPARTMENTID AND P.BLNISVOID = 0
        CONNECT BY PRIOR P.LNGPARENTID = P.LNGDEPARTMENTID)
 WHERE T.BLNISVOID = 0;

重建失效索引

select 'alter index ' || index_name || ' rebuild;', s.*
  from user_indexes s
 where s.status != 'VALID';

恢复误删除数据

如果是update、delete操作导致数据误操作且已经commit，可以使用以下方式恢复：

-- 查询该时间节点之前的数据
 select * from table_name as of timestamp to_timestamp('2024-01-01 10:00:00','yyyy-mm-dd hh24:mi:ss');

如果是drop或truncate操作导致数据误操作，则不能使用闪回查询的方式恢复。此时只能通过备份数据文件的方式恢复。

查询和解除锁表

select s.schemaname,
    o.object_name,
    s.machine,
    s.sid,
    s.serial#,
    'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || ''';' as strsql
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id　 = 　o.object_id
   and l.session_id = s.sid;

创建用户，指定表空间

--查询表空间物理存储地址
select * from dba_data_files;
--表空间和临时表空间
DECLARE
  TABLESPACE_PREFIX VARCHAR2(100);
  TABLESPACE_PATH   VARCHAR2(100);
  VL_NUM            NUMBER(8);
  STRSQL_CREATE     CLOB;
BEGIN
  TABLESPACE_PREFIX := 'DEMO';
  TABLESPACE_PATH   := '/opt/app/oracle/oradata/orcl/';
  -- 创建表空间
  SELECT COUNT(*) INTO VL_NUM FROM DBA_TABLESPACES
   WHERE TABLESPACE_NAME = UPPER(TABLESPACE_PREFIX) || '_DATA';
  IF (VL_NUM = 0) THEN
    STRSQL_CREATE := 'CREATE TABLESPACE ' || UPPER(TABLESPACE_PREFIX) || '_DATA '
                     ||'DATAFILE ''' || TABLESPACE_PATH || UPPER(TABLESPACE_PREFIX) || '_DATA.DBF'' SIZE 100M '
                     ||'AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
    EXECUTE IMMEDIATE STRSQL_CREATE;
  END IF;
  -- 创建临时表空间
  SELECT COUNT(*) INTO VL_NUM FROM DBA_TABLESPACES
   WHERE TABLESPACE_NAME = UPPER(TABLESPACE_PREFIX) || '_TEMP';
  IF (VL_NUM = 0) THEN
    STRSQL_CREATE := 'CREATE TEMPORARY TABLESPACE ' || UPPER(TABLESPACE_PREFIX) || '_TEMP '
                     ||'TEMPFILE ''' || TABLESPACE_PATH || UPPER(TABLESPACE_PREFIX) || '_TEMP.DBF'' SIZE 100M '
                     ||'AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
    EXECUTE IMMEDIATE STRSQL_CREATE;
  END IF;
END;
/
--创建用户
CREATE USER DEMO IDENTIFIED BY 123456
DEFAULT TABLESPACE DEMO_DATA
TEMPORARY TABLESPACE DEMO_TEMP
PROFILE DEFAULT;
--授权
GRANT CONNECT TO DEMO;
GRANT RESOURCE TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO;
GRANT DBA TO DEMO;