常用SQL语句

 

--统计制卡申请量

 

select COUNT(*) 申请量,AA10.AAA103 银行名称,AZ07.AAE008 银行代码,DECODE(AAZ091,1,'预申请','银行申请') 申请渠道 from szjrsbk.az07,szjrsbk.aa10 where to_char(az07.aae310,'yyyyMMdd')='20140827' 

and az07.aae008=aa10.aaa102 and aa10.aaa100='AAE008'

group by AA10.AAA103,AZ07.AAE008,AAZ091 order by count(*) desc

 

 

--激活量

select (select aaa103

           from szjrsbk.aa10

          where aaa100 = 'AAE008'

            and aaa102 = b.aae008) 所属银行,

        count(0) 激活卡片数量

   from szjrsbk.az03 a, szjrsbk.az07 b

  where a.bac001 = b.bac001

    and a.aae330 > '53'

    and a.aae330 < '80'

  group by b.aae008

  order by b.aae008

 

--查看用户数

 

select count(distinct aae011) from szjrsbk.az99,szjrsbk.uap_user  where to_char(aae030,'yyyyMMdd')='20140827'

and az99.aae011=uap_user.userid;

 

select count(distinct aae011) from szjrsbk.az99,szjrsbk.uap_user  where to_char(aae030,'yyyyMMddhh24mi')>'201408280900'

and az99.aae011=uap_user.userid;

 

--卡系统密码

szsbetl/szsbetl

 

--表分析

Select 

　　

　　'analyze table '||owner||'.'||table_name||' compute statistics;'

　　

　　from

　　

　　dba_all_tables 

　　

　　where 1=1 AND owner ='SZJRSBK';

 

--耗时SQL

 select sql_text,first_load_time,last_load_time,parsing_schema_name

  from v$sql 

where (disk_reads > 10000 or (executions > 0 and buffer_gets/executions > 300000)) and parsing_schema_name='SZJRSBK'

order by last_load_time desc;

 

--根据SID查询运行的SQL

select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=1686

 

--数据库导出表：

exp zjsck/wondersandzjrsj@zjsbsck file=d:\zjsck_table.dmp tables=HISTORY_TABLE log=d:\zjsck.log  INDEXES=n STATISTICS=none

 

--版本之间导入导出

--高版本导出

高版本：expdp mam40/password@wlw schemas=mam40 dumpfile=expdp001.dmp DIRECTORY=expdp version=10.2.0.5

--低版本导出

高版本：impdp dtv/dtv@crk directory=dump dumpfile=xxx.dmp version=10.2.0.1 full=y

 

--表执行计划

EXPLAIN PLAN FOR 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

 

--查看历史快照：

 select * from (

 SELECT *                                                                     

      FROM ac60   AS OF TIMESTAMP TO_TIMESTAMP('2012-9-5 14:59:38', 'yyyy-mm-dd hh24:mi:ss'));

 

--插入数据无归档日志

alter table test nologging

insert /*+ append */ into test select

 

--表分区管理

drop table sales;

 

CREATE TABLE sales 

(

invoice_no NUMBER, 

sale_date DATE NOT NULL 

) 

PARTITION BY RANGE (sale_date) 

(

PARTITION sales1999_q1 

VALUES LESS THAN (TO_DATE('1959-04-01','YYYY-MM-DD'))

TABLESPACE szyth_data, 

PARTITION sales1999_q2 

VALUES LESS THAN (TO_DATE('1979-07-01','YYYY-MM-DD'))

TABLESPACE szyth_data, 

PARTITION sales1999_q3 

VALUES LESS THAN (TO_DATE('1999-10-01','YYYY-MM-DD'))

TABLESPACE szyth_data, 

PARTITION sales1999_q4 

VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD'))

TABLESPACE szyth_data 

);

 

insert into sales

 select aac001,to_date(to_char(aac006||'01'),'yyyyMMdd') from ac01 where 1=1  and aac006>=190001 and aac006<=200812;

 

select * from sales PARTITION(sales1999_max);

 

Alter table sales drop partition sales1999_max ;

 

alter table sales  add partition sales1999_max values less than (MAXVALUE)

TABLESPACE szyth_data;

 

insert into sales(invoice_no,sale_date) values(111,to_date('20100101','yyyyMMdd'));

insert into sales(invoice_no,sale_date) values(111,to_date('20110101','yyyyMMdd'));

 

alter table sales  add partition sales1999_q6 values less than (TO_DATE('2009-09-01','YYYY-MM-DD'))

TABLESPACE szyth_data;

 

alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);

--分区分拆

alter table sales split partition sales1999_max at (to_date('20110101','yyyyMMdd')) 

into (partition sales1999_q5,partition sales1999_max);

 

 

---存储过程跟踪

@?/rdbms/admin/profload.sql

@?/rdbms/admin/proftab.sql

 

create table A(aac001 number);

 

declare

BEGIN 

 

DBMS_PROFILER.START_PROFILER('123'); 

 

FOR I IN 1 .. 1000 LOOP 

 

INSERT INTO A VALUES (I || ''); 

 

END LOOP; 

 

COMMIT; 

 

DBMS_PROFILER.STOP_PROFILER(); 

 

END; 

 

SELECT C.LINE#, C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME 

 

FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C 

 

WHERE A.RUN_COMMENT = '123' 

 

--AND B.UNIT_OWNER = '' 

 

AND A.RUNID = B.RUNID 

 

AND A.RUNID = C.RUNID 

 

AND B.UNIT_NUMBER = C.UNIT_NUMBER

 

 

--update 语句

 

alter table AC01

  add constraint PK_AC01 primary key (AAC001)

  using index 

  tablespace SZYTH_DATA

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 5M

    next 1M

    minextents 1

    maxextents unlimited

  );

 

update ac01 set aac003=(select aaa028 from ae01 where ac01.aac001=ae01.aaz010)

 

UPDATE (select aac003,aaa028 from ae01,ac01 where ae01.aaz010=ac01.aac001  )

SET aac003=aaa028;

 

 

--事务回滚段监测

select s.sid,

   s.serial#,

    nvl(s.username,'no transaction') us,

    s.osuser os,

       s.terminal te,

       t.used_urec rec,

       t.used_ublk blk

from v$session s,v$transaction t

where taddr=addr

--and s.username=upper('zjbzk')

and s.status='ACTIVE';

 

select /*+ rule */s.sid,

     r.name rr,

       nvl(s.username,'no transaction') us,

       s.osuser os,

       s.terminal te,

       t.used_urec rec,

       t.used_ublk blk

  from v$lock l, v$session s, v$rollname r,v$transaction t

 where l.sid = s.sid(+) and

       trunc(l.id1/65536) = r.usn and

       l.type = 'TX' and

       t.ses_addr = s.saddr and

       l.lmode = 6

 

--sql执行进度

select b.sql_text, --SQL内容

c.sofar/totalwork*100,--工作进行了百分之多少 

            c.elapsed_seconds, --己经用了多少时间(秒)

            c.time_remaining,--还剩多少时间(秒)

            a.machine,  --哪台机器运行的SQL

            a.username, --哪个用户运行的SQL

            a.module   --是哪运行方式

            

     from  v$session a, v$sqlarea b,v$session_longops c

     where a.sql_hash_value=b.HASH_VALUE and a.sid=c.sid and a.SERIAL#=c.SERIAL#

 

--查询各个表占用的空间

 

select segment_name, sum(bytes)/(1024*1024*1024), count(*) ext_quan

  from dba_extents

 where 1=1

   and segment_type in ( 'TABLE','TABLE PARTITION')

   and owner='ZJSCK'

 group by tablespace_name, segment_name ;

 

--索引存储情况

select segment_name, count(*)

  from dba_extents

 where segment_type = 'INDEX'

   and owner = 'ZJSCK'

 group by segment_name;

 

--检测表空间

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(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 4 DESC;

 

 

 

SELECT T.TABLESPACE_NAME,D.FILE_NAME,

D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;

 

--检测索引是否常用

alter index IDX_AC01_AAC147 monitoring usage;

select * from ac01 where aac147='441421197105172414';

alter index IDX_AC01_AAC147 nomonitoring usage;

select * from v$object_usage

 

--索引碎片分析和重建，超过20%

analyze index IDX_DC03_CAC001 validate structure;

 

select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 from index_stats;

 

alter index IDX_DC03_CAC001 rebuild;

 

 

--查询占用资源高的sql

 

 select sql_text,first_load_time,last_load_time,parsing_schema_name

  from v$sql 

where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000); 

 

 

--查询当前数据库实例

 

select instance_name,host_name from v$instance;

 

 

--查询锁表语句

 

select t2.username,   

       t2.sid,   

       t2.serial#,   

       t3.object_name,   

       t2.OSUSER,   

       t2.MACHINE,   

       t2.PROGRAM,   

       t2.LOGON_TIME,   

       t2.COMMAND,   

       t2.LOCKWAIT,   

       t2.SADDR,   

       t2.PADDR,   

       t2.TADDR,   

       t2.SQL_ADDRESS,   

       t1.LOCKED_MODE   

  from v$locked_object t1, v$session t2, dba_objects t3   

 where t1.session_id = t2.sid   

   and t1.object_id = t3.object_id   

 order by t2.logon_time; 

 

 

--建立数据DBLINK连接

 drop database link ZJCWTEST; 

-- Create database link 

create database link ZJCWTEST 

  connect to ZJCW identified by ZJCW 

  using '(DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.72.254.71)(PORT = 1521)) 

    (CONNECT_DATA = 

      (SERVER = DEDICATED) 

      (SERVICE_NAME = zjsbtest) 

    ) 

  )'; 

 

create public database link ZJJHK 

  connect to JHK identified by jhk 

  using 'ZJJHK';

 

--未使用空表导出

select table_name from user_tables where NUM_ROWS=0;

 

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

 

--添加数据字典

declare

v_aaz094 aa10.aaz094%type;

begin

delete from aa10 where aaa100='BKC106';

select aaz094  into v_aaz094 from aa09 where aaa100='BKC106';

 

insert into aa10(aaz093,aaz094,aaa100,aaa102,aaa103,aae030,aae031,aae100)

values(seq_aaz093.nextval,v_aaz094,'BKC106','3018020901','癌症/精神病自付报销','20010101','29991231','1');

end;

 

--分区表设置是否可跨区修改数据

alter table AC43 enable row movement;

 

--清空表数据

truncate table ke10;

 

 

--清空临时表空间

CREATE UNDO TABLESPACE "UNDOTEMP"  DATAFILE '/oracle/oradata/wd/UNDOTEMP.dbf' SIZE 5M;

 

 

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTEMP 

 

DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES 

 

CREATE UNDO TABLESPACE "UNDOTBS1"  DATAFILE '/oracle/oradata/wd/UNDOTBS1.dbf' SIZE 1024M

 

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1 

 

DROP TABLESPACE "UNDOTEMP" INCLUDING CONTENTS AND DATAFILES 

 

 

 

create temporary tablespace  TEMP_TEMP TEMPFILE '/oracle/oradata/wd/temp_temp.dbf' SIZE 128M REUSE AUTOEXTEND

ON NEXT 640K MAXSIZE UNLIMITED;

 

alter database default  temporary tablespace  TEMP_TEMP;

 

drop tablespace temp including contents and datafiles;

 

create temporary tablespace  TEMP TEMPFILE '/oracle/oradata/wd/temp01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED;

 

alter database default  temporary tablespace  temp;

 

drop tablespace TEMP_TEMP including contents and datafiles;

 

 

---重置USERS表空间

alter database default tablespace SZYTH_DATA;

 

DROP TABLESPACE "USERS" INCLUDING CONTENTS AND DATAFILES;

 

CREATE  TABLESPACE "USERS"  DATAFILE '/oracle/oradata/wd/users01.dbf' SIZE 512M

 REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED;

alter database default tablespace USERS;

 

 

 

select owner, table_name, column_name, tablespace_name

     from dba_lobs

     where tablespace_name = 'USERS';

 

 

 

create table T_STOCK

(

  TIME       VARCHAR2(20),

  PRICE      NUMBER(16,2),

  HAND       NUMBER(16,2),

  MONEY      NUMBER(16,2),

  DIRECTION  VARCHAR2(20)

);

-- Add comments to the columns 

comment on column T_STOCK.TIME

  is '交易分时';

comment on column T_STOCK.PRICE

  is '成交价格';

comment on column T_STOCK.HAND

  is '成交手数';

comment on column T_STOCK.MONEY

  is '成交金额';

comment on column T_STOCK.DIRECTION

  is '成交方向';

/**

1.将交易软件接收到的重复数据删除

**/ 

delete from T_STOCK a where a.rowid<>

(select min(b.rowid) from T_STOCK b group by  time,price,hand

having count(*)>1 and a.time=b.time and a.price=b.price and a.hand=b.hand

);

/**

 2.统计(根据腾讯交易金额超过100W的大单数据)

**/

select sum(decode(t.direction,'买盘',t.money,'卖盘',-t.money,0)) 净流入参考数据,

sum(decode(t.direction,'买盘',t.money*10000,0))/sum(decode(t.direction,'买盘',t.hand*100,0)) 大单主动买入成本,

sum(decode(t.direction,'中性盘',t.money,0)) 中性盘参考数据

  from T_STOCK t;