Skip to content

ORA-03297 how-to find how data distributed

ORA-03297 how-to find how data distributed published on Комментариев к записи ORA-03297 how-to find how data distributed нет

original 

column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a10
select
            tablespace_name,
            file_id,
            file_name,
            dfsizeMB,
            hwmMB,
            dffreeMB,
            trunc((dffreeMB/dfsizeMB)*100,1) "% Free",
            trunc(dfsizeMB-hwmMB,2) "Resizeble"
   from
   (
      select
           df.tablespace_name tablespace_name,
           df.file_id file_id,
           df.file_name file_name,
           round (df.bytes/1024/1024) dfsizeMB,
           round((ex.hwm*(ts.block_size))/1024/1024) hwmMB,
           dffreeMB
      from
           dba_data_files df,
           dba_tablespaces ts,
      (
           select file_id, round(sum(bytes/1024/1024)) dffreeMB
           from dba_free_space
           group by file_id
      ) free,
      (
           select file_id, max(block_id+blocks) hwm
           from dba_extents
           group by file_id
      ) ex
      where df.file_id = ex.file_id
      and df.tablespace_name = ts.tablespace_name
      and df.file_id = free.file_id (+)
      order by df.tablespace_name, df.file_id
    ) 
/


TABLESPACE_NAME      |    FILE_ID | File Name                                          |   DFSIZEMB |      HWMMB |   DFFREEMB |     % Free |  Resizeble
-------------------- | ---------- | -------------------------------------------------- | ---------- | ---------- | ---------- | ---------- | ----------
ADMSF_TS             |         14 | +DATA/S00TESIA2/DATAFILE/admsf_ts.292.924278433    |        100 |         27 |         87 |         87 |         73
ESIA                 |          5 | +DATA/s00tesia2/datafile/esia.262.911677179        |      32767 |      32767 |      25380 |       77.4 |          0
ESIA                 |         12 | +DATA/s00tesia2/datafile/esia.261.911673773        |      32768 |      32768 |      20200 |       61.6 |          0
ESIA                 |         13 | +DATA/s00tesia2/datafile/esia.265.911681735        |       3100 |       3100 | <NULL>     | <NULL>     |          0
ESIA_PDS             |          8 | +DATA/s00tesia2/datafile/esia_pds.271.911682045    |        100 |          6 |         99 |         99 |         94
ESIA_TECH            |          6 | +DATA/s00tesia2/datafile/esia_tech.269.911682035   |        100 |          9 |         93 |         93 |         91
SIA_DB               |         10 | +DATA/s00tesia2/datafile/sia_db.266.911681879      |       1260 |       1199 |         61 |        4.8 |         61
SYSAUX               |          2 | +DATA/s00tesia2/datafile/sysaux.264.911681479      |       4096 |       1591 |       2519 |       61.4 |       2505
SYSTEM               |          1 | +DATA/s00tesia2/datafile/system.267.911681965      |        890 |        886 |         57 |        6.4 |          4
UNDOTBS1             |          3 | +DATA/s00tesia2/datafile/undotbs1.268.911681999    |        910 |        620 |        884 |       97.1 |        290
USERS                |          4 | +DATA/s00tesia2/datafile/users.263.911680523       |       9069 |       8587 |       9030 |       99.5 |        482


column segment heading 'Segment Name' format a40
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a20
select
                file_name,
                segment_type,
                owner||'.'||segment_name segment,
                block_id,
                blockIdMB
        from
        (
         select
              ex.owner owner,
              ex.segment_name segment_name,
              ex.segment_type segment_type,
              ex.block_id block_id,
              df.file_name file_name,
              trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
      from
              dba_extents ex, dba_data_files df, dba_tablespaces ts
              where df.file_id = &file_id
              and df.file_id = ex.file_id
              and df.tablespace_name = ts.tablespace_name
              order by ex.block_id desc
      )
      where rownum <= 100
/


File Name                                          | Segment Type         | Segment Name                             |   BLOCK_ID |  BLOCKIDMB
-------------------------------------------------- | -------------------- | ---------------------------------------- | ---------- | ----------
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194168 |   32766.93
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194160 |   32766.87
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194152 |   32766.81
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194144 |   32766.75
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194136 |   32766.68
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194128 |   32766.62
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194120 |   32766.56
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194112 |    32766.5
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194104 |   32766.43
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194096 |   32766.37
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194088 |   32766.31
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194080 |   32766.25
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194072 |   32766.18
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194064 |   32766.12
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194056 |   32766.06
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194048 |      32766

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar