ORACLE查看数据文件包含哪些对象

      , E.SEGMENT_NAME          <span style="color: #0000ff">AS</span> SEGMENT_NAME

      , F.FILE_NAME             <span style="color: #0000ff">AS</span> FILE_NAME

      , <span style="color: #0000ff">SUM</span>(E.BYTES)/1024/1024  <span style="color: #0000ff">AS</span> SEGMENT_SIZE

<span style="color: #0000ff">FROM</span> DBA_EXTENTS E

<span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> DBA_DATA_FILES F <span style="color: #0000ff">ON</span> E.FILE_ID= F.FILE_ID

<span style="color: #0000ff">WHERE</span> F.FILE_ID =109

<span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span>  E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME

<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> 4 <span style="color: #0000ff">DESC</span>;

<span style="color: #0000ff">SELECT</span>  E.SEGMENT_TYPE          <span style="color: #0000ff">AS</span> SEGMENT_TYPE

      , E.SEGMENT_NAME          <span style="color: #0000ff">AS</span> SEGMENT_NAME

      , F.FILE_NAME             <span style="color: #0000ff">AS</span> FILE_NAME

      , <span style="color: #0000ff">SUM</span>(E.BYTES)/1024/1024  <span style="color: #0000ff">AS</span> SEGMENT_SIZE

<span style="color: #0000ff">FROM</span> DBA_EXTENTS E

<span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> DBA_DATA_FILES F <span style="color: #0000ff">ON</span> E.FILE_ID= F.FILE_ID

<span style="color: #0000ff">WHERE</span> F.FILE_NAME =&FILE_NAME

<span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span>  E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME

<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> 4 <span style="color: #0000ff">DESC</span>;

错误的写法，如下所示，DBA_SEGMENTS的HEADER_FILE表示ID of the file containing the segment header，主要是因为仅仅包含段对象的段头所在的文件。

<span style="color: #0000ff">SELECT</span> ds.OWNER                  <span style="color: #0000ff">AS</span> OWNER

      ,df.FILE_NAME              <span style="color: #0000ff">AS</span> FILE_NAME

      ,ds.SEGMENT_NAME           <span style="color: #0000ff">AS</span> SEGMENT_NAME

      ,ds.SEGMENT_TYPE           <span style="color: #0000ff">AS</span> SEGMENT_TYPE

      ,<span style="color: #0000ff">SUM</span>(ds.BYTES)/1024/1024   <span style="color: #0000ff">AS</span> SEGMENT_SIZE

<span style="color: #0000ff">FROM</span> DBA_SEGMENTS ds 

<span style="color: #0000ff">LEFT</span> <span style="color: #0000ff">JOIN</span> DBA_DATA_FILES df <span style="color: #0000ff">ON</span> ds.HEADER_FILE = df.FILE_ID

<span style="color: #0000ff">WHERE</span> ds.TABLESPACE_NAME=&TABLESPACE_NAME

  <span style="color: #0000ff">AND</span> df.FILE_ID=&FILE_ID

<span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> OWNER,FILE_NAME,SEGMENT_NAME,SEGMENT_TYPE

<span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> 4;

ORACLE查看数据文件包含哪些对象

标签：