drop table orcl_adm.orcl_capacity_ts cascade constraints;

create table orcl_adm.orcl_capacity_ts
(
 stempel            date           not null,
 tablespace_name    varchar2(30)   not null,
 bytes_used         number         not null,
 bytes_free         number         not null,
 bytes_total        number         not null
)
tablespace orcl_adm;

comment on table orcl_adm.orcl_capacity_ts is 'In der Tabelle werden zyklisch die aktuellen Tablespace-Volumina der DB gespeichert';

comment on column orcl_adm.orcl_capacity_ts.stempel is 'Aktueller Zeitstempel';
comment on column orcl_adm.orcl_capacity_ts.tablespace_name is 'Name des Tablespaces';
comment on column orcl_adm.orcl_capacity_ts.bytes_used is 'Benutzte Bytes des Tablespaces';
comment on column orcl_adm.orcl_capacity_ts.bytes_free is 'Freie Bytes des Tablespaces';
comment on column orcl_adm.orcl_capacity_ts.bytes_total is 'Total Bytes des Tablespaces';

grant select on orcl_adm.orcl_capacity_ts to orcl_read_role;



insert into orcl_adm.orcl_capacity_ts
select
  sysdate, df.tablespace_name, totalusedspace, (df.totalspace - tu.totalusedspace), df.totalspace
from
  (select tablespace_name, sum(bytes) totalspace
   from dba_data_files
   group by tablespace_name) df,
  (select sum(bytes) totalusedspace, tablespace_name
   from dba_segments
   group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name;

select
  to_char(stempel,'YYYY-MM-DD_HH24:MI:SS') "Timestamp",
  tablespace_name "Tablespace",
  round(bytes_used/1024/1024)  "MB_used",
  round(bytes_free/1024/1024)  "MB_free",
  round(bytes_total/1024/1024) "MB_total"
from orcl_adm.orcl_capacity_ts
order by 1, 2;

________________________________________________________________________________________________________


drop table orcl_adm.orcl_capacity_df cascade constraints;

create table orcl_adm.orcl_capacity_df
(
 stempel            date            not null,
 tablespace_name    varchar2(30)    not null,
 file_name          varchar2(513)   not null,
 bytes              number          not null,
 autoextensible     varchar2(3)     not null
)
tablespace orcl_adm;

comment on table orcl_adm.orcl_capacity_df is 'In der Tabelle werden zyklisch die aktuellen Datendatei-Volumina der DB gespeichert';

comment on column orcl_adm.orcl_capacity_df.stempel is 'Aktueller Zeitstempel';
comment on column orcl_adm.orcl_capacity_df.tablespace_name is 'Name des Tablespaces';
comment on column orcl_adm.orcl_capacity_df.file_name is 'Vollqualifizierter Name der Datendatei';
comment on column orcl_adm.orcl_capacity_df.bytes is 'Allokierte Bytes der Datendatei';
comment on column orcl_adm.orcl_capacity_df.autoextensible is 'Automatisch erweiterbar (yes/no)';

grant select on orcl_adm.orcl_capacity_df to orcl_read_role;



insert into orcl_adm.orcl_capacity_df
select sysdate, tablespace_name, file_name, bytes, autoextensible from dba_data_files;

col Filename for a75
select
  to_char(stempel,'YYYY-MM-DD_HH24:MI:SS') "Timestamp",
  tablespace_name "Tablespace",
  file_name "Filename",
  round(bytes/1024/1024)  "MB",
  autoextensible "AUT"
from orcl_adm.orcl_capacity_df
order by 1, 2;
  
________________________________________________________________________________________________________