\u6e05\u7406TEMP\u4e34\u65f6\u8868\u7a7a\u95f4\u3001\u6e05\u7406UNDO\u8868\u7a7a\u95f4\u3001\u6e05\u7406TEMPTABS\u8868\u7a7a\u95f4\u7b49\u7b49\uff0c\u6709\u7c7b\u4f3c\u9700\u6c42\u7684\u670b\u53cb\u53ef\u4ee5\u53c2\u8003\u4e0b\u54c8<\/p>\n
1.\u67e5\u8be2\u8868\u7a7a\u95f4\u4f7f\u7528\u60c5\u51b5:
\nsqlplus system\/manager@topprod<\/p>\n
SQL>@q_tbsFREE \r\n\r\n\u6216\u8005\uff1a\r\n\r\nSELECT UPPER(F.TABLESPACE_NAME) \"\u8868\u7a7a\u95f4\u540d\", \r\nD.TOT_GROOTTE_MB \"\u8868\u7a7a\u95f4\u5927\u5c0f(M)\", \r\nD.TOT_GROOTTE_MB - F.TOTAL_BYTES \"\u5df2\u4f7f\u7528\u7a7a\u95f4(M)\", \r\nTO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) \/ D.TOT_GROOTTE_MB * 100,2),'990.99') \"\u4f7f\u7528\u6bd4\", \r\nF.TOTAL_BYTES \"\u7a7a\u95f2\u7a7a\u95f4(M)\", \r\nF.MAX_BYTES \"\u6700\u5927\u5757(M)\" \r\nFROM (SELECT TABLESPACE_NAME, \r\nROUND(SUM(BYTES) \/ (1024 * 1024), 2) TOTAL_BYTES, \r\nROUND(MAX(BYTES) \/ (1024 * 1024), 2) MAX_BYTES \r\nFROM SYS.DBA_FREE_SPACE \r\nGROUP BY TABLESPACE_NAME) F, \r\n(SELECT DD.TABLESPACE_NAME, \r\nROUND(SUM(DD.BYTES) \/ (1024 * 1024), 2) TOT_GROOTTE_MB \r\nFROM SYS.DBA_DATA_FILES DD \r\nGROUP BY DD.TABLESPACE_NAME) D \r\nWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME \r\nORDER BY 4 DESC; \r\n<\/code><\/pre>\n2.\u67e5\u8be2temp\u4f7f\u7528\u65b9\u6cd5:
\nsqlplus system\/manager@topprod<\/p>\n
SQL>SELECT \r\nd.tablespace_name tablespace_name \r\n, d.status tablespace_status \r\n, NVL(a.bytes, 0) tablespace_size \r\n, NVL(t.bytes, 0) used \r\n, TRUNC(NVL(t.bytes \/ a.bytes * 100, 0)) used_pct \r\n, NVL(s.current_users, 0) current_users \r\nFROM \r\nsys.dba_tablespaces d \r\n, ( select tablespace_name, sum(bytes) bytes \r\nfrom dba_temp_files \r\ngroup by tablespace_name \r\n) a \r\n, ( select tablespace_name, sum(bytes_cached) bytes \r\nfrom v$temp_extent_pool \r\ngroup by tablespace_name \r\n) t \r\n, v$sort_segment s \r\nWHERE \r\nd.tablespace_name = a.tablespace_name(+) \r\nAND d.tablespace_name = t.tablespace_name(+) \r\nAND d.tablespace_name = s.tablespace_name(+) \r\nAND d.extent_management like 'LOCAL' \r\nAND d.contents like 'TEMPORARY'; <\/code><\/pre>\n2.\u6e05\u7406TEMP\u4e34\u65f6\u8868\u7a7a\u95f4\uff1a(\u5728\u65e0\u7528\u6237\u8fde\u63a5\u7684\u72b6\u51b5\u4e0b\u64cd\u4f5c,\u6700\u597d\u5728\u6e05\u7406\u4e4b\u524d\u91cd\u542f\u4e00\u4e0b\u6570\u636e\u5e93)<\/p>\n
#\u91cd\u542f\u6570\u636e\u5e93 \r\nsqlplus '\/as sysdba' \r\nSQL>shutdown immediate \r\nSQL>startup \r\n#\u521b\u5efa\u4e00\u4e2a\u4e34\u65f6\u8868\u7a7a\u95f4temp02\uff0c\u7528\u4f5c\u4e34\u65f6\u66ff\u6362 \r\nSQL>create temporary tablespace temp02 tempfile '\/u2\/oradb\/oradata\/topprod\/temp02.dbf' size 10M autoextend on next 10M; \r\n#\u5c06\u7cfb\u7edf\u4e34\u65f6\u8868\u7a7a\u95f4\u6307\u5411temp02 \r\nSQL>alter database default temporary tablespace temp02; \r\n#\u5220\u9664\u539f\u6765\u7684\u4e34\u65f6\u8868\u7a7a\u95f4temp \r\nSQL>drop tablespace temp including contents and datafiles; \r\n#\u521b\u5efa\u65b0\u7684\u4e34\u65f6\u8868\u7a7a\u95f4temp \r\nSQL>create temporary tablespace temp tempfile '\/u2\/oradb\/oradata\/topprod\/temp01.dbf' size 4096M autoextend on next 100M; \r\n#\u5c06\u7cfb\u7edf\u4e34\u65f6\u8868\u7a7a\u95f4\u6307\u56detemp \r\nSQL>alter database default temporary tablespace temp; \r\n#\u5220\u9664\u4e34\u65f6\u8868\u7a7a\u95f4temp02 \r\nSQL>drop tablespace temp02 including contents and datafiles; <\/code><\/pre>\n3.\u6e05\u7406UNDO\u8868\u7a7a\u95f4\uff1a(\u5728\u65e0\u7528\u6237\u8fde\u63a5\u7684\u72b6\u51b5\u4e0b\u64cd\u4f5c,\u6700\u597d\u5728\u6e05\u7406\u4e4b\u524d\u91cd\u542f\u4e00\u4e0b\u6570\u636e\u5e93)<\/p>\n
#\u91cd\u542f\u6570\u636e\u5e93 \r\nsqlplus '\/as sysdba' \r\nSQL>shutdown immediate \r\nSQL>startup \r\n#\u521b\u5efa\u4e00\u4e2aUNDO\u8868\u7a7a\u95f4undotbs2\uff0c\u7528\u4f5c\u4e34\u65f6\u66ff\u6362 \r\nSQL>create undo tablespace undotbs2 datafile '\/u2\/oradb\/oradata\/topprod\/undotbs02.dbf' size 10M autoextend on next 10M; \r\n#\u5c06\u7cfb\u7edfUNDO\u8868\u7a7a\u95f4\u6307\u5411undotbs2 \r\nSQL>alter system set undo_tablespace=undotbs2 scope=both; \r\n#\u786e\u4fdd\u6240\u6709\u5728UNDOTBS1\u7684undo segment\u90fd\u5df2offline \r\nSQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs; \r\n#\u5220\u9664\u539f\u6765\u7684UNDO\u8868\u7a7a\u95f4undotbs1 \r\nSQL>drop tablespace undotbs1 including contents and datafiles; \r\n#\u521b\u5efa\u65b0\u7684\u4e34\u65f6\u8868\u7a7a\u95f4undotbs1 \r\nSQL>create undo tablespace undotbs1 datafile '\/u2\/oradb\/oradata\/topprod\/undotbs01.dbf' size 4096M; \r\n#\u5c06\u7cfb\u7edfUNDO\u8868\u7a7a\u95f4\u6307\u56deundotbs1 \r\nSQL>alter system set undo_tablespace=undotbs1 scope=both; \r\n#\u5220\u9664UNDO\u8868\u7a7a\u95f4undotbs2 \r\nSQL>drop tablespace undotbs2 including contents and datafiles; <\/code><\/pre>\n3.\u6e05\u7406TEMPTABS\u8868\u7a7a\u95f4\uff1a<\/p>\n
#\u5220\u9664TEMPTABS\u8868\u7a7a\u95f4 \r\nSQL>drop tablespace temptabs including contents and datafiles; \r\n#\u521b\u5efaTEMPTABS\u8868\u7a7a\u95f4 \r\nSQL>create tablespace temptabs datafile '\/u2\/oradb\/oradata\/topprod\/temptabs.dbf' size 4096M autoextend on next 100M; \r\n\u6216\u8005\u5220\u9664\u8868 \r\n[code] \r\nselect 'drop table '||segment_name ||';' from dba_segments where tablespace_name='TEMPTABS' and segment_name like 'TT%' and segment_name not like '%_FILE'; <\/code><\/pre>\n4.\u589e\u52a0\u7cfb\u7edf\u8868\u7a7a\u95f4\uff1a<\/p>\n
alter tablespace SYSTEM add datafile '\/u2\/oradb\/oradata\/topprod\/system02.dbf' size 2000M autoextend on next 10M; \r\nalter tablespace SYSAUX add datafile '\/u2\/oradb\/oradata\/topprod\/sysaux02.dbf' size 2000M autoextend on next 10M; <\/code><\/pre>\n <\/p>\n
\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a\u8d6b\u975e\u57df<\/a> » T100\u548cTIPTOP\u4e2doracle\u8868\u7a7a\u5355\u6e05\u7406\u5e38\u7528\u4ee3\u7801\u6bb5\u6574\u7406<\/a><\/p>","protected":false},"excerpt":{"rendered":"\u6e05\u7406TEMP\u4e34\u65f6\u8868\u7a7a\u95f4\u3001\u6e05\u7406UNDO\u8868\u7a7a\u95f4\u3001\u6e05\u7406TEMPTABS\u8868\u7a7a\u95f4\u7b49\u7b49\uff0c\u6709\u7c7b\u4f3c\u9700\u6c42\u7684\u670b\u53cb\u53ef\u4ee5\u53c2\u8003\u4e0b\u54c8 1.\u67e5\u8be2\u8868\u7a7a\u95f4\u4f7f\u7528\u60c5\u51b5: sqlplus system\/manager@topprod SQL>@q_tbsFREE \u6216\u8005\uff1a SELECT […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,490],"tags":[756,757,755],"class_list":["post-5202","post","type-post","status-publish","format-standard","hentry","category-unix","category-490","tag-bytes","tag-nvl","tag-tablespace"],"_links":{"self":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/5202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5202"}],"version-history":[{"count":2,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/5202\/revisions"}],"predecessor-version":[{"id":7406,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/5202\/revisions\/7406"}],"wp:attachment":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}