//This file contains the database scripts used in this blog "Using Amazon EBS elastic volumes with Oracle databases (part 2): databases using LVM" 
//Blog URL - https://aws.amazon.com/blogs/database/using-amazon-ebs-elastic-volumes-with-oracle-databases-part-2-databases-using-lvm/


//Script to create a big file tablespace called EVTestTablespace with a 180-GB data file called evtestdf.dbf, using SQL*Plus 

CREATE BIGFILE TABLESPACE EVTestTablespace DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/customdf/EVTESTDF.dbf' SIZE 180G;

//Script to verify location of the created datafile

SELECT du.username, du.default_tablespace, ts.ts#, df.name, round(df.BYTES/(1024*1024*1024)) size_gb , df.blocks
FROM dba_users du, v$tablespace ts, v$datafile df
WHERE du.default_tablespace = ts.name AND ts.ts# = df.ts# AND du.username = 'TESTEVUSER'


//Script to start the evtestproc stored procedure to insert records into the evtesttab table while we increase the storage provisioned to the database.

begin
  evtestproc();  //PLSQL procedure to insert records into the EVTESTTAB table at 10-second intervals
end;

//Script to verify that records are being inserted.

SELECT * FROM evtesttab;

//Script to increase the database storage available by resizing the big file tablespace to 360 GB, using SQL*Plus 

ALTER TABLESPACE EVTestTableSpace RESIZE 360G;

//Script to verify that the database storage is now increased to 360 GB

SELECT du.username, du.default_tablespace, ts.ts#, df.name, round(df.BYTES/(1024*1024*1024)) size_gb , df.blocks
FROM dba_users du, v$tablespace ts, v$datafile df
WHERE du.default_tablespace = ts.name AND ts.ts# = df.ts# AND du.username = 'TESTEVUSER'

//Script to query the evtesttab to verify that the database was available during the resize

Select * from evtesttab;