Cómo calculo el tamaño de las tablas en Oracle

Siendo utilizado (y potencialmente estropeado por) MSSQL, me pregunto cómo puedo obtener el tamaño de tablas en Oracle 10g. Lo busqué en Google, así que ahora soy consciente de que quizás no tenga una opción tan fácil como sp_spaceused. Aún así, las posibles respuestas que obtuve son la mayoría de las veces obsoletas o no funcionan. Probablemente porque no soy DBA en el esquema con el que estoy trabajando.

¿Alguien tendría soluciones o recomendaciones?

Es posible que le interese esta consulta. Le dice cuánto espacio se asigna para cada tabla teniendo en cuenta los índices y cualquier LOB en la tabla. A menudo le interesa saber “cuántos espacios ocupa la tabla de orden de compra, incluidos los índices” en lugar de solo la tabla en sí. Siempre puedes profundizar en los detalles. Tenga en cuenta que esto requiere acceso a las vistas DBA_ *.

COLUMN TABLE_NAME FORMAT A32 COLUMN OBJECT_NAME FORMAT A32 COLUMN OWNER FORMAT A10 SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg, ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) desc ; 
 -- Tables + Size MB select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB from all_tables where owner not like 'SYS%' -- Exclude system tables. and num_rows > 0 -- Ignore empty Tables. order by MB desc -- Biggest first. ; --Tables + Rows select owner, table_name, num_rows from all_tables where owner not like 'SYS%' -- Exclude system tables. and num_rows > 0 -- Ignore empty Tables. order by num_rows desc -- Biggest first. ; 

Nota: Estas son estimaciones, hechas más precisas con estadísticas de recostackción:

 exec dbms_utility.analyze_schema(user,'COMPUTE'); 

En primer lugar, generalmente advierto que la recostackción de estadísticas de tablas para realizar análisis espaciales es algo potencialmente peligroso. Reunir estadísticas puede cambiar los planes de consulta, especialmente si el DBA ha configurado un trabajo de recostackción de estadísticas que utiliza parámetros no predeterminados que su llamada no está utilizando y hará que Oracle vuelva a analizar las consultas que utilizan la tabla en cuestión, que puede ser un rendimiento golpear. Si el DBA ha dejado intencionalmente algunas tablas sin estadísticas (común si OPTIMIZER_MODE es CHOOSE), recostackr estadísticas puede hacer que Oracle deje de usar el optimizador basado en reglas y comience a usar el optimizador basado en costos para un conjunto de consultas que pueden ser importantes. Dolor de cabeza de rendimiento si se realiza inesperadamente durante la producción. Si sus estadísticas son precisas, puede consultar USER_TABLES (o ALL_TABLES o DBA_TABLES ) directamente sin llamar a GATHER_TABLE_STATS . Si sus estadísticas no son precisas, probablemente haya una razón para eso y no quiera perturbar el status quo.

En segundo lugar, el equivalente más cercano al procedimiento sp_spaceused SQL Server probablemente DBMS_SPACE paquete DBMS_SPACE de Oracle. Tom Kyte tiene un buen procedimiento show_space que proporciona una interfaz simple para este paquete e imprime información similar a la que imprime sp_spaceused .

Primero, reúna las estadísticas del optimizador en la tabla (si aún no lo hizo):

 begin dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE'); end; / 

ADVERTENCIA: como dice Justin en su respuesta, la recostackción de las estadísticas del optimizador afecta la optimización de la consulta y no debe realizarse sin la debida atención y consideración .

Luego, busca la cantidad de bloques ocupados por la tabla a partir de las estadísticas generadas:

 select blocks, empty_blocks, num_freelist_blocks from all_tables where owner = 'MYSCHEMA' and table_name = 'MYTABLE'; 
  • El número total de bloques asignados a la tabla es blocks + empty_blocks + num_freelist_blocks.

  • bloques es la cantidad de bloques que realmente contienen datos.

Multiplique el número de bloques por el tamaño de bloque en uso (generalmente 8 KB) para obtener el espacio consumido, por ejemplo, 17 bloques x 8 KB = 136 KB.

Para hacer esto para todas las tablas en un esquema a la vez:

 begin dbms_stats.gather_schema_stats ('MYSCHEMA'); end; / select table_name, blocks, empty_blocks, num_freelist_blocks from user_tables; 

Nota: Cambios hechos a lo anterior después de leer este hilo AskTom

Para tablas e índices subdivididos, podemos usar la siguiente consulta

 SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) DESC ; 

Modifiqué la consulta de WW para proporcionar información más detallada:

 SELECT * FROM ( SELECT owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, tablespace_name, extents, initial_extent, ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg FROM ( -- Tables SELECT owner, segment_name AS object_name, 'TABLE' AS object_type, segment_name AS table_name, bytes, tablespace_name, extents, initial_extent FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL -- Indexes SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') -- LOB Segments UNION ALL SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' -- LOB Indexes UNION ALL SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX' ) WHERE owner = UPPER('&owner') ) WHERE total_table_meg > 10 ORDER BY total_table_meg DESC, meg DESC / 

IIRC las tablas que necesita son DBA_TABLES, DBA_EXTENTS o DBA_SEGMENTS y ​​DBA_DATA_FILES. También hay USER_ y ALL_ versiones de estos para las tablas que puede ver si no tiene permisos de administración en la máquina.

 select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc; 

Aquí hay una variante de la respuesta WW, incluye particiones y subparticiones, como han sugerido otras más arriba, más una columna para mostrar el TIPO: Tabla / Índice / LOB, etc.

 SELECT owner, "Type", table_name "Name", TRUNC(sum(bytes)/1024/1024) Meg FROM ( SELECT segment_name table_name, owner, bytes, 'Table' as "Type" FROM dba_segments WHERE segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type" FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes, 'LOB' as "Type" FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type" FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner, "Type" HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) desc; 

Depende de lo que quiere decir con “tamaño de tabla”. Una tabla no se relaciona con un archivo específico en el sistema de archivos. Una tabla residirá en un espacio de tablas (posiblemente múltiples espacios de tablas si está particionado, y posiblemente múltiples espacios de tablas si también desea tener en cuenta los índices en la tabla). Un tablespace a menudo tendrá varias tablas y se puede dividir en varios archivos.

Si está estimando cuánto espacio necesitará para el crecimiento futuro de la tabla, entonces avg_row_len multiplicado por el número de filas en la tabla (o el número de filas que espera en la tabla) será una buena guía. Pero Oracle dejará algo de espacio libre en cada bloque, en parte para permitir que las filas “crezcan” si se actualizan, en parte porque no es posible ajustar otra fila completa en ese bloque (por ejemplo, un bloque de 8K solo cabría en 2 filas de 3K, aunque sería un ejemplo extremo ya que 3K es mucho más grande que la mayoría de los tamaños de fila). Entonces BLOCKS (en USER_TABLES) podría ser una mejor guía.

Pero si tuviera 200,000 filas en una tabla, eliminara la mitad de ellas, entonces la tabla aún tendría el mismo número de bloques. No los libera para otras tablas para usar. Además, los bloques no se agregan a una tabla individualmente, sino en grupos llamados ‘extensión’. Entonces generalmente habrá EMPTY_BLOCKS (también en USER_TABLES) en una tabla.

Corrección para tablas particionadas:

 SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name and s.owner = l.owner AND s.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ order by sum(bytes) desc ; 

Selección simple que devuelve los tamaños sin procesar de las tablas, según el tamaño del bloque, también incluye tamaño con índice

seleccione nombre_tabla, (nvl ((seleccione sum (bloques) de dba_indexes a, dba_segments b donde a.nombre_de_ndice = b.segment_name y a.table_name = dba_tables.table_name), 0) + bloques) * 8192/1024 TotalSize, blocks * 8 tableSize de dba_tables ordenar por 3

Encontré que esto es un poco más preciso:

 SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type in ('TABLE','TABLE PARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type in ('INDEX','INDEX PARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') ---WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) desc 
 select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024)) as tablesize_in_GB From dba_segments /* if looking at tables not owned by you else use user_segments */ where segment_name = 'TABLE_WHOSE_SIZE_I_WANT_TO_KNOW' and OWNER = 'WHO OWNS THAT TABLE' /* if user_segments is used delete this line */ group by segment_name ; 

Modifiqué la consulta para obtener el tamaño de esquema por espacio de tabla.

 SELECT owner, tablespace_name, TRUNC (SUM (bytes) / 1024 / 1024) Meg, ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent FROM (SELECT tablespace_name, owner, bytes FROM dba_segments WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') UNION ALL SELECT i.tablespace_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') UNION ALL SELECT l.tablespace_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') UNION ALL SELECT l.tablespace_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner IN UPPER ('&owner') GROUP BY owner, tablespace_name --HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */ ORDER BY tablespace_name -- desc ; 

hay una opción más que permite obtener el tamaño de “seleccionar” con combinaciones, y el tamaño de la tabla como opción también

 -- 1 EXPLAIN PLAN FOR SELECT Scheme.Table_name.table_column1 AS "column1", Scheme.Table_name.table_column2 AS "column2", Scheme.Table_name.table_column3 AS "column3", FROM Scheme.Table_name WHERE ; SELECT * FROM TABLE (DBMS_XPLAN.display); 

Tengo la misma variante que las últimas que calcula segmentos de datos de tabla, índices de tabla y blob-fields:

 CREATE OR REPLACE FUNCTION SYS.RAZMER_TABLICY_RAW(pNazvanie in varchar, pOwner in varchar2) return number is val number(16); sz number(16); begin sz := 0; --Calculate size of table data segments select sum(t.bytes) into val from sys.dba_segments t where t.segment_name = upper(pNazvanie) and t.owner = upper(pOwner); sz := sz + nvl(val,0); --Calculate size of table indexes segments select sum(s.bytes) into val from all_indexes t inner join dba_segments s on t.index_name = s.segment_name where t.table_name = upper(pNazvanie) and t.owner = upper(pOwner); sz := sz + nvl(val,0); --Calculate size of table blob segments select sum(s.bytes) into val from all_lobs t inner join dba_segments s on t.segment_name = s.segment_name where t.table_name = upper(pNazvanie) and t.owner = upper(pOwner); sz := sz + nvl(val,0); return sz; end razmer_tablicy_raw; 

Fuente