Hice los siguientes pasos sobre una tabla:
1- Lanzo estas consultas antes de reorganizar la tabla.
"Begin
dbms_space.space_usage('SAPSR3','DOKCLU', 'TABLE', :unfblock, :unfbyte, :fs1block, :fs1byte, :fs2block, :fs2byte, :fs3block, :fs3byte, :fs4block, :fs4byte, :fullblock, :fullbyte);
end;
/"
RESULTADO:
************************************************** **************
UNFBLOCK UNFBYTE FS4BLOCK FS4BYTE FS3BLOCK FS3BYTE FS2BLOCK
----------- ----------- ----------- ----------- ----------- ----------- -----------
0 0 12202 99958784 15 122880 7
FS2BYTE FS1BLOCK FS1BYTE FULLBLOCK
----------- ----------- ----------- -----------
57344 0 0 64937
************************************************** **************
################################################## ##
QUERY 2
################################################## #
BEGIN
DBMS_SPACE.UNUSED_SPACE('SAPSR3','APQD', 'TABLE', :totalblocks, :totalbytes, :unblock, :unbytes, :last_ext_file_id, :last_ext_block_id, :partition);
END;
/
RESULTADO
************************************************** ***************
TOTALBLOCKS TOTALBYTES UNBLOCK UNBYTES
----------- ---------- ---------- ----------
77824 637534208 256 2097152
LAST_EXT_FILE_ID LAST_EXT_BLOCK_ID PARTITION
---------------- ----------------- -----------------
14 181897 768
************************************************** ***************
################################################## ##
REORGANIZO LA TABLA Y LANZO LAS CONSULTAS DE NUEVO
################################################## ##
RESULTADO CONSULTA 1
UNFBLOCK UNFBYTE FS4BLOCK FS4BYTE FS3BLOCK FS3BYTE FS2BLOCK
----------- ----------- ----------- ----------- ----------- ----------- -----------
0 0 0 0 0 0 0
FS2BYTE FS1BLOCK FS1BYTE FULLBLOCK FULLBYTE
----------- ----------- ----------- ----------- -----------
0 0 0 64801 530849792
************************************************** ***************
RESULTADO CONSULTA 2
************************************************** ***************
TOTALBLOCKS TOTALBYTES UNBLOCK UNBYTES
----------- ---------- ---------- ----------
65160 533790720 0 0
LAST_EXT_FILE_ID LAST_EXT_BLOCK_ID PARTITION
---------------- ----------------- -----------------
20 102281 648
************************************************** ***************
Os muestro que significa cada campo:
campos consulta 1:
UNFBLOCK Total number of blocks that are unformatted
UNFBYTE Total number of bytes that are unformatted
FS4BLOCK Number of blocks that has at least 0 to 25% free space
FS4BYTE Number of bytes that has at least 0 to 25% free space
FS3BLOCK Number of blocks that has at least 25 to 50% free space
FS3BYTE Number of bytes that has at least 25 to 50% free space
FS2BLOCK Number of blocks that has at least 50 to 75% free space
FS2BYTE Number of bytes that has at least 50 to 75% free space
FS1BLOCK Number of blocks that has at least 75 to 100% free space
FS1BYTE Number of bytes that has at least 75 to 100% free space
FULLBLOCK Total number of blocks that are full in the segment
FULLBYTE Total number of bytes that are full in the segment
Campos consulta 2:
total_blocks Returns total number of blocks in the segment.
total_bytes Returns total number of blocks in the segment, in bytes.
unused_blocks Returns number of blocks which are not used.
unused_bytes Returns, in bytes, number of blocks which are not used.
last_used_extent_ file_id Returns the file ID of the last extent which contains data.
last_used_extent_ block_id Returns the block ID of the last extent which contains data.
last_used_block Returns the last block within this extent which contains data.
partition_name Partition name of the segment to be analyzed.
////////////////////////////////////////////////////////////////////////////////////////////////////////
Esto ha sido una prueba para comprobar que realmente las consultas me muestran que la tabla ha reducido, pero lo que me interesa es saber cuánto va a ocupar la tabla con sólo ver las primeras consultas.
No estoy seguro de que puedo sacar esta información unicamente con las primeras consultas.
Si estoy equivocado (posiblemente
) agradecería que me orientarais como estimar el tamaño de un objeto después de organizarlo.
Gracias!