1b.7. Space Utilization Summary (OCI360_INSTANCES)

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 SHAPE                                                                                        VARCHAR2(32)
 REGION                                                                                       VARCHAR2(4)
 IMAGE_ID                                                                                     VARCHAR2(128)
 METADATA$SSH_AUTHORIZED_KEYS                                                                 VARCHAR2(512)
 IPXE_SCRIPT                                                                                  VARCHAR2(4)
 LAUNCH_MODE                                                                                  VARCHAR2(16)
 SYSTEM_TAGS$ORCL_CLOUD$FREE_TIER_RETAINED                                                    VARCHAR2(4)
 AGENT_CONFIG$IS_MANAGEMENT_DISABLED                                                          VARCHAR2(8)
 AGENT_CONFIG$IS_MONITORING_DISABLED                                                          VARCHAR2(8)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(16)
 FAULT_DOMAIN                                                                                 VARCHAR2(16)
 SHAPE_CONFIG$GPUS                                                                            NUMBER
 SHAPE_CONFIG$OCPUS                                                                           NUMBER
 SHAPE_CONFIG$LOCAL_DISKS                                                                     NUMBER
 SHAPE_CONFIG$MEMORY_IN_GBS                                                                   NUMBER
 SHAPE_CONFIG$GPU_DESCRIPTION                                                                 VARCHAR2(4)
 SHAPE_CONFIG$MAX_VNIC_ATTACHMENTS                                                            NUMBER
 SHAPE_CONFIG$PROCESSOR_DESCRIPTION                                                           VARCHAR2(64)
 SHAPE_CONFIG$LOCAL_DISK_DESCRIPTION                                                          VARCHAR2(4)
 SHAPE_CONFIG$NETWORKING_BANDWIDTH_IN_GBPS                                                    NUMBER
 SHAPE_CONFIG$LOCAL_DISKS_TOTAL_SIZE_IN_GBS                                                   VARCHAR2(4)
 TIME_CREATED                                                                                 VARCHAR2(32)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 LAUNCH_OPTIONS$FIRMWARE                                                                      VARCHAR2(8)
 LAUNCH_OPTIONS$NETWORK_TYPE                                                                  VARCHAR2(16)
 LAUNCH_OPTIONS$BOOT_VOLUME_TYPE                                                              VARCHAR2(16)
 LAUNCH_OPTIONS$REMOTE_DATA_VOLUME_TYPE                                                       VARCHAR2(16)
 LAUNCH_OPTIONS$IS_CONSISTENT_VOLUME_NAMING_ENABLED                                           VARCHAR2(4)
 LAUNCH_OPTIONS$IS_PV_ENCRYPTION_IN_TRANSIT_ENABLED                                           VARCHAR2(8)
 SOURCE_DETAILS$IMAGE_ID                                                                      VARCHAR2(128)
 SOURCE_DETAILS$KMS_KEY_ID                                                                    VARCHAR2(4)
 SOURCE_DETAILS$SOURCE_TYPE                                                                   VARCHAR2(8)
 SOURCE_DETAILS$BOOT_VOLUME_SIZE_IN_GBS                                                       VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(8)
 AVAILABILITY_DOMAIN                                                                          VARCHAR2(32)
 DEDICATED_VM_HOST_ID                                                                         VARCHAR2(4)
 TIME_MAINTENANCE_REBOOT_DUE                                                                  VARCHAR2(4)


WITH tvols  AS (select nvl(sum(SIZE_IN_GBS),0) tsize from OCI360_VOLUMES where lifecycle_state = 'AVAILABLE'),
     tbvols AS (select nvl(sum(SIZE_IN_GBS),0) tsize from OCI360_BVOLUMES where lifecycle_state = 'AVAILABLE'),
     tnodes AS (select nvl(sum(SOFTWARE_STORAGE_SIZE_IN_GB),0) tsize from OCI360_DB_NODES where lifecycle_state in ('AVAILABLE','STOPPED')),
     tdbs AS (select nvl(sum(DATA_STORAGE_SIZE_IN_GBS+RECO_STORAGE_SIZE_IN_GB),0) tsize from (select distinct id,DATA_STORAGE_SIZE_IN_GBS,RECO_STORAGE_SIZE_IN_GB,lifecycle_state from OCI360_DB_SYSTEMS) where lifecycle_state = 'AVAILABLE'),
     tbkps  AS (select nvl(sum(UNIQUE_SIZE_IN_GBS),0) tsize from OCI360_BACKUPS where lifecycle_state = 'AVAILABLE'),
     tbbkps AS (select nvl(sum(UNIQUE_SIZE_IN_GBS),0) tsize from OCI360_BV_BACKUPS where lifecycle_state = 'AVAILABLE'),
     tbos   AS (select nvl(round(sum("APPROXIMATE_SIZE")/power(1024,3)),0) tsize from OCI360_BUCKETS),
     tbds   AS (SELECT nvl(sum(NODES$ATTACHED_BLOCK_VOLUMES$VOLUME_SIZE_IN_GBS),0) tsize FROM OCI360_BDS_INSTANCES)
SELECT label, (tsize/SUM(tsize) over())*100 dummy_01, style dummy_02, mo
FROM (
     SELECT 'Volume Size' label, tsize, '34CF27' style, tsize || ' GB' mo FROM tvols
     UNION ALL
     SELECT 'Boot-Volume Size',  tsize, '9FFA9D', tsize || ' GB' FROM tbvols
     UNION ALL
     SELECT 'Backup Size', tbkps.tsize + tbbkps.tsize, '0252D7', (tbkps.tsize + tbbkps.tsize) || ' GB' FROM tbkps, tbbkps
     UNION ALL
     SELECT 'Database Size', tnodes.tsize + tdbs.tsize, '1E96DD', (tnodes.tsize + tdbs.tsize) || ' GB' FROM tnodes,tdbs
     UNION ALL
     SELECT 'Object Storage Size', tsize, 'CFCF26', tsize || ' GB' FROM tbos
     UNION ALL
     SELECT 'Bigdata Size', tsize, 'ABABAB', tsize || ' GB' FROM tbds
     );


6 rows selected.