oracle表空間增長趨勢分析

zhouwf0726發表於2019-07-12
Oracle
10號新上線個系統，發現表空間增長挺快，個別資料檔案自動擴充套件（理論上不建議自動擴充套件），分析下增長趨勢：
 
SQL> SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
  2  ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
  3  FROM
  4  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
  5  FROM DBA_DATA_FILES
  6  GROUP BY TABLESPACE_NAME) D,
  7  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
  8  FROM DBA_FREE_SPACE
  9  GROUP BY TABLESPACE_NAME) F
 10  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 11  UNION ALL --IF HAVE TEMPFILE
 12  SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
 13  USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
 14  NVL(FREE_SPACE,0) "FREE_SPACE(M)"
 15  FROM
 16  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
 17  FROM DBA_TEMP_FILES
 18  GROUP BY TABLESPACE_NAME) D,
 19  (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
 20  ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
 21  FROM V$TEMP_SPACE_HEADER
 22  GROUP BY TABLESPACE_NAME) F
 23  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
SYSAUX                                 2048     262144        915.44         44.7       1132.56
UNDOTBS1                              18360    2350080        267.62         1.46      18092.38
NDDZ_TBS                              35904    4595712      34307.12        95.55       1596.88
CHENHONG                                200      25600             1           .5           199
SUPERVISE_TBS                         10240    1310720             3          .03         10237
USERS                                   100      12800          94.5         94.5           5.5
GG                                    63488    8126464      53730.44        84.63       9757.56
SYSTEM                                 1660     212480         770.5        46.42         889.5
WEBGAMEHALL_TBS                       10240    1310720          1.06          .01      10238.94
NDDZIDX_TBS                           10240    1310720       1887.19        18.43       8352.81
TEMP                                  27726    3548928         27726          100             0
11 rows selected.
 
 
TABLESPACE_USEDSIZE TABLESPACE_SIZE 這兩個欄位單位應該是block。
TABLESPACE_SIZE NUMBER   Tablespace Size
TABLESPACE_MAXSIZE NUMBER   Maximum size of the tablespace
TABLESPACE_USEDSIZE NUMBER   Used size of the tablespace
 
 
SQL> select b.name,
  2         a.rtime,
  3         a.tablespace_usedsize,
  4         a.tablespace_size,
  5         round(100 * a.tablespace_usedsize / a.tablespace_size) used_percent
  6    from dba_hist_tbspc_space_usage a,
  7         (select t2.name,
  8                 min(rtime) rtime,
  9                 min(tablespace_id) tablespace_id
 10            from dba_hist_tbspc_space_usage t1
 11           inner join v$tablespace t2 on t1.tablespace_id = t2.TS#
 12           where t2.NAME = upper('&1')
 13           group by name, substr(rtime,1,10)        
 14  ) b
 15   where a.tablespace_id = b.tablespace_id
 16     and a.rtime = b.rtime
 17  order by a.rtime;
Enter value for 1: NDDZ_TBS
old  12:          where t2.NAME = upper('&1')
new  12:          where t2.NAME = upper('NDDZ_TBS')
NAME                                          RTIME                     TABLESPACE_USEDSIZE TABLESPACE_SIZE USED_PERCENT
--------------------------------------------- ------------------------- ------------------- --------------- ------------
NDDZ_TBS                                      05/08/2011 09:00:10                    494480         3932160           13
NDDZ_TBS                                      05/09/2011 00:00:40                    494488         3932160           13
NDDZ_TBS                                      05/10/2011 00:00:28                    504128         3932160           13
NDDZ_TBS                                      05/11/2011 00:00:18                    718328         3932160           18
NDDZ_TBS                                      05/12/2011 00:00:08                   1359216         3932160           35
NDDZ_TBS                                      05/13/2011 00:00:59                   2167744         3932160           55
NDDZ_TBS                                      05/14/2011 00:00:51                   3132144         3932160           80
NDDZ_TBS                                      05/15/2011 00:00:43                   3745936         3932160           95
NDDZ_TBS                                      05/16/2011 00:00:36                   4356104         4497408           97
9 rows selected.
 
 
PROJECTED估計是自動擴充套件的。
 
SQL> select * from table(dbms_space.OBJECT_GROWTH_TREND('OG', 'GAMEDETAIL', 'TABLE PARTITION', 'P201105'));
TIMEPOINT                                                                   SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
16-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
17-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
18-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
19-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
20-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
21-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
22-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
23-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
24-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
25-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
26-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
27-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
28-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
29-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
30-APR-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
01-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
02-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
03-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
04-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
05-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
06-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
07-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
08-MAY-11 10.16.24.924137 AM                                                 2082570215  2082570215 INTERPOLATED
09-MAY-11 10.16.24.924137 AM                                                 1948127785  1948127785 INTERPOLATED
10-MAY-11 10.16.24.924137 AM                                                 2014279440  2014279440 GOOD
11-MAY-11 10.16.24.924137 AM                                                 3204436699  3204436699 GOOD
12-MAY-11 10.16.24.924137 AM                                                 5169728993  5169728993 GOOD
13-MAY-11 10.16.24.924137 AM                                                 8160586783  8160586783 GOOD
14-MAY-11 10.16.24.924137 AM                                                 1.1474E+10  1.1837E+10 GOOD
15-MAY-11 10.16.24.924137 AM                                                 1.3568E+10  1.4301E+10 GOOD
16-MAY-11 10.16.24.924137 AM                                                 1.5848E+10  1.6823E+10 GOOD
17-MAY-11 10.16.24.924137 AM                                                 1.6093E+10  1.7103E+10 PROJECTED
18-MAY-11 10.16.24.924137 AM                                                 1.6337E+10  1.7383E+10 PROJECTED
19-MAY-11 10.16.24.924137 AM                                                 1.6581E+10  1.7663E+10 PROJECTED
20-MAY-11 10.16.24.924137 AM                                                 1.6825E+10  1.7943E+10 PROJECTED
21-MAY-11 10.16.24.924137 AM                                                 1.7070E+10  1.8222E+10 PROJECTED
36 rows selected.
EXCEPTION in chrow processing -  code: -14551  msg: ORA-14551: cannot perform. a DML operation inside a query

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-695454/，如需轉載，請註明出處，否則將追究法律責任。

相關文章