common sqls

      
create index table_name_INDEX on table_name(BATCH_ID,REGION,RATING,ULT_PARENT_NAME ) ;
       
select * from v$session where sql_id  in 
      ( select sql_id from v$sql where sql_text like '%table_name%');
select * from v$session where sql_id = 'ccfuhxhykcn0f';
       
select * from v$sql where sql_id in ('bhwknvq9f1v2s','2at3x2umsdsyp','4ava239hg70kf');
       
alter system kill session '300,1387'
       
select row_number() over(partition by a.REGION,a.RATING,a.ULT_PARENT_NAME order by a.REGION,a.RATING,a.ULT_PARENT_NAME desc) rd,  
                      a.REGION,a.RATING,a.ULT_PARENT_NAME,a.rowid row_id  
                 from table_name a;
      
      


select row_number() over(partition by a.REGION,a.RATING,a.ULT_PARENT_NAME order by a.REGION,a.RATING,a.ULT_PARENT_NAME desc) rd,  
                      a.REGION,a.RATING,a.ULT_PARENT_NAME,  
                      a.rowid row_id  
                 from table_name a ;
                 
delete from table_name a where row_number() over(partition by a.REGION,a.RATING,a.ULT_PARENT_NAME order by a.REGION,a.RATING,a.ULT_PARENT_NAME desc)=2;

delete from 
   table_name
where rowid in
 (select rowid from 
   (select 
     rowid,
     row_number()
    over 
     (partition by a.REGION,a.RATING,a.ULT_PARENT_NAME order by a.REGION,a.RATING,a.ULT_PARENT_NAME desc) dup
    from table_name a)
  where dup > 1);


select count(1) from  table_name where batch_id='REPORTING-C-20161018_092911610';


--------------------------
 
DROP INDEX table_name_INDEX ;
create index IDR_table_name_INDEX on table_name (BATCH_ID,REGION,RATING,ULT_PARENT_NAME ,TABLE_NAME_B,TABLE_NAME_D) parallel 10 ;      


ALTER TABLE table_name TRUNCATE PARTITION "P20161018_092911610" UPDATE INDEXES;
ALTER TABLE table_name drop PARTITION P20161017_145909285;




select * from v$session where sql_id  in 
      ( select sql_id from v$sql where sql_text like '%table_name%');
       select * from v$session where sql_id = 'cuw8ck6bn00na';
       
       select * from v$sql where sql_id in ('bhwknvq9f1v2s','2at3x2umsdsyp','4ava239hg70kf');
       
       alter system kill session '300,1389'




SELECT Upper(F.TABLESPACE_NAME)         "tableSpaceName",
       D.TOT_GROOTTE_MB                 "tableSpaceSize(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "usedTableSpaceSize(M)",
       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
       || '%'                           "useRadio",
       F.TOTAL_BYTES                    "availibleTableSpaceSize(M)",
       F.MAX_BYTES                      "maxBlock(M)"
FROM   (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM   SYS.DBA_DATA_FILES DD
        GROUP  BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  BY 1;




SELECT OWNER,segment_name,SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE segment_type LIKE 'TABLE%'
GROUP BY OWNER,segment_name ORDER BY 3 DESC;


TRUNCATE TABLE CST.CST_WSC_RAW_FEED;
DROP TABLE CCAR_ICG_IDR_CC_RAW_FEED_ADJ3;
select * from all_tab_partitions where table_name = 'CCAR_ICG_IDR_CC_RAW_FEED_ADJ3';
drop index IDR_CC_RAW_FEED_ADJ_INDEX;


EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname =>'CLOUDAPP', tabname =>'TABLE_NAME',degree =>'8') ;


SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'TABLE_NAME';




CREATE TABLE "CLOUDAPP"."TABLE_NAME" 
   (           "SCENARIO" VARCHAR2(100), 
                "AMOUNT" NUMBER, 
                "CORRELATION_ID" VARCHAR2(50), 
                "EFFECTIVE_DATE" VARCHAR2(50), 
                "BATCH_ID" VARCHAR2(100), 
                "DATE_ADDED" TIMESTAMP (6), 
                "ADJ_VERSION" NUMBER(*,0), 
                "RUN_MANAGER_VERSION" NUMBER(10,0)
   ) 
  TABLESPACE "DATA"
    PARTITION BY LIST ("BATCH_ID") 
 (
PARTITION "P20161018_092911610"  VALUES ('20161018_092911610') ,
PARTITION "P_DEFAULT"  VALUES (DEFAULT)
) ;


CREATE INDEX "TABLE_NAME_INDEX" ON "TABLE_NAME" ("BATCH_ID", "TRADING_CVA_FLAG", "FULL_REVAL_FLAG")  LOCAL;
INSERT /*+ PARALLEL(A 6) */ INTO TABLE_NAME A
SELECT /*+ PARALLEL(B 6) */  * FROM TABLE_NAME B;
COMMIT;


select owner from all_tables where table_name = 'TABLE_NAME';


alter table TABLE_NAME rename partition "20161003_130011111" to p20161003_130011111 ;


EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'CLOUDAPP', TABNAME=>'TABLE_NAME' , partname=>'P20161003_130011111', DEGREE=> 8);




= = = = =
backup database:


exp cloudapp/Welcome1@ICG_TRADING_ORACLE_ADMIN FILE=/home/rcastdev/tmp/test.dmp TABLES=TEST compress=y;
imp cloudapp/Welcome1@ICG_TRADING_ORACLE_ADMIN FILE=/home/rcastdev/tmp/test.dmp commit=y ignore=y;


1）需创建存储目录
mkdir backup


2）编写shell脚步#!/bin/bash
export ORACLE_BASE=/opt/oracle    --路径需按照实际路径更改
export ORACLE_HOME=$ORACLE_BASE/ora10gr2/db_1   --路径需按照实际路径更改
export PATH=$ORACLE_HOME/bin:$PATH
d=$(date '+%d')
exp metabase/metabase@dskdb file=/home/oracle/backup/'metabase'$d.dmp 
exp data/data@dskdb file=/home/oracle/backup/'data'$d.dmp
zip -m /home/oracle/backup/'metabase'$d.zip /home/oracle/backup/'metabase'$d.dmp
zip -m /home/oracle/backup/'data'$d.zip /home/oracle/backup/'data'$d.dmp


3）设置定时执行命令
crontab -e 进入编辑页面设置定时
00 23 * * * sh /home/oracle/data_bash.sh >> /home/oracle/error.log 2>&1  --路径需按照实际路径更改，以及日志可以不输出




update TABLE_NAME batch_id:


UPDATE TABLE_NAME COB SET COB.BATCH_ID = 
(
WITH RAW_FEED AS(SELECT /*+ parallel(adj, 8)*/ DISTINCT CONCAT('updated_',BATCH_ID)AS BATCH_ID, EFFECTIVE_DATE, RUN_MANAGER_VERSION, ADJ_VERSION FROM TABLE_NAME_2 ADJ)
SELECT r.BATCH_ID FROM RAW_FEED r WHERE COB.EFFECTIVE_DATE=r.EFFECTIVE_DATE 
   AND COB.RUN_MANAGER_VERSION=r.RUN_MANAGER_VERSION AND COB.ADJ_VERSION=r.ADJ_VERSION)
  WHERE COB.RISK_STRIPE='Credit' AND COB.BATCH_ID IS NULL;