Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- dba:oracle_index_organised_table [2014/07/31 22:09] – gpipperr
+++ dba:oracle_index_organised_table [2014/08/01 10:27] – [Script für die Analyse einer IOT] gpipperr
@@ Zeile 139: / Zeile 139: @@
 ==== Script für die Analyse einer IOT ====
-<code sql>
+<code sql tab_iot.sql>
-ET pagesize 300
+SET pagesize 300
-SET linesize 150
+SET linesize 120
 SET VERIFY OFF
@@ Zeile 158: / Zeile 158: @@
  where upper(table_name) like upper('&ENTER_TABLE.')
    and upper(owner) = upper('&ENTER_OWNER.')
 /
 ttitle left  "IOT Name and Table space for Overflow Segments" skip 2
+column owner            format a10 heading "Owner"
-column owner     		format a10 heading "Owner"
 column overflow_table   format a20 heading "OverFlow Table|Name"
-column IOT_TYPE   		format a14  heading "IOT|Type"
+column IOT_TYPE         format a14  heading "IOT|Type"
-column IOT_NAME   		format a20 heading "IOT|Name"
+column IOT_NAME         format a20 heading "IOT|Name"
 column tablespace_name  format a12 heading "IDX|TBS Name"
 column overFlowTabspace format a12 heading "OverFlow|TBS Name"
-column index_name 	    format a20 heading "IOT Index|Name"
+column index_name       format a20 heading "IOT Index|Name"
 column iot_name_table   format a20 heading "IOT Tab|Name"
-select   i.owner
+select i.owner
-	   , i.table_name as iot_name_table
+     , i.table_name as iot_name_table
-       , nvl(t.table_name,'-') as overflow_table
+     , nvl(t.table_name,'-') as overflow_table
-	   , nvl(i.index_name,'-') as index_name
+     , nvl(i.index_name,'-') as index_name
-	   , nvl(t.IOT_TYPE,'-') as IOT_TYPE
+     , nvl(t.IOT_TYPE,'-') as IOT_TYPE
-	   , i.tablespace_name
+     , i.tablespace_name
-	   , t.tablespace_name as overFlowTabspace
+     , t.tablespace_name as overFlowTabspace
  from dba_tables  t
     , dba_indexes i
 where t.IOT_NAME (+) = i.table_name
   and upper(i.table_name) like upper('&&ENTER_TABLE.')
@@ Zeile 186: / Zeile 185: @@
 /
-ttitle left  "IOT SIZE " skip 2
+ttitle left  "IOT Table SIZE " skip 2
-select  nvl(i.index_name,'-') as index_name
+set heading off
-	,  (select sum(bytes) from dba_segments where segment_name=i.index_name) as IndexSize
-	,  nvl(t.table_name,'-') as overflow_table
+column index_nameMB      fold_after
-	,  nvl((select sum(bytes) from dba_segments where segment_name=t.table_name),0)   as OverFlowSize
+column IndexSizeMB       fold_after
-	, (select sum(bytes) from dba_segments where segment_name=i.index_name)+nvl((select sum(bytes) from dba_segments where segment_name=t.table_name),0)  as total
+column overflow_tableMB  fold_after
- from dba_tables  t
+column OverFlowSizeMB    fold_after
-    , dba_indexes i
+column totalMB           fold_after
-where t.IOT_NAME (+) = i.table_name
-  and upper(i.table_name) like upper('&&ENTER_TABLE.')
+select rpad('Index Name',30,' ')   ||'::'||lpad(index_name,22,' ')                                                             as index_nameMB
-  and upper(i.owner)       =  upper('&&ENTER_OWNER.')
+    ,  rpad('Index Size',30,' ')   ||'::'||to_char(round((IndexSize/1024/1024),3),'999G999G999G999D99')                ||' MB' as IndexSizeMB
+	 ,  rpad('Overflow Name',30,' ')||'::'||lpad(overflow_table,22,' ')                                                         as overflow_tableMB
+	 ,  rpad('Overflow Size',30,' ')||'::'||to_char(round((OverFlowSize/1024/1024),3),'999G999G999G999D99')             ||' MB' as OverFlowSizeMB
+	 ,  rpad('Total',30,' ')        ||'::'||to_char(round(((IndexSize+OverFlowSize)/1024/1024),3),'999G999G999G999D99') ||' MB' as totalMB
+  from (
+	select nvl(i.index_name,'-') as index_name
+		, (select sum(bytes) from dba_segments where segment_name=i.index_name) as IndexSize
+		, nvl(t.table_name,'-') as overflow_table
+		, nvl((select sum(bytes) from dba_segments where segment_name=t.table_name),0)   as OverFlowSize
+	from dba_tables  t
+		 , dba_indexes i
+	where t.IOT_NAME (+) = i.table_name
+	  and upper(i.table_name) like upper('&&ENTER_TABLE.')
+	  and upper(i.owner)       =  upper('&&ENTER_OWNER.')
+)
 /
+set heading on
 ttitle left  "Check if the columns are in the overflow segment of the IOT Table" skip 2
 select c.table_name
      , c.column_name
-	 , case
+     , case
-	    when i.include_column != 0 then  ( case when c.column_id < i.include_column then 'TOP' else 'OVERFLOW' end )
+         when i.include_column != 0 then  ( case when c.column_id < i.include_column then 'TOP' else 'OVERFLOW' end )
-	  else 'TOP'
+       else 'TOP'
-	  end as segment
+       end as segment
   from dba_tab_columns c
      , dba_indexes     i
- where i.table_name (+) = c.table_name
+where i.table_name (+) = c.table_name
    and upper(c.table_name) like upper('&enter_table.')
    and upper(c.owner) = upper('&enter_owner.')
@@ Zeile 217: / Zeile 231: @@
         , column_id
 /
-ttitle off
+ttitle of
 </code>