Tracking Raw Schema Size Over Time
Jim_Knicely
Administrator
There is no way to go back in time to see how large a schema was a day/month/year ago.
But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit.
Example:
dbadmin=> CREATE EXTERNAL TABLE table_raw_size (audit_date DATE, table_schema VARCHAR(128), raw_size VARCHAR(100)) AS COPY FROM ‘/home/dbadmin/table_raw_size.txt’; CREATE TABLE dbadmin=> \! vsql -Atc “SELECT ‘SELECT sysdate::DATE, ”’ || schema_name || ”’, audit(”’ || schema_name || ”’);’ FROM schemata WHERE NOT is_system_schema;” |vsql -At >> /home/dbadmin/table_raw_size.txt dbadmin=> SELECT * FROM table_raw_size ORDER BY audit_date, table_schema; audit_date | table_schema | raw_size ————+———————+————- 2019-09-25 | P02R | 16384 2019-09-25 | b | 0 2019-09-25 | bnym | 14367347 2019-09-25 | chewy | 0 2019-09-25 | ctg_analytics | 0 2019-09-25 | dds | 1992179 2019-09-25 | entity | 0 2019-09-25 | etl_temp | 0 2019-09-25 | facts | 0 2019-09-25 | global_table_schema | 0 2019-09-25 | groupm_ee_gcp | 0 2019-09-25 | idea_dwh | 12288 2019-09-25 | integrate | 0 2019-09-25 | ix_platonic_tables | 54724325801 2019-09-25 | jack | 0 2019-09-25 | jim | 0 2019-09-25 | julie | 0 2019-09-25 | local_table_schema | 0 2019-09-25 | my_system_tables | 195015 2019-09-25 | my_system_views | 0 2019-09-25 | nba | 64 2019-09-25 | public | 16814285982 2019-09-25 | s1 | 0 2019-09-25 | stlouis | 398319385 2019-09-25 | v_func | 0 2019-09-25 | v_txtindex | 0 2019-09-25 | views | 4 2019-09-25 | walkme | 387 2019-09-25 | z | 0 (29 rows)
Using cron, you can run this daily.
Have fun!
1