Oracle常用语句，一招吃天下（不断更新中...）

转手机号的公式：MSISDN*30+3

 

--锁表：

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')) ;

--创建索引

Create index local INDEX_GRID_RSRP on P_RESULT_LTE_MR_GRID_RSRP (GRID_NO);

--时间查询

Select * from P_MROL2L_UELOCATION where TO_CHAR(TIMES,'yyyy-mm-dd hh24:mi:ss') = '2016-10-27 12:02:00'    ;

Select * from P_MROL2L_UELOCATION where TIMES between to_date('2016-10-27 00:00:00','yyyy-mm-dd hh24:mi:ss')  and to_date('2016-10-27 23:59:59','yyyy-mm-dd hh24:mi:ss');

Select Count(*) from P_MROL2L_UELOCATION;

Select Count(*) from P_MROL2L_UELOCATION Partition(P_KM);

Select Count(*) from P_MROL2L_UELOCATION SUBPartition(P_SYS20100101_0000);

表空间 索引

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

--Case When

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

decode(1,1,0)

Case When 1=1 then 1 else 0 end

--NVL(X,VALUE) 如果X为空，返回value，否则返回X 示例：NVL(COMM,100) 注：X和Value类型相同

--NVL2(x,value1,value2) 如果x非空，返回value1，否则返回value2 NVL2(COMM,comm+100,200) 注：value2会转换类型成value1

--NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

--COALESCE(表达式1,表达式2,...,表达式n) 返回最左边的第一个非空表达式，所有的表达式类型相同

 

--转毫秒

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

 

--打印一个表的创建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;

 

 --删除用户

drop user ACP cascade;

 

--删除空间

DROP TABLESPACE ACP_BASE INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE ACP_TEMP INCLUDING CONTENTS AND DATAFILES;

 

 --删除用户

 drop user LTE cascade;

 

--删除空间

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 user_tab_columns where Table_Name='用户表' order by column_name;

select * from user_tab_columns where column_name='RSPOWER' order by column_name;

 

--修改表字段

alter table P_LTE_AM_SECTOR_SCHEME modify(RSPOWER NUMBER(10,3));

 

--RSPOWER

 

--获取表注释：

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; --//当前用户的表     

select table_name from all_tables;-- //所有用户的表

select table_name from dba_tables; --//包括系统表

select table_name from dba_tables where owner='zfxfzb';

 

/*  获取表字段：*/

select * from user_tab_columns where Table_Name='P_LTE_SECTOR';

select * from all_tab_columns where Table_Name='用户表';

select * from dba_tab_columns where Table_Name='用户表';

 

/*  获取表注释：*/

select * from user_tab_comments;

 

/* 获取字段注释：*/

select * from user_col_comments;

 

--顺序

--LTE中导入MR原始的数据表

select * from P_MROLTE2LTE;

--LTE建模型后的表

select * from P_MRO_C2I;

--LTE中UE栅格化前的表

select * from P_MROL2L_UELOCATION;

--ACP栅格化后存储MR数据的表

select * from acp.P_LTE_MR_GRID_RSRP;

 

-----------------------------更新数据----------------

create table tmp123 as select * from P_LTE_GRID_RSRP Partition(P29) where SEQ!='4f5e0230-db02-4fb5-a054-ee5d4a81d194';

alter table P_LTE_GRID_RSRP drop partition P29;

ALTER TABLE P_LTE_GRID_RSRP ADD PARTITION P29 VALUES (29) TABLESPACE ACP_BASE;

 

INSERT INTO P_LTE_GRID_RSRP(PROJECT_ID, PROGRAMME_ID, TIMES, SECTOR_ID, EARFCN, PCI, LONGITUDE, LATITUDE, GRID_ID, GRID_NO, LEFT_DOWN_X, LEFT_DOWN_Y,RIGHT_DOWN_X, RIGHT_DOWN_Y, LEFT_UP_X, LEFT_UP_Y, RIGHT_UP_X, RIGHT_UP_Y, RSRP, SINR, RN, SEQ)

Select  PROJECT_ID, PROGRAMME_ID, TIMES, SECTOR_ID, EARFCN, PCI, LONGITUDE, LATITUDE, GRID_ID, GRID_NO, LEFT_DOWN_X, LEFT_DOWN_Y, RIGHT_DOWN_X, RIGHT_DOWN_Y, LEFT_UP_X, LEFT_UP_Y, RIGHT_UP_X, RIGHT_UP_Y, RSRP, SINR, RN, SEQ from TMP123;

DROP table tmp123;

--------------------完---------更新数据----------------

 

--无关联插入数据

insert into P_LTE_ENODEB_WARNING (PROJECT_ID,SECTOR_ID,SECTOR_NAME,ENODEBID,ENODEBNAME,CODE,S_LEVEL,S_DATE,S_RECOVERYDATE,S_NAME,NETYPE,WARNTYPE,CAUSE)

With WAR as

(

select PROJECT_ID,SECTOR_ID,SECTOR_NAME,ENODEBID,ENODEBNAME,CODE,S_LEVEL,S_DATE,S_RECOVERYDATE,S_NAME,NETYPE,WARNTYPE,CAUSE ,row_number() over(ORDER BY SECTOR_ID) NUM

from P_LTE_ENODEB_WARNING Partition(P28)

),SEC as

(

Select PROJECT_ID,SECTOR_ID,SECTOR_NAME,ENODEBID ,row_number()  over(ORDER BY SECTOR_ID) NUM from P_LTE_SECTOR Partition(P9)

)

select s.PROJECT_ID,s.SECTOR_ID,s.SECTOR_NAME,s.ENODEBID,r.ENODEBNAME,r.CODE,r.S_LEVEL,r.S_DATE,r.S_RECOVERYDATE,r.S_NAME,r.NETYPE,r.WARNTYPE,r.CAUSE

from WAR r,SEC s where r.NUM=s.NUM;

 

----------------创建用户----------------

CREATE USER WAP IDENTIFIED BY WAP;

 

--授权

BEGIN

EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE TO WAP';

EXECUTE IMMEDIATE 'GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO WAP';

EXECUTE IMMEDIATE 'GRANT ALTER TABLESPACE,CREATE TABLESPACE,CREATE ANY TABLE,CREATE ANY VIEW TO WAP';

EXECUTE IMMEDIATE 'GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO WAP';

EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY,SELECT ANY TABLE TO WAP';

EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO WAP';

EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_LOCK TO WAP';

EXECUTE IMMEDIATE 'call dbms_java.grant_permission( ''WAP'', ''SYS:java.io.FilePermission'', ''<<ALL FILES>>'', ''execute'')';

EXECUTE IMMEDIATE 'call dbms_java.grant_permission( ''WAP'', ''SYS:java.lang.RuntimePermission'', ''writeFileDescriptor'', '''')';

EXECUTE IMMEDIATE 'call dbms_java.grant_permission( ''WAP'', ''SYS:java.lang.RuntimePermission'', ''readFileDescriptor'', '''')';

END;

 

------------------ 创建表空间

create tablespace WAP_BASE

logging

datafile

'D:\DB\WAP_BASE1.dbf' size 100M autoextend on next 1000M maxsize unlimited

extent management local autoallocate

segment space management auto;

 

create temporary tablespace WAP_TEMP

tempfile 'D:\DB\WAP_TEMP1.dbf' size 100M autoextend on next 1000M maxsize unlimited

extent management local;

 

--用户和表空间关联

ALTER USER WAP DEFAULT TABLESPACE WAP_BASE temporary tablespace WAP_TEMP;

----------完------创建用户----------------

 

------------------ 查看活动链接

Select username,osuser,machine,process,terminal,program

From V$SESSION

--筛选

--Where USERNAME='用户名' And machine='机器名';

Select username,osuser,machine,process,terminal,program

From V$SESSION

     Where USERNAME='LTE' And machine='WORKGROUP\DTKD-126';

------------------ 查看后台挂起语句

Select * From  v$sqlarea

--联查，某个用户，某台机挂起的语句

Select  sql_text,username,machine,program 

From  v$sqlarea，V$SESSION

Where hash_value=sql_hash_value And USERNAME='LTE'

     And machine='WORKGROUP\DTKD-126';

 

 

------------------ 批量删除表

begin

  for t in (select table_name from user_tables where table_name like '%_BG' ) loop

   execute immediate 'drop table '||t.table_name;

  end loop;

end;

------------------ 查询表里面的列信息

Select * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MRS_MRO_RESULT_CITYS'

          ORDER BY COLUMN_ID

------------------ 添加/删除 主键

Alter Table p_gsm_sector Drop Constraint pk_zhujian_p_gsm_sector;

 

Alter Table p_gsm_sector Add Constraint pk_zhujian_p_gsm_sector

Primary Key(sector_id);

 

------------------ 实现 Select * Into

Create table newTable As Select * From p_lte_sector

 

------------------- 动态执行语句

-- 执行有参数的语句，using 后面跟参数

execute immediate 'drop table :1'

Using 'UserTableName';

 

 

-------------------- 查看已删除的表

 select * from recyclebin

        where original_name like 'E_L%' or original_name like 'SL_%';

 

--------------------------- dbms_output.put_line 超长输出内容

如果是存储过程，有超长语句记得在begin下一行加上：

     DBMS_OUTPUT.ENABLE (buffer_size=>null); 

不限制DBMS_OUTPUT打印语句长度。

 

for i in 1 .. length(str) - length(replace(str, chr(10))) + 1

loop

dbms_output.put_line(substr(

str

, instr(chr(10) ||str, chr(10), 1, i)

, instr(str|| chr(10), chr(10), 1, i) -

instr(chr(10) ||str, chr(10), 1, i))

);

end loop;

 

-----------------  循环语句

BEGIN

for v_counter in 1 .. 99

loop

       dbms_output.put_line('执行到：'|| v_counter );

       EXECUTE IMMEDIATE ' drop table P_SL_TMPSECTOR_'||v_counter;

END LOOP;

END;

 

-----------------  批量删除表

begin

  for t in (select table_name from user_tables where table_name like 'P_SL_TMPSECTOR_%' )

     loop

   execute immediate 'drop table '||t.table_name;

  end loop;

end;