AIX/LINUX
文件分割
split -l 2000000(行) f_PROT_201411.csv


-------------------------
1、查看当前链接的数据库、用户信息；
db2 connect

2、DB2数据库登陆
db2 connect to ZMCCDW user "appqry" using "qry.APP1"

3、DB2数据库导出数据
（http://blog.itpub.net/25744374/viewspace-750571/ 或者 http://henry406.iteye.com/blog/1388887）
db2 export to /home/appqry/test/exp_sampl_staff.ixf of ixf messages /home/appqry/test/msgs.txt select * from dw.DW_NET_URL_570_${data_date} fetch first 10 rows only---导出来是二进制
 

--查询数据库使用率
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 1

 高效率表空间查询

     SELECT TABLESPACE_NAME "表空间",
           To_char(Round(BYTES / 1024, 2), '99990.00')
           || 'G'           "实有",
           To_char(Round(FREE / 1024, 2), '99990.00')
           || 'G'          "现有",
           To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
           || 'G'          "使用",
           To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
           || '%'          "比例"
    FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                   Sum(BYTES)      BYTES
            FROM   DBA_DATA_FILES
            GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                   Sum(BYTES)      FREE
            FROM   DBA_FREE_SPACE
            GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
    /*WHERE TABLESPACE_NAME LIKE 'UNDO%' --这一句用于指定表空间名称*/
    ORDER  BY Floor(10000 * USED / BYTES) DESC;

select * from (select t.segment_name, sum(t.bytes) / 1024 / 1024/ 1024 as tab_size
  from user_segments t
 group by t.segment_name
 )
 where tab_size>=50
 order by tab_size desc ;


--分区查询
select 'alter table ' || t.segment_name || ' drop partition ' ||
       t.partition_name || ';'
  from user_segments t
 where t.segment_name = upper('TB_MK_RING_DAY_JF');


----------------------------------BEGIN
表空间

    select /*+parallel(t,16)*/ t.segment_name,sum(t.bytes)/1024/1024/*distinct 'alter table ' || t.segment_name || ' drop partition ' ||
            substr(t.partition_name, 1, 13) || ';'*/
      from sys.dba_segments t
     where  substr(t.partition_name, 6, 13) <= '201401'
       and t.tablespace_name ='TBS_KR'
       group by t.segment_name
       order by sum(t.bytes)/1024/1024 desc
;

--CB层

select /*+parallel(t,16)*/ distinct 'alter table ' || t.segment_name || ' drop partition ' ||
               t.partition_name || ';'
  from sys.dba_segments t
 where (  t.partition_name LIKE '%2013%' or t.partition_name LIKE '%2012%' or t.partition_name LIKE '%2011%'   )
   and t.tablespace_name ='TBS_KR'
   
;
----------------------------------END
--1.查询被锁的对象：
select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       sess.LOGON_TIME,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid
 order by sess.LOGON_TIME;


SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='1925'
SELECT SPID FROM V$PROCESS WHERE ADDR='070000078F7EF198';

ALTER SYSTEM KILL SESSION '1925,53292';

SELECT 'kill -9 '||SPID
  FROM V$PROCESS
 WHERE ADDR in (select t.PADDR
                  from v$session t
                 where t.OSUSER = '4A_jianghui11'
                   and T.STATUS = 'ACTIVE'
                 );

kill -9 23978(SPID);
--2. 杀掉被锁的进程
alter system kill session '1903,64383';


15209

表容量查询
select /*+parallel(t,4)*/
        round(sum(bytes)/1024/1024/1024,2) "size(g)"  -- 2030.73  1992.46
   from user_segments t                               
  where t.segment_name like 'T%'
  order by "size(g)" desc;


Oracle WHERE条件执行顺序 1.ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如: (低效) SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGEROracle WHERE条件执行顺序 1.ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

例如:

(低效)

SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);

(高效)

SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

2.SELECT子句中避免使用’*’

   当在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.可是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

3.   使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)



  ---正在执行的
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b
where a.sql_address = b.address
---执行过的
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 语句）
 
其他
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXT
from V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE order by b.cpu_time desc
 
select address, sql_text, piece
  from v$session, v$sqltext
where address = sql_address
  -- and machine = < you machine name >
order by address, piece
 
查找前十条性能差的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 ;
 
查看占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



oracle 临时表空间的增删改查

1、查看临时表空间 （dba_temp_files视图）（v_$tempfile视图）
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from sys.v_$tempfile;--sys用户查看

--查询占用情况
SELECT a.tablespace_name, a.BYTES/1024/1024 total, (a.bytes - nvl(b.bytes, 0))/1024/1024 free
  FROM (SELECT   tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, SUM (bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);




 8、查找消耗资源比较的sql语句
    Select se.username,
           se.sid,
           su.extents,
           su.blocks * to_number(rtrim(p.value)) as Space,
           tablespace,
           segtype,
           sql_text
      from v$sort_usage su, v$parameter p, v$session se, v$sql s
     where p.name = 'db_block_size'
       and su.session_addr = se.saddr
       and s.hash_value = su.sqlhash
       and s.address = su.sqladdr
     order by se.username, se.sid
 
9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

--查询所有临时表空间情况
select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;

2、缩小临时表空间大小
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;

3、扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

4、创建临时表空间：
SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;

5、更改系统的默认临时表空间：
--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间：
select username,temporary_tablespace,default_ from dba_users;
--更改某一用户的临时表空间：
alter user scott temporary tablespace temp;

6、删除临时表空间
删除临时表空间的一个数据文件：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
删除临时表空间(彻底删除)：
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;




10、临时表空间组介绍
  1）创建临时表空间组：
create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1;
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2;
 
 2）查询临时表空间组：dba_tablespace_groups视图
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1                         TEMPTS1
GROUP2                         TEMPTS2

 3）将表空间从一个临时表空间组移动到另外一个临时表空间组：
alter tablespace tempts1 tablespace group GROUP2 ;
select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMPTS1
GROUP2                         TEMPTS2

 4）把临时表空间组指定给用户
alter user scott temporary tablespace GROUP2;

 5）在数据库级设置临时表空间
alter database <db_name> default temporary tablespace GROUP2;

 6）删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMPTS2

drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME

11、对临时表空间进行shrink（11g新增的功能）
--将temp表空间收缩为20M
alter tablespace temp shrink space keep 20M;
--自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;

临时表空间作用
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。
也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：
  索引create或rebuild；
  Order by 或 group by；
  Distinct 操作；
  Union 或 intersect 或 minus；
  Sort-merge joins；
  analyze.

undo表空间回滚段概念

可以这样认为。特别是unxpstealcnt。但具体还要看情况而定。
回滚空间重用机制如下：
1、首先检查本回滚段当前区是否有空间块，如果有直接使用。
2、如果没有，事务伸展到下一个区，这被称为一次Wrap，v$rollstat中的WRAPS列加1。如果本回滚段有多个事务，第一个伸展到下一个区的事务会引起WRAPS列加1，其他事务再伸展至一下区，此列不再增加。
3、如果本回滚段中没有可用的区，从UNDO表空间中请求区。这被称为一次Extend，记入v$rollstat的EXTENTS列。
4、UNDO表空间中没有Free区，从其他回滚段Steal过期的区。Steal的单位一定是以区为单位。无论Steal是否成功，V$UNDOSTAT的EXPSTEALCNT列都会加1，此列统计的是次数。EXPBLKRELCNT列是成功Steal的块数。
5、如果Steal过期区不成功，试图扩展数据文件。
6、如果无法扩展文件，在本回滚段中重用未过期的区，V$UNDOSTAT.UNXPBLKREUCNT列增加（增加值是区中块的个数）
7、如本回滚段无过期的块，则Steal其他回滚段中未过期的区。每Steal一次，无论是否成功，都会记入V$UNDOSTAT.UNXPSTEALCNT列。Steal的块数记入UNXPBLKREUCNT列。
8、如还不行，报ORA-30036错误。

如果都开始偷窃其他回滚段中未过期的空间了，说明可用空间已经被用完了。





     1.查询回滚段的信息。所用数据字典：DBA_ROLLBACK_SEGS，可以查询的信息：回滚段的标识(SEGMENT_ID)、名称(SEGMENT_NAME)、所在表空间(TABLESPACE_NAME)、类型(OWNER)、状态(STATUS)。
select * from DBA_ROLLBACK_SEGS
查看回滚段的统计信息：
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
3.查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;


 SELECT S.SID,  
         S.USERNAME,  
         U.NAME,  
         Q.SQL_TEXT,  
         Q.HASH_VALUE,  
         T.UBABLK  
    FROM V$TRANSACTION T,  
         V$ROLLSTAT R,  
         V$ROLLNAME U,  
         V$SESSION S,  
         V$SQL Q  
   WHERE     S.TADDR = T.ADDR  
         AND T.XIDUSN = R.USN  
         AND R.USN = U.USN  
         AND Q.HASH_VALUE =  
                DECODE(S.SQL_HASH_VALUE,  
                        NULL, S.PREV_HASH_VALUE,  
                        S.SQL_HASH_VALUE)  
ORDER BY S.USERNAME



UNDO表空间的使用情况会因事务量变化而变化，一般我们在计算时同时参考UNDO表空间的平均使用情况和峰值使用情况。
以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量：
select ur undo_retention,
dbs db_block_size,
((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
from v$undostat),
(select value as dbs from v$parameter where name = 'db_block_size')
/
以下SQL语句则按峰值情况计算UNDO表空间所需空间：
select ur undo_retention,
dbs db_block_size,
((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (undoblks / ((end_time - begin_time) * 86400)) ups
from v$undostat
where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = 'db_block_size')
group by ur,dbs,((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024
/
该查询将返回以STATUS分组的各状态回滚信息所使用的空间量，一般存在三种STATUS状态：EXPIRED，UNEXPIRED，ACTIVE。ACTIVE表示目前仍活跃的事务相关回滚信息，UNEXPIRED表示虽然事务已经结束但回滚信息保留的时间仍未超过实例参数UNDO_RETENTION所设定的值，EXPIRED表示回滚信息保留时间已超过UNDO_RETENTION所设定的值。
在UNDO表空间未启用guarantee选项的情况下（当前使用情况），新事务的回滚空间分配遵循以下依据：
a)         寻找不存在ACTIVE区间的回滚段，若没有则创建一个新的回滚段，若空间不允许生成新段，则返回错误。
b)        如果有一个回滚段被选中，但是其中空闲的空间并不足以存储该事务的回滚信息，那么它将尝试创建区间，如果表空间上没有空间，那么将会进入下一步。
c)         如果创建新区间失败，它将会搜索其他回滚段中的EXPIRED区间并重用。
d)        如果其他回滚段中没有EXPIRED区间可使用，那么它会继续搜索其他回滚段中UNEXPIRED区间并重用，注意事务不会重用本回滚段中的UNEXPIRED区间，故UNEXPIRED的回滚空间仅部分可以为Oracle重用；若仍得不到所需则返回错误。
当我们观察到ACTIVE回滚信息所占用空间很大时，说明系统目前运行的事务繁忙。因目前未启用UNDO表空间的guarantee选项，故EXPIRED的全部回滚空间与UNEXPIRED的部分回滚空间可以为Oracle复用，在实时监控时主要观察ACTIVE状态回滚信息使用的空间即可。
在系统相关业务不变的情况下，我们通过计算UNDO表空间的峰值使用情况即可最大程度完善UNDO表空间的配置；而当系统处于业务调整阶段，如新的业务加入或业务时段调整情况下，则需要进一步实时监控UNDO表空间使用情况，以满足动态调整需求。



select b.tablespace_name, AUTOEXTENSIBLE, RETENTION
  from dba_tablespaces a, dba_data_files b
 where a.TABLESPACE_NAME = b.TABLESPACE_NAME
   and b.TABLESPACE_NAME = 'UNDOTBS1';


 select tablespace_name,file_name,bytes/1024/1024,autoextensible from dba_data_files;