CREATE TABLE edw.sales_tran_201501
(sales_tran_id       NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 sales_date          DATE,
 sales_qty           NUMBER,
 sales_amt           NUMBER(25,2)
)
 PARTITION BY RANGE (sales_date)
 (PARTITION ST_20150101 VALUES LESS THAN TO_DATE(20150102,'yyyymmdd') 
);

-- 201501 (Jan-2015 setup):
-- Create a dynamic daily date range time partition script
-- The value to be passed for &yyyymm is 201501
-- Since first value is created, add day 2 to 31 in this example

SELECT
 'ALTER TABLE edw.sales_tran_'||month_id||' ADD PARTITION ST_'||
  TO_CHAR(cal_day,'yyyymmdd')||' VALUES LESS THAN TO_DATE('||
  TO_CHAR(cal_day+1,'yyyymmdd')||'''yyyymmdd'''||','||
  '''NLS_CALENDAR=GREGORIAN'''||
  ')) COMPRESS;'                  ddl_str
FROM
 (SELECT
    level  idx,
    qt.month_id,
    TO_DATE(qt.month_id,'yyyymm')+level-1  cal_day
  FROM DUAL,
   (SELECT  &yyyymm month_id FROM DUAL)
  CONNECT BY level <= TO_CHAR(LAST_DAY(TO_DATE(qt.month_id,'yyyymm')),'dd')
 )
WHERE idx > 1;

-- In range partition, if a later date partition exists with a gap in date, 
-- (e.g. Jan-5th and Jan-7th) middle value of Jan-6th cannot inserted.  It will 
-- result in ORA-14074 error.

-- Script based on SYSDATE

SELECT
 'ALTER TABLE edw.sales_tran_'||month_id||' ADD PARTITION ST_'||
  TO_CHAR(calendar_day,'yyyymmdd')||' VALUES LESS THAN TO_DATE('||
  TO_CHAR(calendar_day+1,'yyyymmdd')||'''yyyymmdd'''||','||
  '''NLS_CALENDAR=GREGORIAN'''||
  ')) COMPRESS;'              ddl_str
FROM
(SELECT
   level  idx,
   month_id,
   TO_DATE(qt.month_id,'yyyymm')+level-1 calendar_day
 FROM DUAL,
       (SELECT TO_CHAR(SYSDATE,'yyyymm') month_id FROM DUAL) qt
 CONNECT BY level <= TO_CHAR(LAST_DAY(SYSDATE),'DD')
)
WHERE  idx > 1
;

-- Get details of the partitions for all tables for a owner/schema
SELECT * FROM all_tab_partitions
WHERE owner = NVL(UPPER('&p_owner'),user);

-- Get data in a specific partition (e.g. ST_20150101)
SELECT * FROM sales_tran_201501 PARTITION(ST_20150101)
;