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(+);
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
------------------------------ ------------ ---------- ------------- ------------ -------------
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
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')
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
--------------------------------------------- ------------------------- ------------------- --------------- ------------
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
--------------------------------------------------------------------------- ----------- ----------- --------------------
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/756652/viewspace-695454/