SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET PAGESIZE 50000
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF

SET MARKUP HTML ON SPOOL ON ENTMAP OFF PREFORMAT OFF

SPOOL large_obj.html


PROMPT ============================================================
PROMPT DATABASE INFORMATION
PROMPT ============================================================

COLUMN name FORMAT A20
COLUMN open_mode FORMAT A20
COLUMN cdb FORMAT A10

SELECT name,
       open_mode,
       cdb
FROM v$database;


PROMPT ============================================================
PROMPT RAC INSTANCE COUNT
PROMPT ============================================================

SELECT COUNT(*) AS instance_count
FROM gv$instance;


PROMPT ============================================================
PROMPT RAC INSTANCE DETAILS
PROMPT ============================================================

COLUMN instance_name FORMAT A20
COLUMN host_name FORMAT A40
COLUMN status FORMAT A15

SELECT inst_id,
       instance_number,
       instance_name,
       host_name,
       status
FROM gv$instance
ORDER BY inst_id;


PROMPT ============================================================
PROMPT RAC CONFIGURATION
PROMPT ============================================================

SELECT name,
       value
FROM v$parameter
WHERE name = 'cluster_database';


PROMPT ============================================================
PROMPT ASM DISK GROUP SIZE
PROMPT ============================================================

COLUMN name FORMAT A20
COLUMN total_mb FORMAT 999999999
COLUMN free_mb FORMAT 999999999
COLUMN used_gb FORMAT 99999999.99
COLUMN total_gb FORMAT 99999999.99
COLUMN free_gb FORMAT 99999999.99
COLUMN used_pct FORMAT 999.99

SELECT name,
       total_mb,
       free_mb,
       ROUND((total_mb - free_mb) / 1024, 2) AS used_gb,
       ROUND(total_mb / 1024, 2) AS total_gb,
       ROUND(free_mb / 1024, 2) AS free_gb,
       ROUND(((total_mb - free_mb) / total_mb) * 100, 2) AS used_pct
FROM v$asm_diskgroup
ORDER BY used_pct DESC;


PROMPT ============================================================
PROMPT TOTAL ORACLE DATABASE SIZE
PROMPT ============================================================

COLUMN "Size in GB" FORMAT 99999999.99

SELECT b.name,
       ( SELECT NVL(SUM(bytes),0)/1024/1024/1024
           FROM dba_data_files ) +
       ( SELECT NVL(SUM(bytes),0)/1024/1024/1024
           FROM dba_temp_files ) +
       ( SELECT NVL(SUM(bytes),0)/1024/1024/1024
           FROM sys.v_$log ) +
       ( SELECT NVL(SUM(BLOCK_SIZE * FILE_SIZE_BLKS),0)/1024/1024/1024
           FROM v$controlfile ) AS "Size in GB"
FROM dual,
     (SELECT name FROM v$database) b;


PROMPT ============================================================
PROMPT DATA OCCUPIED SIZE
PROMPT ============================================================

SELECT name,
       ROUND(SUM(bytes)/1024/1024/1024,2) AS "Size in GB"
FROM dba_segments,
     (SELECT name FROM v$database)
GROUP BY name;


PROMPT ============================================================
PROMPT TABLESPACE DATAFILE SPACE
PROMPT ============================================================

COLUMN tablespace_name FORMAT A25
COLUMN file_name FORMAT A100
COLUMN autoextensible FORMAT A15
COLUMN total_space FORMAT 999999999.99
COLUMN free_space FORMAT 999999999.99
COLUMN "Free%" FORMAT 999.99
COLUMN max_space FORMAT 999999999.99

SELECT c.tablespace_name,
       a.autoextensible,
       a.file_name,
       a.total_space,
       b.free_space,
       ROUND(b.free_space / NULLIF(a.total_space,0) * 100,2) AS "Free%",
       a.max_space
FROM
    (
        SELECT file_id,
               file_name,
               SUM(bytes)/1024/1024 AS total_space,
               SUM(maxbytes)/1024/1024 AS max_space,
               autoextensible
        FROM dba_data_files
        GROUP BY file_id,
                 file_name,
                 autoextensible
    ) a,
    (
        SELECT file_id,
               NVL(SUM(bytes)/1024/1024,0) AS free_space
        FROM dba_free_space
        GROUP BY file_id
    ) b,
    (
        SELECT tablespace_name,
               file_id
        FROM dba_data_files
    ) c
WHERE a.file_id = b.file_id(+)
AND   a.file_id = c.file_id
ORDER BY c.tablespace_name;


PROMPT ============================================================
PROMPT TABLESPACE USAGE
PROMPT ============================================================

COLUMN tablespace_name FORMAT A30
COLUMN Allocated_size FORMAT 99999999.99
COLUMN Current_size FORMAT 99999999.99
COLUMN Used_size FORMAT 99999999.99
COLUMN Available_size FORMAT 99999999.99
COLUMN Pct_used FORMAT 999.99

SELECT a.tablespace_name,
       a.alloc_size/1024/1024/1024 AS Allocated_size,
       a.cur_size/1024/1024/1024 AS Current_size,
       (u.used + a.file_count*65536)/1024/1024/1024 AS Used_size,
       (a.alloc_size -
        (u.used + a.file_count*65536))/1024/1024/1024 AS Available_size,
       ((u.used + a.file_count*65536)*100)/a.alloc_size AS Pct_used
FROM dba_tablespaces t,
     (
        SELECT t1.tablespace_name,
               NVL(SUM(s.bytes),0) used
        FROM dba_segments s,
             dba_tablespaces t1
        WHERE t1.tablespace_name = s.tablespace_name(+)
        GROUP BY t1.tablespace_name
     ) u,
     (
        SELECT d.tablespace_name,
               SUM(GREATEST(d.bytes,NVL(d.maxbytes,0))) alloc_size,
               SUM(d.bytes) cur_size,
               COUNT(*) file_count
        FROM dba_data_files d
        GROUP BY d.tablespace_name
     ) a
WHERE t.tablespace_name = u.tablespace_name
AND   t.tablespace_name = a.tablespace_name
ORDER BY Pct_used DESC;


PROMPT ============================================================
PROMPT TOP 10 LARGE SEGMENTS
PROMPT ============================================================

COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A40
COLUMN segment_type FORMAT A25
COLUMN tablespace_name FORMAT A30
COLUMN size_gb FORMAT 99999999.99

SELECT *
FROM (
    SELECT owner,
           segment_name,
           segment_type,
           tablespace_name,
           ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb
    FROM dba_segments
    ORDER BY bytes DESC
)
WHERE ROWNUM <= 10;


PROMPT ============================================================
PROMPT TOP 10 LARGE LOB SEGMENTS
PROMPT ============================================================

COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A40
COLUMN tablespace_name FORMAT A30
COLUMN size_gb FORMAT 99999999.99

SELECT *
FROM (
    SELECT l.owner,
           l.table_name,
           l.column_name,
           l.segment_name,
           l.tablespace_name,
           ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS size_gb
    FROM dba_lobs l
    JOIN dba_segments s
      ON s.owner = l.owner
     AND s.segment_name = l.segment_name
    ORDER BY s.bytes DESC
)
WHERE ROWNUM <= 10;


PROMPT ============================================================
PROMPT TOP 10 FRAGMENTED TABLES
PROMPT ============================================================

COLUMN table_name FORMAT A40
COLUMN "size (GB)" FORMAT 99999999.99
COLUMN "actual_data (GB)" FORMAT 99999999.99
COLUMN "wasted_space (GB)" FORMAT 99999999.99

SELECT *
FROM (
    SELECT owner || '.' || table_name AS table_name,
           ROUND((blocks * 8)/1024/1024, 2) AS "size (GB)",
           ROUND((num_rows * avg_row_len)/1024/1024/1024, 2)
                AS "actual_data (GB)",
           ROUND((blocks * 8)/1024/1024, 2) -
           ROUND((num_rows * avg_row_len)/1024/1024/1024, 2)
                AS "wasted_space (GB)"
    FROM dba_tables
    WHERE blocks IS NOT NULL
      AND num_rows IS NOT NULL
      AND blocks > 0
    ORDER BY 4 DESC
)
WHERE ROWNUM <= 10;


PROMPT ============================================================
PROMPT REPORT COMPLETED
PROMPT ============================================================


SPOOL OFF

SET MARKUP HTML OFF
SET VERIFY ON