常用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 object_name,
       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
  VL_NUM        NUMBER(8);
  STRSQL_CREATE VARCHAR2(1024);
BEGIN
  SELECT COUNT(*) INTO VL_NUM FROM DBA_TABLESPACES T WHERE T.TABLESPACE_NAME='DEMO_DATA';
  IF (VL_NUM = 0) THEN
    STRSQL_CREATE := '
CREATE TABLESPACE DEMO_DATA 
DATAFILE ''/db/app/oracle/oradata/orcl/DEMO_DATA.DBF'' SIZE 100M 
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL';
    EXECUTE IMMEDIATE STRSQL_CREATE;
  END IF;
END;
/
--临时表空间
DECLARE
  VL_NUM        NUMBER(8);
  STRSQL_CREATE VARCHAR2(1024);
BEGIN
  SELECT COUNT(*) INTO VL_NUM FROM DBA_TABLESPACES T WHERE T.TABLESPACE_NAME='DEMO_TEMP';
  IF (VL_NUM = 0) THEN
    STRSQL_CREATE := '
CREATE TEMPORARY TABLESPACE DEMO_TEMP 
TEMPFILE ''/db/app/oracle/oradata/orcl/DEMO_TEMP.DBF'' SIZE 100M 
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL';
    EXECUTE IMMEDIATE STRSQL_CREATE;
  END IF;
END;
/
--创建用户
DECLARE
  VL_NUM        NUMBER(8);
  STRSQL_CREATE VARCHAR2(1024);
BEGIN
  SELECT COUNT(*) INTO VL_NUM FROM ALL_USERS WHERE USERNAME = 'DEMO';
  IF (VL_NUM = 0) THEN
    STRSQL_CREATE := '
CREATE USER DEMO
  IDENTIFIED BY 123456
  DEFAULT TABLESPACE DEMO_DATA
  TEMPORARY TABLESPACE DEMO_TEMP
  PROFILE DEFAULT';
    EXECUTE IMMEDIATE STRSQL_CREATE;
  END IF;
END;
/
--授权
GRANT CONNECT TO DEMO;
GRANT RESOURCE TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO;
GRANT DBA TO DEMO;