--
-- Script to setup all of the table needed for  concurrent statistics gathering for a subset of objects in a schema
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

connect sh/sh

set pages 9999
set lines 150
set echo on

-- Setup
drop table sales2 purge ;
create table sales2
partition by range(time_id)
(partition SALES_1995       values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_1996        values less than(TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_H1_1997     values less than(TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_H2_1997     values less than(TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_1998     values less than(TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_1998     values less than(TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_1998     values less than(TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_1998     values less than(TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_1999     values less than(TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_1999     values less than(TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_1999     values less than(TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_1999     values less than(TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2000     values less than(TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2000     values less than(TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2000     values less than(TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2000     values less than(TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2001     values less than(TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2001     values less than(TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2001     values less than(TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2001     values less than(TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2002     values less than(TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2002     values less than(TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2002     values less than(TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2002     values less than(TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))
as select * from sh.sales;

drop table sales3 purge;
create table sales3
partition by range(time_id)
(partition SALES_1995       values less than(TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_1996        values less than(TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_H1_1997     values less than(TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_H2_1997     values less than(TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_1998     values less than(TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_1998     values less than(TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_1998     values less than(TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_1998     values less than(TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_1999     values less than(TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_1999     values less than(TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_1999     values less than(TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_1999     values less than(TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2000     values less than(TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2000     values less than(TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2000     values less than(TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2000     values less than(TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2001     values less than(TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2001     values less than(TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2001     values less than(TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2001     values less than(TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q1_2002     values less than(TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q2_2002     values less than(TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q3_2002     values less than(TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
partition SALES_Q4_2002     values less than(TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')))
as select * from sh.sales;

SELECT table_name, to_char(last_analyzed, 'yyyy/mm/dd hh:mi:ss') LA
FROM   user_tables;

--