Skip to main content

Is it possible to compute licensed data size at the schema (not database) granularity?

  • October 2, 2014
  • 2 replies
  • 15 views
IgorM_1
If so, how it's done (is there any API or any scripts you can share or at least an exact definition/formula of how this licensed size is supposed to be computed at per-table or per-schema granularity).

Thanks.

    2 replies

    Evan_H
    Forum|alt.badge.img
    • Evan_H
    • Participating Frequently
    • October 2, 2014
    To check the raw size of the schema, you can run the command audit(). It estimates the data size of a database, a schema, a projection, or a table as it is counted in an audit of the database size.

    select audit('<schema_name>');
    norbert_krupa
    Forum|alt.badge.img+2
    • norbert_krupa
    • Participating Frequently
    • October 2, 2014
    If you want something more holistic (less accurate), try out this query from my diagnostic queries: 
    -- Shows compressed and raw estimate data space utilization by schema -- http://wp.me/p3Qalh-jA SELECT /*+label(diag_schema_space_utilization)*/         pj.anchor_table_schema,         pj.used_compressed_gb,         pj.used_compressed_gb * la.ratio AS raw_estimate_gb  FROM   (SELECT ps.anchor_table_schema,                 SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb          FROM   v_catalog.projections p                 JOIN v_monitor.projection_storage ps                   ON ps.projection_id = p.projection_id          WHERE  p.is_super_projection = 't'          GROUP  BY ps.anchor_table_schema) pj         CROSS JOIN (SELECT (SELECT database_size_bytes                             FROM   v_catalog.license_audits                             ORDER  BY audit_start_timestamp DESC                             LIMIT  1) / (SELECT SUM(used_bytes)                                          FROM   v_monitor.projection_storage) AS ratio) la  ORDER  BY pj.used_compressed_gb DESC;
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement