1.自定义函数查询
SELECT * FROM USER_SOURCE WHERE NAME = 'IS_NUMBER'; IS_NUMBER函数名称
2.替换
REPLACE ( string_expression , string_pattern , string_replacement )
参数
string_expression 要搜索的字符串表达式。string_expression 可以是字符或二进制数据类型。
string_pattern 是要查找的子字符串。string_pattern 可以是字符或二进制数据类型。string_pattern 不能是空字符串 ('')。
string_replacement 替换字符串。string_replacement 可以是字符或二进制数据类型。
返回类型
如果其中的一个输入参数数据类型为 nvarchar,则返回 nvarchar;否则 REPLACE 返回 varchar。
如果任何一个参数为 NULL,则返回 NULL。
上面都是官话,不好懂!翻成白话:REPLACE(String,from_str,to_str) 即:将String中所有出现的from_str替换为to_str。
3.分组排序
row_number() over (order by zq)
4.case when then else end
case tt when '测试' then '1' else '0' end
当字段tt为'测试' 取值为1,不等于取值为‘0’
5.oracle里的to_char是什么意思
比如一个字段类型是date类型,那么查询出来的结果想要2009-09-09,就要
select to_char(字段名,'yyyy-mm-dd') from...
查系统时间
select to_char(sysdate,'yyyy-mm-dd') from dual
6.oracle 的exists 的用法
https://www.cnblogs.com/gaofangquan/p/7356675.html
7.获取数据库使用情况
select tablespace_name "表空间名",
max_gb "总空间(G)",
used_gb "已使用(G)",
TO_CHAR(ROUND(used_gb / max_gb * 100,2),'990.99') || '%' "使用比"
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
round(sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) / power(2, 30),
2) max_gb
from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name)
order by 1 desc;
8.管理员密码忘记办法
1.输入下面命令,进去了
sqlplus / as sysdba
2.alter user system identified by system
https://jingyan.baidu.com/article/36d6ed1f86eaae1bce488350.html
9.快速插入另一张表的数据
insert into 表1 select * from 表2;
10.查看锁表,解锁
--查看锁表
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode,
sess.sql_id
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
--查看导致锁表的sql语句是那一条
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
--杀锁表进程,sid,serial#
alter system kill session '738,1429';
11.查询一段时间的数据
createtime BETWEEN TO_DATE('20190101','yyyymmdd') AND TO_DATE('20200120 00:00:00','yyyymmdd hh24:mi:ss')
to_char(e.cjsj, 'yyyymmdd') >= to_char( sysdate - 6 , 'yyyymmdd hh24:mi:ss')
12.创建索引
create index 索引名
on 表名(字段名);
//删除索引
drop index 索引
13.查字符集
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
14. 根据分组,排序取最新的一条数据
select * from
(
select row_number() over (partition by t.sblsh order by t.s_last_updated desc) rn ,t.* from test1 t where
exists( select distinct p.sblsh from test2 p where p.sblsh=t.sblsh)
)
where rn=1
//sblsh 分组字段,s_last_updated排序字段 distinct 因为test2 有相同的条件
15.查看表空间,以及使用情况
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 ;
16.查询登陆会话信息
select * from v$session where username is not null and osuser='zhouxy' order by logon_time desc
17.查看表大小
SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name='EP_SC106' //表名