miércoles, 29 de mayo de 2013

Estadísticas BD Oracle


Estadísticas BD Oracle 

/*
--------------------------------------------------------------------------------------
SELECT * FROM all_tables

SELECT * from all_tables   where owner = 'ESQUEMA1'

SELECT * from all_INDEXES

SELECT DISTINCT  OWNER from all_tables ORDER BY OWNER;
--------------------------------------------------------------------------------------------
  SELECT table_name, table_name Object_name, num_rows, last_analyzed
  from user_tables
 -- where table_name = 'TABLA1'
  union
  select table_name,index_name Object_name, num_rows, last_analyzed
  from user_indexes
 -- where table_name = 'TABLA1'
-------------------------------------------------------------------------------------------

  SELECT owner, table_name, table_name Object_name, num_rows, last_analyzed
  from all_tables
 -- WHERE owner = 'ESQUEMA1'
  where table_name = 'TABLA1'
  union
  select owner, table_name,index_name Object_name, num_rows, last_analyzed
  from all_indexes
-- WHERE owner = 'ESQUEMA1'
 where table_name = 'TABLA1'
------------------------------------------------------------------------------------------------------ EXEC DBMS_STATS.gather_table_stats('ESQUEMA1', 'TABLA1', estimate_percent => 5);

         select 'ALTER TABLE NOMBRE_ESQUEMA.' || table_name || ' MOVE TABLESPACE NOMBRE_TABLESPACE;' from all_tables where owner = 'NOMBRE_ESQUEMA'

         select 'EXEC DBMS_STATS.gather_table_stats('''||OWNER ||''', '''||TABLE_NAME||''', estimate_percent => 30);' from all_tables where owner = 'ESQUEMA1'

EJEMPLO DEL CODIGO DE SALIDA:
           EXEC DBMS_STATS.gather_table_stats('ESQUEMA1', 'TABLA1', estimate_percent => 30);
           EXEC DBMS_STATS.gather_table_stats('ESQUEMA1', 'TABLA2', estimate_percent => 30);

-----------------------------------------------------------------------------
UN CASO ENGORROSO:

EXEC DBMS_STATS.gather_table_stats('ESQUEMA1', '"ESQUEMA2.TABLA_UNO-3"', estimate_percent => 30);

EL MENOS PARECE HACER RUIDO.
PERO IGUAL PARECE MEZCLADO CON OTRO ESQUEMA, Y PERTENECER A UN ESQUEMA INICIAL.
SIN EMBARGO, ASÍ SE RESOLVIÓ, COMILLAS DOBLES. 

*/