-----------------------------------------------------------------------------------
--------------------- Scrip para Rebuild de índices -------------------------------
-----------------------------------------------------------------------------------
-- Cria script para o rebuild online dos indices "FUNCTION-BASED NORMAL, NORMAL" --
select distinct 
       'ALTER INDEX '||t1.OWNER||'.'||t1.INDEX_NAME||' REBUILD ONLINE; ' COMAND0_REBUILD_DDL,
       --'ALTER INDEX '||t1.OWNER||'.'||t1.INDEX_NAME||' REBUILD ONLINE TABLESPACE CORP_INDICES NOLOGGING ' COMAND0_REBUILD_DDL,
       t1.OWNER, t1.INDEX_NAME
from   dba_indexes t1,
       dba_tables t2
where  t1.table_name = t2.table_name
--and    t1.owner in ('RHORA8UNESCO', 'DSLICIT', 'SAO', 'SITE', 'UNESCO', 'CORPORATIVO', 'CEP', 'INTRANET', 'UNESCONET', 'SEGURANCA', 'INTEGRATOR', 'TARIFADOR')
--and    t1.owner = 'CORP_INDICES'
and    t1.index_type in ('FUNCTION-BASED NORMAL','NORMAL')
--and    t1.owner = 'CORPORATIVO'
and    t1.index_name <> 'AX_1214_410'
and    t2.table_name in ('UN_SOL_PGTO','UN_LOG_CERTIFICACAO','UN_SOL_PGTO_DOC_ANEXOS')
--and    trunc(t1.last_analyzed) < trunc(sysdate)
and    t2.num_rows > 0

-- cria script para o rebuild dos indices "BITMAP", DOMAIN --
select distinct
       'ALTER INDEX '||t1.OWNER||'.'||t1.INDEX_NAME||' REBUILD; ' COMAND0_REBUILD_DDL,
       t1.OWNER, t1.INDEX_NAME
from   dba_indexes t1,
       dba_tables t2
where  t1.table_name = t2.table_name
and    t1.owner in ('RHORA8UNESCO', 'DSLICIT', 'SAO', 'SITE', 'UNESCO', 'CORPORATIVO', 'CEP','INTRANET', 'UNESCONET', 'SEGURANCA', 'INTEGRATOR', 'TARIFADOR')
and    t1.index_type in ('BITMAP','DOMAIN')
and    t2.num_rows > 0

-------------------------------------------------------------------------------------------
--------------------- Scrip para Analyze de índices e tabelas -----------------------------
-------------------------------------------------------------------------------------------
-- Cria script para o analyze dos indices "FUNCTION-BASED NORMAL, NORMAL, BITMAP,DOMAIN" --
select distinct 'ANALYZE INDEX '||t1.OWNER||'.'||t1.INDEX_NAME||' ESTIMATE STATISTICS SAMPLE 45 PERCENT; ' COMAND0_ANALYZE_DDL,
       t1.OWNER, t1.INDEX_NAME, t1.last_analyzed
from   dba_indexes t1, dba_tables t2
where  t1.table_name = t2.table_name
and    t1.owner in ('RHORA8UNESCO', 'DSLICIT', 'SAO', 'SITE', 'UNESCO', 'CORPORATIVO', 'CEP','INTRANET', 'UNESCONET', 'SEGURANCA', 'INTEGRATOR', 'TARIFADOR')
--where   t1.owner = 'CORPORATIVO'
--and    trunc(t1.last_analyzed) = trunc(sysdate) 
--and    t1.index_type in ('FUNCTION-BASED NORMAL','NORMAL','BITMAP','DOMAIN')
--and    trunc(t1.last_analyzed) < trunc(sysdate)
and    t1.table_name in ('UN_SOL_PGTO','UN_SOL_PGTO_DOC_ANEXOS','UN_LOG_CERTIFICACAO')
and    t2.num_rows > 0

-- Cria script para o analyze das tabelas --
select 'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 45 PERCENT; ' COMAND0_ANALYZE_DDL,
       OWNER, TABLE_NAME, LAST_ANALYZED
from   dba_tables
where  owner in ('RHORA8UNESCO', 'DSLICIT', 'SAO', 'SITE', 'UNESCO', 'CORPORATIVO', 'CEP', 'INTRANET', 'UNESCONET', 'SEGURANCA', 'INTEGRATOR', 'TARIFADOR')
and    table_name in ('UN_SOL_PGTO','UN_SOL_PGTO_DOC_ANEXOS','UN_LOG_CERTIFICACAO')
--and    trunc(last_analyzed) < trunc(sysdate) 
--and    trunc(last_analyzed) is not null
--and    num_rows > 0

hostgator