About Oracle 多用Sql总结

多用SQL语句

--清空表数据并重新插入数据
TRUNCATE TABLE   DWR_GLS_HIS_AF_TEST ;
INSERT INTO DWR_GLS_HIS_AF_TEST
SELECT * FROM DWR_GLS_HIS_AF WHERE PERIOD_DATE <= TO_DATE('20180401000000','YYYYMMDD HH24:MI:SS');
COMMIT;

--统计
SELECT count(0) FROM DWR_GLS_HIS_AF WHERE PERIOD_DATE <= TO_DATE('20180401000000','YYYYMMDD HH24:MI:SS');

--删除索引
drop index IDX_DWR_GLS_HIS_AF_TEST_1;
drop index IDX_DWR_GLS_HIS_AF_TEST_2;

--删除表主键
alter table DWR_GLS_HIS_AF_TEST drop constraint DWR_GLS_HIS_AF_TEST_PK;

 
ORA-01658: unable to create INITIAL extent for segment in tablespace  EDW_COM_DAT;  


SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME; 

--查询表空间使用情况
select a.tablespace_name as "表空间名",
       a.bytes / 1024 / 1024 as "表空间大小(M)",
       (a.bytes - b.bytes) / 1024 / 1024 as "已使用空间(M)",
       b.bytes / 1024 / 1024 "空闲空间(M)",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;
 
--马上回收空间：

　　alter table DWT_WIP_EOH_LOT_M_S truncate partition 'SYS_P11045' drop storage; 

--删除表分区	
	ALTER TABLE table_name DROP PARTITION partition_name; 

--查看表中某个分区中的数据
Select * from tablename partition(partitionname);


--查看表大小 (G)
select segment_name,sum(bytes/1024/1024/1024) bytes 
from user_segments 
where tablespace_name = 'EDW_GLS_DAT'
GROUP BY SEGMENT_NAME 
ORDER BY bytes DESC;

--ORA_SCN
SELECT to_char(scn_to_timestamp(ORA_ROWSCN),'YYYYMMDD HH24:MI:SS') AA,T.* FROM TABLE_NAME;

--查看锁死的信息

select b.OWNER,b.OBJECT_NAME,b.object_type,a.OBJECT_ID,a.session_id,a.LOCKED_MODE,d.SID,d.SERIAL#,e.SQL_TEXT
from v$locked_object a 
join dba_objects b on a.OBJECT_ID = b.object_id
join v$locked_object c on b.OBJECT_ID = c.OBJECT_ID 
join v$session d on  a.SESSION_ID = d.SID
join v$sql e on d.SQL_ID = e.SQL_ID
;

--杀掉进程
alter system kill session 'sid,serial#'  

--修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;

--查看DDL语句：
select dbms_metadata.get_ddl('TABLE','DWT_WIP_EOH_GLS_A_S') as text from dual

--查看Oracle操作记录
select * from v$sqlarea t
where sql_text like '%DWR_WMS_TIMELY3104_V525%'-- and 
--FIRST_LOAD_TIME >= to_date('20180914080000','yyyymmdd hh24:mi:ss')
 order by t.FIRST_LOAD_TIME desc;
 

--修改字段长度
ALTER TABLE tablename MODIFY LOT_COMMENT VARCHAR2(bytes);

--格式由NUMBER(19)改为NUMBER
ALTER TABLE dwr_lot_assembly_his_c MODIFY (CONSUME_MAIN_QTY NUMBER);
ALTER TABLE dwr_lot_assembly_his_c MODIFY (CONSUME_SUB_QTY NUMBER);

--x修改表名
ALTER TABLE dwr_lot_assembly_c RENAME COLUMN CONSUME_MAIN_QTY TO CONSUME_MAIN_QTY_tmp;
--增加列
ALTER TABLE dwr_lot_assembly_c ADD CONSUME_MAIN_QTY NUMBER;
--更新列数据
UPDATE dwr_lot_assembly_c SET CONSUME_MAIN_QTY=CONSUME_MAIN_QTY_tmp;
COMMIT;

SELECT CONSUME_MAIN_QTY,CONSUME_MAIN_QTY_tmp FROM dwr_lot_assembly_c;
--删除列
ALTER TABLE dwr_lot_assembly_c DROP COLUMN CONSUME_MAIN_QTY_tmp;

--创建位图索引（）
CREATE BITMAP INDEX IDX_NAME ON 表名(字段名) NOLOGGING LOCAL;

--存储过程
CREATE OR REPLACE PROCEDURE LOOP_INSERT_INTO(startdate in varchar,enddate in varchar)
AS
V_START_DAY VARCHAR2(64);
V_END_DAY VARCHAR2(64);
V_EXECUTE_SQL VARCHAR2(400);

CURSOR CUR_STR IS
select to_char((trunc(to_date(startdate,'yyyymmdd') + Rownum - 1,'dd')),'yyyymmdd') START_DAY,to_char((trunc(to_date(startdate,'yyyymmdd') + Rownum,'dd')),'yyyymmdd') END_DAY from dual connect by rownum <= to_date(enddate,'yyyymmdd') - to_date(startdate,'yyyymmdd');
BEGIN
    FOR CUR_RESULT IN CUR_STR LOOP
      V_START_DAY:=CUR_RESULT.START_DAY;
      V_END_DAY:=CUR_RESULT.END_DAY;
      V_EXECUTE_SQL:='INSERT INTO DWR_GLS_UNIT_HIS_AF SELECT * FROM DWR_GLS_UNIT_HIS_AF_BAK WHERE PERIOD_DATE>=TO_DATE('''||CUR_RESULT.START_DAY||'080000'||''','||'''yyyymmddhh24miss'''||') AND PERIOD_DATE<TO_DATE('''||CUR_RESULT.END_DAY||'080000'''||','||'''yyyymmddhh24miss'||''')';
      --DBMS_OUTPUT.PUT_LINE(V_EXECUTE_SQL);
      --DBMS_OUTPUT.PUT_LINE('COMMIT');
      EXECUTE IMMEDIATE V_EXECUTE_SQL;
      COMMIT;
    END LOOP;
END;

ALTER TABLE dwt_readytoin_m ADD interface_time DATE default SYSDATE;
ALTER TABLE DWT_CT_SHIPLOT DROP COLUMN lot_alias;

--数据表删除列流程
ALTER TABLE DWT_CT_SHIPLOT SET UNUSED COLUMN panel_qty;
ALTER TABLE DWT_CT_SHIPLOT DROP UNUSED COLUMNS;
--Parallel
/*
强行启用并行度来执行当前SQL;强行启用Oracle的多线程处理功能。
但本身启动这个功能，也是要消耗资源与性能的。所有，一般都会在返回记录数大于100万时使用，效果也会比较明显。
*/

 语法： /*+ parallel(table_short_name --表别名,cash_number --小于物理机内核数)*/ --必须注释起来使用

/*这个可以加到insert、delete、update、select的后面来使用（和rule的用法差不多） 
开启parallel功能的语句是：

alter session enable parallel dml;
这个语句是DML语句哦，如果在程序中用，用execute的方法打开。

*/

-- NO_INDEX
/*
索引不合适SQL查询，使用此方法屏蔽索引
*/
语法：/*+ NO_INDEX(table_short_name IndexName) */
/*追加在 Select之后使用 
sql*plus ：set autotrace on
*/ 
屏蔽表中全部索引语法： /*+ NO_INDEX (table_short_name)*/