sql语句整理

新增字段

alter table 表名 add 字段 varchar(50) ;
comment on column 表名.字段 is '备注';

给字段扩容

alter table 表名 modify 字段 varchar2(20);

删除字段

 alter table 表 drop column 字段;

转换字段类型 NCLOB（可改为其他数据类型）

----修改原字段名为 (字段名备份)NCLOB
ALTER TABLE 表名 RENAME COLUMN  字段 TO  C_ROUTE_B;

----添加一个和原字段同名的字段(注：类型与原来不同)
 ALTER TABLE 表名  ADD 字段 NCLOB;

---将原来的数据更新到新字段中 
UPDATE  表名   SET  字段= C_ROUTE_B;

---删除原来的备份字段
 ALTER TABLE 表名 DROP COLUMN C_ROUTE_B;

多表关联去重

--1.使用distinct字段
select distinct  b.n_resv,a.*   from  表A a, 表B b where a.c_no =b.c_no

插入clob类型数据

DECLARE  C_PLAN_CTNT  clob;
BEGIN
  C_PLAN_CTNT:='String’;
  insert into 表A (C_PLAN_CTNT ) 
    values (C_PLAN_CTNT );
      COMMIT;
     END;
/

修改clob 数据

DECLARE clobValue 表A.c_plan_ctnt%TYPE;
BEGIN
  clobValue := 'String';
  UPDATE表A t
     SET t.C_PLAN_CTNT = clobValue,
         t.t_crt_tm    = t.t_upd_tm,
         t.t_upd_tm    = sysdate
   WHERE t.A = 'A';
  COMMIT;
END;
/

DECLARE  clobValue 表A.c_plan_ctnt%TYPE;
BEGIN
  clobValue :='String'||'String';
  UPDATE表A t
     SET t.C_PLAN_CTNT = clobValue,
         t.t_crt_tm    = t.t_upd_tm,
         t.t_upd_tm    = sysdate
   WHERE t.A = 'A';
  COMMIT;
END;
/

索引

--主键
alter table 表名
  add constraint PK_BAK primary key (字段C);
    
--普通索引
create index PIDX_Typ on 表名 (字段C) online;

--唯一索引
create unique index IDX_no on 表名 (字段A, 字段B);

alter table 表名 
  add constraint UNIQUE_NAME unique (字段A, 字段B);
  
--删除索引
ALTER TABLE 表名 DROP CONSTRAINT 约束名;;

建议： 创建index的全部加上online 
create unique index IDX_no on 表名 (字段A, 字段B);

问题描述
添加索引大家都知道，但是添加索引的过程中Oracle会锁全表，
不久前因为添加索引的问题，造成了线上很多笔业务出现了异常
解决方案
online关键字是oracle提供的非独占锁的方式进行添加索引。
online使用了ROW-SHARE锁临时表，并不会对原表的DML操作造成影响。
10g以下的版本使用online中断或者手动中断执行会出现重新创建索引已经存在，删除无法删除的bug。
10g以上可以手动清除。

查某字段的值，A表有且B表没有的（去重）

select county
  from Region A
 where not exists (select area
          from AREA B
         where B.area = A.county
           and B.c_type = '3')

把,141319拼接到表codelist的enm字段中，中间已“,”隔开

 update codelist set enm = concat((SELECT enm
  FROM CODELIST
 WHERE cde = 'C0010'),'141319') where CDE = 'C0010';

 update codelist set enm = enm || ‘141319’ where CDE = 'C0010';

在插入一条sql语句中 有&& 这两字段 执行后查看数据库就只剩一个& 家具方法 在中间加|| 执行

INSERT INTO PCISNEW.WEB_PRD_RISK_UNIT_REL
  (UUID, C_CDE, C_EXPRESS)
VALUES
  (SYS_guid(), '0000', 'STATE == ''1'' &'||'& SEX != ''2''');

PLSQL查询锁表语句

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#'

PLSQL查询锁表并拼接解表语句

SELECT 'alter system kill session ''' || s.sid||',' || s.serial# ||''';'
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;

违反唯一约束

select table_name, column_name from user_cons_columns where constraint_name = 'SYS_C0061398';
select * from user_constraints t where t.table_name = '表名';

insert into

INSERT INTO web_user value SELECT rel.C_PK_ID, rel.C_NAME, '1' ,null ,null FROM web_user_rel rel WHERE rel .C_APP_NO ='' ;

INSERT INTO web_user (C_PK_ID,C_NAME, C_TYP, C_A, C_B)SELECT rel.C_PK_ID, rel.C_NAME, '1' ,null ,null FROM web_user_rel rel WHERE rel .C_APP_NO ='' ;

–把数据库中的表 excel导出

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default "默认值"
--       t4.created                                  AS "建表时间"
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name
      )
         -- and t1.TABLE_NAME='ACCCLAIM' --这里注意哦换成自己要查的那个表的表名，如果去掉这句话会查出该数据库所有的表结构
  ORDER BY t1.Table_Name, t1.Column_ID;
  --以上所查的列如果不符合自己的要求 可以通过自己更改sql语句来获得想要查询的列，想要的属性上面几张系统表里都有,自己写也很简单

–查看数据库表空间使用率

select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct 
from (
select tablespace_name, sum(bytes) /1024/1024 as MB 
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files group by tablespace_name) total     
where free.tablespace_name = total.tablespace_name 
order by used_pct desc;

–RUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。（可以用于是否数据库空间）

TRUNCATE TABLE 表名