{"id":1658,"date":"2019-07-31T16:41:20","date_gmt":"2019-07-31T08:41:20","guid":{"rendered":"http:\/\/www.llku.com\/?p=1658"},"modified":"2019-07-31T16:42:44","modified_gmt":"2019-07-31T08:42:44","slug":"oracle%e5%a2%9e%e5%8a%a0%e8%a1%a8%e7%a9%ba%e9%97%b4%e6%ad%a5%e9%aa%a4","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=1658","title":{"rendered":"Oracle\u589e\u52a0\u8868\u7a7a\u95f4\u6b65\u9aa4"},"content":{"rendered":"

\u672c\u6587\u53ea\u4ecb\u7ecd\u65b0\u589e\u6587\u4ef6\u7684\u65b9\u5f0f\uff08\u5176\u4ed6\u65b9\u5f0f\u7c7b\u4f3c\u53ef\u81ea\u884c\u7814\u7a76\uff09\uff1a<\/p>\n

1\u3001\u901a\u8fc7\u547d\u4ee4\uff1ashow parameter db_block_size\uff0c\u6765\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\u5355\u4e2a\u6587\u4ef6\u7684\u6700\u5927size\uff0c\u5982\u679c\u662f8192\uff088K\uff09\uff0c\u6700\u5927\u5355\u4e2a\u6587\u4ef6\u4e3a32G\uff0c\u5982\u679c\u662f16384\uff0816K\uff09\uff0c\u6700\u5927\u5355\u4e2a\u6587\u4ef6\u4e3a64G\u3002<\/p>\n

2\u3001\u901a\u8fc7\u4ee5\u4e0b\u547d\u4ee4\u67e5\u8be2\u5f53\u524d\u7cfb\u7edf\u5bf9\u5e94\u9700\u8981\u589e\u52a0\u6570\u636e\u6587\u4ef6\u7684\u8868\u7a7a\u95f4\u4fe1\u606f\u3002<\/p>\n

SELECT Upper(F.TABLESPACE_NAME) “\u8868\u7a7a\u95f4\u540d”,
\nD.TOT_GROOTTE_MB “\u8868\u7a7a\u95f4\u5927\u5c0f(M)”,
\nD.TOT_GROOTTE_MB – F.TOTAL_BYTES “\u5df2\u4f7f\u7528\u7a7a\u95f4(M)”,
\nTo_char(Round((D.TOT_GROOTTE_MB – F.TOTAL_BYTES) \/ D.TOT_GROOTTE_MB * 100,
\n2),
\n‘990.99’) || ‘%’ “\u4f7f\u7528\u6bd4”,
\nF.TOTAL_BYTES “\u7a7a\u95f2\u7a7a\u95f4(M)”,
\nF.MAX_BYTES “\u6700\u5927\u5757(M)”
\nFROM (SELECT TABLESPACE_NAME,
\nRound(Sum(BYTES) \/ (1024 * 1024), 2) TOTAL_BYTES,
\nRound(Max(BYTES) \/ (1024 * 1024), 2) MAX_BYTES
\nFROM SYS.DBA_FREE_SPACE
\nGROUP BY TABLESPACE_NAME) F,
\n(SELECT DD.TABLESPACE_NAME,
\nRound(Sum(DD.BYTES) \/ (1024 * 1024), 2) TOT_GROOTTE_MB
\nFROM SYS.DBA_DATA_FILES DD
\nGROUP BY DD.TABLESPACE_NAME) D
\nWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
\nORDER BY 1;<\/p>\n

3\u3001\u901a\u8fc7\u4ee5\u4e0b\u547d\u4ee4\u67e5\u8be2\u8868\u7a7a\u95f4\u5bf9\u5e94\u5355\u4e2a\u6587\u4ef6\u7684\u4fe1\u606f\uff08\u4e3b\u8981\u662f\u4fdd\u8bc1\u65b0\u589e\u7684\u6570\u636e\u6587\u4ef6\u8ddf\u539f\u6709\u6587\u4ef6\u4fdd\u5b58\u4fe1\u606f\u4e00\u81f4\uff0c\u5982\u4fdd\u8bc1\u5355\u4e2a\u6587\u4ef6\u4e00\u81f4\uff09\u3002<\/p>\n

SELECT “File Name”, “Tablespace”, “Status”, “Size (MB)”, “Used (MB)”, “Used (Proportion)”, “Used (%)”, “Auto Extend” FROM(
\nselect * from (
\nSELECT \/*+ all_rows use_concat *\/
\n‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||ddf.file_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
\nddf.tablespace_name as “Tablespace”,
\nddf.online_status as “Status”,
\nTO_CHAR(NVL(ddf.bytes \/ 1024 \/ 1024, 0), ‘99999990.000’) as “Size (MB)”,
\nTO_CHAR(DECODE(NVL(u.bytes\/1024\/1024, 0), 0, NVL((ddf.bytes – NVL(s.bytes, 0))\/1024\/1024, 0), NVL(u.bytes\/1024\/1024, 0)), ‘99999999.999’) as “Used (MB)”,
\nCASE
\nwhen ddf.online_status = ‘OFFLINE’ then
\n‘OFFLINE’
\nwhen ddf.online_status = ‘RECOVER’ then
\n‘RECOVER’
\nelse
\n‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(DECODE((NVL(u.bytes, 0) \/ ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) \/ ddf.bytes * 100, 0), (NVL(u.bytes, 0) \/ ddf.bytes * 100)), ‘990’))
\nend as “Used (Proportion)”,
\nTO_CHAR(DECODE((NVL(u.bytes, 0) \/ ddf.bytes * 100), 0, NVL((ddf.bytes – NVL(s.bytes, 0)) \/ ddf.bytes * 100, 0), (NVL(u.bytes, 0) \/ ddf.bytes * 100)), ‘990.99’) as “Used (%)”,
\nddf.autoextensible as “Auto Extend”
\nFROM
\nsys.dba_data_files ddf,
\n(
\nSELECT
\nfile_id,
\nSUM(bytes) bytes
\nFROM
\nsys.dba_free_space GROUP BY file_id
\n) s,
\n(
\nSELECT
\nfile_id,
\nSUM(bytes) bytes
\nFROM
\nsys.dba_undo_extents
\nWHERE
\nstatus <> ‘EXPIRED’
\nGROUP BY file_id
\n) u
\nWHERE
\n(ddf.file_id = s.file_id(+) and ddf.file_id=u.file_id(+))
\nUNION
\nSELECT
\n‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||v.name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
\ndtf.tablespace_name as “Tablespace”,
\ndtf.status as “Status”,
\nTO_CHAR(NVL(dtf.bytes \/ 1024 \/ 1024, 0), ‘99999990.000’) as “Size (MB)”,
\nTO_CHAR(NVL(t.bytes_used\/1024\/1024, 0), ‘99999990.000’) as “Used (MB)”,
\nCASE
\nwhen dtf.status = ‘OFFLINE’ then
\n‘OFFLINE’
\nelse
\n‘SQLDEV:GAUGE:0:100:0:0:’|| TRIM(TO_CHAR(NVL(t.bytes_used \/ dtf.bytes * 100, 0), ‘990.99’))
\nend as “Used (Proportion)”,
\nTO_CHAR(NVL(t.bytes_used \/ dtf.bytes * 100, 0), ‘990’) as “Used (%)”,
\ndtf.autoextensible as “Auto Extend”
\nFROM
\nsys.dba_temp_files dtf,
\nsys.v_$tempfile v,
\nv$temp_extent_pool t
\nWHERE
\n(dtf.file_name = v.name or dtf.file_id = v.file#)
\nand dtf.file_id = t.file_id(+)
\nORDER BY 1
\n) sub1 order by 2 asc
\n)<\/p>\n

4\u3001\u65b0\u589e\u6587\u4ef6\u547d\u4ee4\u3002<\/p>\n

alter tablespace APPS_TS_TX_DATA add datafile ‘\/u01\/DEV\/db\/data\/a_txn_data07.dbf’ size 10000m;<\/p>\n","protected":false},"excerpt":{"rendered":"