pstreeselect_interval_partitioning.sql

REM pstreeselect_interval_partitioning.sql
spool pstreeselect_interval_partitioning append

set echo on
REM spool &&1._pstreeselect&&2._interval_partitioning

ALTER SESSION SET CURRENT_SCHEMA=&&1.;

DROP TABLE &&1..PSYPSTREESELECT&&2. PURGE
/

CREATE TABLE &&1..PSYPSTREESELECT&&2. 
(SELECTOR_NUM   INTEGER NOT NULL,
 TREE_NODE_NUM  INTEGER NOT NULL,
 RANGE_FROM_&&2. VARCHAR2(&&2.) NOT NULL,
 RANGE_TO_&&2.   VARCHAR2(&&2.) NOT NULL) 
PARTITION BY RANGE (selector_num) INTERVAL (1)
(PARTITION VALUES LESS THAN(2))
TABLESPACE "PTWORK" STORAGE(INITIAL 64K NEXT 64K)
/
INSERT /*+APPEND*/ INTO &&1..PSYPSTREESELECT&&2. 
      (SELECTOR_NUM,
       TREE_NODE_NUM,
       RANGE_FROM_&&2.,
       RANGE_TO_&&2.)
SELECT SELECTOR_NUM,
       TREE_NODE_NUM,
       RANGE_FROM_&&2.,
       RANGE_TO_&&2.
  FROM &&1..PSTREESELECT&&2.
/
COMMIT
/

DROP TABLE &&1..PSTREESELECT&&2. PURGE;
/
ALTER TABLE &&1..PSYPSTREESELECT&&2. RENAME TO PSTREESELECT&&2.
/

CREATE UNIQUE  INDEX &&1..PS_PSTREESELECT&&2. ON &&1..PSTREESELECT&&2.
(SELECTOR_NUM
,TREE_NODE_NUM
,RANGE_FROM_&&2.
,RANGE_TO_&&2.) 
local PCTFREE 5 COMPRESS 1 TABLESPACE "PSINDEX" STORAGE(INITIAL 64K NEXT 64K)
/
ALTER INDEX &&1..PS_PSTREESELECT&&2. NOPARALLEL LOGGING
/
CREATE INDEX &&1..PSAPSTREESELECT&&2. ON &&1..PSTREESELECT&&2. 
(SELECTOR_NUM
,RANGE_FROM_&&2.
,RANGE_TO_&&2.
,TREE_NODE_NUM) 
local PCTFREE 5 COMPRESS 1 TABLESPACE "PSINDEX" STORAGE(INITIAL 64K NEXT 64K)
/
ALTER INDEX &&1..PSAPSTREESELECT&&2. NOPARALLEL LOGGING
/

--add your own roles here
GRANT SELECT ON &&1..pstreeselect&&2. TO sysadm;
GRANT SELECT ON &&1..pstreeselect&&2. TO ps_read_all_role;
GRANT SELECT ON &&1..pstreeselect&&2. TO ace_read_only;
GRANT SELECT ON &&1..pstreeselect&&2. TO psteam;
GRANT SELECT, INSERT, UPDATE, DELETE ON &&1..pstreeselect&&2. TO ace_read_write;

spool off