Oracle常用SQL

最新推荐文章于 2025-04-01 22:37:54 发布

经历就是经验

最新推荐文章于 2025-04-01 22:37:54 发布

阅读量313

点赞数

--格式化语句： CTRL+F7

--更改当前会话架构：alter session set current_schema=ACP/SPDB_NB/COC

--大小写转换：CTRL+ALT+U

--输出：DBMS_OUTPUT.PUT_LINE('123')

--列转行：SELECT * FROM TABLE(FN_SPLIT_STR('23,22,21,20,19,18,17,16,15,14,13,12,11,10,09,08,07,06,05,04,03,02,01',','))

--行转列一：select 'create or replace view as select '|| xmlagg(xmlparse(content column_name||',' wellformed) order by column_name).getclobval() || ' from P_XDR_HOUR_USER_RSRP' sqlStr from user_tab_columns where table_name='P_XDR_HOUR_USER_RSRP';

--行转列二：select 'create or replace view as select '|| xmlagg(xmlparse(content column_name||',' wellformed) order by column_name).getclobval()|| ' from P_XDR_HOUR_USER_RSRP' sqlStr from user_tab_columns where table_name='P_XDR_HOUR_USER_RSRP';

--clob转字符串：dbms_lob.substr()

--锁表：select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id　=　o.object_id and l.session_id=s.sid;

alter system kill session '24,111';

--查找表名 select * from User_tables where table_name like '%C2I%';

--查找某个表的分区 select * from user_tab_partitions where table_name = 'P_MROL2L_UELOCATION';

--查找某个表的子分区 select * from user_tab_subpartitions where table_name = 'P_MROL2L_UELOCATION';

--删除分区 alter table P_RESULT_LTE_MR_GRID_NCELL DROP PARTITION P9;

--添加分区 ALTER TABLE P_RESULT_LTE_MR_GRID_NCELL ADD PARTITION P9 VALUES (9) TABLESPACE ACP_BASE;

--添加子分区 ALTER TABLE P_LTE_GRID_RSRP ADD PARTITION P2 VALUES (2) (SUBPARTITION P2P1 values (1)) ;

ALTER TABLE P_MR_G_LTDX MODIFY PARTITION P530100 add SUBPARTITION P530100_20170602 values (TO_DATE(' 2017-06-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ;

--打印表结构SQL Select dbms_metadata.get_ddl(object_type => 'TABLE',name => 'P_RESULT_LTE_MR_GCELL_RSRP') from dual;

--打印存储过程SQL Select dbms_metadata.get_ddl(object_type => 'PROCEDURE',name => 'PRO_D_COMM_RESULT_CONTAINS_DEL') from dual;

--创建索引

本地索引：CREATE INDEX INDEX_CP_CELLKPI_UNION_DAY_V1 ON CP_CELLKPI_UNION_DAY_V1 ("站号") local;

唯一索引：CREATE UNIQUE INDEX IDX_L_PARMETER_COMPLAINT ON P_LTE_PARMETER_COMPLAINT (ORDERNUM) ;

索引是否失效：select status from user_indexes T where table_name='P_LTE_PARMETER_COMPLAINT'

重构分区索引：alter index 索引名 rebuild subpartition P530100_20180930

重构子分区索引：alter index 索引名 rebuild partition P530100

查找分区和子分区失效索引：

select index_owner 库, index_name 索引, partition_name 分区,'' 子分区,'分区' 索引类型 from dba_ind_partitions where status ='UNUSABLE' and index_owner='COC'

union all

Select Index_Owner 库, Index_Name 索引,partition_name 分区,SUBPARTITION_NAME 子分区,'子分区' 索引类型 From DBA_IND_SUBPARTITIONS Where status = 'UNUSABLE' and index_owner='COC'

索引查询：dba_indexes user_indexes dba_ind_partitions DBA_IND_SUBPARTITIONS

--时间查询

Select * from table_name where TO_CHAR(TIMES,'YYYY-MM-DD HH24:MI:SS') = '2016-10-27 12:02:00' or version_date=to_date('2016-10-27','YYYY-MM-DD') or truncate(version_date,'DD')=to_date('2016-10-27','YYYY-MM-DD') or (TIMES between to_date('2016-10-01','YYYY-MM-DD') and to_date('2016-10-31','YYYY-MM-DD')+1);

--获取连续天时间 SELECT to_date('2018-08-01','YYYY-MM-DD')+level-1 as dates FROM dual CONNECT BY level <= to_date('2018-08-08','YYYY-MM-DD')-to_date('2018-08-01','YYYY-MM-DD')+1;

--获取连续年 SELECT (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-ROWNUM+1) || '' AS YEAR FROM DUAL CONNECT BY ROWNUM<6

--日期计算 select trunc(sysdate, 'month') 本月第一天,trunc(last_day(sysdate)) 本月最后一天,trunc(add_months(sysdate, -1), 'month') 上月第一天,trunc(last_day(add_months(sysdate, -1))) 上月最后一天,

trunc(add_months(sysdate, -12), 'month') 去年本月第一天,trunc(last_day(add_months(sysdate, -12))) 去年本月最后一天,trunc(sysdate, 'd') + 1 本周第一天,trunc(sysdate, 'd') + 7 本周最后一天 from dual

select to_char(sysdate,'d') 每周第几天 , to_char(sysdate,'dd') 每月第几天 , to_char(sysdate,'ddd') 每年第几天 , to_char(sysdate,'ww') 每年第几周 , to_char(sysdate,'mm') 每年第几月 , to_char(sysdate,'q') 每年第几季 , to_char(sysdate,'yyyy') 年 from dual;

group by rollup(a,(b,c))

--转毫秒 select TO_CHAR(TIMES,'yyyy-mm-dd hh24:mi:ss.ff') TIMES from P_LTE_GRID_RSRP SUBPartition(P52P1);

--转日期格式带毫秒 select TO_TIMESTAMP(replace(substr( SEQ,0,23),'T',' '),'yyyy-mm-dd hh24:MI:SS.ff') from P_LTE_GRID_RSRP SubPartition(P2P1);

--日期转换毫秒 SELECT TO_NUMBER(TO_DATE('2005-03-29 12:30:45', 'YYYY-MM-DD HH24:MI:SS') -TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL;

--毫秒转换日期 SELECT TO_CHAR(1112070645000 / (1000 * 60 * 60 * 24) +TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS CDATE FROM DUAL;

--数据串函数使用

http://183.230.77.22/vmphls.tc.qq.com/zGV8QcxADxxeMuHbze5PIMoTkVGdSwnZGdqa2f0bPPBz4Y-wgt05qxdJxk1gTUgKgQaJyokfC0nnqJxvFBQDEKvkHMzsfYKlVBU6NyPZ8C7C4e2bC-tZ9A/1077_6X000000000000000000000000tUrh2J.f10.ts?start=0&end=10416&brs=0&bre=479211&ver=4

substr(URI,instr(URI,'&',-1)+1)==>从右边开始取，返回ver=4

substr(URI,INSTR(URI,'/',-1)+1,INSTR(URI,'?',1,1)-INSTR(URI,'/',-1)-1)==>取中间，返回1077_6X000000000000000000000000tUrh2J.f10.ts

substr(URI,INSTR(URI,'&',1,2)+1,INSTR(URI,'&',1,3)-INSTR(URI,'&',1,2)-1)==>取二个/之间数据，返回183.230.77.22

--删除用户 drop user ACP cascade;

--删除空间 DROP TABLESPACE ACP_BASE INCLUDING CONTENTS AND DATAFILES;

--查看表空间 select * from dba_data_files

--查看临时表空间 select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

--删除空间 DROP TABLESPACE LTE_DATA_BASE INCLUDING CONTENTS AND DATAFILES;

--默认表空间是1G，自动增长不限制最大值 ALTER TABLESPACE ACP_BASE ADD DATAFILE 'D:\DB\ACP_BASE2.DBF' SIZE 1024M AUTOEXTEND ON;

--数据库链接 Select * from DEPARTMENT@LINK_ORCL130_ACP

--修改表字段 alter table P_LTE_AM_SECTOR_SCHEME modify(RSPOWER NUMBER(10,3));

--获取表注释：select * from user_tab_comments where Table_Name='P_LTE_SECTOR' order by Table_Name;

--获取字段注释：select * from user_col_comments where Table_Name='用户表' order by column_name ;

--获取表：select table_name from user_tables / all_tables / dba_tables ; --//当前用户的表

--获取表字段：select * from user_tab_columns / all_tab_columns / dba_tab_columns where Table_Name='用户表';

--常用字符函数

select LOWER('HTTP') 转小写,UPPER('www') 转大写,INITCAP('jelly think') 首字母大写,SUBSTR('注：下标从1开始', 3, 5) 截取,LENGTH('JellyThink') 长度,

ASCII('A'),CHR(65),REPLACE('jellythink', 'think', ' is good') 替换,INSTR('JellyThink', 'Jelly', 1) 索引查找,LPAD('JellyThink', 12, '*') 左补充,RPAD('JellyThink', 12, '*') 右补充,

LTRIM('**JellyThink', '*') 左删除,RTRIM('JellyThink**', '*') 右删除,TRIM(' JellyThink ') 去掉空格,TRIM('*' from '*!*JellyThink**') 去掉字符,CONCAT('Jelly', 'Think') 连接

from dual;

--常用日期函数

select TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mi:ss') 当前时间,NEXT_DAY(SYSDATE, '星期一') 下周一,LAST_DAY(SYSDATE) 本月末,

trunc(sysdate,'iw') - 7 上周一,trunc(sysdate,'iw') - 1 上周日 ,

MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) 月份差,

sysdate+1 加一天,ADD_MONTHS(sysdate,12) 加一年,TRUNC(SYSDATE) 当前年月日,TRUNC(SYSDATE,'YY') 当年第一天,TRUNC(SYSDATE,'MM') 当月第一天,

TRUNC(SYSDATE,'D') 星期天,TRUNC(SYSDATE,'DD') 当前年月日,TRUNC(SYSDATE ,'HH24') ,TRUNC(SYSDATE ,'MI'),TRUNC(SYSDATE ,'Q') 当季第一天,

TRUNC(SYSDATE ,'W') 本周第一天,TRUNC(SYSDATE ,'WW') 本周第一天,trunc(sysdate,'iw') 本周一,CURRENT_DATE 当前时间,EXTRACT(YEAR from SYSDATE) 年,

EXTRACT(MONTH from SYSDATE) 月,EXTRACT(DAY from SYSDATE) 天,ROUND(SYSDATE, 'MONTH') 当月第一天,to_char(sysdate,'DY') 周几 from dual;

--EXTRACT

SELECT SYSDATE "DATE",

EXTRACT(YEAR FROM SYSDATE) "YEAR",

EXTRACT(MONTH FROM SYSDATE) "MONTH",

EXTRACT(DAY FROM SYSDATE) "DAY",

EXTRACT(HOUR FROM SYSTIMESTAMP) "HOUR",

EXTRACT(MINUTE FROM SYSTIMESTAMP) "MINUTE",

EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"

FROM DUAL;

--判断函数

select CAST('100' as NUMBER) "CAST",DECODE(20, 10, 5, 200, 10, 20, 30) "DECODE",TRUNC(20.2183, 2),NVL(NULL, 30) NVL,NVL2(NULL, 20, 30) NVL2,

GREATEST(20, 100, 30, 20, 40, 400) 最大值,LEAST(20, 100, 30, 20, 40, 400) 最小值 from dual;

--数值函数

Select ceil(10.56) 最小整数,floor(10.56) 最大整数,mod(7,5) 模余数,power(3,2) 幂,round(1234.5678,2) 四舍五入,sqrt(25) 平方根 from dual;

--数据删除后恢复(Drop)

drop table P_GD;

flashback table P_GD to before drop ;

flashback table P_GD to before drop rename to P_GD1

alter table P_GD1 rename to P_GD;

--数据删除后恢复(Delete)，找出来数据库服务器的时间点

delete from ACP_GROUP_GRID where grid_id=911;

alter table ACP_GROUP_GRID enable row movement; --开启行移动功能闪退回

flashback table ACP_GROUP_GRID to timestamp to_timestamp('2018-12-07 18:27:00','yyyy-mm-dd hh24:mi:ss');

alter table ACP_GROUP_GRID disable row movement;--关闭行移动功能闪退回

--scn恢复删除

select current_scn from v$database; --(切换到sys用户或system用户查询) -->找出来scn:4578748，

select * from ACP_GROUP_GRID as of scn 4578748;--查询如果没有scn中记录删除的数据，继续缩小scn

alter table ACP_GROUP_GRID enable row movement; --开启行移动

flashback table ACP_GROUP_GRID to scn 4578748;

alter table ACP_GROUP_GRID disable row movement;--关闭行移动

--跨库数据库链接配置

转载：https://note.youdao.com/ynoteshare1/index.html?id=d9abfde148d0d16c130bed985f771f22&type=note