常用语句整理

》对比两个用户下的表结构>修改sql句里面的两个用户名称

select a.table_name  user1_table,
       a.column_name user1_column,
       b.table_name,
       b.column_name user2_column
  from (select table_name, column_name
          from dba_tab_columns
         where owner = 'DM_0207') a,
       (select table_name, column_name
          from dba_tab_columns
         where owner = 'O2O_DEV') b
 where a.table_name(+) = b.table_name
   and a.column_name(+) = b.column_name
   and a.column_name is null
union all
select a.table_name  user1_table,
       a.column_name user1_column,
       b.table_name  user2_table,
       b.column_name user2_column
  from (select table_name, column_name
          from dba_tab_columns
         where owner = 'DM_0207') a,
       (select table_name, column_name
          from dba_tab_columns
         where owner = 'O2O_DEV') b
 where a.table_name = b.table_name(+)
   and a.column_name = b.column_name(+)
   and b.column_name is null;

 》分类查询>统计分类下有多少个数量

select t.type,t.belong
,sum(case x.machine_type when 0 then 1 else 0 end)
,sum(case x.machine_type when 1 then 1 else 0 end)
,sum(case x.machine_type when 2 then 1 else 0 end)
from t_dismounting_inform t
left join t_register_no x on t.regist_no = x.id
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'

》删除所有对象除指定内容

DECLARE
TYPE name_list IS TABLE OF VARCHAR2(40);
TYPE type_list IS TABLE OF VARCHAR2(20);
Tab_name name_list:=name_list();
Tab_type type_list:=type_list();
sql_str VARCHAR2(500);
BEGIN
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';
EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;
FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP
sql_str := 'DROP ' || Tab_type(i) || ' ' || Tab_name(i);
/*EXECUTE IMMEDIATE sql_str;*/
dbms_output.put_line(sql_str);
END LOOP;
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;