• CMDS目的端数据库碎片整理记录

    CMDS目的端数据库碎片整理记录

    看看数据库里面需要做整理的表有哪些,条件可以根据需求稍微改动一下
    1. SQL> select * from (
    2.   2  select a.owner,
    3.   3         a.table_name,
    4.   4         a.num_rows,
    5.   5         a.avg_row_len * a.num_rows,
    6.   6         sum(b.bytes),
    7.   7         (a.avg_row_len * a.num_rows) / sum(b.bytes) frag
    8.   8    from dba_tables a, dba_segments b
    9.   9   where a.table_name = b.segment_name
    10.  10  and a.owner= b.owner and a.owner='CMDS' and a.num_rows !=0 and a.num_rows >= 10000000
    11.  11     and a.owner not in
    12.  12         ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
    13.  13          'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN','PATROL')
    14.  14     group by  a.owner,a.table_name,a.avg_row_len, a.num_rows
    15.  15    having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
    16.  16    order by sum(b.bytes) desc)
    17.  17  where rownum <= 100;
    19. OWNER                          TABLE_NAME                       NUM_ROWS A.AVG_ROW_LEN*A.NUM_ROWS SUM(B.BYTES)       FRAG
    20. ------------------------------ ------------------------------ ---------- ------------------------ ------------ ----------
    21. CMDS                           CONTACT_LIST                   3800662127               2.4704E+11   4.0227E+11        .61
    22. CMDS                           INSURED_BACKUP                 2916523023               1.5458E+11   2.7488E+11        .56
    23. CMDS                           CONTACT_COMB                   1249703629               5.7486E+10   8.4437E+10        .68
    24. CMDS                           CUST_RELATION_UPD               386995715               1.4706E+10   2.3089E+10        .64
    25. CMDS                           PARTY                          1235330775               1.1118E+10   2.1475E+10        .52
    26. CMDS                           PSN_CUSTOMER_UPD_20160901       351127588               1.3343E+10   2.0420E+10        .65
    27. CMDS                           MOBILE_1                        345051414               1.1387E+10   1.6300E+10        .70
    28. CMDS                           PSN_HLDR_UPD                    219959628               8798385120   1.4997E+10        .59
    29. CMDS                           INSURED_UPD                     224986718               9224455438   1.4346E+10        .64
    30. CMDS                           GRP_CUSTOMER                     13639704               1473088032   5638127616        .26
    31. CMDS                           APPL_STATE                       57276612               3780256392   5552209920        .68
    32. CMDS                           GRP_CNTR_HLDR                    49157672               3096933336   4894752768        .63
    33. CMDS                           PSN_CUSTOMER_DEL                 66607272               3130541784   4728029184        .66
    34. CMDS                           TMP_PSN_REL_1_END               182973898               2561634572   3892314112        .66
    35. CMDS                           APPL_INSURED                     29878010               2001826670   3116367872        .64
    36. CMDS                           GRP_PARTY_RELATION               13545920                690841920   2264924160        .31
    37. CMDS                           GRP_PERSON                       10076783                433301669   2087714816        .21
    38. CMDS                           PSN_CNTR_HOLDER_UBPS_BAK         17219039               1256989847   1857945600        .68
    39. CMDS                           HTEL_ERROR_N                     19520444                995542644   1677721600        .59
    40. CMDS                           BACK_PERSON_REP                  24107652                916090776   1326448640        .69
    41. CMDS                           TEMP_PERSON_REP_3_ALLFIELDS      24020055                912762090   1326448640        .69
    42. CMDS                           PID_RELATION_1                   64767526                777210312   1282408448        .61
    43. CMDS                           PERSON_DELETED                   20662170                805824630   1210056704        .67
    44. CMDS                           SUSPECT_3_0                      47021762                799369954   1207959552        .66
    45. CMDS                           PID_RELATION_2                   53957397                647488764   1044381696        .62
    46. CMDS                           TEMP_CUSTIDTOMERG_MISS_0225      12511777                412888641    751828992        .55
    47. CMDS                           TEMP_PERSON_DEFAULT_1            29635442                444531630    705691648        .63
    48. CMDS                           ZB_DUP_PARTY_RELATION_12_A       35121535                351215350    603979776        .58
    49. CMDS                           T_CUST_INFO_1512                 44795595                313569165    594542592        .53
    50. CMDS                           PURVIEWINFO                      12383109                383876379    571473920        .67
    51. CMDS                           TEMP_PERSON_DEL                  24904280                174329960    344981504        .51
    52. CMDS                           TEMP_PERSON_DEL_1                24100913                168706391    335544320        .50
    53. CMDS                           SUSPECT_1_0                      11442724                217411756    318767104        .68
    54. CMDS                           MOBILE_3                         10994502                164917530    252706816        .65
    55. CMDS                           TEMP_PERSON_REP                  12172895                121728950    208666624        .58
    56. CMDS                           TEMP_PERSON_REP_3                11103556                111035560    192937984        .58
    57. CMDS                           TEMP_PERSON_REP_1                11103556                111035560    190840832        .58
    58. CMDS                           TEMP_PERSON_REP_12               11099995                110999950    187695104        .59
    59. CMDS                           PARTYID_DISTINCT                 10882249                 76175743    147849216        .52

    1. SQL> select * from (
    2.   2  select a.owner,
    3.   3         a.table_name,
    4.   4         a.num_rows,
    5.   5         a.last_analyzed,
    6.   6         a.avg_row_len * a.num_rows,
    7.   7         sum(b.bytes),
    8.   8         ROUND((a.avg_row_len * a.num_rows) / sum(b.bytes),2) frag
    9.   9    from dba_tables a, dba_segments b
    10.  10   where a.table_name = b.segment_name
    11.  11  and a.owner='CMDS' and a.num_rows !=0 and a.num_rows >= 1000000 and a.table_name not in(select table_name from dba_tables where table_name  like '%TEMP%' or a.table_name  like '%BAK%' or a.table_name  like '%TMP%' and owner='CMDS') 
    12.  12     group by  a.owner,a.table_name,a.avg_row_len, a.num_rows,a.last_analyzed
    13.  13    having a.avg_row_len * a.num_rows / sum(b.bytes) <= 0.7
    14.  14    order by a.table_name,sum(b.bytes) desc)
    15.  15  where rownum <= 100;
    17. OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANAL A.AVG_ROW_LEN*A.NUM_ROWS SUM(B.BYTES)       FRAG
    18. ------------------------------ ------------------------------ ---------- --------- ------------------------ ------------ ----------
    19. CMDS                           APPL_BNFR                         1656401 23-JAN-17                159014496    821035008        .19
    20. CMDS                           APPL_INSURED                     29878010 23-JAN-17               2001826670   3116367872        .64
    21. CMDS                           APPL_STATE                       57276612 23-JAN-17               3780256392   5560598528        .68
    22. CMDS                           BACK_PERSON_REP                  24107652 17-DEC-14                916090776   1326448640        .69
    23. CMDS                           BINDPOLINFO_L_1                   8278592 08-OCT-16                 82785920    142606336        .58
    24. CMDS                           BINDPOLOPTION                     3407344 04-JAN-17                 61332192    100663296        .61
    25. CMDS                           CONTACT_COMB                   1249703629 30-JUN-16               5.7486E+10   8.4437E+10        .68
    26. CMDS                           CONTACT_LIST                   3800662127 15-JAN-17               2.4704E+11   4.0227E+11        .61
    27. CMDS                           CUSTOMINFO_E_SINGLE_CNTR          2923310 23-JAN-17                464806290   1083179008        .43
    28. CMDS                           CUST_RELATION_UPD               386995715 25-NOV-16               1.4706E+10   2.3089E+10        .64
    29. CMDS                           C_CUST_RELATION_UPD               1034538 01-SEP-16                 42416058     94371840        .45
    30. CMDS                           C_INSURED_UPD                     2598511 01-SEP-16                 93546396    152043520        .62
    31. CMDS                           C_PSN_CNTR_HOLDER_SINCR_SR1       1392097 23-OCT-14                 26449843     38797312        .68
    32. CMDS                           C_PSN_CNTR_HOLDER_SINCR_SR2       1083707 04-MAY-15                 20590433     30408704        .68
    33. CMDS                           C_PSN_HLDR_UPD                    1229821 01-SEP-16                 50422661     75497472        .67
    34. CMDS                           GRP_CNTR_HLDR                    49157672 09-JAN-17               3096933336   4894752768        .63
    35. CMDS                           GRP_CUSTOMER                     13639704 09-JAN-17               1473088032   5638127616        .26
    36. CMDS                           GRP_PARTY_RELATION               13545920 09-JAN-17                690841920   2264924160        .31
    37. CMDS                           GRP_PERSON                       10076783 09-JAN-17                433301669   2087714816        .21
    38. CMDS                           HTEL_ERROR_N                     19520444 13-JUL-14                995542644   1677721600        .59
    39. CMDS                           INSURED_BACKUP                 2916523023 18-OCT-15               1.5458E+11   2.7488E+11        .56
    40. CMDS                           INSURED_UPD                     224986718 08-DEC-16               9224455438   1.4346E+10        .64
    41. CMDS                           JIEGUO20161227                    1108122 27-DEC-16                 15513708     24117248        .64
    42. CMDS                           MOBILE_1                        345051414 18-MAR-15               1.1387E+10   1.6300E+10         .7
    43. CMDS                           MOBILE_3                         10994502 01-APR-15                164917530    252706816        .65
    44. CMDS                           OTEL_ERROR_N                      8937206 11-JUL-14                455797506    756023296         .6
    45. CMDS                           PARTY                          1235330775 20-JAN-17               1.1118E+10   2.1475E+10        .52
    46. CMDS                           PARTYID_DISTINCT                 10882249 12-MAY-15                 76175743    147849216        .52
    47. CMDS                           PASSWORDSTAT                      9357346 27-DEC-16                215218958    313524224        .69
    48. CMDS                           PERSON_DELETED                   20662170 12-MAY-15                805824630   1210056704        .67
    49. CMDS                           PID_RELATION_1                   64767526 09-SEP-14                777210312   1282408448        .61
    50. CMDS                           PID_RELATION_2                   53957397 09-SEP-14                647488764   1044381696        .62
    51. CMDS                           PSN_CUSTOMER_DEL                 66607272 21-JAN-17               3130541784   4728029184        .66
    52. CMDS                           PSN_CUSTOMER_UPD_20160901       351127588 11-AUG-16               1.3343E+10   2.0420E+10        .65
    53. CMDS                           PSN_HLDR_UPD                    219959628 04-NOV-16               8798385120   1.4997E+10        .59
    54. CMDS                           PURVIEWINFO                      12383109 13-JAN-17                383876379    571473920        .67
    55. CMDS                           STD_CONTRACT_NULL                 1674265 16-JUN-15                112175755    166723584        .67
    56. CMDS                           SUSPECT_1_0                      11442724 07-SEP-14                217411756    318767104        .68
    57. CMDS                           SUSPECT_3_0                      47021762 07-SEP-14                799369954   1207959552        .66
    58. CMDS                           SUSPECT_4_0                       2980964 08-SEP-14                 56638316     83886080        .68
    59. CMDS                           SUSPECT_4_1                       2980964 08-SEP-14                 74524100    109051904        .68
    60. CMDS                           SUSPECT_5_0                       4629724 08-SEP-14                101853928    150994944        .67
    61. CMDS                           TASKTRACE                         1169349 23-FEB-15                 56128752     83886080        .67
    62. CMDS                           TTEL_ERROR_N                      6398147 11-JUL-14                326305497    545259520         .6
    63. CMDS                           TTT                               3289563 15-JAN-15                 65791260    100663296        .65
    64. CMDS                           T_CUST_INFO_1512                 44795595 04-DEC-15                313569165    594542592        .53
    65. CMDS                           VIPINFO_3                         3113050 01-APR-15                 31130500     52428800        .59
    66. CMDS                           ZB_DUP_PARTY_RELATION             1238876 05-JAN-15                 44599536   1708130304        .03
    67. CMDS                           ZB_DUP_PARTY_RELATION_12_A       35121535 15-JAN-15                351215350    603979776        .58
    68. CMDS                           ZB_MUL_BRANCH_PARTY_STAT          9914140 15-SEP-15                 99141400    170917888        .58
    69. CMDS                           ZB_PERSON_PK_FLD_DUP_0529         1096878 29-MAY-15                 10968780     18874368        .58
    70. CMDS                           ZB_TELREUSED_HLDR                 1339541 19-MAY-15                 25451279     36700160        .69
    71. CMDS                           ZB_TELREUSED_HLDR_2000            4508181 27-MAY-15                 99179982    142606336         .7
    72. CMDS                           ZB_TELREUSED_STAT_INTER_PROV      3959576 04-JUN-15                 59393640     94371840        .63
    74. 54 rows selected.

    最后整理结果:
    APPL_BNFR频繁delete insert
    APPL_INSURED频繁delete insert
    APPL_STATE频繁delete insert
    BACK_PERSON_REP重复数据清理数据表
    BINDPOLINFO_L_1备份数据表
    BINDPOLOPTION只insert
    CONTACT_COMB频繁delete insert
    CONTACT_LIST频繁delete insert
    CUSTOMINFO_E_SINGLE_CNTR新表
    CUST_RELATION_UPD原归并用，现在已经停用
    C_CUST_RELATION_UPD原归并用，现在已经停用
    C_INSURED_UPD原归并用，现在已经停用
    C_PSN_CNTR_HOLDER_SINCR_SR1备份数据表
    C_PSN_CNTR_HOLDER_SINCR_SR2备份数据表
    C_PSN_HLDR_UPD原归并用，现在已经停用
    GRP_CNTR_HLDR频繁delete insert
    GRP_CUSTOMER频繁delete insert
    GRP_PARTY_RELATION频繁delete insert
    GRP_PERSON频繁delete insert
    HTEL_ERROR_N备份数据表
    INSURED_BACKUP备份数据表
    INSURED_UPD原归并用，现在已经停用
    JIEGUO20161227数据提取数据表
    MOBILE_1数据提取数据表
    MOBILE_3数据提取数据表
    OTEL_ERROR_N数据提取数据表
    PARTY新表
    PARTYID_DISTINCT重复数据清理数据表
    PASSWORDSTAT只insert、update
    PERSON_DELETED重复数据清理数据表
    PID_RELATION_1备份数据表
    PID_RELATION_2备份数据表
    PSN_CUSTOMER_DEL只insert
    PSN_CUSTOMER_UPD_20160901备份数据表
    PSN_HLDR_UPD原归并用，现在已经停用
    PURVIEWINFO只insert、update
    STD_CONTRACT_NULL备份数据表
    SUSPECT_1_0数据提取数据表
    SUSPECT_3_0数据提取数据表
    SUSPECT_4_0数据提取数据表
    SUSPECT_4_1数据提取数据表
    SUSPECT_5_0数据提取数据表
    TASKTRACE原抽取用，现在已经停用
    TTEL_ERROR_N数据提取数据表
    TTT数据提取数据表
    T_CUST_INFO_1512数据提取数据表
    VIPINFO_3数据提取数据表
    ZB_DUP_PARTY_RELATION重复数据清理数据表
    ZB_DUP_PARTY_RELATION_12_A重复数据清理数据表
    ZB_MUL_BRANCH_PARTY_STAT重复数据清理数据表
    ZB_PERSON_PK_FLD_DUP_0529重复数据清理数据表
    ZB_TELREUSED_HLDR重复数据清理数据表
    ZB_TELREUSED_HLDR_2000重复数据清理数据表
    ZB_TELREUSED_STAT_INTER_PROV重复数据清理数据表
    psn_cntr_holder
    insured
    std_CONTRACT
    beneficiary
    person
    party_relation
    CUST_RELATION

    整理之前的表空间使用情况:
    1. TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
    2. ------------------------------ ------------ ---------- ------------- ------------ -------------
    3. CUST                             2097096.93  268428407     1982107.8        94.52     114989.13
    4. TBS210000                             65534    8388352      59268.37        90.44       6265.63
    5. TBS140000                             65534    8388352      58680.31        89.54       6853.69
    6. TBS320000                            196602   25165056     170611.44        86.78      25990.56
    7. TBS440000                            196602   25165056     166456.06        84.67      30145.94
    8. TBS650000                             65534    8388352      54905.75        83.78      10628.25
    9. CMDSIDXTBS                          2194843  280939904    1775170.37        80.88     419672.63
    10. TBS310000                             98301   12582528         76889        78.22         21412
    11. TBS420000                             98301   12582528      76621.62        77.95      21679.38
    12. TBS220000                             32767    4194176      25470.69        77.73       7296.31
    13. TBS510000                            196602   25165056     148146.69        75.35      48455.31
    14. TBS350000                             98301   12582528      72193.31        73.44      26107.69
    15. TBS330000                             98301   12582528      70315.31        71.53      27985.69
    16. TBS110000                             49151    6291328      34597.12        70.39      14553.88
    17. TBS430000                            163835   20970880     114531.06        69.91      49303.94
    18. SYSTEM                                 2048     262144       1413.19           69        634.81
    19. TBS530000                             65534    8388352      44278.81        67.57      21255.19
    20. TBS150000                             65534    8388352      43737.12        66.74      21796.88
    21. TBS620000                             65534    8388352      43712.69         66.7      21821.31
    22. TBS340000                             98301   12582528      64618.12        65.73      33682.88
    23. TBS610000                             98301   12582528      62513.25        63.59      35787.75
    24. TBS520000                             32767    4194176      20766.56        63.38      12000.44
    25. TBS410000                            196602   25165056     123419.87        62.78      73182.13
    26. TBS230000                            163835   20970880     101563.44        61.99      62271.56
    27. TBS370000                            294903   37747584     182646.81        61.93     112256.19
    28. TBS660000                             98301   12582528      59170.06        60.19      39130.94
    29. TBS130000                            196602   25165056     117995.19        60.02      78606.81
    30. TBS450000                             98301   12582528      55677.87        56.64      42623.13
    31. TBS360000                             98301   12582528      52901.12        53.82      45399.88
    32. TBS640000                             32767    4194176         15780        48.16         16987
    33. TBS120000                             32767    4194176      15653.94        47.77      17113.06
    34. TBS000002                             98301   12582528      44034.25         44.8      54266.75
    35. TBS210200                             32767    4194176      13331.94        40.69      19435.06
    36. TBS370200                             32767    4194176      12572.31        38.37      20194.69
    37. TBS330200                             32767    4194176      11091.87        33.85      21675.13
    38. TBS440200                             32767    4194176          9439        28.81         23328
    39. TBS350200                             32767    4194176       9103.87        27.78      23663.13
    40. UNDOTBS1                          553834.95   70890874     123857.39        22.36     429977.56
    41. TBS460000                             32767    4194176       6132.94        18.72      26634.06
    42. SYSAUX                             65534.98    8388478      11063.17        16.88      54471.81
    43. TBS630000                             32767    4194176       3667.62        11.19      29099.38
    44. USERS                                   512      65536         39.75         7.76        472.25
    45. CMDSDATTBS                           163835   20970880          8393         5.12        155442
    46. TBS850000                             32767    4194176        794.69         2.43      31972.31
    47. UNDOTBS2                          163837.95   20971258       3099.57         1.89     160738.38
    48. TBS_OGG                               10240    1310720             1          .01         10239

    表的大小:
    1. TABLE_NAME                     ROW_MOVE   NUM_ROWS
    2. ------------------------------ -------- ----------
    3. BENEFICIARY                    ENABLED           0
    4. APPL_BNFR                      ENABLED     1617277
    5. GRP_PERSON                     ENABLED    10076783
    6. GRP_PARTY_RELATION             ENABLED    13545920
    7. GRP_CUSTOMER                   ENABLED    13639704
    8. APPL_INSURED                   ENABLED    30180886
    9. GRP_CNTR_HLDR                  ENABLED    49157672
    10. APPL_STATE                     ENABLED    57509863
    11. PARTY_RELATION                 ENABLED   739262989
    12. PERSON                         ENABLED  1038646626
    13. CUST_RELATION                  ENABLED  1166641056
    15. TABLE_NAME                     ROW_MOVE   NUM_ROWS
    16. ------------------------------ -------- ----------
    17. CONTACT_COMB                   ENABLED  1249703629
    18. STD_CONTRACT                   ENABLED  1369860819
    19. PSN_CNTR_HOLDER                ENABLED  1403102188
    20. INSURED                        ENABLED  1715030701
    21. CONTACT_LIST                   ENABLED  3800662127

    整理脚本
    1. echo `date +"%B %d %H:%M:%S"` `hostname` starting... >> $ORACLE_HOME/log/shrink_cmds_20170127.log
    3. $ORACLE_HOME/bin/sqlplus -S /nolog<<!
    4. connect / as sysdba;
    6. set timing on;
    8. alter session set sort_area_size=1024000000;
    9. alter session set sort_multiblock_read_count=128;
    10. alter session set db_file_multiblock_read_count=128;
    12. alter table CMDS.APPL_BNFR            shrink space cascade;
    13. commit;
    14. alter table CMDS.APPL_INSURED         shrink space cascade;
    15. commit;
    16. alter table CMDS.APPL_STATE           shrink space cascade;
    17. commit;
    18. alter table CMDS.CONTACT_COMB         shrink space cascade;
    19. commit;
    20. alter table CMDS.CONTACT_LIST         shrink space cascade;
    21. commit;
    22. alter table CMDS.GRP_CNTR_HLDR        shrink space cascade;
    23. commit;
    24. alter table CMDS.GRP_CUSTOMER         shrink space cascade;
    25. commit;
    26. alter table CMDS.GRP_PARTY_RELATION   shrink space cascade;
    27. commit;
    28. alter table CMDS.GRP_PERSON           shrink space cascade;
    29. commit;
    30. alter table CMDS.PSN_CNTR_HOLDER      shrink space cascade;
    31. commit;
    32. alter table CMDS.INSURED              shrink space cascade;
    33. commit;
    34. alter table CMDS.STD_CONTRACT         shrink space cascade;
    35. commit;
    36. alter table CMDS.BENEFICIARY          shrink space cascade;
    37. commit;
    38. alter table CMDS.PERSON               shrink space cascade;
    39. commit;
    40. alter table CMDS.PARTY_RELATION       shrink space cascade;
    41. commit;
    42. alter table CMDS.CUST_RELATION        shrink space cascade;
    43. commit;
    46. exec DBMS_STATS.GATHER_table_STATS ('CMDS','APPL_BNFR',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    47. exec DBMS_STATS.GATHER_table_STATS ('CMDS','APPL_INSURED',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    48. exec DBMS_STATS.GATHER_table_STATS ('CMDS','APPL_STATE',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    49. exec DBMS_STATS.GATHER_table_STATS ('CMDS','CONTACT_COMB',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    50. exec DBMS_STATS.GATHER_table_STATS ('CMDS','CONTACT_LIST',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    51. exec DBMS_STATS.GATHER_table_STATS ('CMDS','GRP_CNTR_HLDR',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    52. exec DBMS_STATS.GATHER_table_STATS ('CMDS','GRP_CUSTOMER',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    53. exec DBMS_STATS.GATHER_table_STATS ('CMDS','PSN_CNTR_HOLDER',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    54. exec DBMS_STATS.GATHER_table_STATS ('CMDS','STD_CONTRACT',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    55. exec DBMS_STATS.GATHER_table_STATS ('CMDS','GRP_PARTY_RELATION',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    56. exec DBMS_STATS.GATHER_table_STATS ('CMDS','GRP_PERSON',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    57. exec DBMS_STATS.GATHER_table_STATS ('CMDS','INSURED',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    58. exec DBMS_STATS.GATHER_table_STATS ('CMDS','BENEFICIARY',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    59. exec DBMS_STATS.GATHER_table_STATS ('CMDS','PERSON',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    60. exec DBMS_STATS.GATHER_table_STATS ('CMDS','PARTY_RELATION',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    61. exec DBMS_STATS.GATHER_table_STATS ('CMDS','CUST_RELATION',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
    62. exit;
    63. !
    65. echo `date +"%B %d %H:%M:%S"` `hostname` end ... >> $ORACLE_HOME/log/shrink_cmds_20170127.log

    完成之后的表空间情况:
    1. TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
    2. ------------------------------ ------------ ---------- ------------- ------------ -------------
    3. CUST                             2097096.93  268428407     1983211.8        94.57     113885.13
    4. TBS210000                             65534    8388352       58583.5        89.39        6950.5
    5. TBS140000                             65534    8388352      57379.62        87.56       8154.38
    6. TBS320000                            196602   25165056     166830.94        84.86      29771.06
    7. TBS650000                             65534    8388352      52828.94        80.61      12705.06
    8. TBS440000                            196602   25165056     153142.94        77.89      43459.06
    9. TBS420000                             98301   12582528      74099.75        75.38      24201.25
    10. TBS510000                            196602   25165056        147728        75.14         48874
    11. TBS220000                             32767    4194176      24342.69        74.29       8424.31
    12. CMDSIDXTBS                          2194843  280939904    1612798.56        73.48     582044.44
    13. TBS310000                             98301   12582528      71163.25        72.39      27137.75
    14. TBS110000                             49151    6291328      35264.31        71.75      13886.69
    15. TBS350000                             98301   12582528      70178.06        71.39      28122.94
    16. TBS330000                             98301   12582528      68109.81        69.29      30191.19
    17. SYSTEM                                 2048     262144       1413.19           69        634.81
    18. TBS430000                            163835   20970880     112369.44        68.59      51465.56
    19. TBS530000                             65534    8388352      44357.44        67.69      21176.56
    20. TBS620000                             65534    8388352      42577.56        64.97      22956.44
    21. TBS340000                             98301   12582528      63759.94        64.86      34541.06
    22. TBS150000                             65534    8388352      42301.25        64.55      23232.75
    23. TBS520000                             32767    4194176      20951.44        63.94      11815.56
    24. TBS610000                             98301   12582528      62170.37        63.24      36130.63
    25. TBS230000                            163835   20970880     100844.06        61.55      62990.94
    26. TBS410000                            196602   25165056     120568.94        61.33      76033.06
    27. TBS370000                            294903   37747584     178076.19        60.38     116826.81
    28. TBS130000                            196602   25165056     116789.06         59.4      79812.94
    29. TBS660000                             98301   12582528      57226.12        58.22      41074.88
    30. TBS450000                             98301   12582528         54508        55.45         43793
    31. TBS360000                             98301   12582528      52396.75         53.3      45904.25
    32. TBS120000                             32767    4194176      15767.12        48.12      16999.88
    33. TBS640000                             32767    4194176      15709.81        47.94      17057.19
    34. TBS000002                             98301   12582528      44410.81        45.18      53890.19
    35. TBS210200                             32767    4194176      12610.94        38.49      20156.06
    36. TBS370200                             32767    4194176      12490.87        38.12      20276.13
    37. TBS330200                             32767    4194176      10765.62        32.86      22001.38
    38. TBS440200                             32767    4194176       9236.81        28.19      23530.19
    39. TBS350200                             32767    4194176       8944.87         27.3      23822.13
    40. SYSAUX                             65534.98    8388478      12071.92        18.42      53463.06
    41. TBS460000                             32767    4194176       5505.87         16.8      27261.13
    42. TBS630000                             32767    4194176       3513.56        10.72      29253.44
    43. USERS                                   512      65536         39.75         7.76        472.25
    44. CMDSDATTBS                           163835   20970880          8457         5.16        155378
    45. UNDOTBS1                          553834.95   70890874       16979.2         3.07     536855.75
    46. TBS850000                             32767    4194176        721.19          2.2      32045.81
    47. UNDOTBS2                          163837.95   20971258       3374.89         2.06     160463.06
    48. TBS_OGG                               10240    1310720             1          .01         10239
  • 相关阅读:
    观察者模式 vs 发布订阅模式 simple
    微信小程序使用骨架屏 simple
    图片变形处理，设置属性objectfit: cover完美解决！ simple
    Vue中computed和watch的区别 simple
    深入理解React底层实现原理 simple
    Vue 打包上线后的缓存问题 simple
    软件性能测试分析与调优实践之路Java应用程序的性能分析与调优手稿节选
    spark 下java list 或者scala list 转DataFrame or DataSet 总结
    Clickhouse 分布式表&本地表 &ClickHouse实现时序数据管理和挖掘
    C# Lottie 动画播放开发笔记.
  • 原文地址：https://www.cnblogs.com/wangrongxin/p/6653993.html
Copyright © 2020-2023  润新知