ORACLE 一些常见操作

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'  //表名