Oracle 表空间

--查看所有表空间的碎片程度（值在30以下表示碎片很多）
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);

--查看某个表空间内最大连续的自由空间大小
SELECT tablespace_name,max(bytes)/1024/1024 "max_free_space(M)"
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1;

--查看某个表空间内所占空间大于10G的段（表或索引）
col segment_name for a40
set linesize 200
SELECT segment_name,owner,segment_type,round(bytes/1024/1024,2) as "bytes(M)",tablespace_name FROM dba_segments
WHERE bytes>10000000000 AND tablespace_name='&tablespace_name' order by round(bytes/1024/1024,2);

--查看临时表空间使用情况
col tablespace_name for a20
col "Tempfile name" for a40
set wrap off
set linesize 200
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) "Used_Rate(%)",
0 "Fragmentation Index"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;


--查看表空间使用情况
set wrap off
set linesize 200
col tablespace_name for a20
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) order by 1;


--查找表空间IO情况

set linesize 200
set feedback off
set verify off
col tablespace for a20

--drop temporary table

drop table TEMP_HIST_FILESTATXS;
drop table TEMP_HIST_TEMPSTATXS;

--create temporary table

CREATE GLOBAL TEMPORARY TABLE TEMP_HIST_FILESTATXS(SNAP_ID NUMBER,
                                                     FILE# NUMBER NOT NULL
                                                     ENABLE,
                                                     CREATION_CHANGE#
                                                     NUMBER,
                                                     FILENAME VARCHAR2(600),
                                                     TS# NUMBER NOT NULL
                                                     ENABLE,
                                                     TSNAME VARCHAR2(30)
                                                     NOT NULL ENABLE,
                                                     BLOCK_SIZE NUMBER
                                                     NOT NULL ENABLE,
                                                     PHYRDS NUMBER,
                                                     PHYWRTS NUMBER,
                                                     SINGLEBLKRDS NUMBER,
                                                     READTIM NUMBER,
                                                     WRITETIM NUMBER,
                                                     SINGLEBLKRDTIM NUMBER,
                                                     PHYBLKRD NUMBER,
                                                     PHYBLKWRT NUMBER,
                                                     WAIT_COUNT NUMBER,
                                                     TIME NUMBER) ON COMMIT PRESERVE ROWS;

CREATE GLOBAL TEMPORARY TABLE TEMP_HIST_TEMPSTATXS(SNAP_ID NUMBER,
                                                     FILE# NUMBER NOT NULL
                                                     ENABLE,
                                                     CREATION_CHANGE#
                                                     NUMBER,
                                                     FILENAME VARCHAR2(600),
                                                     TS# NUMBER NOT NULL
                                                     ENABLE,
                                                     TSNAME VARCHAR2(30)
                                                     NOT NULL ENABLE,
                                                     BLOCK_SIZE NUMBER
                                                     NOT NULL ENABLE,
                                                     PHYRDS NUMBER,
                                                     PHYWRTS NUMBER,
                                                     SINGLEBLKRDS NUMBER,
                                                     READTIM NUMBER,
                                                     WRITETIM NUMBER,
                                                     SINGLEBLKRDTIM NUMBER,
                                                     PHYBLKRD NUMBER,
                                                     PHYBLKWRT NUMBER,
                                                     WAIT_COUNT NUMBER,
                                                     TIME NUMBER) ON COMMIT PRESERVE ROWS;

  --first snapshot

  insert into TEMP_HIST_FILESTATXS
    select 1 snap_id,
           f.file#,
           f.creation_change#,
           fn.filename,
           fn.ts#,
           fn.tsname,
           fn.block_size,
           phyrds,
           phywrts,
           singleblkrds,
           readtim,
           writetim,
           singleblkrdtim,
           phyblkrd,
           phyblkwrt,
           wait_count,
           time
      from (select df.file#,
                   (df.crscnbas + (df.crscnwrp * power(2, 32))) creation_change#,
                   fs.kcfiopyr phyrds,
                   fs.kcfiopyw phywrts,
                   fs.kcfiosbr singleblkrds,
                   fs.kcfioprt readtim,
                   fs.kcfiopwt writetim,
                   fs.kcfiosbt singleblkrdtim,
                   fs.kcfiopbr phyblkrd,
                   fs.kcfiopbw phyblkwrt,
                   fw.count wait_count,
                   fw.time time
              from x$kcfio fs, file$ df, x$kcbfwait fw
             where fw.indx + 1 = fs.kcfiofno
               and df.file# = fs.kcfiofno
               and df.status$ = 2) f,
           (select file#,
                   creation_change#,
                   filename,
                   ts#,
                   tsname,
                   block_size
              from (SELECT /*+ ordered index(f) index(ts) */
                     f.file# file#,
                     f.crscnbas + (f.crscnwrp * power(2, 32)) creation_change#,
                     v.name filename,
                     ts.ts# ts#,
                     ts.name tsname,
                     ts.blocksize block_size
                      FROM v$dbfile v, file$ f, ts$ ts
                     WHERE f.file# = v.file#
                       and f.status$ = 2
                       and f.ts# = ts.ts#)) fn
     where f.file# = fn.file#
       and f.creation_change# = fn.creation_change#;
commit;

  insert into TEMP_HIST_TEMPSTATXS
    select 1 snap_id,
           t.file#,
           t.creation_change#,
           tn.filename,
           tn.ts#,
           tn.tsname,
           tn.block_size,
           phyrds,
           phywrts,
           singleblkrds,
           readtim,
           writetim,
           singleblkrdtim,
           phyblkrd,
           phyblkwrt,
           wait_count,
           time
      from (select tf.tfnum file#,
                   to_number(tf.tfcrc_scn) creation_change#,
                   ts.kcftiopyr phyrds,
                   ts.kcftiopyw phywrts,
                   ts.kcftiosbr singleblkrds,
                   ts.kcftioprt readtim,
                   ts.kcftiopwt writetim,
                   ts.kcftiosbt singleblkrdtim,
                   ts.kcftiopbr phyblkrd,
                   ts.kcftiopbw phyblkwrt,
                   fw.count wait_count,
                   fw.time time
              from x$kcftio ts, x$kcctf tf, x$kcbfwait fw
             where tf.tfdup != 0
               and tf.tfnum = ts.kcftiofno
               and fw.indx + 1 =
                   (ts.kcftiofno +
                   (select value from v$parameter where name = 'db_files'))) t,
           (select file#,
                   creation_change#,
                   filename,
                   ts#,
                   tsname,
                   block_size
              from (SELECT tf.file# file#,
                           tf.creation_change# creation_change#,
                           tf.name filename,
                           ts.ts# ts#,
                           ts.name tsname,
                           tf.block_size block_size
                      FROM v$tempfile tf, ts$ ts
                     WHERE tf.ts# = ts.ts#)) tn
     where t.file# = tn.file#
       and t.creation_change# = tn.creation_change#;

  commit;

  --wait 30 seconds


exec sys.dbms_lock.sleep(30);

  --second snapshot

  insert into TEMP_HIST_FILESTATXS
    select 2 snap_id,
           f.file#,
           f.creation_change#,
           fn.filename,
           fn.ts#,
           fn.tsname,
           fn.block_size,
           phyrds,
           phywrts,
           singleblkrds,
           readtim,
           writetim,
           singleblkrdtim,
           phyblkrd,
           phyblkwrt,
           wait_count,
           time
      from (select df.file#,
                   (df.crscnbas + (df.crscnwrp * power(2, 32))) creation_change#,
                   fs.kcfiopyr phyrds,
                   fs.kcfiopyw phywrts,
                   fs.kcfiosbr singleblkrds,
                   fs.kcfioprt readtim,
                   fs.kcfiopwt writetim,
                   fs.kcfiosbt singleblkrdtim,
                   fs.kcfiopbr phyblkrd,
                   fs.kcfiopbw phyblkwrt,
                   fw.count wait_count,
                   fw.time time
              from x$kcfio fs, file$ df, x$kcbfwait fw
             where fw.indx + 1 = fs.kcfiofno
               and df.file# = fs.kcfiofno
               and df.status$ = 2) f,
           (select file#,
                   creation_change#,
                   filename,
                   ts#,
                   tsname,
                   block_size
              from (SELECT /*+ ordered index(f) index(ts) */
                     f.file# file#,
                     f.crscnbas + (f.crscnwrp * power(2, 32)) creation_change#,
                     v.name filename,
                     ts.ts# ts#,
                     ts.name tsname,
                     ts.blocksize block_size
                      FROM v$dbfile v, file$ f, ts$ ts
                     WHERE f.file# = v.file#
                       and f.status$ = 2
                       and f.ts# = ts.ts#)) fn
     where f.file# = fn.file#
       and f.creation_change# = fn.creation_change#;
commit;

  insert into TEMP_HIST_TEMPSTATXS
    select 2 snap_id,
           t.file#,
           t.creation_change#,
           tn.filename,
           tn.ts#,
           tn.tsname,
           tn.block_size,
           phyrds,
           phywrts,
           singleblkrds,
           readtim,
           writetim,
           singleblkrdtim,
           phyblkrd,
           phyblkwrt,
           wait_count,
           time
      from (select tf.tfnum file#,
                   to_number(tf.tfcrc_scn) creation_change#,
                   ts.kcftiopyr phyrds,
                   ts.kcftiopyw phywrts,
                   ts.kcftiosbr singleblkrds,
                   ts.kcftioprt readtim,
                   ts.kcftiopwt writetim,
                   ts.kcftiosbt singleblkrdtim,
                   ts.kcftiopbr phyblkrd,
                   ts.kcftiopbw phyblkwrt,
                   fw.count wait_count,
                   fw.time time
              from x$kcftio ts, x$kcctf tf, x$kcbfwait fw
             where tf.tfdup != 0
               and tf.tfnum = ts.kcftiofno
               and fw.indx + 1 =
                   (ts.kcftiofno +
                   (select value from v$parameter where name = 'db_files'))) t,
           (select file#,
                   creation_change#,
                   filename,
                   ts#,
                   tsname,
                   block_size
              from (SELECT tf.file# file#,
                           tf.creation_change# creation_change#,
                           tf.name filename,
                           ts.ts# ts#,
                           ts.name tsname,
                           tf.block_size block_size
                      FROM v$tempfile tf, ts$ ts
                     WHERE tf.ts# = ts.ts#)) tn
     where t.file# = tn.file#
       and t.creation_change# = tn.creation_change#;
commit;

--tablespace_io_result

prompt the normal value of "Av Rd(ms)" is always 20~30ms
select e.tsname "Tablespace",
       sum(e.phyrds - nvl(b.phyrds, 0)) "reads",
       round(sum(e.phyrds - nvl(b.phyrds, 0)) / 30, 0) "Av Reads/s",
       round(decode(sum(e.phyrds - nvl(b.phyrds, 0)),
                    0,
                    0,
                    10 * (sum(e.readtim - nvl(b.readtim, 0)) /
                    sum(e.phyrds - nvl(b.phyrds, 0)))),
             2) "Av Rd(ms)",
       round(decode(sum(e.phyrds - nvl(b.phyrds, 0)),
                    0,
                    0,
                    sum(e.phyblkrd - nvl(b.phyblkrd, 0)) /
                    sum(e.phyrds - nvl(b.phyrds, 0))),
             2) "Av Blks/Rd",
       sum(e.phywrts - nvl(b.phywrts, 0)) "writes",
       round(sum(e.phywrts - nvl(b.phywrts, 0)) / 30, 0) "Av Writes/s",
       sum(e.wait_count - nvl(b.wait_count, 0)) "Buffer Waits",
       round(decode(sum(e.wait_count - nvl(b.wait_count, 0)),
                    0,
                    0,
                    10 * (sum(e.time - nvl(b.time, 0)) /
                    sum(e.wait_count - nvl(b.wait_count, 0)))),
             2) "Av Buf Wt(ms)",
       sum(e.phyrds - nvl(b.phyrds, 0)) +
       sum(e.phywrts - nvl(b.phywrts, 0)) "Reads + Writes"
  from TEMP_HIST_FILESTATXS e, TEMP_HIST_FILESTATXS b
 where b.snap_id(+) = 1
   and e.snap_id = 2
   and b.tsname(+) = e.tsname
   and b.file#(+) = e.file#
   and b.creation_change#(+) = e.creation_change#
   and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
 group by e.tsname
union all
select e.tsname "Tablespace",
       sum(e.phyrds - nvl(b.phyrds, 0)) "reads",
       sum(e.phyrds - nvl(b.phyrds, 0)) / 30 "Av Reads/s",
       decode(sum(e.phyrds - nvl(b.phyrds, 0)),
              0,
              0,
              10 * (sum(e.readtim - nvl(b.readtim, 0)) /
              sum(e.phyrds - nvl(b.phyrds, 0)))) "Av Rd(ms)",
       decode(sum(e.phyrds - nvl(b.phyrds, 0)),
              0,
              0,
              sum(e.phyblkrd - nvl(b.phyblkrd, 0)) /
              sum(e.phyrds - nvl(b.phyrds, 0))) "Av Blks/Rd",
       sum(e.phywrts - nvl(b.phywrts, 0)) "writes",
       sum(e.phywrts - nvl(b.phywrts, 0)) / 30 "Av Writes/s",
       sum(e.wait_count - nvl(b.wait_count, 0)) "Buffer Waits",
       decode(sum(e.wait_count - nvl(b.wait_count, 0)),
              0,
              0,
              10 * (sum(e.time - nvl(b.time, 0)) /
              sum(e.wait_count - nvl(b.wait_count, 0)))) "Av Buf Wt(ms)",
       sum(e.phyrds - nvl(b.phyrds, 0)) +
       sum(e.phywrts - nvl(b.phywrts, 0)) "Reads + Writes"
  from TEMP_HIST_TEMPSTATXS e, TEMP_HIST_TEMPSTATXS b
 where b.snap_id(+) = 1
   and e.snap_id = 2
   and b.tsname(+) = e.tsname
   and b.file#(+) = e.file#
   and b.creation_change#(+) = e.creation_change#
   and ((e.phyrds - nvl(b.phyrds, 0)) + (e.phywrts - nvl(b.phywrts, 0))) > 0
 group by e.tsname
 order by "Reads + Writes" desc, "Tablespace";
exit;

补充：

Pctfree | pctused

对于pctfree 很pctused值

对于MSSM时，pctused和pctfree都很重要

对于ASMM时，仅仅使用pctfree，pctfree很重要

高pctfree，低pctused，如果插入将要更新的大量数据，更新会频繁的增加行的大小，适合该设置。

低pctfree，高pctused，如果只行对表完成insert或delete，或者如果你确实要完成update，但是 update只是缩小行的大小，该情况适合。

Logging|Nologging

通常对象都采用logging方式创建。对象上完成的操作只要能生成redo，就会生成redo

Nologging，则允许改对象的某些操作时可以不生成redo，只影响几个特定的操作。

Initrans |maxtrans

段中某个块有一个事务表，事务表会建立一些条目来描述这些事务将快上哪些行/元素锁定。

事务表的初始大小由对象的initrans设置。

对于表：默认是2

对于索引：默认是2

事务根据需要动态扩展，最大达到maxtrans。对于10g，maxtrans忽略，所有段的maxtrans都是255