oracle常用sql语句

2019-06-19 本文已影响17人清远_03d9

oracle数据库相关学习

标签（空格分隔）：数据库

[toc]

1. 查询oracle版本号

select * from v$version

2. 数据库备份

打开数据库服务器，cmd打开执行如下语句

exp CHECK2014/CHECK2014@192.168.1.199/CHECK owner=CHECK2014 file=D:\SGHY_WPZF\CHECK2014-201805082312.dmp log=D:\SGHY_WPZF\CHECK2014-201805082312.log compress=n

D:\SGHY_WPZF为要保存备份数据文件的路径
用户为CHECK2014，数据库所在服务器地址为CHECK2014@192.168.1.199

3. 视图创建

create or replace view view_hz as
select
--listagg(m.jctbbh,',')  WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
 ,count(*)as sl,xzqdm,'兵团图斑' as fl,1 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '兵团图斑'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
     union all
           --规划院 合法图斑
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
 WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'合法图斑' as fl,2 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '合法图斑'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

             union all
--建设用地处 国家和自治区重点工程项目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
 WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'国家和自治区重点工程项目' as fl,3 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '国家和自治区重点工程项目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--建设用地处 民生工程
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'民生工程' as fl,4 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '民生工程项目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm



           union all
--建设用地处 援疆项目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'援疆项目' as fl,5 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '援疆项目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--建设用地处 自行纠正前
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'自行纠正前' as fl,6 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '自行纠正'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and ( t.sftg='0' or t.sftg='11') --自行纠正点击通过、2018年前、2018年后
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'自行纠正后' as fl,18 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '自行纠正'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and (t.sftg='12') --自行纠正点击通过、2018年前、2018年后
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--执法处 移交其他部门
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'移交其他部门' as fl,7 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '移交其他部门'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--执法处 违法已结案
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'违法已结案' as fl,8 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where ( m.tbflqk='违法已立案' or m.tbflqk='违法已结案' )
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--执法处 违法未处理
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'违法未处理' as fl,9 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where (m.tbflqk ='违法未处理' )  --or m.tbflqk='移交其他部门'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.wtglx='6'
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--执法处 违法未结案
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'违法未结案' as fl,10 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where ( m.tbflqk='违法未结案' ) --or m.tbflqk='移交其他部门'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where (t.csid = '3' or t.csid = '6')  and t.wtglx='5'
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

           union all
--耕保处 设施农用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'设施农用地' as fl,11 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where (m.tbflqk = '变更审核通过的设施农用地' or m.tbflqk = '设施农用地')
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '5'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--利用处 临时用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'临时用地' as fl,12 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '临时用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '1'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
            union all
--地籍处 农村道路用地
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'农村道路用地' as fl,13 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '农村道路用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '4'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--地籍处 实地伪变化
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'实地伪变化' as fl,14 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '实地伪变化'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '4'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--建设用地处 不改变原用地性质的光伏项目
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'光伏项目用地' as fl,15 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.tbflqk = '不改变原用地性质的光伏项目'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '2'  and (t.sftg=0 and t.sftg=2)
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm
           union all
--规划院 军事用地图斑
select
--listagg(m.jctbbh,',')   WITHIN GROUP(order by m.jctbbh) as jctbbhs
WM_CONCAT(wptbbh) as jctbbhs
,count(*) as sl,xzqdm,'军事用地图斑' as fl,16 as sy
  from YGJCTBXXHSJLB_ZFJC m
 where m.qtydtbflqk = '特殊用地'
   and exists (select *
          from DEPARTMENTOPTION_ZFJC t
         where t.csid = '0'  and t.sftg=0
           and m.wptbbh = t.jctbbh
           and t.xzqdm = m.xzqdm
           and t.yx = '0')
           group by xzqdm

4. 常用sql语句函数

1. sql中in和exist语句的区别

in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)效率高，用到了B表上cc列的索引。
相反的2：select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)效率低，用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not exists 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');与
select name from student where name='zhang' or name='li' or
name='wang' or name='zhao'
的结果是相同的。

2. 数据库导出导入备份

导出

exp CHECK2014/CHECK2014@192.168.1.199/CHECK owner=CHECK2014 file=文件夹路径\CHECK2014-201805082312.dmp  log=文件夹路径\CHECK2014-201805082312.log compress=n




set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

exp bigdata/bigdata@localhost/orcl owner=bigdata file=D:\oracle数据库备份\bigdata.dmp  log=D:\oracle数据库备份\bigdata.log compress=n

导入 pcjy

imp US_OneMap/US_OneMap@localhost/orcl full=y ignore=y file= --导入数据的文件路径(数据文件在Data文件夹下)

exp check2014qu/check2014qu@localhost/orcl owner=check2014qu file=D:\check2014qu.dmp log=D:\check2014qu.log compress=n

5. oracle sqlplus登陆

1、win键+R键，输入cmd，打开命令提示符。

2、输入sqlplus /nolog

3、继续输入conn /as sysdba

4、输入 alter user 用户名 identified by 密码;

5、修改成功后，会有上图“用户已更改”的提示，再次登录时用自己设定的密码即可

6. ORACLE 11g登陆时出现: 协议适配器错误的解决方法

右键点击计算机---> 服务与应用程序 ---> 服务 ---> 将oracle0raDb11g_home1ClrAgent、 oracleOraDb11g_home1TNSListener和oracleServiceORCL这三项启动即可

7.ORA-12504：TNS：监听程序在CONNECT_DATA中未获得SERVICE_NAME

检查tnsnames.ora文件中TNS是否配置正确，如下所示，SERVICE_NAME 名字弄错了，结果报如上错误：

GSP =
　　(DESCRIPTION =
　　　　(ADDRESS =(PROTOCOL = TCP)(HOST = 172.20.36.79)(PORT = 1521))
　　　　(CONNECT_DATA=
　　　　　　(SERVER = DEDICATED)
　　　　　　(SERVER_NAME = gsp)
　　　　)
　　)

修改为

GSP =
　　(DESCRIPTION =
　　　　(ADDRESS =(PROTOCOL = TCP)(HOST = 172.20.36.79)(PORT = 1521))
　　　　(CONNECT_DATA=
　　　　　　(SERVER = DEDICATED)
　　　　　　(SERVICE_NAME = gsp)
　　　　)
　　)

1.监听服务没有起起来。windows平台个一如下操作：开始---程序---管理工具---服务，打开服务面板,启动oraclehome92TNSlistener服务。
2.database instance没有起起来。windows平台如下操作：开始---程序---管理工具---服务，打开服务面板，启动oracleserviceXXXX,XXXX就是你的database SID.
3.注册表问题。regedit，然后进入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0将该环境变量ORACLE_SID设置为XXXX,XXXX就是你的database SID.或者右几我的电脑，属性--高级--环境变量---系统变量--新建，变量名=oracle_sid,变量值=XXXX,XXXX就是你的database SID.或者进入sqlplus前，在command line下输set oracle_sid=XXXX,XXXX就是你的database SID.
4.需要更改path中oracle的顺序就可以了。
C:\oracle\product\10.2.0\db_1\bin;C:\oracle\product\10.2.0\client_1\bin;

8.oracle环境变量配置

ORACLE_HOME=D:\app\Administrator\product\12.1.0\dbhome_1（oracle安装目录）

Path=D:\app\Administrator\product\12.1.0\dbhome_1\BIN

ORACLE_SID=orcl（数据库实例名）

TNS_ADMIN=D:\app\Administrator\product\12.1.0\dbhome_1\NETWORK\ADMIN

NLS_LANG=AMERICAN_AMERICA.AL32UTF8（utf8字符串）

9.oracle用户创建

create user check0512 identified by check0512;
    grant connect,resource,dba to check0512; --把connect，resource权限授权给新用户
grant dba to check0512 把dba权限授予给check0512

指定表空间

create user check0512 identified by wbs123 account unlock
default tablespace hjb
temporary tablespace hjb_temp;

为已有用户指定表空间

alter user check0512  default tablespace userspace;

10.删除用户

删除用户：drop user hjb_wbs cascade;

删除表空间

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

10.数据库表空间查看

select tablespace_name,file_name,bytes from dba_data_files

11.创建表空间

create tablespace hjb_data datafile 'hj_data.dbf' size 50m
autoextend on next 50M maxsize unlimited

12.建表

create table usptotest
(
pn varchar(10) not null,
ss varchar(12) ,
isd varchar(20) not null  ,
title varchar(150) not null ,
abst varchar(2000) not null ,
appno varchar(20) not null  ,
appdate varchar(20) not null ,
inventor varchar(200) not null ,
assignee_name varchar(50) not null ,
assignee_country varchar(20) not null ,
assignee_city varchar(20) not null ,
assignee_state varchar(10) not null ,
primary key (pn)
)

13.用户授权

grant select on DBA_DATA_FILES to US_OneMap;
grant select on DBA_FREE_SPACE to US_OneMap;
grant select on DBA_TABLES to US_OneMap;
grant select on DBA_TABLESPACES to US_OneMap;
-- Grant/Revoke role privileges 
grant connect to US_OneMap with admin option;
grant dba to US_OneMap with admin option;
grant resource to US_OneMap with admin option;
-- Grant/Revoke system privileges 
grant create view to US_OneMap;
grant select any table to US_OneMap;
grant unlimited tablespace to US_OneMap with admin option;

14. DROP MATERIALIZED VIEW <materialize view name>;

15.exp无法导出空表

链接
执行如下语句

对空表分配segment，避免空表无法用exp导出
alter system set deferred_segment_creation=false;
修改已有的空表（防止已存在的空表导不出）
select 'alter table ' || table_name || ' allocate extent;' from user_tables where num_rows=0;
复制查询的语句并执行；

16.表空间自动扩展

alter database datafile 'E:\APP\QINGYUAN\PRODUCT\11.2.0\DBHOME_1\DATABASE\TS_DZZW.DBF' 
  autoextend on next 100m

16.查询表空间使用大小

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表空间使用大小",

total / (1024 * 1024 * 1024) "表空间大小(G)",

free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name ;

17.arcsde库查询面积

SELECT SDE.st_astext(SHAPE) wkt FROM table WHERE objectid = ?

18. 如何解决 ORA-28595: Extproc 代理: DLL 路径无效

但是一直提示如下错误：

ORA-28595: Extproc 代理: DLL 路径无效
ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 70
ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 68

百度了一番也没找到方法，后来还是通过 google 查找到了线索，通过调试一番终于解决，在此给大家说明一下解决的思路。
我本机的 oracle11g 安装目录为 E:\oracle11g。

1、打开 E:\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN 目录下的 listener.ora 文件，
发现之前写的是：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\oracle11g\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\oracle11g\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
把它修改为：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\oracle11g\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ANY")
)
)
2、打开 E:\oracle11g\product\11.2.0\dbhome_1\hs\admin 目录下的 extproc.ora文件，
最后一行原来写的是 SET EXTPROC_DLLS= ，把他修改为 SET EXTPROC_DLLS=ANY
3、重启 OracleOraDb11g_home1TNSListener服务和 OracleServiceORCL服务。
4、重启 PL/SQL ，再次运行 select sde.st_astext(shape) from DLWG_SQ，这次终于不再提示错误了，正常查询到结果

19.查看dll

select * from user_libraries

19.修改dmp文件默认的表空间名称

利用notepat++打开导出dmp文件，搜索到表空间名称，批量修改表空间
如:TABLESPACE "TS_ONEMAP"改为TABLESPACE "TS_ONEMAP_new"

20.数据库优化配置

1. 数据库设置备份。

看部署文档里面写着如何写脚本的。

2. 设置账户永不过期。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

3. 设置密码不区分大小写

    ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

4. 对空表分配segment，避免空表无法用exp导出

alter system set deferred_segment_creation=false;

5. 修改已有的空表（防止已存在的空表导不出）

select 'alter table ' || table_name || ' allocate extent;' from user_tables where num_rows=0;

将上述语句查出的结果，进行运行。

6. 扩大open_cursors参数

alter system set open_cursors=3000 scope=both;
alter system set session_cached_cursors=100 scope=spfile;
alter system set cursor_space_for_time=true scope=spfile;

7. 扩大实例数

alter system set processes = 300 scope = spfile;

8. 索引成本和缓存相关

alter system set optimizer_index_cost_adj=20;
alter system set optimizer_index_caching=80;

9. 两数据库之间表的比较

/*** 对比两个用户下的表名称，检查数据迁移中是否遗漏表
**使用方法如下：
*** 1. 用PL/SQL 登陆原数据库
*** 2. 下面语句中【原数据库用户名】填写当前用户名
*** 3. 创建一个迁移数据库的dbLink
*** 4. 【dbLink名称】的填写创建的dbLink名称
*** 5. 【迁移后的数据库用户名】填写新迁移的数据库用户名
*** 6. 执行语句查看结果，如果有记录，说明新数据库中缺这几个表
**/

    select t.TABLE_NAME from all_tables t 
       where owner=upper('原数据库用户名')
         and t.TABLE_NAME not in(select p.TABLE_NAME from all_tables@dbLink名称 p where owner=upper('迁移后的数据库用户名'));

21. orcle error 12899

https://blog.csdn.net/iamlaosong/article/details/52316844

22.parttion by

Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区,它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。

23.数字操作

-- select MOD(65,50) from dual     --取余 
-- select  trunc( 65/33) from dual  -- 取整 trunc (1.9) = 1
-- select ceil(65/60) from dual          -- 取整 ceil(1.1) = 2