/*
   Following objects need to be created to capture ZFSSA monitoring data
*/



/*
To capture ZFS pool space usage
*/
create table zfs_pool_space
(
   capture_time     date           NOT NULL
 , zfssa_nodename   VARCHAR2(20)   NOT NULL
 , pool_name        VARCHAR2(20)   NOT NULL
 , used             NUMBER         NOT NULL
 , avail            NUMBER         NOT NULL
 , compression      NUMBER         
 , dedup            NUMBER         
)
partition by RANGE  (capture_time) INTERVAL  (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_pool_space  
     add constraint  zfs_pool_space_pk 
     primary key (capture_time, zfssa_nodename, pool_name) 
     using index LOCAL
     PCTFREE 0
;

/*
To capture ZFS project space usage
*/
create table zfs_project_space
(
   capture_time             date           NOT NULL
 , zfssa_nodename           VARCHAR2(20)   NOT NULL
 , pool_name                VARCHAR2(20)   NOT NULL
 , project_name             VARCHAR2(30)   NOT NULL
 , space_total              NUMBER         NOT NULL
 , space_data               NUMBER         NOT NULL
 , space_available          NUMBER         NOT NULL
 , space_snapshots          NUMBER         NOT NULL
 , space_unused_res         NUMBER         NOT NULL
 , space_unused_res_shares  NUMBER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_project_space  
     add constraint  zfs_project_space_pk 
     primary key (capture_time, zfssa_nodename, pool_name, project_name) 
     using index LOCAL
     PCTFREE 0
;

/*
To capture ZFS snapshot space usage
*/
create table zfs_snapshot_space
(
   capture_time             date           NOT NULL
 , zfssa_nodename           VARCHAR2(20)   NOT NULL
 , pool_name                VARCHAR2(20)   NOT NULL
 , project_name             VARCHAR2(30)   NOT NULL
 , snapshot_name            VARCHAR2(60)   NOT NULL
 , unique_space             NUMBER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_snapshot_space  
     add constraint  zfs_snapshot_space_pk 
     primary key (capture_time, zfssa_nodename, pool_name, project_name,snapshot_name) 
     using index LOCAL
     PCTFREE 0
;



/*
To capture ZFS share space usage
*/
create table zfs_share_space
(
   capture_time      date           NOT NULL
 , zfssa_nodename    VARCHAR2(20)   NOT NULL
 , pool_name         VARCHAR2(20)   NOT NULL
 , project_name      VARCHAR2(30)   NOT NULL
 , share_name        VARCHAR2(30)   NOT NULL
 , space_total       NUMBER         NOT NULL
 , space_data        NUMBER         NOT NULL
 , space_available   NUMBER         NOT NULL
 , space_snapshots   NUMBER         NOT NULL
 , space_unused_res  NUMBER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_share_space  
     add constraint  zfs_share_space_pk 
     primary key (capture_time, zfssa_nodename, pool_name, project_name, share_name) 
     using index LOCAL
     PCTFREE 0
;


/*
To capture following dataset
   Statistics = ip.bytes[hostname]
   Description = Network: IP bytes per second broken down by hostname
*/
create table zfs_ipbytes_by_host
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , hostname         VARCHAR2(60)    NOT NULL
 , bytes_per_sec    INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_ipbytes_by_host  
     add constraint  zfs_ipbytes_by_host_pk 
     primary key (capture_time, zfssa_nodename, hostname) 
     using index LOCAL
     PCTFREE 0
;

/*
To capture following dataset
   Statistics = nfs3.bytes[project]
   Description = Protocol: NFSv3 bytes per second broken down by project
*/
create table zfs_nfsv3_bytes_by_project
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , project          VARCHAR2(60)    NOT NULL
 , bytes_per_sec    INTEGER       NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_nfsv3_bytes_by_project  
     add constraint  zfs_nfsv3_bytes_by_project_pk 
     primary key (capture_time, zfssa_nodename, project) 
     using index LOCAL
     PCTFREE 0
;


/*
To capture following dataset
   Statistics = nfs3.ops[project]
   Description = Network: IP bytes per second broken down by hostname
*/
create table zfs_nfsv3_ops_by_project
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , project          VARCHAR2(60)    NOT NULL
 , ops_per_sec      INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_nfsv3_ops_by_project  
     add constraint  zfs_nfsv3_ops_by_project_pk 
     primary key (capture_time, zfssa_nodename, project) 
     using index LOCAL
     PCTFREE 0
;



/*
To capture following dataset
   Statistics = nfs3.ops[share]
   Description = Network: IP bytes per second broken down by hostname
*/
create table zfs_nfsv3_ops_by_share
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , sharename        VARCHAR2(60)    NOT NULL
 , ops_per_sec      INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_nfsv3_ops_by_share  
     add constraint  zfs_nfsv3_ops_by_share_pk 
     primary key (capture_time, zfssa_nodename, sharename) 
     using index LOCAL
     PCTFREE 0
;


/*
To capture following dataset
   Statistics = cpu.utilization
   Description = CPU: percent utilization
*/
create table zfs_cpu_utilization
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , util_percent     INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_cpu_utilization  
     add constraint  zfs_cpu_utilization_pk 
     primary key (capture_time, zfssa_nodename ) 
     using index LOCAL
     PCTFREE 0
;



/*
To capture following dataset
   Statistics = 
   Description = 
*/

create table zfs_nfsv3_operations
(
   capture_time        date            NOT NULL
 , zfssa_nodename      VARCHAR2(20)    NOT NULL
 , operations_persec   INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_nfsv3_operations  
     add constraint  zfs_nfsv3_operations_pk 
     primary key (capture_time, zfssa_nodename ) 
     using index LOCAL
     PCTFREE 0
;


/*
To capture following dataset
   Statistics = ndmp.diskkb
   Description = Data Movement: NDMP bytes transferred to/from disk per second
*/
create table zfs_ndmp_diskio
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , ndmp_diskkb      INTEGER         NOT NULL
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_ndmp_diskio  
     add constraint  zfs_ndmp_diskio_pk 
     primary key (capture_time, zfssa_nodename ) 
     using index LOCAL
     PCTFREE 0
;



/*
To capture following dataset
   Statistics = nic.kilobytes[direction]
   Description = 
*/
create table zfs_network_stats
(
   capture_time     date            NOT NULL
 , zfssa_nodename   VARCHAR2(20)    NOT NULL
 , in_kb            INTEGER         
 , out_kb           INTEGER         
)
partition by RANGE  (capture_time) INTERVAL  (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
)
PCTFREE 0
PCTUSED 99
;

alter table zfs_network_stats  
     add constraint  zfs_network_stats_pk 
     primary key (capture_time, zfssa_nodename ) 
     using index LOCAL
     PCTFREE 0
;