一、常用的查询语句

1.1 常用查询

查表中有多少个字段

select count(*) from user_tab_columns where table_name=upper('表名')

或者

select max(column_id) from user_tab_columns where table_name=upper('表名')

1.2 日期/时间相关查询

获取当前月份的第一天

SELECT

TRUNC (SYSDATE,

'MONTH'

)

"First day of current month"

FROM

DUAL;

获取当前月份的最后一天

这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT

TRUNC (LAST_DAY (SYSDATE))

"Last day of current month"

FROM

DUAL;

获取当前年份的第一天

每年的第一天都是1 月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT

TRUNC (SYSDATE,

'YEAR'

)

"Year First Day"

FROM

DUAL;

获取当前年份的最后一天

类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

SELECT

ADD_MONTHS (TRUNC (SYSDATE,

'YEAR'

), 12) - 1

"Year Last Day"

FROM

DUAL

获取当前月份的天数

SELECT

CAST

(TO_CHAR (LAST_DAY (SYSDATE),

'dd'

)

AS

INT

) number_of_days

FROM

DUAL;

获取当前月份剩下的天数

                                                          
SELECT
SYSDATE,
       
LAST_DAY (SYSDATE) 
"Last"
,
       
LAST_DAY (SYSDATE) - SYSDATE 
"Days left"
  
FROM
DUAL;
                   
                 

获取两个日期之间的天数

如果你需要查询一些特定日期的天数，可以使用第二个查询语句。这个例子是计算员工入职的天数。

                                                    
SELECT
ROUND ( (MONTHS_BETWEEN (
'01-Feb-2014'
, 
'01-Mar-2012'
) * 30), 0)
          
num_of_days
  
FROM
DUAL;
 
OR
 
SELECT
TRUNC(sysdate) - TRUNC(e.hire_date) 
FROM
employees;
                 
               

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

这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。

                                                    
SELECT
ADD_MONTHS (TRUNC (SYSDATE, 
'MONTH'
), i) start_date,
       
TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
  
FROM
XMLTABLE (
          
'for $i in 0 to xs:int(D) return $i'
          
PASSING XMLELEMENT (
                     
d,
                     
FLOOR (
                        
MONTHS_BETWEEN (
                           
ADD_MONTHS (TRUNC (SYSDATE, 
'YEAR'
) - 1, 12),
                           
SYSDATE)))
          
COLUMNS i 
INTEGER
PATH 
'.'
);
                 
               

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

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL;

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

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL;

1.3 数据字典查询

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

SELECT

table_name

FROM

user_tables

WHERE

table_name =

'TABLE_NAME'

;

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

SELECT

column_name

AS

FOUND

FROM

user_tab_cols

WHERE

table_name =

'TABLE_NAME'

AND

column_name =

'COLUMN_NAME'

;

显示表结构

这个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘TABLE’作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW’，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。

SELECT

DBMS_METADATA.get_ddl (

'TABLE'

,

'TABLE_NAME'

,

'USER_NAME'

)

FROM

DUAL;

获取当前模式

SELECT

SYS_CONTEXT (

'userenv'

,

'current_schema'

)

FROM

DUAL;

修改当前模式

ALTER

SESSION

SET

CURRENT_SCHEMA = new_schema;

1.4 数据库管理查询

数据库版本信息，返回 Oracle 数据库版本

SELECT

*

FROM

v$version;

或

SELECT

VALUE

FROM

v$system_parameter

WHERE

name

=

'compatible'

;

数据库默认信息

SELECT

username,

profile,

default_tablespace,

temporary_tablespace

FROM

dba_users;

数据库字符设置信息

SELECT

*

FROM

nls_database_parameters;

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

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

                                                                
REATE 
TABLE
tab (col1 VARCHAR2 (10));
 
CREATE
INDEX
idx1
   
ON
tab (
UPPER
(col1));
 
ANALYZE 
TABLE
a COMPUTE 
STATISTICS
;
                     
                   

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

ALTER

DATABASE

DATAFILE

'/work/oradata/STARTST/STAR02D.dbf'

resize 2000M;

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

SELECT

SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE

FROM

dba_data_files;

(

OR

)

SELECT

tablespace_name, AUTOEXTENSIBLE

FROM

dba_data_files;

在表空间添加数据文件

ALTER

TABLESPACE data01

ADD

DATAFILE

'/work/oradata/STARTST/data01.dbf'

SIZE

1000M AUTOEXTEND

OFF

;

增加数据文件的大小

给指定的表空间增加大小

ALTER

DATABASE

DATAFILE

'/u01/app/Test_data_01.dbf'

RESIZE 2G;

查询数据库的实际大小

SELECT

SUM

(bytes) / 1024 / 1024 / 1024

AS

GB

FROM

dba_data_files;

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

SELECT

SUM

(bytes) / 1024 / 1024 / 1024

AS

GB

FROM

dba_segments;

查询模式或者用户的大小

SELECT

SUM

(bytes / 1024 / 1024)

"size"

FROM

dba_segments

WHERE

owner =

'&owner'

;

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

                                                                                                          
SELECT
S.USERNAME || 
'('
|| s.sid || 
')-'
|| s.osuser UNAME,
         
s.program || 
'-'
|| s.terminal || 
'('
|| s.machine || 
')'
PROG,
         
s.sid || 
'/'
|| s.serial# sid,
         
s.status 
"Status"
,
         
p.spid,
         
sql_text sqltext
    
FROM
v$sqltext_with_newlines t, V$SESSION s, v$process p
   
WHERE
t.address = s.sql_address
         
AND
p.addr = s.paddr(+)
         
AND
t.hash_value = s.sql_hash_value
ORDER
BY
s.sid, t.piece;
                                   
                                 

1.6 性能相关查询

查询用户 CPU 的使用率

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

                                                                                                          
SELECT
ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    
FROM
v$session ss, v$sesstat se, v$statname sn
   
WHERE
se.STATISTIC# = sn.STATISTIC#
         
AND
NAME
LIKE
'%CPU used by this session%'
         
AND
se.SID = ss.SID
         
AND
ss.status = 
'ACTIVE'
         
AND
ss.username 
IS
NOT
NULL
ORDER
BY
VALUE 
DESC
;
                                   
                                 

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

                                                                                                          
SELECT
a.sid,
         
a.serial#,
         
b.username,
         
opname OPERATION,
         
target OBJECT,
         
TRUNC (elapsed_seconds, 5) 
"ET (s)"
,
         
TO_CHAR (start_time, 
'HH24:MI:SS'
) start_time,
         
ROUND ( (sofar / totalwork) * 100, 2) 
"COMPLETE (%)"
    
FROM
v$session_longops a, v$session b
   
WHERE
a.sid = b.sid
         
AND
b.username 
NOT
IN
(
'SYS'
, 
'SYSTEM'
)
         
AND
totalwork > 0
ORDER
BY
elapsed_seconds;
                                   
                                 

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

这个专门提供给想使用进程 ID 和会话 ID 做些 voodoo magic 的用户。

                                                                                                          
SELECT
b.sid,
       
b.serial#,
       
a.spid processid,
       
b.process clientpid
  
FROM
v$process a, v$session b
 
WHERE
a.addr = b.paddr 
AND
b.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 语句

SELECT

CREATED,

TIMESTAMP

, last_ddl_time

FROM

all_objects

WHERE

OWNER =

'MYSCHEMA'

AND

OBJECT_TYPE =

'TABLE'

AND

OBJECT_NAME =

'EMPLOYEE_TABLE'

;

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

                                                                      
SELECT
*
  
FROM
(  
SELECT
ROWNUM,
                 
SUBSTR (a.sql_text, 1, 200) sql_text,
                 
TRUNC (
                    
a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                    
reads_per_execution,
                 
a.buffer_gets,
                 
a.disk_reads,
                 
a.executions,
                 
a.sorts,
                 
a.address
            
FROM
v$sqlarea a
        
ORDER
BY
3 
DESC
)
 
WHERE
ROWNUM < 10;
                       
                     

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

                                                                         
SELECT
osuser,
         
username,
         
machine,
         
program
    
FROM
v$session
ORDER
BY
osuser;

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

                                                                         
SELECT
program application, 
COUNT
(program) Numero_Sesiones
    
FROM
v$session
GROUP
BY
program
ORDER
BY
Numero_Sesiones 
DESC
;
                        
                      

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

                                                                         
SELECT
username Usuario_Oracle, 
COUNT
(username) Numero_Sesiones
    
FROM
v$session
GROUP
BY
username
ORDER
BY
Numero_Sesiones 
DESC
;
                        
                      

获取拥有者的对象数量

SELECT

owner,

COUNT

(owner) number_of_objects

FROM

dba_objects

GROUP

BY

owner

ORDER

BY

number_of_objects

DESC

;

1.7 实用/数学相关的查询

把数值转换成文字

SELECT

TO_CHAR (TO_DATE (1526,

'j'

),

'jsp'

)

FROM

DUAL;

输出

one thousand five hundred twenty-six

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

这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。

                                                                                  
--search a string foo_something in package source code
SELECT
*
  
FROM
dba_source
 
WHERE
UPPER
(text) 
LIKE
'%FOO_SOMETHING%'
AND
owner = 
'USER_NAME'
;
                           
                         

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

当你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF’转换成包含 AA,BB，CC 等作为一行的表，

这样你就很容易把这些字符串插入到其他表中，并快速的做一些相关的操作。

                                                                                  
WITH
csv
     
AS
(
SELECT
'AA,BB,CC,DD,EE,FF'
                   
AS
csvdata
           
FROM
DUAL)
    
SELECT
REGEXP_SUBSTR (csv.csvdata, 
'[^,]+'
, 1, 
LEVEL
) pivot_char
      
FROM
DUAL, csv
CONNECT
BY
REGEXP_SUBSTR (csv.csvdata,
'[^,]+'
, 1, 
LEVEL
) 
IS
NOT
NULL
;
                           
                         

查询表中的最后一个记录

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

                                                                                  
SELECT
*
  
FROM
employees
 
WHERE
ROWID 
IN
(
SELECT
MAX
(ROWID) 
FROM
employees);
 
(
OR
)
 
SELECT
* 
FROM
employees
MINUS
SELECT
*
  
FROM
employees
 
WHERE
ROWNUM < (
SELECT
COUNT
(*) 
FROM
employees);
                           
                         

在 Oracle 中做行数据乘法

                                                                                  
WITH
tbl
     
AS
(
SELECT
-2 num 
FROM
DUAL
         
UNION
         
SELECT
-3 num 
FROM
DUAL
         
UNION
         
SELECT
-4 num 
FROM
DUAL),
     
sign_val
     
AS
(
SELECT
CASE
MOD (
COUNT
(*), 2) 
WHEN
0 
THEN
1 
ELSE
-1 
END
val
           
FROM
tbl
          
WHERE
num < 0)
  
SELECT
EXP (
SUM
(LN (
ABS
(num)))) * val
    
FROM
tbl, sign_val
GROUP
BY
val;
                           
                         

在 Oracle 生成随机数据

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

                                                                                  
SELECT
LEVEL
empl_id,
           
MOD (ROWNUM, 50000) dept_id,
           
TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 
'M'
,  2, 
'F'
) gender,
           
TO_DATE (
                 
ROUND (DBMS_RANDOM.VALUE (1, 28))
              
|| 
'-'
              
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
              
|| 
'-'
              
|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              
'DD-MM-YYYY'
)
              
dob,
           
DBMS_RANDOM.STRING (
'x'
, DBMS_RANDOM.VALUE (20, 50)) address
      
FROM
DUAL
CONNECT
BY
LEVEL
< 10000;
                           
                         

在 Oracle 中生成随机数值

这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。

--generate random number between 0 and 100

SELECT

ROUND (DBMS_RANDOM.VALUE () * 100) + 1

AS

random_num

FROM

DUAL;

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

这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。

                                                                                        
SELECT
1
  
FROM
TABLE_NAME
 
WHERE
ROWNUM = 1;

参考文章

没有整理与归纳的知识，一文不值！高度概括与梳理的知识，才是自己真正的知识与技能。永远不要让自己的自由、好奇、充满创造力的想法被现实的框架所束缚，让创造力自由成长吧！多花时间，关心他（她）人，正如别人所关心你的。理想的腾飞与实现，没有别人的支持与帮助，是万万不能的。

本文转自wenglabs博客园博客，原文链接：http://www.cnblogs.com/arxive/p/5821702.html

，如需转载请自行联系原作者

一、常用的查询语句

1.1 常用查询

1.2 日期/时间 相关查询

获取当前月份剩下的天数

获取两个日期之间的天数

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

1.3 数据字典查询

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

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

显示表结构

获取当前模式

修改当前模式

1.4 数据库管理查询

数据库默认信息

数据库字符设置信息

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

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

在表空间添加数据文件

增加数据文件的大小

查询数据库的实际大小

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

查询模式或者用户的大小

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

1.6 性能相关查询

查询用户 CPU 的使用率

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

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

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

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

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

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

获取拥有者的对象数量

1.7 实用/数学 相关的查询

把数值转换成文字

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

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

1.2 日期/时间相关查询

1.7 实用/数学相关的查询