CODEWITHSUNDEEP
oracle-databaseplsqloracle11gtablespace
peedee
peedee

Reputation: 2031

Pl/sql script to compute the free space in the users tablespace in oracle

SELECT tablespace_name, sum(bytes)/1024/1024 "MB Free"
  FROM dba_free_space 
 WHERE tablespace_name = 'USERS'
 GROUP BY tablespace_name;

Hi everyone, the above query is what i use for showing the free space in user tablespace but how do i write a separate PL/SQL script to store it in separate table with tablespace name and time stamp.

Kindly help me as i need this run on job scheduler for every hour.

Thanks in advance

Upvotes: 1

Views: 2338

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Assuming you've already created the table you want to store the data in, simply

CREATE OR REPLACE PROCEDURE snap_free_space
AS
BEGIN
  INSERT INTO new_table( tablespace_name, free_bytes, collection_time )
    SELECT tablespace_name, sum(bytes), sysdate
      FROM dba_free_space
     GROUP BY tablespace_name;
END;

Upvotes: 2

Related Questions