------------------------------SGA---------------------------------------------
--SGA 各部分大小
show sga
select * from v$sga;
SELECT * FROM V$SGAINFO;
--SGA设置大小
show parameter sga_target

--SGA各个池大小
COL name FORMAT a32;
SELECT pool, name, bytes/1024/1024 M
  FROM v$sgastat
WHERE pool IS NULL
    OR pool != 'shared pool'
    OR (pool = 'shared pool' AND
       (name IN
       ('dictionary cache', 'enqueue', 'library
       cache', 'parameters', 'processes', 'sessions', 'free memory')))
ORDER BY pool DESC NULLS FIRST, name;
  
  
------------------------BUFFER CACHE-----------------------------------------------
--查看buffer cache 命中率
select 1 - (sum(decode(name, 'physical reads', value, 0)) /
        (sum(decode(name, 'db block gets', value, 0)) +
        (sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
   from v$sysstat;

  select name,
         physical_reads,
         (consistent_gets + db_block_gets) logic_reads,
         1 - (physical_reads) / (consistent_gets + db_block_gets) hit_radio
    from v$buffer_pool_statistics;

     --查看buffer cache建议  
     select size_for_estimate,
            estd_physical_read_factor,
            to_char(estd_physical_reads, 99999999999999999999999) as"estd_physical_reads"
       from v$db_cache_advice
     where name = 'DEFAULT';
  
     --查看buffer cache建议   --适用于指定SGA的目的
     COL pool FORMAT a10;
     SELECT (SELECT ROUND(value / 1024 / 1024, 0)
               FROM v$parameter
              WHERE name = 'db_cache_size') "Current Cache(Mb)",
            name "Pool",
            size_for_estimate "Projected Cache(Mb)",
            ROUND(100 - estd_physical_read_factor, 0) "Cache Hit Ratio%"
       FROM v$db_cache_advice
     WHERE block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
     ORDER BY 3;

     --查看cache
     show parameter cache

  --各种读取的统计
  ---Database read buffer cache hit ratio =
  ---1 – (physical reads / (db block gets + consistent gets))
  SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN
  ('physical reads', 'db block gets', 'consistent gets');

  SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"
          , ROUND((1-
          ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')
          / ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')
          + (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')
          ))) * 100)||'%' "Percentage"
          FROM DUAL;
      

------------------------SHARED_POOL-----------------------------------------------

show parameter shared

     ---检查整体命中率(library cache)
select sum(pinhits) get ,
       sum(pins)-sum(pinhits) miss,
       sum(pinhits) / sum(pins)
  from v$librarycache;

-- 查看library cache 命中率（分类）

   select t.NAMESPACE,t.GETHITRATIO*100
    from v$librarycache t;



     select sum(pins) "hits",
            sum(reloads) "misses",
            sum(pins) / (sum(pins) + sum(reloads)) "Hits Ratio"
       from v$librarycache;e
   
     ---检查shered pool  free  space
SELECT *
  FROM V$SGASTAT
WHERE NAME = 'free memory'
   AND POOL = 'shared pool';

     ---检查row cache(数据字典缓冲区)命中率
     ---当执行一个dml或ddl都会造成对数据字典的递归修改
     column updates format 999,999,999
     SELECT parameter
                  , sum(gets)
                  , sum(getmisses)
                 , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
                  , sum(modifications)                     updates
             FROM V$ROWCACHE
              WHERE gets > 0
             GROUP BY parameter;

  SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE"
    FROM V$ROWCACHE;

     ---查看Shared pool latch(多池技术)
  
     /*col parameter for a20
     col session for a20*/
       select a.ksppinm "Parameter",
             b.ksppstvl "Session Value",
             c.ksppstvl "Instance Value"
             from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
             where a.indx = b.indx and a.indx = c.indx
             and a.ksppinm = '_kghdsidx_count';
         
  ---查看shared pool建议
   column c1     heading 'Pool |Size(M)'
   column c2     heading 'Size|Factor'
   column c3     heading 'Est|LC(M)  '
   column c4     heading 'Est LC|Mem. Obj.'
   column c5     heading 'Est|Time|Saved|(sec)'
   column c6     heading 'Est|Parse|Saved|Factor'
   column c7     heading 'Est|Object Hits'   format 999,999,999
   SELECT shared_pool_size_for_estimate c1,
          shared_pool_size_factor c2,
          estd_lc_size c3,
          estd_lc_memory_objects c4,
          estd_lc_time_saved c5,
          estd_lc_time_saved_factor c6,
          to_char(estd_lc_memory_object_hits, 99999999999) c7
     FROM V$SHARED_POOL_ADVICE;     

  --查看shared pool中 各种类型的chunk的大小数量   
  SELECT KSMCHCLS CLASS,
         COUNT(KSMCHCLS) NUM,
         SUM(KSMCHSIZ) SIZ,
         To_char(((SUM(KSMCHSIZ) / COUNT(KSMCHCLS) / 1024)), '999,999.00') || 'k' "AVG SIzE"
    FROM X$KSMSP
   GROUP BY KSMCHCLS;

  --查看是否有库缓冲有关的等待事件
     select sid, seq#, event, p1, p1raw, p2, p2raw, p3, p3raw, state
       from v$session_wait
     where event like 'library%';   
      
--row cache命中率
SELECT 'Dictionary Cache Hit Ratio ' "Ratio",
       ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) * 100, 2) || '%' "Percentage"
  FROM V$ROWCACHE;

  ---library cache中详细比率信息
     SELECT 'Library Lock Requests' "Ratio",
            ROUND(AVG(gethitratio) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE
     UNION all
     SELECT 'Library Pin Requests' "Ratio",
            ROUND(AVG(pinhitratio) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE
     UNION all
     SELECT 'Library I/O Reloads' "Ratio",
            ROUND((SUM(reloads) / SUM(pins)) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE ;
   
  
  --查看library cache 内存分配情况(对哪类对象)
     SELECT lc_namespace               "Library",
       LC_INUSE_MEMORY_OBJECTS    "Objects",
       LC_INUSE_MEMORY_SIZE       "Objects Mb",
       LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects",
       LC_FREEABLE_MEMORY_SIZE    "Freeable Mb"
  FROM v$library_cache_memory;   

     ---查看使用shard_pool保留池情况
     SELECT request_misses, request_failures, free_space
     FROM v$shared_pool_reserved;
  
  
  
     ---查看cache中所有pool,命中情况
     COL pool FORMAT a10;
     SELECT a.name "Pool", a.physical_reads, a.db_block_gets
     , a.consistent_gets
     ,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)
     FROM v$buffer_pool_statistics
     WHERE db_block_gets+consistent_gets != 0
     AND name = a.name) "Ratio"
     FROM v$buffer_pool_statistics a;
  
  
     相关命令
     --- alter table xx cache
     ---ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);
     ---取消cache或keep(keep pool)
     ---ALTER TABLE XX NOCACHE;
     ---SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
     ---FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';



  
------------------------PGA-----------------------------------------------
  
     ---查看pga
     show parameters area_size
     --- 查看pga
     SELECT * FROM v$pgastat;
  
     --查看pga建议
     SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
     WHERE name = 'pga_aggregate_target') "Current Mb"
     , ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
     , ROUND(estd_pga_cache_hit_percentage) "%"
     FROM v$pga_target_advice
     ORDER BY 2;


  ------------------------其他指标类---------------------------------------

  ---查看数据库中行chain
  SELECT 'Chained Rows ' "Ratio",
                         ROUND((SELECT SUM(value) FROM V$SYSSTAT  WHERE name = 'table fetch continued row')/
                               (SELECT SUM(value) FROM V$SYSSTAT  WHERE name IN ('table scan rows gotten', 'table fetch byrowid')
                               )* 100, 3)||'%' "Percentage"
                         FROM DUAL;
                     
     ---在内存中排序比率(最优排序)
     SELECT 'Sorts in Memory ' "Ratio",
            ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)') /
                  (SELECT SUM(value)
                     FROM V$SYSSTAT
                    WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100,
                  5) || '%' "Percentage"
       FROM DUAL;
   
   
     ---查询解析比率
     SELECT 'Soft Parses ' "Ratio",
            ROUND(((SELECT SUM(value)
                      FROM V$SYSSTAT
                     WHERE name = 'parse count (total)') -
                  (SELECT SUM(value)
                      FROM V$SYSSTAT
                     WHERE name = 'parse count (hard)')) /
                  (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100,
                  2) || '%' "Percentage"
       FROM DUAL
     UNION
     SELECT 'Hard Parses ' "Ratio",
            ROUND((SELECT SUM(value)
                     FROM V$SYSSTAT
                    WHERE name = 'parse count (hard)') /
                  (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100,
                  2) || '%' "Percentage"
       FROM DUAL
     UNION
     SELECT 'Parse Failures ' "Ratio",
                 ROUND((SELECT SUM(value)
                     FROM V$SYSSTAT
                    WHERE name = 'parse count (failures)') /
                  (SELECT SUM(value)
                     FROM V$SYSSTAT
                    WHERE name = 'parse count (total)') * 100,
                  5) || '%' "Percentage"
       FROM DUAL;
   
   
     --查询latch free 等待事件相关信息
     COL event FORMAT a20;
     COL waits FORMAT 9999990;
     COL timeouts FORMAT 99999990;
     COL average FORMAT 99999990;
     SELECT event          "Event",
            time_waited    "Total Time",
            total_waits    "Waits",
            average_wait   "Average",
            total_timeouts "Timeouts"
       FROM V$SYSTEM_EVENT
     WHERE event = 'latch free'
     ORDER BY EVENT;   
     ---查看数据库中查询时主要访问方式,获取大表小表访问比率(2个表的访问算法不同)
   ---table scans (long tables)过多的话,一般db file scattered read比较显著
     ---_small_table_threshold来定义大表和小表的界限。缺省为2%的Buffer数量 ,>这个参数为大表
     ---default 大表的全表扫描会被置于LRU的末端(最近最少使用,冷端)，以期尽快老化(让其尽快换出buffer cache)，减少Buffer的占用
     --表访问统计
     SELECT value, name
     FROM V$SYSSTAT
    WHERE name IN ('table fetch by rowid',
                 'table scans (short tables)',
                 'table scans (long tables)');

  -----查看大表小表扫描对应的值
     SELECT value, name FROM V$SYSSTAT WHERE name IN
     ('table fetch by rowid', 'table scans (short tables)'
     , 'table scans (long tables)');
  
  
     SELECT 'Short to Long Full Table Scans' "Ratio"
     , ROUND(
     (SELECT SUM(value) FROM V$SYSSTAT
     WHERE name = 'table scans (short tables)')
     / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
     ('table scans (short tables)', 'table scans (long tables)'))
     * 100, 2)||'%' "Percentage"
     FROM DUAL
     UNION
     SELECT 'Short Table Scans ' "Ratio"
     , ROUND(
     (SELECT SUM(value) FROM V$SYSSTAT
     WHERE name = 'table scans (short tables)')
     / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
     ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
     * 100, 2)||'%' "Percentage"
     FROM DUAL
     UNION
     SELECT 'Long Table Scans ' "Ratio"
     , ROUND(
     (SELECT SUM(value) FROM V$SYSSTAT
     WHERE name = 'table scans (long tables)')
     / (SELECT SUM(value) FROM V$SYSSTAT WHERE name
     IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))
     * 100, 2)||'%' "Percentage"
     FROM DUAL
     UNION
     SELECT 'Table by Index ' "Ratio"
     , ROUND(
     (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')
     / (SELECT SUM(value) FROM V$SYSSTAT WHERE name
     IN ('table scans (short tables)', 'table scans (long tables)'
     , 'table fetch by rowid'))
     * 100, 2)||'%' "Percentage"
     FROM DUAL
     UNION
     SELECT 'Efficient Table Access ' "Ratio"
     , ROUND(
     (SELECT SUM(value) FROM V$SYSSTAT WHERE name
     IN ('table scans (short tables)','table fetch by rowid'))
     / (SELECT SUM(value) FROM V$SYSSTAT WHERE name
     IN ('table scans (short tables)', 'table scans (long tables)'
     , 'table fetch by rowid'))
     * 100, 2)||'%' "Percentage"
     FROM DUAL;
  

老师你这么牛，其他人造吗

--各种文件数量
select count(*) from v$tempfile;                       
select count(*) from v$datafile;

--表空间大小
select tablespace_name , sum(bytes)/1024/1024 M from dba_temp_files group by tablespace_name
union all
select tablespace_name , sum(bytes)/1024/1024 M from dba_data_files group by tablespace_name;

--数据文件状态
select  t.online_status,count(*)
from dba_data_files  t
group by  t.online_status ;

--表空间基本信息
SELECT t.status,
       t.tablespace_name,
       t.extent_management,
       t.segment_space_management,
       t.contents
FROM DBA_TABLESPACES  t
order by t.status


--临时段使用情况
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username,
       segtype,
       extents "Extents Allocated",
       blocks "Blocks Allocated"
  FROM v$tempseg_usage;


--查看临时表空间总体使用情况
SELECT TMP_TBS.TABLESPACE_NAME,
       SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
       SUM(USED_TOT.USED_MB) USED_MB,
       SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) TMP_TBS,
       (SELECT TMP_USED.TABLESPACE,
               SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
          FROM V$SORT_USAGE TMP_USED,
               (SELECT VALUE DB_BLOCK_SIZE
                  FROM V$PARAMETER
                 WHERE NAME = 'db_block_size') PARA
         GROUP BY TMP_USED.TABLESPACE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;
        

--查看临时表空间中排序段和数据段的使用情况
SELECT TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE TEMP_SEG_TYPE,
       SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
       SUM(USED_TOT.USED_MB) USED_MB,
       SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) TMP_TBS,
       (SELECT TMP_USED.TABLESPACE, TMP_USED.SEGTYPE,
               SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
          FROM V$SORT_USAGE TMP_USED,
               (SELECT VALUE DB_BLOCK_SIZE
                  FROM V$PARAMETER
                 WHERE NAME = 'db_block_size') PARA
         GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;



--表空间
set linesize 200;
col TABLESPACE_NAME  for a30;
select a.TABLESPACE_NAME tbs_name,
       round(a.BYTES/1024/1024) Total_MB,
       round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) Used_MB,
       round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) Pct_USED,
       nvl(round(b.BYTES/1024/1024), 0) Free_MB ,
       auto
from   (select   TABLESPACE_NAME,
                 sum(BYTES) BYTES,
                 max(AUTOEXTENSIBLE) AUTO
        from     sys.dba_data_files
        group by TABLESPACE_NAME) a,
       (select   TABLESPACE_NAME,
                 sum(BYTES) BYTES
        from     sys.dba_free_space
        group by TABLESPACE_NAME) b
where  a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc
/


---查看数据文件物理IO信息
SELECT fs.phyrds   "Reads",
       fs.phywrts  "Writes",
       fs.avgiotim "Average I/O Time",
       df.name     "Datafile"
  FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#
/



--查看所有数据文件i/o情况
/*COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990999;
COL writes FORMAT 999999990;
COL br FORMAT 999999990 HEADING "BlksRead";
COL bw FORMAT 9999999990 HEADING "BlksWrite";
COL rtime FORMAT 9999999990;
COL wtime FORMAT 9999999990;
set linesize 3000;
set pagesize 9999;*/
SELECT ts.name      AS ts,
       fs.phyrds    "Reads",
       fs.phywrts   "Writes",
       fs.phyblkrd  AS br,
       fs.phyblkwrt AS bw,
       fs.readtim/100   "RTime*s",
       fs.writetim/100  "WTime*s"
  FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts#
   AND df.file# = fs.file#
UNION
SELECT ts.name      AS ts,
       ts.phyrds    "Reads",
       ts.phywrts   "Writes",
       ts.phyblkrd  AS br,
       ts.phyblkwrt AS bw,
       ts.readtim /100  "RTime*s",
       ts.writetim/100  "WTime*s"
  FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts#
   AND tf.file# = ts.file#
ORDER BY 1;

--定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,
         o.name object_name,count(*) BLOCKS
         from obj$ o, x$bh x where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0
         group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
order by  decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),bh.blocks;
      



--针对不同用户的占用buffer的合计

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         own,sum(bh.blocks)*8192/1024/1024  used_M
from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,
         o.name object_name,count(*) BLOCKS,u.name own
         from obj$ o, x$bh x,user$ u where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0 and o.owner#=u.user#
         group by set_ds,o.name,u.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
And pd.bp_size != 0
and ds.addr=bh.set_ds
group by decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
         4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),
         own
order  by own;


--buffer cache 对象所有者，名称， 类型，总大小，cache大小
column c1 heading "Object|Name" format a30 truncate
column c2 heading "Object|Type" format a12 truncate
column c3 heading "Number of|Blocks" format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999
break on report
compute sum of c3 on report
select owner,
       object_name,
       object_type,
       num_blocks,
       sum(blocks),
       trunc((num_blocks / decode(sum(blocks), 0, .001, sum(blocks))), 4) * 100 || '%' -- buffer中的数据块比例
  from (select o.owner owner_name,
               o.object_name object_name,
               o.object_type object_type,
               count(1) num_blocks
          from dba_objects o, v$bh bh
         where o.object_id = bh.objd
           and o.owner not in ('SYS', 'SYSTEM')
         group by o.object_name, o.object_type, o.owner
         order by count(1) desc) t1,
       dba_segments s
where s.segment_name = t1.object_name
   and s.owner = t1.owner_name
   and num_blocks > 10
group by object_name, object_type, num_blocks, owner
order by num_blocks desc;

--10个热点对象
col objct_name for a30
select * from
(select
  ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
  and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10
/

--逻辑读
col objct_name for a30
select *
  from (select owner, object_name, value
          from v$segment_statistics
         where statistic_name = 'logical reads'
         order by value desc)
where rownum <= 10
/

--物理读最多十个对象
col objct_name for a30
select *
  from (select owner, object_name, value
          from v$segment_statistics
         where statistic_name = 'physical reads'
         order by value desc)
where rownum <= 10
/


---查看热点数据文件(从单块读取时间判断)
COL FILE_NAME FOR A30
COL TABLESPACE_NAME FOR A20
SELECT T.FILE_NAME,
       T.TABLESPACE_NAME,
       ROUND(S.SINGLEBLKRDTIM / S.SINGLEBLKRDS, 2) AS CS,
       S.READTIM/100 READTIME_S,
       S.WRITETIM/100 WIRTETIME_S
  FROM V$FILESTAT S, DBA_DATA_FILES T
WHERE S.FILE# = T.FILE_ID
   AND ROWNUM <= 10
ORDER BY CS DESC
/


select 'CREATE TABLESPACE ' || t.tablespace_name || ' datafile ''' ||
       t1.path || t.tablespace_name || '01.dbf' ||
       ''' SIZE 500M  autoextend on  next 50m ;'
  from dba_tablespaces t,
       (select substr(tt.file_name, 1, instr(tt.file_name, '/', '-1')) path
          from dba_data_files tt
         where rownum = 1) t1`
where t.tablespace_name not in ( 'SYSTEM', 'SYSAUX')
   AND T.contents = 'PERMANENT';

精品，孙老师出手

不错，挺全的；学习了！谢谢分享

参考学习了，有用的就留下了

参考学习一下

必须顶啊，学习了

很厉害，学习学习~~~

能把你学习经验介绍下吗？我是个新手