常用的Oracle数据库语句 (待更新完毕)

一、常用的查询语句

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;

 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

参考文章

1.45 个非常有用的 Oracle 查询语句

posted @ 2016-08-30 14:13 wenglabs 阅读(708) 评论(0) 编辑收藏举报

刷新页面返回顶部

WENG'S LABS

Growing up with the help of the world's genius!

常用的Oracle数据库语句 (待更新完毕)

一、常用的查询语句

1.1 常用查询

1.2 日期/时间相关查询

获取当前月份剩下的天数

获取两个日期之间的天数

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

1.3 数据字典查询

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

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

显示表结构

获取当前模式

修改当前模式

1.4 数据库管理查询

数据库默认信息

数据库字符设置信息

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

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

在表空间添加数据文件

增加数据文件的大小

查询数据库的实际大小

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

查询模式或者用户的大小

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

1.6 性能相关查询

查询用户 CPU 的使用率

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

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

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

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

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

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

获取拥有者的对象数量

1.7 实用/数学相关的查询

把数值转换成文字

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

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

公告

WENG'S LABS

Growing up with the help of the world's genius!

常用的Oracle数据库语句 (待更新完毕)

一、常用的查询语句

1.1 常用查询

1.2 日期/时间 相关查询

获取当前月份剩下的天数

获取两个日期之间的天数

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

1.3 数据字典查询

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

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

显示表结构

获取当前模式

修改当前模式

1.4 数据库管理查询

数据库默认信息

数据库字符设置信息

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

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

在表空间添加数据文件

增加数据文件的大小

查询数据库的实际大小

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

查询模式或者用户的大小

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

1.6 性能相关查询

查询用户 CPU 的使用率

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

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

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

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

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

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

获取拥有者的对象数量

1.7 实用/数学 相关的查询

把数值转换成文字

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

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

公告

1.2 日期/时间相关查询

1.7 实用/数学相关的查询