[ORACLE] Script Espacio ocupado y libre de tablespaces (incluyendo los temporary)
En este script utilizo la vista dba_temp_free_space que está disponible
a partir de la versión 11g de Oracle:
SELECT a.tablespace_name, a.asignado, b.libre, c.contents, c.status FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS asignado FROM dba_Data_files GROUP BY tablespace_name ) a, (SELECT tablespace_name, SUM(bytes)/1024/1024 AS libre FROM dba_free_space GROUP BY tablespace_name ) b, (SELECT tablespace_name,contents,status FROM dba_tablespaces ) c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name UNION ALL SELECT d.TABLESPACE_NAME, d.ALLOCATED_SPACE/1024/1024, d.FREE_SPACE /1024/1024, e.contents, e.status FROM dba_temp_free_space d, dba_tablespaces e WHERE d.tablespace_name=e.tablespace_name AND contents='TEMPORARY' ;
Para la versión 10g podemos crear una vista tal y como está definida
en la versión 11g:
a partir de la versión 11g de Oracle:
SELECT a.tablespace_name, a.asignado, b.libre, c.contents, c.status FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS asignado FROM dba_Data_files GROUP BY tablespace_name ) a, (SELECT tablespace_name, SUM(bytes)/1024/1024 AS libre FROM dba_free_space GROUP BY tablespace_name ) b, (SELECT tablespace_name,contents,status FROM dba_tablespaces ) c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name UNION ALL SELECT d.TABLESPACE_NAME, d.ALLOCATED_SPACE/1024/1024, d.FREE_SPACE /1024/1024, e.contents, e.status FROM dba_temp_free_space d, dba_tablespaces e WHERE d.tablespace_name=e.tablespace_name AND contents='TEMPORARY' ;
Para la versión 10g podemos crear una vista tal y como está definida
en la versión 11g:
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TEMP_FREE_SPACE"
("TABLESPACE_NAME", "TABLESPACE_SIZE", "ALLOCATED_SPACE", "FREE_SPACE")
AS SELECT tsh.tablespace_name, tsh.total_bytes, tsh.bytes_used, tsh.bytes_free + (nvl(ss.free_blocks, 0) * ts$.blocksize) FROM (SELECT tablespace_name, sum(bytes_used + bytes_free) total_bytes, sum(bytes_used) bytes_used, sum(bytes_free) bytes_free FROM gv$temp_space_header GROUP BY tablespace_name) tsh, (SELECT tablespace_name, sum(free_blocks) free_blocks FROM gv$sort_segment GROUP BY tablespace_name) ss, ts$ WHERE ts$.name = tsh.tablespace_name and tsh.tablespace_name = ss.tablespace_name (+); COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."TABLESPACE_NAME"
IS 'Tablespace name'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."TABLESPACE_SIZE"
IS 'Total size of the tablespace'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."ALLOCATED_SPACE"
IS 'Total allocated space for sort segments'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."FREE_SPACE"
IS 'Total free space available'; COMMENT ON TABLE "SYS"."DBA_TEMP_FREE_SPACE"
IS 'Summary of temporary space usage';
("TABLESPACE_NAME", "TABLESPACE_SIZE", "ALLOCATED_SPACE", "FREE_SPACE")
AS SELECT tsh.tablespace_name, tsh.total_bytes, tsh.bytes_used, tsh.bytes_free + (nvl(ss.free_blocks, 0) * ts$.blocksize) FROM (SELECT tablespace_name, sum(bytes_used + bytes_free) total_bytes, sum(bytes_used) bytes_used, sum(bytes_free) bytes_free FROM gv$temp_space_header GROUP BY tablespace_name) tsh, (SELECT tablespace_name, sum(free_blocks) free_blocks FROM gv$sort_segment GROUP BY tablespace_name) ss, ts$ WHERE ts$.name = tsh.tablespace_name and tsh.tablespace_name = ss.tablespace_name (+); COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."TABLESPACE_NAME"
IS 'Tablespace name'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."TABLESPACE_SIZE"
IS 'Total size of the tablespace'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."ALLOCATED_SPACE"
IS 'Total allocated space for sort segments'; COMMENT ON COLUMN "SYS"."DBA_TEMP_FREE_SPACE"."FREE_SPACE"
IS 'Total free space available'; COMMENT ON TABLE "SYS"."DBA_TEMP_FREE_SPACE"
IS 'Summary of temporary space usage';
Comentarios