Oracle 常用SQL语句-汇总

oracle 专栏收录该内容
4 篇文章 0 订阅

Oracle-查询数据库表锁定语句

Active:正执行SQL语句。Inactive:等待操作。Killed:被标注为删除。

1、先查询

SELECT object_name, machine, s.sid, s.serial# ,'alter system kill session ' || '''' || 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;

2、解除锁定

alter system kill session 'SID,serial#';  
alter system kill session '2134,35949';

3、报错:ORA-00030: 用户会话 ID 不存在

查看进程号和实例名,需要进入对应的服务器中杀掉相关进程。

select *  from gv$process
 where addr in (                
    select paddr from gv$session where sid = 146                
    );

ps -ef | grep 进程号 查看是否有相关进程号
kill -9 进程号 使用root用户杀掉进程

4、报错 ORA-00031: 标记要终止的会话
查出session的spid

select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid =197;
  1. 在linux上, kill -9 12345

  2. 在windows上,C:\Documents and Settings\gg>orakill orcl 12345
    orcl:表示要杀死的进程属于的实例名
    12345:是要杀掉的线程号

Oracle查询库中所有表

1、all_tables查出来是查得所有用户下的表,当然也包括你登录的用下的表,然后加一个where你要查的那个用户名就可以了。(记得用户名要大写)

select * from all_tables where owner='VTATEST';

2、查的你所登录的用户下的表,不会显示其他用户下的表。

select * from user_tables;

oracle-同时查询编码和名称

SELECT t.*  FROM QC_SYNC_RULE_LOG  t
 where 1=1 		and 
 (interfaceaccess_domain_codes like '%太和%' 
 or 	 t.interfaceaccess_domain_names like '%太和%')

oracle-求平方

select  t.*, t.rowid  from  di_interface.DI_PSS_PROREG_INFO  t 
where  t.DE05_10_075_00 <> (DE04_10_188_00 / power(DE04_10_167_00,2))

oracle-update 语句

update table_name  p  set  (family_code,family_name) =(
select m.code_new,m.name_new from table_name_b  m  where  p.family_code= m.code_old
)where  exists(
select 1 from table_name_b  m  where  p.family_code= m.code_old
)

oracle-更新时间最新的数据

 update tb_chss_grjbxx g
set (jt_no, family_relations_code, family_relations_name) =
    (select jt.zzdabm  as jt_no
           ,family_relations_code as family_relations_code
           ,family_relations_name as family_relations_name
     from (select jtdaid,zzdabm,daid,row_number() OVER(PARTITION BY daid ORDER BY sjscsj desc) as row_flg    from tb_chss_jtjkda) jt
      left join tb_chss_jtcygx gx on gx.jtdaid = jt.jtdaid and gx.daid = jt.daid     
      where jt.daid = g.daid and jt.row_flg  = '1'   )

oracle 字段处理

oracle-判断某个字段是否含有字母

select * from taccount t where  regexp_like(t.vc_code, '[a-zA-Z]');  

查找是否含有中文字符

select distinct huzusfzh
,translate(jtdh,'#'|| translate(jtdh,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','#') ,'/') 
from tb_chss_jtjkda  j 
where ASCIISTR(huzusfzh) LIKE '%\%' 

适合于数字字段去除中文字符

select  distinct jtdh,REGEXP_REPLACE (jtdh, '[^0-9]+', '') 
 from TB_CHSS_JTJKDA where length(jtdh)<2

oracle-闪回

先select 查询闪回时间段的数据

SELECT * FROM Hzst_Diagnosis 
AS OF TIMESTAMP TO_TIMESTAMP('2019-08-23 16:28:00', 'YYYY-MM-DD HH24:MI:SS')
where  DIAG_CLASS = '1' ;

进行闪回操作

flashback table tb_chss_jtjkda to timestamp to_timestamp('2018-04-24 20:00:00','yyyy-mm-dd hh24:mi:ss');

**启用行移动 **

alter table tb_chss_jtjkda enable row movement;

oracle-dblink

查看用户所有的dblink

select * from ALL_DB_LINKS;

删除dblink aa

drop public database link  aa;

①先确定用户是否有创建DBLink的权限:

select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

如果没有,则需要使用 sysdba 角色给aa用户赋权:

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to aa;  
  1. dblink有三种权限:
    CREATE DATABASE LINK(所创建的dblink只能是创建者能使用,别的用户使用不了)
    CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用)
    DROP PUBLIC DATABASE LINK(删除dblink)

2.以用户orcl登录数据库,创建DBLink

create public database link hzst_12    
 connect to hzst identified by "jxkj_2018"  
 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

说明:etl59为你创建的dblink的名字;mars为远程数据库用户的用户名;后边是mars对应的密码;Host=后边是服务器的地址;SERVICE_NAME=后边是远程数据库的实例名称。

oracle-表分区

1、查看表分区

SELECT * FROM user_tab_partitions
WHERE table_name=upper('gdws_sign_pasthistory');

2、新增一个list-list分区
不带子分区

alter table GDWS_SIGN_PERSON  add partition P520624 values('520624');

3、 删除分区

ALTER TABLE gdws_sign_person DROP PARTITION P520623

4、用分区删数据

alter table gdws_sign_person  truncate partition P520623 update global indexes;

oracle-无效的索引

查看某个表是否已有索引

 select * from user_indexes where table_name='GDWS_SIGN_PERSON' ;

查询无效的索引

select index_name from  user_indexes where status <> 'VALID';

批量rebuild下

select 'alter index '||index_name||' rebuild online;' from  user_indexes where status <> 'VALID' and index_name not like'%$$';

oracle-查看表空间信息

查询所有表及其所使用的表空间

select table_name 表名 ,tablespace_name 所使用表空间 from user_tables;
select b.tablespace_name as "表空间",
       b.file_name as "物理文件名",
       b.bytes / 1024 / 1024 as "当前大小(M)",
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as "已使用(M)",
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as "使用率(%)",
       case b.autoextensible
         when 'YES' then '是'
         else '否'
       end as "是否自增",
       b.maxbytes / 1024 / 1024 as "自增最大容量(M)"
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
   and a.tablespace_name in ( 'UNDOTBS02')
 group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
 order by b.tablespace_name;

增加表空间

alter database 
     datafile 'D:\HOME\ORACLE\ORADATA\GDWS\UNDOTBS02.DBF'
     autoextend on next 1M maxsize 8199M;

oracle-行转列 pivot

1、使用oracle的pivot函数
 语法:pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值));
 
原表:
在这里插入图片描述
行转列后的效果
在这里插入图片描述

2、多行转一列

使用 wm_concat 函数

select replace(wm_concat(diag_code),',',';') diag_code from Hzst_Diagnosis;

在这里插入图片描述

在这里插入图片描述

oracle-列转行 unpivot

1、使用oracle的unpivot函数
原表
在这里插入图片描述
列转行后-效果
在这里插入图片描述
unpivot(value FOR times IN(TIME_1A, TIME_2A, TIME_3A,
TIME_4P, TIME_5P, TIME_6P))

unpivot(列名(列的值) for 列名(列得名称) in (需转为行的列名));

oracle-用户被锁定,解锁

1、使用plsql工具sys用户登录后,执行解锁语句:

ALTER USER 用户名 ACCOUNT UNLOCK;

2、那么遇到这种用户会被锁的情况,我们可以设置用户密码无限次尝试登录,而该用户不会被锁定:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

3、另外,做项目运维的小伙伴经常遇到数据库用户密码过期的情况,要定期去修改密码很麻烦,那么我们可以设置用户密码不过期:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

oracle-重复数据取第一条

select yblx from (
select 
yblx
,row_number() OVER(PARTITION BY ghxxid ORDER BY ghxxid desc) as row_flg  from a_mz_ybxx t 
) temp 
where row_flg  = 1

oracle-快速把一张表数据复制到另外一张表中

DECLARE
CURSOR cur IS
SELECT * FROM aa;--定义游标(aa换成你自己表名)
TYPE rec IS TABLE OF aa%ROWTYPE;--定义类型(这里的aa也要替换成你自己表名)
recs rec;
BEGIN
OPEN cur; --打开游标
WHILE (TRUE) LOOP --循环条件
FETCH cur BULK COLLECT --取游标里的值
INTO recs LIMIT 10000; --提交条件,每10000条提交
FORALL i IN 1 .. recs.COUNT --这个是个计数器,用来确保提交的条数,也是循环
INSERT INTO bb VALUES recs (i);--插入目标表(bb替换成你目标表名)
COMMIT; --提交
EXIT WHEN cur%NOTFOUND;--游标结束条件
END LOOP; --停止循环
CLOSE cur;--关闭游标
END;

oracle-导入、导出表结构

导出:
1、选择工具–导出用户对象–
1、选择User------------》勾选Include storege或者去掉勾选(include storege表示表空间的意思)-----------》选择导出文件存放目录---------》Export按钮导出

ps:plsql导出数据乱码问题:用记事本打开。然后另存为 ansi 编码

在这里插入图片描述

导入表结构
1、导入表结构:import tables -->sqlInsert --> (选择:sqlPlus.exe)
2、工具–导入表

在这里插入图片描述

SQL Server

SQL Server查询所有的表名、字段名、注释

SELECT
表名=case   when   a.colorder=1   then   d.name   else   ''   end,
表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,
字段序号=a.colorder,
字段名=a.name,
标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,
主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (
SELECT   name   FROM   sysindexes   WHERE   indid   in(
SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   '√'   else   ''   end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case   when   a.isnullable=1   then   '√'else   ''   end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM   syscolumns   a
left   join   systypes   b   on   a.xusertype=b.xusertype
inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
left   join   syscomments   e   on   a.cdefault=e.id
left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id
left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
--where   d.name='orders'         --如果只查询指定表,加上此条件
order   by   a.id,a.colorder

sql server 字段处理

sql server- 查看身份证号是否含有中文

select  distinct  sfzh from T_GRJKDA  where sfzh LIKE '%[吖-座]%';

sql server 查看字段中是否含有字符

SELECT  distinct zyts ,PATINDEX('%[^0-9]%', zyts) FROM    BL_SCD_MAIN  WHERE    PATINDEX('%[^0-9]%', zyts) != 0 ;

sql server 去除中文字符

select dbo.m_getnumber(SCARDNO)
 from PersonInfo p 
ALTER function [dbo].[m_getnumber]
(
   @mysql_one nvarchar(200)
)
returns varchar(200)
begin
    declare @mysql_two varchar(200)
    select @mysql_two=
    substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),patindex('%[^0-9.]%',substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),
    len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))-1)
    return @mysql_two;
end

sql server-快速建表语句

temp_gdws_person_id 表要存在

Insert into temp_gdws_person_id 
select REPLACE(SPERSONID, '|', '_') AS ID  
from PersonInfo

sqlserver-列转行

create table tt
(
   Id varchar(50)
)
insert into tt values('1,2,3,4,5,6,7');
Select
  id=substring(id, b.number, charindex(',', id+',',b.number)-b.number) 
from 
  tt a join master..spt_values  b 
  ON b.type='p' AND b.number BETWEEN 1 AND LEN(a.id)
where
   substring(','+a.id,b.number,1)=','
  • 2
    点赞
  • 0
    评论
  • 6
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值