IT 数据工人

今天收到ITPUB“盛拓传媒杯”SQL大赛 收获参与奖.以此记录！

遗憾没有全程参与。

活动链接：

http://www.itpub.net/thread-1417703-1-1.html

恭喜各位获奖者。

1. 表空间剩余空间

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /
(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

SELECT * FROM dba_tablespace_usage_metrics;

2. 表空间数据量情况显示

SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks,
to_char(100*sum_free_blocks/sum_alloc_blocks, '99.99') || '%' AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS max_blocks
, count(blocks) AS count_blocks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name

3. 表和索引分析

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);
END ;
或者
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);

4. 检查tablespace情况

SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk

5. 检查已经存在的空间extent

SELECT count(*), segment_name, segment_type, dt.tablespace_name
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dt.next_extent != dx.bytes AND dx.owner = 'ECIF'
GROUP BY segment_name, segment_type, dt.tablespace_name

6. 检查没有主键的表

SELECT table_name
FROM all_tables
WHERE owner = 'ECIF'
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = 'ECIF'
AND constraint_type = 'P'

7. 检查失效的主键

SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = 'ECIF' AND status = 'DISABLED' AND constraint_type = 'P'

8. 重建某个表空间上的全部索引

SELECT 'alter index ' || index_name || ' rebuild '
, 'tablespace new_tablespace_name nologging; '
FROM all_indexes
WHERE ( tablespace_name= 'TABLESPACE_NAME') AND owner = 'ECIF'

9. 对比两个schema的不同

SELECT object_name, object_type
FROM dba_objects where owner='OWNER_NAME_1'
MINUS
SELECT object_name, object_type
FROM dba_objects where owner='OWNER_NAME_2';

10. 查看全部动态性能视图

SELECT * from V$FIXED_TABLE;

11. 查看约束

select a.constraint_name, a.constraint_type,a.*
from user_constraints a
where table_name='TABLE_NAME';

select constraint_name, column_name
from user_cons_columns
where table_name='TABLE_NAME';

12. 查看索引
user_indexes包含索引的名字,user_ind_columns包含索引的列.

13. 查看数据库启动参数：
show parameter para，v$parameter提供当前会话信息，v$system_parameter提供当前系统信息。其中isses_modifiable，issys_modifiable表示是否允许动态修改。

14. 查看进程号：

select p.spid, s.username
from v$process p , v$session s
where p.addr=s.paddr;

15. 查看数据文件：

select name, status
from v$datafile;

select *
from dba_data_files;

16. 查看数据文件状态

select d.file# f#, d.name, d.status, h.status
from v$datafile d, v$datafile_header h
where d.file#=h.file#;

17. 查看控制文件

select name
from v$controlfile;

select type, record_size, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';

18. 查看是否归档模式：

archive log list

select name, log_mode
from v$database;

select archiver
from v$instance;

19. 查看日志组：

select groups, current_group#, sequence#
from v$thread;

select group#, sequence#, bytes, members, status
from v$log;

select *
from v$logfile;

其中status为空表示正常。

20. 查看large pool

select *
from v$sgastat
where pool='large pool';

21. 查看归档位置

show parameter archive

select destination, binding, target, status
from v$archive_dest;

22. 查看归档进程

select *
from v$archive_processes;

23. 查看正在备份的数据文件

select *
from v$backup;

24. 查看需要恢复的文件

select *
from v$recover_file;

25. 查看所有归档日志文件

select *
from v$archived_log;

26. 查看恢复时要用到的日志文件

select *
from v$recovery_log;

27. 查看SGA的结构

Show sga;

select *
from v$sgastat;

28. 提取library cache的命中率

select gethitratio
from v$librarycache
where namespace='…';

29. 查看正在运行的SQL语句

select sql_text, users_executing, executions, loads
from v$sqlarea;

30. 查看library cache reload情况：

select sum(pins) "Executions", sum(reloads) "cache Misses", sum(reloads)/sum(pins)
from v$librarycache;

31. 查看大匿名块

select sql_text from v$sqlarea
where command_type=47
and length(sql_text)>500;

32. 查看当前会话的UGA区

select sum(value)||'bytes' "Total session memory"
from v$mystat, v$statname
where name='session uga memory'
and v$mystat.statistic#=v$statname.statistic#;

33. 查看所有MTS用户的UGA区：

select sum(value)||'bytes' "Total session memory"
from v$sesstat, v$statname
where name='session uga memory'
and v$sesstat.statistic#=v$statname.statistic#;

34. 查看所有用户使用的最大的UGA区：

select sum(value)||'bytes' "Total session memory"
from v$sesstat, v$statname
where name='session uga memory max'
and v$sesstat.statistic#=v$statname.statistic#;

35. 查看high-water mark以下的块数

select table_name, blocks
from dba_tables
where table_name='table_name';

36. 查看会话的I/O：

select io.block_gets, io.consistent_gets, io.physical_reads
from v$sess_io io, v$session s
where s.audsid=USERENV('SESSIONID')
and io.sid=s.sid;

37. 查看Buffer pool的命中率

select name, 1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets+consistent_gets>0;

38. 查看free list的竞争

select class, count, time
from v$waitstat
where class='segment header';

select event, total_waits
from v$system_event
where event='buffer busy waits';

buffer busy waits可在两种情况发生：1dirty queue已满，2free list竞争。

39. 查看free list竞争发生在哪个segment上

select s.segment_name, s.segment_type, s.freelists, w.wait_time,
w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event='buffer busy waits'
and w.p1=s.header_file
and w.p2=s.header_block;

40. 查看全表扫描发生的次数

select name, value
from v$sysstat
where name like '%table scan%';

41. 查看大操作的执行情况

select sid, serial#, opname,
to_char(start_time, 'HH24:MI:SS') as start_t,
(sofar/totalwork)*100 as percent_complete
from v$session_longops;

42. 查看数据文件的I/O

select phyrds, phywrts, d.name
from v$datafile d, v$filestat f
where d.file#=f.file# order by d.name;

43. 查看空闲块数少于10%的segment(blocks在high-water mark以下，empty_blocks其上)

select owner, table_name, blocks, empty_blocks
from dba_tables
where empty_blocks/(blocks+empty_blocks)<0.1 and blocks+empty_blocks!=0;

44. 查看migration和chaining

检查之前要先分析表,dbms_stats.gather_table_stats一下.
select num_rows, chain_cnt
from dba_tables
where table_name='table_name';

45. 查看表的统计信息
先分析表
select num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
from dba_tables
where owner='ECIF' and
table_name='table_name';

46. 查看索引的统计信息
先分析索引
select (del_lf_rows_len/lf_rows_len)*100 as index_usage
from index_stats;

----------------------------------------------------------------------------------
-- 确认控制文件重做日志文件 数据文件的位置和名称
-- 这些文件和初始化参数文件{init(SID).ora}是冷备份的对象
----------------------------------------------------------------------------------
select * from v$controlfile;
select * from v$logfile;
select FILE#, STATUS, ENABLED, NAME from v$datafile;

----------------------------------------------------------------------------------
--关于重做日志的信息
----------------------------------------------------------------------------------
select * from v$log;
select * from v$logfile;

----------------------------------------------------------------------------------
--关于数据文件的信息
----------------------------------------------------------------------------------

select a.TABLESPACE_NAME, a.FILE_ID, a.FILE_NAME,
a.BYTES/1024/1024 SIZE_MB, b.CREATE_BYTES/1024/1024 CREATE_MB,
a.INCREMENT_BY*b.BLOCK_SIZE/1024/1024 NEXT_MB, a.MAXBYTES/1024/1024 MAX_MB
from DBA_DATA_FILES a, v$datafile b
where a.FILE_ID = b.FILE#;

----------------------------------------------------------------------------------
-- 数据文件的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.FILE_ID, a.FILE_NAME,
min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB,
sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.FILE_ID, a.FILE_NAME;
----------------------------------------------------------------------------------
--关于表空间的信息
----------------------------------------------------------------------------------

select TABLESPACE_NAME, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB, NEXT_EXTENT/1024 NEXT_EXTENT_KB,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, LOGGING
from dba_tablespaces;

----------------------------------------------------------------------------------
-- 表空间的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.TABLESPACE_NAME,
min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME;

select a.TABLESPACE_NAME, a.FILE_NAME, sum(a.BYTES)/1024 SIZE_KB, sum(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME, a.FILE_NAME;

------------------------------------------------------------------------------------
--extent的信息:extent数为2个以上的数据段的一览表
------------------------------------------------------------------------------------
select TABLESPACE_NAME, OWNER, SEGMENT_NAME, count(*)
from dba_extents
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME
having count(*) >= 2;

select TABLESPACE_NAME, OWNER, TABLE_NAME,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
from dba_tables
where (OWNER, TABLE_NAME) in (
select OWNER, SEGMENT_NAME
from dba_extents
where SEGMENT_TYPE = 'TABLE'
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
having count(*) >= 2)
order by TABLESPACE_NAME, OWNER, TABLE_NAME;

select TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
from dba_indexes
where (OWNER, INDEX_NAME) in (
select OWNER, SEGMENT_NAME
from dba_extents
where SEGMENT_TYPE = 'INDEX'
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
having count(*) >= 2)
order by TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME;
------------------------------------------------------------------------------------
--表信息表信息
------------------------------------------------------------------------------------
break on OWNER
select OWNER, TABLE_NAME,
PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
TABLESPACE_NAME
from dba_tables -- WHERE TABLE_NAME='TABLE_NAME'
order by OWNER, TABLE_NAME;

--表信息(2)
----------------------------------------------------------------------------------
-- 为了求出NUM_ROWS,BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN的值,
-- 需要事先执行dbms_stats命令。
-- exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);
----------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
NUM_ROWS, BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
TABLESPACE_NAME
from dba_tables
order by OWNER, TABLE_NAME;

------------------------------------------------------------------------------------
--索引信息:索引信息(1)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
INI_TRANS, MAX_TRANS, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE,
STATUS, TABLESPACE_NAME
from dba_indexes
order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;

------------------------------------------------------------------------------------
--索引信息(2)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY,
STATUS, TABLESPACE_NAME
from dba_indexes
order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;

------------------------------------------------------------------------------------
--关于用户的信息用户信息
------------------------------------------------------------------------------------
select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE
from dba_users
order by USERNAME;
------------------------------------------------------------------------------------
--分配给用户的权限
------------------------------------------------------------------------------------
break on GRANTEE
select * from dba_role_privs order by GRANTEE, GRANTED_ROLE;

----------------------------------------------------------------------------------
--基本信息
----------------------------------------------------------------------------------
select * from v$version;
select * from v$option;
select * from v$sga;
select * from v$instance;
select * from v$database;
col VALUE format a40
select * from v$parameter;

DB2 专用寄存器

values(current date)

values(current refresh age)

values(current default transform group)

values(current schema)

values(current degree current server)

values(current explain mode)

values(current time)

values(current explain snapshot)

values(current timestamp)

values(current node)

values(current timezone)

values(current path user)

values(current query optimization)

DB2 临时表定义规则

declare global temporary table session.templ --定义临时表templ

like employee --定义临时表类型与 employee表名类似

on commit preserve rows --定义当数据提交后写入临时表

not logged --不记录历史

in mytempspace --定义表空间

与Oracle区别在与create declare

具体语法可以参考官方：http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0003272.htm

应用举例：

declare global temporary table session.templ --定义临时表templ

like a --定义临时表类型与 employee表名类似

on commit preserve rows --定义当数据提交后写入临时表；与Oracle一致的

not logged --不记录历史

in test --定义表空间

declare global temporary table session.test --定义临时表templ

like a --定义临时表类型与 employee表名类似

on commit delete rows --数据在 Transaction 期间有效，一旦COMMIT后，数据就被自动 TRUNCATE 掉了；与Oracle一致的

not logged --不记录历史

in test --定义表空间

DB2 访问前10行数据与Oracle区别

DB2 -- select * from test fetch first 10 rows only

Oracle -- select * from test where rownum<=10

DB2 Insert into 与Oracle区别

DB2 允许有类似这样多行插入 insert into staff values(1212,'cemy',20,'sales',3,90000,30000),

(1212,'cemy',20,'sales',3,90000,30000);

举例：

SQL> insert into staff values(1212,'cemy',20,'sales',3,90000,30000);

已创建 1 行。

SQL> insert into staff values(1212,'cemy',20,'sales',3,90000,30000),(1212,'cemy'

,20,'sales',3,90000,30000);

insert into staff values(1212,'cemy',20,'sales',3,90000,30000),(1212,'cemy',20,'

sales',3,90000,30000) *

第 1 行出现错误:

ORA-00933: SQL 命令未正确结束

DB2 Update 与Oracle区别

DB2 update staff set (salary,comm)=(80000,50000);

DB2 update staff set salary=80000,comm=50000;

举例：

SQL> update staff set (salary,comm)=(80000,50000);

update staff set (salary,comm)=(80000,50000) *

第 1 行出现错误:

ORA-01767: UPDATE ...SET 表达式必须是子查询

SQL> update staff set salary=80000,comm=50000;

已更新 1 行。

DB2 合并查询 与Oracle区别

DB2 INTERSECT、EXCEPT、UNION集合操作测试

说明：DB2支持三种集合操作INTERSECT（交集）、EXCEPT（差集）、UNION（合集），在ORACLE等数据库里面也有类似的操作，但是细微的地方可能有些差别，现在专门测试一下，以求准确！玩数据千万要细心啊，否则会带来难以意料的结果，呵呵！

测试环境：

DROP TABLE AA_WEEK;

CREATE TABLE AA_WEEK(

CODE SMALLINT NOT NULL,

EN_NAME VARCHAR(20) NOT NULL,

ABB VARCHAR(20),

CONSTRAINT P_AA_WEEK_CODE PRIMARY KEY (CODE)

);

COMMENT ON TABLE AA_WEEK IS

'星期';

COMMENT ON AA_WEEK

(CODE IS '星期代码',

EN_NAME IS '英文星期名',

ABB IS '英文缩写'

);

INSERT INTO AA_WEEK VALUES

(1,'Monday','Mon'),

(2,'Tuesday','Tues'),

(3,'Wednesday','Wed'),

(4,'Thursday','Thurs'),

(5,'Friday','Friday'),

(6,'Saturday','Sat'),

(7,'Sunday','Sun');

COMMIT;

查询一把，看看所有数据：

SELECT * FROM AA_WEEK;

+------+-----------+--------+

| CODE | EN_NAME | ABB |

+------+-----------+--------+

| 1 | Monday | Mon |

| 2 | Tuesday | Tues |

| 3 | Wednesday | Wed |

| 4 | Thursday | Thurs |

| 5 | Friday | Friday |

| 6 | Saturday | Sat |

| 7 | Sunday | Sun |

+------+-----------+--------+

一、交集运算：INTERSECT、INTERSECT ALL

-- 求交集：A交B 貌似Oracle没有求交集，项目暂时还没有留意到

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

INTERSECT -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------

2 'Tuesday' 'Tues'

3 'Wednesday' 'Wed'

说明：INTERSECT和INTERSECT ALL是等效的。

二、差集运算：EXCEPT、EXCEPT ALL 与Oracle 的minus 一致

-- 求差集：A减B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

EXCEPT -- 集合B

SELECT * FROM AA_WEEK w WHERE w.CODE IN (2,3,4);

说明：EXCEPT和EXCEPT ALL是等效的。

---------------------------------

1 'Monday' 'Mon'

三、合集运算 UNION 、UNION ALL 与Oracle时等效的

DB2 与 Oracle 类似语法

如：existscase when in

DB2 With as语法 与Oracle区别

DB2 WITH TMP_MON(MON) --(MON) 必须加上否则会有错误

AS (VALUES 1,2,3,4,5,6,7,8,9,10,11,12) SELECT * FROM TMP_MON

如： WITH TMP_MON AS (VALUES 1,2,3,4,5,6,7,8,9,10,11,12)

SELECT * FROM TMP_MON

DB2 Database Error: ERROR [42908] [IBM][DB2/NT64] SQL0153N The statement does not include a required column list. SQLSTATE=42908

Oracle with a as (select * from test)

select * from a;

Oracle其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了

DB2 截断 与Oracle truncate区别

DB2: ALTER TABLE TEST ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

Oracle: truncate table

Truncate语法：TRUNCATE TABLE name [DROP/REUSE STORAGE] 　　

DROP STORAGE：显式指明释放数据表和索引的空间 　　

REUSE STORAGE：显式指明不释放数据表和索引的空间

该死遇到这样错误

http://www.itpub.net/thread-1418178-1-1.html