常用语句整理
》对比两个用户下的表结构>修改sql句里面的两个用户名称
1 select a.table_name user1_table, 2 a.column_name user1_column, 3 b.table_name, 4 b.column_name user2_column 5 from (select table_name, column_name 6 from dba_tab_columns 7 where owner = 'DM_0207') a, 8 (select table_name, column_name 9 from dba_tab_columns 10 where owner = 'O2O_DEV') b 11 where a.table_name(+) = b.table_name 12 and a.column_name(+) = b.column_name 13 and a.column_name is null 14 union all 15 select a.table_name user1_table, 16 a.column_name user1_column, 17 b.table_name user2_table, 18 b.column_name user2_column 19 from (select table_name, column_name 20 from dba_tab_columns 21 where owner = 'DM_0207') a, 22 (select table_name, column_name 23 from dba_tab_columns 24 where owner = 'O2O_DEV') b 25 where a.table_name = b.table_name(+) 26 and a.column_name = b.column_name(+) 27 and b.column_name is null;
》分类查询>统计分类下有多少个数量
1 select t.type,t.belong 2 ,sum(case x.machine_type when 0 then 1 else 0 end) 3 ,sum(case x.machine_type when 1 then 1 else 0 end) 4 ,sum(case x.machine_type when 2 then 1 else 0 end) 5 from t_dismounting_inform t 6 left join t_register_no x on t.regist_no = x.id 7 group by t.type,t.belong
》查询所有表数据量
declare sql_str varchar2(255); v_count number; v_name varchar2(255); cursor v_table_name is select table_name asd from user_tab_comments t order by table_name desc; begin for R in v_table_name loop sql_str := 'select count(*) from '||r.asd; execute immediate sql_str into v_count ; dbms_output.put_line(r.asd||','||v_count); end loop; end;
》创建同义词
SELECT 'CREATE OR REPLACE SYNONYM '||TABLE_NAME||' FOR HBSHPORTAL.'||TABLE_NAME||';' FROM all_tables WHERE owner ='HBSHPORTAL'
》删除所有对象除指定内容
1 DECLARE 2 TYPE name_list IS TABLE OF VARCHAR2(40); 3 TYPE type_list IS TABLE OF VARCHAR2(20); 4 Tab_name name_list:=name_list(); 5 Tab_type type_list:=type_list(); 6 sql_str VARCHAR2(500); 7 BEGIN 8 sql_str := 'SELECT UO.OBJECT_NAME, UO.OBJECT_TYPE FROM USER_OBJECTS UO WHERE UO.OBJECT_TYPE NOT IN (''PACKAGE BODY'',''PACKAGE'', ''LOB'' ) AND UO.OBJECT_NAME NOT LIKE ''SYS%'' ORDER BY UO.OBJECT_TYPE DESC'; 9 EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type; 10 FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP 11 sql_str := 'DROP ' || Tab_type(i) || ' ' || Tab_name(i); 12 /*EXECUTE IMMEDIATE sql_str;*/ 13 dbms_output.put_line(sql_str); 14 END LOOP; 15 END;
》导出大批量数据到excel
sqlplus中可以使用spool导出excel格式,比cvs友好的就是,cvs中字段内容如果含有逗号,格式会乱,excel就不会了,利用spool导出html的特性,而且会给你自动加上列名, 列名和下面的get_tables.sql中的一致,可以自己指定。 --main.sql set linesize 200 set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat off spool c:\tables.xls @get_tables.sql spool off exit --get_tables.sql select id,name,to_char(trade_date,'yyyymmdd hh24:mi:ss') from trademark order by id; --execute spool to excel sqlplus username/passwd @main
》删除所有约束
DECLARE c1 cursor for select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' from sysobjects where xtype = 'F' open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1
》删除数据库所有表
declare @tname varchar(8000) set @tname='' select @tname=@tname + Name + ',' from sysobjects where xtype='U' select @tname='drop table ' + left(@tname,len(@tname)-1) exec(@tname)
》杀进程
SELECT 'alter system kill session '''||sid||','||SERIAL#||''' immediate;' FROM v$session WHERE machine='qiguanzixitong'
》bat调用文件名为日期
sqlplus -L -S tasker/108~write@datacenter @sqls\T_HR.sql>>logs\T_HR_%date:~0,4%%date:~5,2%%date:~8,2%.log
》查看十条性能最差sql
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;
》正在执行sql
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT from v$session a, v$sqlarea b where a.sql_address = b.address
》执行过sql
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT from v$sqlarea b where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and '2009-10-15/09:24:47' order by b.FIRST_LOAD_TIME (此方法好处可以查看某一时间段执行过的sql,并且 SQL_FULLTEXT 包含了完整的 sql 语句)
》查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st. p1text,si.physical_reads, si.block_changes FROM v$session se,v$session_wait st, v$sess_io si,v$process pr WHERE st.sid=se.sid AND st. sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC
》查看表空间大小
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;
》查看数据库引起锁表的SQL语句:
SELECT A.USERNAME, A.MACHINE, A.PROGRAM, A.SID, A.SERIAL#, A.STATUS, C.PIECE, C.SQL_TEXT FROM V$SESSION A, V$SQLTEXT C WHERE A.SID IN (SELECT DISTINCT T2.SID FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID) AND A.SQL_ADDRESS = C.ADDRESS(+) ORDER BY C.PIECE;
》查看连接数
select to_char(appear_date,'yyyy-mm-dd')dt,machine,session_ip ,SUM(CASE WHEN status='logon' THEN 1 ELSE 0 END) logon ,SUM(CASE WHEN status='logoff' THEN 1 ELSE 0 END) logoff ,SUM(CASE WHEN status='logon' THEN 1 ELSE -1 END) val from HBSHPORTAL.catch_session_on_off t GROUP BY to_char(appear_date,'yyyy-mm-dd'),machine,session_ip order BY session_ip,dt DESC ;
》时间获取
--本周 select trunc(sysdate,'d')+1 from dual; select trunc(sysdate,'d')+7 from dual; --本月 select trunc(sysdate,'mm') from dual; select last_day(trunc(sysdate)) from dual; --本季 select trunc(sysdate,'Q') from dual; select add_months(trunc(sysdate,'Q'),3)-1 from dual; --本年 select trunc(sysdate,'yyyy') from dual; select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;
--别碰!这只是一只小尾巴! →_→ 会掉的!!!