这里我们介绍的是 40+ 个非常有用的 Oracle 查询语句，主要涵盖了日期操作，获取服务器信息，获取执行状态，计算数据库大小等等方面的查询。这些是所有 Oracle 开发者都必备的技能，所以快快收藏吧！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 | ****************************** 日期/时间 相关查询 ***************************** -- 1、获取当前月份的第一天 select trunc (sysdate, 'month') "First day of current month" from dual; -- 2、获取当前月份的最后一天 select trunc (last_day (sysdate)) "Last day of current month" from dual; -- 3、获取当前年份的第一天 select trunc (sysdate, 'YEAR') "Year First Day" from dual; -- 4、获取当前年份的最后一天 select add_months (trunc (sysdate, 'YEAR'), 12) - 1 "Year Last Day" from dual -- 5、获取当前月份的天数 select cast (to_char (last_day (sysdate), 'dd') as int) number_of_days from dual; -- 6、获取当前月份剩下的天数 select sysdate, last_day (sysdate) "Last", last_day (sysdate) - sysdate "Days left" from dual; -- 7、获取两个日期之间的天数 -- 方法一 select round((months_between(to_date('2015-05-12','yyyy-mm-dd'), sysdate) * 30), 0) num_of_days from dual; -- 方法二 select trunc(sysdate) - trunc(to_date('2015-05-9','yyyy-mm-dd')) from dual; -- 8、显示当前年份截止到上个月每个月份开始和结束的日期 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 '.'); -- 9、获取直到目前为止今天过去的秒数（从 00：00 开始算 select (sysdate - trunc (sysdate)) * 24 * 60 * 60 num_of_sec_since_morning from dual; -- 10、获取今天剩下的秒数（直到 23：59：59 结束） select (trunc (sysdate+1) - sysdate) * 24 * 60 * 60 num_of_sec_left from dual; ****************************** 数据字典 相关查询 ***************************** -- 11、检查在当前数据库模式下是否存在指定的表 select table_name from user_tables where table_name = 'TABLE_NAME'; -- 12、检查在当前表中是否存在指定的列 select column_name as found from user_tab_cols where table_name = 'TABLE_NAME' and column_name = 'COLUMN_NAME'; -- 13、显示表结构 -- 方法一: 括号内容大写 select dbms_metadata.get_ddl('TABLE','ACCT') from dual; -- 方法二: 拼接方式（NO，必须大写） select t.table_name,t.column_name,t.data_type,t.data_length,t.nullable,t.column_id,c.comments, (select case when t.column_name=m.column_name then 1 else 0 end from dual) iskey from user_tab_cols t, user_col_comments c, (select m.column_name from user_constraints s, user_cons_columns m where lower(m.table_name)='表名' and m.table_name=s.table_name and m.constraint_name=s.constraint_name and s.constraint_type='P') m where lower(t.table_name)='表名' and c.table_name=t.table_name and c.column_name=t.column_name and t.hidden_column='NO' order by t.column_id -- 方法三。使用sql命令 desc 表名 -- 14、获取当前模式 -- 这是另一个可以获得当前模式的名字的查询语句。有关sys_context的用法参数很多，详情自寻 select sys_context ('userenv', 'current_schema') from dual; -- 15、修改当前模式 -- 这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式 alter session set current_schema = new_schema; ****************************** 数据库管理 相关查询 ***************************** -- 16、返回 Oracle 数据库版本 select * from v$version; -- 17、返回一些系统默认的信息（dba用户登录） select username, profile, default_tablespace, temporary_tablespace from dba_users; -- 18、显示数据库的字符设置信息 select * from nls_database_parameters; -- 19、获取 Oracle 版本 select value from v$system_parameter where name = 'compatible'; -- 20、存储区分大小写的数据，但是索引不区分大小写 -- 有些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询， -- 所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求 。 create table tab (col1 varchar2 (10)); create index idx1 on tab (upper (col1)); analyze table a compute statistics; -- 21、调整没有添加数据文件的表空间 --- 另一个 DDL 查询来调整表空间大小 alter database datafile '/work/oradata/STARTST/STAR02D.dbf' resize 2000m; -- 22、检查表空间的自动扩展开关 -- 方法一 select substr (file_name, 1, 50), autoextensible from dba_data_files; -- 方法二 select tablespace_name, autoextensible from dba_data_files; -- 23、在表空间添加数据文件 alter tablespace data01 add datafile '/work/oradata/STARTST/data01.dbf' size 1000m autoextend off; -- 24、增加数据文件的大小 alter database datafile '/u01/app/Test_data_01.dbf' resize 2g; -- 25、查询数据库的实际大小、以GB为单位 select sum (bytes) / 1024 / 1024 / 1024 as gb from dba_data_files; -- 26、查询数据库中数据占用的大小或者是数据库使用细节 select sum (bytes) / 1024 / 1024 / 1024 as gb from dba_segments; -- 27、查询模式或者用户的大小、以 MB 为单位给出用户的空间大小 select sum (bytes / 1024 / 1024) "size" from dba_segments where owner = '&owner'; -- 28、查询数据库中每个用户最后使用的 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; ****************************** 性能 相关查询 ***************************** -- 29、查询每个用户 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; -- 30、显示运行中的数据库长查询进展情况 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; -- 31、获取当前会话 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. -- 32、查询特定的模式或者表中执行的最后一个 SQL 语句 select created, timestamp, last_ddl_time from all_objects where owner = 'MYSCHEMA' and object_type = 'TABLE' and object_name = 'EMPLOYEE_TABLE'; -- 33、查询每个执行读取的前十个 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; -- 34、在视图中查询并显示实际的 Oracle 连接 select osuser, username, machine, program from v$session order by osuser; -- 35、查询并显示通过打开连接程序打开连接的组 select program application, count (program) numero_sesiones from v$session group by program order by numero_sesiones desc; -- 36、查询并显示连接 Oracle 的用户和用户的会话数量 select username usuario_oracle, count (username) numero_sesiones from v$session group by username order by numero_sesiones desc; -- 37、获取拥有者的对象数量 select owner, count (owner) number_of_objects from dba_objects group by owner order by number_of_objects desc; ****************************** 实用/数学 相关的查询 ***************************** -- 38、把数值转换成文字 -- 更多信息查看：http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/ select to_char (to_date (1526, 'j'), 'jsp') from dual; -- 输出：one thousand five hundred twenty-six -- 39、在包的源代码中查询字符串 -- 这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用 select * from dba_source where upper (text) like '%FOO_SOMETHING%' and owner = 'USER_NAME'; -- 40、把用逗号分隔的数据插入的表中 -- 当你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 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; -- 41、查询表中的最后一条数据记录 -- 方法一 select * from dept where rowid in (select max (rowid) from 表名); -- 方法二 select * from dept minus select * from dept where rownum < (select count (*) from 表名); -- 42、在 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; -- 43、在 oracle 生成随机数据 -- 每个开发者都想能轻松生成一堆随机数据来测试数据库多好，下面这条查询语句就可以满足你，它可以在 oracle 中生成随机的数据插入到表中 -- http://viralpatel.net/blogs/generating-random-data-in-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; -- 44、在 oracle 中生成随机数值 -- 这是 oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。 --generate random number between 0 and 100 select round (dbms_random.value () * 100) + 1 as random_num from dual; -- 45、检查表中是否含有任何的数据 -- 这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。 select 1 from 表名 where rownum = 1; |
