分类导航

Oracle 发布时间：2022-05-17 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了40+ 个非常有用的 Oracle 查询语句，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

40+ 个非常有用的 Oracle 查询语句，主要涵盖了日期操作，获取服务器信息，获取执行状态，计算数据库大小等等方面的查询。这些是所有 Oracle 开发者都必备的技能，所以快快收藏吧！

日期/时间相关查询

获取当前月份的第一天
运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
SELECTTrunc(SYSDATE,'MONTH')"Firstdayofcurrentmonth"
FROMDUAL;
```
获取当前月份的最后一天
这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
LAST_DAY(SYSDATE))"Lastdayofcurrentmonth"
FROMDUAL;
```
获取当前年份的第一天
每年的第一天都是1 月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
'YEAR')"YearFirstDay"FROMDUAL;
```
获取当前年份的最后一天
类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
SELECTADD_MONTHS(Trunc('YEAR'),12)-1"YearLastDay"FROMDUAL
```
获取当前月份的天数
这个语句非常有用，可以计算出当前月份的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
SELECTCAST(TO_CHAR(SYSDATE),147);">'dd')ASINT)number_of_days
FROMDUAL;
```
获取当前月份剩下的天数
下面的语句用来计算当前月份剩下的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
SYSDATE)"Last",171);">SYSDATE)-SYSDATE"Daysleft"
FROMDUAL;
```
获取两个日期之间的天数
使用这个语句来获取两个不同日期自检的天数。
```
ROUND((MONTHS_BETWEEN('01-Feb-2014',147);">'01-Mar-2012')*30),211);">0)
num_of_days
FROMDUAL;

OR

sysdate)-Trunc(e.hire_date)FROMemployees;
```
如果你需要查询一些特定日期的天数，可以使用第二个查询语句。这个例子是计算员工入职的天数。
显示当前年份截止到上个月每个月份开始和结束的日期
这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。
```
'MONTH'),i)start_date,Trunc(LAST_DAY(ADD_MONTHS(FROMXMLtable(
'for$iin0toxs:int(D)return$i'
PASSINGXMLELEMENT(
d,171);">FLOOR(
MONTHS_BETWEEN(
ADD_MONTHS(Trunc('YEAR')-1,211);">12),171);">SYSDATE)))
ColUMNSiINTEGERPATH'.');
```

获取直到目前为止今天过去的秒数（从 00：00 开始算）

SELECT(SYSDATE-Trunc(SYSDATE))*24*60*60num_of_sec_since_morning
FROMDUAL;

获取今天剩下的秒数（直到 23：59：59 结束）
```
SELECT(Trunc(SYSDATE+1)-SYSDATE)*60num_of_sec_left
FROMDUAL;
```
数据字典查询
检查在当前数据库模式下是否存在指定的表
这是一个简单的查询语句，用来检查当前数据库是否有你想要创建的表，允许你重新运行创建表脚本，这个也可以检查当前用户是否已经创建了指定的表（根据这个查询语句在什么环境下运行来查询）。
```
SELECTtable_name
FROMuser_tables
WHEREtable_name='table_name';
```
检查在当前表中是否存在指定的列
这是个简单的查询语句来检查表里是否有指定的列，在你尝试使用 ALTER table 来添加新的列新到表中的时候非常有用，它会提示你是否已经存在这个列。
```
SELECTcolumn_nameASFOUND
FROMuser_tab_cols
'table_name'ANDcolumn_name='ColUMN_name';
```
显示表结构
这个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘table’作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW’，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。
```
SELECTDBMS_MetaDATA.get_ddl('table',147);">'table_name',147);">'USER_name')FROMDUAL;
```
获取当前模式
这是另一个可以获得当前模式的名字的查询语句。
```
SELECTSYS_CONTEXT('userenv',147);">'current_schema')FROMDUAL;
```
修改当前模式
这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式。
```
ALTERSESSIONSETCURRENT_SCHEMA=new_schema;
```
数据库管理查询
数据库版本信息
返回 Oracle 数据库版本
```
SELECT*FROMv$version;
```

数据库默认信息

返回一些系统默认的信息

SELECTusername,profile,default_tablespace,temporary_tablespace
FROMdba_users;

数据库字符设置信息
显示数据库的字符设置信息
```
FROMnls_database_parameters;
```

获取 Oracle 版本

VALUE
FROMv$system_parameter
WHEREname='compatible';

存储区分大小写的数据，但是索引不区分大小写
某些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询，所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求。
```
CREATEtabletab(col1VARCHAR2(10));

INDEXIDx1
ONtab(UPPER(col1));

ANALYZEtableaCOmpuTESTATISTICS;
```
调整没有添加数据文件的表空间
另一个 DDL 查询来调整表空间大小
```
DATABASEDATAfile'/work/oradata/STARTST/STAR02D.dbf'resize2000M;
```

检查表空间的自动扩展开关

在给定的表空间中查询是否打开了自动扩展开关

SUBSTR(file_name,211);">50),autoEXTENSIBLEFROMdba_data_files;

(OR)

SELECTtablespace_name,171);">FROMdba_data_files;

在表空间添加数据文件

在表空间中添加数据文件

tableSPACEdata01ADD'/work/oradata/STARTST/data01.dbf'
SIZE1000MautoEXTENDOFF;

增加数据文件的大小
给指定的表空间增加大小
```
'/u01/app/Test_data_01.dbf'RESIZE2G;
```
查询数据库的实际大小
给出以 GB 为单位的数据库的实际大小
```
SUM(bytes)/1024/1024ASGBFROMdba_data_files;
```
查询数据库中数据占用的大小或者是数据库使用细节
给出在数据库中数据占据的空间大小
```
FROMdba_segments;
```
查询模式或者用户的大小
以 MB 为单位给出用户的空间大小
```
bytes/1024)"size"
FROMdba_segments
WHEREowner='&owner';
```

查询数据库中每个用户最后使用的 sql 查询

此查询语句会显示当前数据库中每个用户最后使用的 sql 语句。

SELECTS.USERname||'('||s.sID||')-'||s.osuserUname,s.program||'-'||s.terminal||'('||s.machine||')'PROG,s.sID||'/'||s.serial#sID,s.status"Status",p.spID,sql_textsqltext
FROMv$sqltext_with_newlinest,V$SESSIONs,v$processp
WHEREt.address=s.sql_address
ANDp.addr=s.paddr(+)
ANDt.hash_value=s.sql_hash_value
ORDERBYs.sID,t.pIEce;

性能相关查询

查询用户 cpu 的使用率

这个语句是用来显示每个用户的 cpu 使用率，有助于用户理解数据库负载情况

SELECTss.username,se.SID,171);">VALUE/100cpu_usage_seconds
sessionss,v$sesstatse,v$statnamesn
WHEREse.STATISTIC#=sn.STATISTIC#
ANDnameliKE'%cpuusedbythissession%'
ANDse.SID=ss.SID
ANDss.status='ACTIVE'
ANDss.usernameISNOTNulL
BYVALUEDESC;

查询数据库长查询进展情况

显示运行中的长查询的进展情况

SELECTa.sID,a.serial#,b.username,opnameOPERATION,targetOBJECT,Trunc(elapsed_seconds,211);">5)"ET(s)",TO_CHAR(start_time,147);">'HH24:MI:SS')start_time,171);">ROUND((sofar/totalwork)*100,211);">2)"COMPLETE(%)"
FROMv$session_longopsa,v$sessionb
WHEREa.sID=b.sID
ANDb.usernameNOTIN('SYS',147);">'SYstem')
ANDtotalwork>0
BYelapsed_seconds;

获取当前会话 ID，进程 ID，客户端 ID 等
这个专门提供给想使用进程 ID 和会话 ID 做些 voodoo magic 的用户。
```
SELECTb.sID,b.serial#,a.spIDprocessID,b.processclIEntpID
FROMv$processa,171);">WHEREa.addr=b.paddrANDb.audsID=USERENV('sessionID');
```

V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID
V$PROCESS.SPID 是数据库服务器后台进程 ID
V$SESSION.PROCESS 是客户端 PROCESS ID,ON windows it IS : separated THE FirsT # IS THE PROCESS ID ON THE clIEnt AND 2nd one IS THE THREAD ID.

查询特定的模式或者表中执行的最后一个 sql 语句

SELECTCREATED,171);">TIMESTAMP,last_ddl_time
FROMall_objects
WHEREOWNER='MYSCHEMA'
ANDOBJECT_TYPE='table'
ANDOBJECT_name='EMPLOYEE_table';

查询每个执行读取的前十个 sql

SELECT*
FROM(ROWNUM,171);">SUBSTR(a.sql_text,211);">200)sql_text,Trunc(
a.disk_reads/DECODE(a.executions,211);">0,a.executions))
reads_per_execution,a.buffer_gets,a.disk_reads,a.executions,a.sorts,a.address
FROMv$sqlareaa
BY3DESC)
ROWNUM<10;

在视图中查询并显示实际的 Oracle 连接

SELECTosuser,username,machine,program
session
BYosuser;

查询并显示通过打开连接程序打开连接的组

SELECTprogramapplication,171);">COUNT(program)Numero_Sesiones
GROUPBYprogram
BYNumero_SesionesDESC;

查询并显示连接 Oracle 的用户和用户的会话数量

SELECTusernameUsuario_Oracle,171);">COUNT(username)Numero_Sesiones
BYusername
DESC;

获取拥有者的对象数量

SELECTowner,171);">COUNT(owner)number_of_objects
FROMdba_objects
BYowner
BYnumber_of_objectsDESC;

实用/数学相关的查询

把数值转换成文字
更多信息可以查看：Converting number into words in Oracle
```
SELECTTO_CHAR(TO_DATE(1526,147);">'j'),147);">'Jsp')FROMDUAL;
```
输出:
```
onethousandfivehundredtwenty-six
```
在包的源代码中查询字符串
这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。
```
--searchastringfoo_somethinginpackagesourcecode
FROMdba_source
UPPER(text)'%FOO_SOMETHING%'
ANDowner='USER_name';
```
把用逗号分隔的数据插入的表中
当你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF’转换成包含 AA,BB，CC 等作为一行的表，这样你就很容易把这些字符串插入到其他表中，并快速的做一些相关的操作。

WITHcsv
AS(SELECT'AA,FF'
AScsvdata
FROMDUAL)
SELECTREGEXP_SUBSTR(csv.csvdata,147);">'[^,]+',171);">LEVEL)pivot_char
FROMDUAL,csv
CONNECTBYREGEXP_SUBSTR(csv.csvdata,171);">LEVEL)NulL;

查询表中的最后一个记录
这个查询语句很直接，表中没有主键，或者是用户不确定记录最大主键是否是最新的那个记录时，就可以使用这个语句来查询表中最后一个记录。
```
FROMemployees
ROWIDIN(MAX(ROWID)FROMemployees);

(OR)

MINUS
ROWNUM<(COUNT(*)FROMemployees);
```

在 Oracle 中做行数据乘法

这个查询语句使用一些复杂的数学函数来做每个行的数值乘法。更多内容请查阅：Row Data Multiplication In Oracle

WITHtbl
AS(SELECT-2numFROMDUAL
UNION
-3-4FROMDUAL),sign_val
AS(CASEMOD(COUNT(*),211);">2)WHEN0THEN1ELSE-1ENDval
FROMtbl
num<0)
EXP(LN(ABS(num))))*val
FROMtbl,171);">BYval;

在 Oracle 生成随机数据

每个开发者都想能轻松生成一堆随机数据来测试数据库多好，下面这条查询语句就可以满足你，它可以在 Oracle 中生成随机的数据插入到表中。详细信息可以查看Random Data in Oracle

LEVELempl_ID,211);">50000)dept_ID,Trunc(DBMS_RANDOM.VALUE(1000,211);">500000),211);">2)salary,171);">DECODE(ROUND(DBMS_RANDOM.VALUE(2)),147);">'M',211);">2,147);">'F')gender,171);">TO_DATE(
28))
||'-'
||12))
||1900,211);">2010)),147);">'DD-MM-YYYY')
dob,DBMS_RANDOM.STRING('x',DBMS_RANDOM.VALUE(20,211);">50))address
LEVEL<10000;

在 Oracle 中生成随机数值
这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。
```
--generaterandomnumberbetween0and100
ROUND(DBMS_RANDOM.VALUE()*100)+ASrandom_numFROMDUAL;
```
检查表中是否含有任何的数据
这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。
```
1
FROMtable_name
ROWNUM=1;
```

如果你知道一些很好用的查询语句，可以减轻 Oracle 开发者的负担，那么在评论分享一下吧：）

大佬总结

以上是大佬教程为你收集整理的40+ 个非常有用的 Oracle 查询语句全部内容，希望文章能够帮你解决40+ 个非常有用的 Oracle 查询语句所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：

上一篇: Oracle同义词创建及其作用下一篇:Oracle Study---Oracle 11g 不可...

猜你在找的Oracle相关文章

Oracle 闪回找回数据的实现方法 2022-05-17
Oracle PL/SQL中异常高级特性示例解析 2019-11-05
Oracle中触发器示例详解 2019-11-05
Oracle游标的使用实例详解 2019-11-05
oracle net manager 数据传输安全步骤详解 2019-11-05
ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解 2019-11-05
ORACLE中段的HEADER_BLOCK示例详析 2019-11-05
修改oracle密码有效期限制的两种思路详解 2019-11-05
修改ORACLE数据库密码有效期的方法 2019-11-05
plsql 登录 oracle11 12514 错误的处理方法 2019-11-05

其他相关热搜词更多

JavaScript之正则表达式学习笔记

40+ 个非常有用的 Oracle 查询语句

日期/时间 相关查询

获取当前月份的第一天

获取当前月份的最后一天

获取当前年份的第一天

获取当前年份的最后一天

获取当前月份的天数

获取当前月份剩下的天数

获取两个日期之间的天数

显示当前年份截止到上个月每个月份开始和结束的日期

获取直到目前为止今天过去的秒数（从 00：00 开始算）

获取今天剩下的秒数（直到 23：59：59 结束）

数据字典查询

检查在当前数据库模式下是否存在指定的表

检查在当前表中是否存在指定的列

显示表结构

获取当前模式

修改当前模式

数据库管理查询

数据库版本信息

数据库默认信息

数据库字符设置信息

获取 Oracle 版本

存储区分大小写的数据，但是索引不区分大小写

调整没有添加数据文件的表空间

检查表空间的自动扩展开关

在表空间添加数据文件

增加数据文件的大小

查询数据库的实际大小

查询数据库中数据占用的大小或者是数据库使用细节

查询模式或者用户的大小

查询数据库中每个用户最后使用的 sql 查询

性能相关查询

查询用户 cpu 的使用率

查询数据库长查询进展情况

获取当前会话 ID，进程 ID，客户端 ID 等

查询特定的模式或者表中执行的最后一个 sql 语句

查询每个执行读取的前十个 sql

在视图中查询并显示实际的 Oracle 连接

查询并显示通过打开连接程序打开连接的组

查询并显示连接 Oracle 的用户和用户的会话数量

获取拥有者的对象数量

实用/数学 相关的查询

把数值转换成文字

在包的源代码中查询字符串

把用逗号分隔的数据插入的表中

查询表中的最后一个记录

在 Oracle 中做行数据乘法

在 Oracle 生成随机数据

在 Oracle 中生成随机数值

检查表中是否含有任何的数据

大佬总结

日期/时间相关查询

实用/数学相关的查询