常用sql汇总

• 关于表结构
  1. 增加一个列
     • ALTER TABLE 表名 ADD(列名 数据类型);
     • 如：ALTER TABLE emp ADD(license varchar2(256));
  2. 修改一个列的数据模型
     • ALTER TABLE 表名 MODIFY(列名 数据类型);
     • ALTER TABLE emp MODIFY(weight NUMBER(3,0) NOT NULL);
  3. 修改列名
     • ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;
     • 如：ALTER TABLE emp RENAME COLUMN abcTO abc_new;
  4. 删除列
     • ALTER TABLE 表名 DROP COLUMN 列名;
     • 如：ALTER TABLE emp DROP COLUMN memo;
  5. 修改表名
     • ALTER TABLE 当前表名 RENAME TO 新表名;
     • 如：ALTER TABLE emp RENAME TO emp_new;
  6. 修改字段类型
     • ALTER TABLE 表名 MODIFY 列名 数据类型(长度);
     • 如：ALTER TABLE emp MOFIFY username varchar2(20);

 

 

• 关于数据
  1. 生成随机数
     • select to_char(sysdate,'yyyymmdd')||lpad(round(dbms_random.value(1,999999999)),9,0) from dual;
  2. 递归查询
     • 查询父节点下的子节点
       • SELECT * FROM ftzj_dept START WITH dept_id=415514 CONNECT BY PRIOR dept_id = father_id
     • 查询子节点上所有根节点
       • SELECT * FROM  ftzj_dept CONNECT BY PRIOR father_id = dept_id START WITH dept_id =414540;
  3. 误删数据与恢复
     • 查询删除数据的时间点的数据
       
       • select * from 表名 as of timestamp to_timestamp('2017-11-29 15:29:00','yyyy-mm-dd hh24:mi:ss');  (如果不是，则继续缩小范围)
     • 恢复删除且已提交的数据
       • flashback table 表名 to timestamp to_timestamp('2017-11-29 15:29:00','yyyy-mm-dd hh24:mi:ss');
       • 注意：如果在执行上面的语句，出现错误。可以尝试执行 alter table 表名 enable row movement; //允许更改时间戳

 

• 关于锁表与解锁
  1. 查看锁表进程SQL语句1：

     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;

  2. 查看锁表进程SQL语句2： 
     select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

  3. 查看锁表进程SQL语句3：
     SELECT object_name, machine, s.sid, s.serial#
     FROM gv$locked_object l, dba_objects o, gv$session s
     WHERE l.OBJECT_ID = o.OBJECT_ID
     AND l.SESSION_ID = s.SID;

  4. 杀掉锁表进程： 
     如有记录则表示有lock，记录下SID和serial# ，将记录的ID替换下面的738,1429，即可解除LOCK 
     alter system kill session '738,1429';