oracle常用SQL

ask_baidu

已于 2024-01-12 17:43:12 修改

阅读量708

点赞数

分类专栏：数据库文章标签： oracle 数据库 dba

于 2022-11-04 22:49:30 首次发布

本文链接：https://blog.csdn.net/ask_baidu/article/details/127697748

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

概述：
oracle常用sql：查询分区字段、查询所有表每天的全量和增量数据等

1. 查询分区字段

select * from dba_part_key_columns where name='大写表名';

2. 查询容量大于10G的所有表

SELECT owner,segment_name,segment_type,sum(bytes/1024/1024/1024) 'sum(GB)' FROM 
DBA_SEGMENTS GROUP BY owner,segment_name,segment_type HAVING sum(bytes/1024/1024/1024)>10

3. 查看表空间数据总量和每天的增量

引用

select t3.tablespace_name,t4."Total(GB)",t4."Used(GB)" - t3."Used(GB)" AS "Increment(GB)",to_char(trunc(sysdate - 1),'yyyy/mm/dd') "TIME"
from (select t2.name tablespace_name,
case when t2.name not like 'UNDO%' then round(t1.tablespace_size * 8 / 1024 / 1024)
when t2.name like 'UNDO%' then round(t1.tablespace_size * 8 / 1024 / 1024 / 2)
END as "Total(GB)",round(t1.tablespace_usedsize*8 / 1024 / 1024) "Used(GB)",t1.rtime
from DBA_HIST_TBSPC_SPACE_USAGE t1, v$tablespace t2
where t1.tablespace_id = t2.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) t3,
(select t2.name tablespace_name,
case when t2.name not like 'UNDO%' then round(t1.tablespace_size * 8 / 1024 / 1024)
when t2.name like 'UNDO%' then round(t1.tablespace_size * 8 / 1024 / 1024 / 2)
END as "Total(GB)",
round(t1.tablespace_usedsize*8 / 1024 / 1024) "Used(GB)",
t1.rtime
from DBA_HIST_TBSPC_SPACE_USAGE t1, v$tablespace t2
where t1.tablespace_id = t2.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) t4
where t3.tablespace_name = t4.tablespace_name;

4. 统计oracle每天的归档日志增长情况（占用空间大小）

SELECT t.archiveDate,t.archiveSize AS archiveSize_GB FROM
(SELECT trunc(first_time) AS ArchiveDate,sum(block_size * blocks) / 1024 / 1024 / 1024 AS archiveSize
FROM v$archived_log
GROUP BY trunc(first_time)
) t
ORDER BY t.archiveDate DESC

5. 统计oracle每小时的归档日志产生数量

SELECT
    THREAD# id,substr(to_char(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  DAY,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, 
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
  sum(decode(substr(to_char(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
  count(*) TOTAL
FROM
  v$log_history t
GROUP BY substr(to_char(first_time, 'MM/DD/RR HH:MI:SS'),1,5),THREAD#
ORDER BY id,substr(to_char(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC

6. oracle造数据

DECLARE  --声明函数
i INT;
BEGIN 
i:=1;
WHILE(i<=100000000) 
LOOP  --设置loop循环
    i:=i+1;
    INSERT INTO student (ID, NAME , SEX ) 
    VALUES (i, '丽丽_'||i, '女');
   
 if MOD(i, 1000) = 0 THEN
   COMMIT;
  END IF;
END LOOP;
END;

7. oracle替换ASCII字符回车、换行、空字符

在这里插入图片描述

select replace(replace(replace(userName,chr(13)),chr(10)),chr(0)) userName from table;

8. 字符串日期转Date类型

to_date('2022-12-12 15:00:00','YYYY-MM-dd HH24:mi:ss')

9. 查询哪些分区有数据

SELECT partition_name,num_rows,high_value  FROM all_tab_partitions WHERE table_name=upper('表名') AND table_owner ='用户名' AND num_rows>0;

10. 根据分区名称查询数据

分区名称即是partition_name，不要加单引号

select * from 表名  partition(分区名称);

11. 重建分区命令

alter table 表名 truncate partition 分区名称;

12. 根据日期获取该表的分区名称

WITH t1 AS (SELECT table_name,partition_name,to_date (
trim (
'''' from regexp_substr (
extractvalue (
dbms_xmlgen.getxmltype (
'select high_value from all_tab_partitions where table_name='''
|| table_name
|| ''' and table_owner = '''
|| table_owner
|| ''' and partition_name = '''
|| partition_name
|| ''''),
'//text()'),
'''.*?''')),
'syyyy-mm-dd hh24:mi:ss') partitionDate
FROM all_tab_partitions
WHERE  table_name=upper('表名') AND table_owner = '用户名'
)
SELECT partition_name FROM t1 WHERE to_char(partitionDate,'YYYY-MM-dd') = '2023-01-01';

13.统计分区的记录数并写到all_tab_partitions的num_rows字段中

analyze table 表名 compute statistics;

14.根据日期得到是当月的第几周

SELECT TO_CHAR (to_date('2023-01-01','YYYY-MM-dd'), 'IW') - TO_CHAR (TRUNC (to_date('2023-01-01','YYYY-MM-dd'), 'MM'), 'IW') + 1  WEEK FROM DUAL;

15.根据开始和结束日期得到所有的年

SELECT 
	to_number(substr('2023-01',0,4))+rownum-1
	year
FROM DUAL
CONNECT BY ROWNUM <=to_number(substr('2023-03',0,4))-to_number(substr('2023-01',0,4))+1

16.根据开始和结束日期得到所有的年月

SELECT 
	TO_CHAR(ADD_MONTHS(TO_DATE('2023-01', 'YYYY-MM'),ROWNUM - 1), 'YYYY-MM')
	year_month
FROM DUAL
CONNECT BY ROWNUM <=MONTHS_BETWEEN(TO_DATE('2023-03', 'YYYY-MM'), TO_DATE('2023-01', 'YYYY-MM')) + 1

17.获取近五年的年份

SELECT to_char(SYSDATE,'YYYY') - LEVEL + 1 as year
FROM dual
CONNECT BY LEVEL <= 5
ORDER BY to_char(SYSDATE,'YYYY') - LEVEL + 1 ASC

18.根据日期得到年、季度、月、周

SELECT
	to_char(create_date, 'YYYY-MM') YEAR_MONTH,
	to_number(to_char(create_date, 'YYYY')) YEAR,
	to_char(create_date, 'Q') Q, 
	to_number(to_char(create_date, 'mm')) MONTH,
	to_char(create_date, 'w') week
FROM table;

19.提取字符串中的数字

 提取字段中的数字：如 学生1003学号，结果1003
 REGEXP_SUBSTR(ORG_NAME,'[0-9]+')

20. 特殊值拆分并查询

不规范的值拆分成行如city 上海/天津,长沙,郑州 结果
 city
 上海
 天津 
 长沙
 郑州
 比如 选中上海,长沙，返回字符串为“上海,长沙”
 1.city作为查询条件：
 SELECT
	REGEXP_SUBSTR( city, '[^/]+', 1, L ) city
FROM
	student_info,
	( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL < 3 ) --限制一下level数量，提高查询速度。否则数据较多的情况下，会一直查询不出结果
WHERE
    city is not null 
    and 
	L ( + ) <= regexp_count ( city, '/' ) + 1  -- 计算需要分割后的数量
ORDER BY
	city
 2.主表查询用法：
 select *  from student_info where regexp_like (city,regexp_replace(city,',','|'));--英文下的逗号替换为正则表达式的竖线|，意思是字段city中包含上海或者长沙的记录都会被查询出来