Oracle Live

11/04/2016

QUERY – Resize datafile

Filed under: Queries — mogukiller @ 11:46 pm
Tags:

Query que indica el tamaño al que podemos hacer el resize de los datafiles.

/*
                                                                                      SMALLEST
                                                                                          SIZE    CURRENT      POSS.
TABLESPACE_NAME     FILE_NAME                                                 POSS.       SIZE    SAVINGS
------------------- ---------------------------------------------------- ---------- ---------- ----------
MG001GDE            +DG_DATOS/PMG000/DATAFILE/MG001gde.1223.905974813       193,207    200,000      6,793
MG001GIE            +DG_DATOS/PMG000/DATAFILE/MG001gie.1222.905974949       128,905    200,000     71,095
MG001MDE            +DG_DATOS/PMG000/DATAFILE/MG001mde.1214.905975079        26,625     30,000      3,375
MG001MIE            +DG_DATOS/PMG000/DATAFILE/MG001mie.1215.905975099        61,496     75,000     13,504
MG001PDE            +DG_DATOS/PMG000/DATAFILE/MG001pde.1216.905975147            86      3,000      2,914
MG001PIE            +DG_DATOS/PMG000/DATAFILE/MG001pie.1217.905975149         1,704      5,000      3,296
USUARIOS            +DG_DATOS/PMG000/DATAFILE/usuarios.1233.906643679             7         50         43
                                                                                                          ----------
sum                                                                                                          101,309
                                                                       
*/

SET VERIFY OFF
SET LINES 150 PAGES 1000
COLUMN FILE_NAME FORMAT A80 WORD_WRAPPED
COLUMN SMALLEST FORMAT 99999,990 HEADING "SMALLEST|SIZE|POSS."
COLUMN CURRSIZE FORMAT 99999,990 HEADING "CURRENT|SIZE"
COLUMN SAVINGS  FORMAT 99999,990 HEADING "POSS.|SAVINGS"
BREAK ON REPORT
COMPUTE SUM OF SAVINGS ON REPORT

COLUMN VALMG NEW_VAL BLKSIZE
SELECT VALMG FROM V$PARAMETER WHERE NAME = 'db_block_size'
/

SELECT TABLESPACE_NAME, FILE_NAME,
       CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
       CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
       CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES A,
     ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM
         FROM DBA_EXTENTS
        GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID(+)
AND A.TABLESPACE_NAME NOT LIKE '%SYS%'
AND A.TABLESPACE_NAME NOT LIKE '%UNDO%'
ORDER BY 1,4 DESC
/

COLUMN CMD FORMAT A75 WORD_WRAPPED

SELECT 'ALTER DATABASE DATAFILE ' || Q'{'}' || FILE_NAME || Q'{'}' || ' RESIZE ' || CEIL((NVL(HWM,1)*&&BLKSIZE)/1024/1024)  || 'M;' AS COMMAND
FROM DBA_DATA_FILES A,
     ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM
         FROM DBA_EXTENTS
        GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID(+)
  AND CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL((NVL(HWM,1)*&&BLKSIZE)/1024/1024) > 0
  AND A.TABLESPACE_NAME NOT LIKE '%SYS%'
  AND A.TABLESPACE_NAME NOT LIKE '%UNDO%'
/
/*
COMMAND
-----------------------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001gie.1222.905974949' RESIZE 128905M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/prMGbas.1232.906553033' RESIZE 9M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/usuarios.1233.906643679' RESIZE 7M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001gde.1223.905974813' RESIZE 193207M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001mie.1215.905975099' RESIZE 61496M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001mde.1214.905975079' RESIZE 26625M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001pde.1216.905975147' RESIZE 86M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MG001pie.1217.905975149' RESIZE 1704M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/MGaudmde.1219.905975153' RESIZE 1M;
ALTER DATABASE DATAFILE '+DG_DATOS/PMG000/DATAFILE/patrol.1224.905974813' RESIZE 1M;
*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: