miércoles, 18 de agosto de 2010

Script - Infotablas_particionadas

Monitorización de espacio de las tablas particionadas de la base de datos.

BREAK ON TABLA
SELECT NAME "TABLA",
    COLUMN_NAME "COLUMNA DE PARTICIONAMIENTO",
    COLUMN_POSITION "POSICION"
FROM DBA_PART_KEY_COLUMNS
WHERE OWNER IN ('USUARIO') AND
    OBJECT_TYPE LIKE 'TABLE%'
ORDER BY 1,3;

SET LONG 10
BREAK ON TABLA SKIP 1
SELECT
    SUBSTR(S.SEGMENT_NAME,1,25) "TABLA",
    SUBSTR(S.PARTITION_NAME,1,13) "PARTICION",
    P.HIGH_VALUE "MAXIMO",
    SUBSTR(S.TABLESPACE_NAME,1,12) "TABLESPACE",
    S.INITIAL_EXTENT/(1024) "INIT(Kb)",
    S.NEXT_EXTENT/(1024) "NEXT(Kb)",
    S.EXTENTS "NUM EXT",
    S.MAX_EXTENTS "MAX EXT",
    S.BYTES/(1024) "TAM(Kb)"
FROM DBA_SEGMENTS S,
     DBA_TAB_PARTITIONS P
WHERE
    S.OWNER IN ('USUARIO') AND
    S.SEGMENT_NAME = P.TABLE_NAME AND
    S.PARTITION_NAME = P.PARTITION_NAME AND
    S.SEGMENT_TYPE LIKE 'TABLE%'
ORDER BY S.SEGMENT_NAME,
    P.PARTITION_POSITION;

SET LONG 1000
CLEAR BREAKS

No hay comentarios:

Publicar un comentario