Data Management of Table MAXVALUE partition

–Data Verification on PRD

SELECT MAX(CREATE_DATE) FROM TBLEMPSTATISTICS PARTITION (P2013_10);

SELECT MAX(CREATE_DATE) FROM TBLEMPSTATISTICS PARTITION (PMAXDATE);

SQL> SQL>  set colsep "|"
SQL>  set linesize 32767
SQL>  set pagesize 10000
SQL>  COLUMN table_name     FORMAT A20
SQL>  COLUMN partition_name FORMAT A20
SQL>  COLUMN high_value     FORMAT 999
SQL>  COLUMN num_rows       FORMAT 999999999
SQL>
SQL>  SELECT table_name,
  2          partition_name,
  3          high_value,
  4          num_rows
  5   FROM   user_tab_partitions
  6   WHERE table_name = 'TBLEMPSTATISTICS'
  7   ORDER BY table_name, partition_name;

TABLE_NAME          |PARTITION_NAME      |HIGH_VALUE                                                                      |  NUM_ROWS
--------------------|--------------------|--------------------------------------------------------------------------------|----------
TBLEMPSTATISTICS   |P2013_10            |TIMESTAMP' 2013-11-01 00:00:00'                                                 |   8308323
TBLEMPSTATISTICS   |P2013_11            |TIMESTAMP' 2013-12-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_12            |TIMESTAMP' 2014-01-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_5             |TIMESTAMP' 2013-06-01 00:00:00'                                                 |   6116188
TBLEMPSTATISTICS   |P2013_6             |TIMESTAMP' 2013-07-01 00:00:00'                                                 |  23133572
TBLEMPSTATISTICS   |P2013_7             |TIMESTAMP' 2013-08-01 00:00:00'                                                 |   8822588
TBLEMPSTATISTICS   |P2013_8             |TIMESTAMP' 2013-09-01 00:00:00'                                                 |   9575553
TBLEMPSTATISTICS   |P2013_9             |TIMESTAMP' 2013-10-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |PMAXDATE            |MAXVALUE 			                                                 |   4100000

–Temporary shift the data of nov to other table

CREATE TABLE TBLEMPSTATISTICS_P2013_11
AS
SELECT * FROM TBLEMPSTATISTICS PARTITION (PMAXDATE);

–drop the max partition

ALTER TABLE TBLEMPSTATISTICS DROP PARTITION PMAXDATE UPDATE INDEXES;

–add the new partitions for 12 months

ALTER TABLE TBLEMPSTATISTICS ADD PARTITION P2013_11 VALUES LESS THAN (TO_DATE('2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2013_11;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2013_12  VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2013_12;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_01  VALUES LESS THAN (TO_DATE('2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_01;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_02  VALUES LESS THAN (TO_DATE('2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_02;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_03  VALUES LESS THAN (TO_DATE('2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_03;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_04  VALUES LESS THAN (TO_DATE('2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_04;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_05  VALUES LESS THAN (TO_DATE('2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_05;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_06  VALUES LESS THAN (TO_DATE('2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_06;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_07  VALUES LESS THAN (TO_DATE('2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_07;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_08  VALUES LESS THAN (TO_DATE('2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_08;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_09  VALUES LESS THAN (TO_DATE('2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_09;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_10  VALUES LESS THAN (TO_DATE('2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_10;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION PMAXDATE  VALUES LESS THAN (MAXVALUE) TABLESPACE tbsuserstatisticsmaxdate;

–OUTPUT================

SQL>  set colsep "|"
SQL>  set linesize 32767
SQL>  set pagesize 10000
SQL>  COLUMN table_name     FORMAT A20
SQL>  COLUMN partition_name FORMAT A20
SQL>  COLUMN high_value     FORMAT 999
SQL>  COLUMN num_rows       FORMAT 999999999
SQL>
SQL>  SELECT table_name,
  2          partition_name,
  3          high_value,
  4          num_rows
  5   FROM   user_tab_partitions
  6   WHERE table_name = 'TBLEMPSTATISTICS'
  7   ORDER BY table_name, partition_name;

TABLE_NAME          |PARTITION_NAME      |HIGH_VALUE                                                                      |  NUM_ROWS
--------------------|--------------------|--------------------------------------------------------------------------------|----------
TBLEMPSTATISTICS   |P2013_10            |TIMESTAMP' 2013-11-01 00:00:00'                                                 |   8308323
TBLEMPSTATISTICS   |P2013_11            |TIMESTAMP' 2013-12-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_12            |TIMESTAMP' 2014-01-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_5             |TIMESTAMP' 2013-06-01 00:00:00'                                                 |   6116188
TBLEMPSTATISTICS   |P2013_6             |TIMESTAMP' 2013-07-01 00:00:00'                                                 |  23133572
TBLEMPSTATISTICS   |P2013_7             |TIMESTAMP' 2013-08-01 00:00:00'                                                 |   8822588
TBLEMPSTATISTICS   |P2013_8             |TIMESTAMP' 2013-09-01 00:00:00'                                                 |   9575553
TBLEMPSTATISTICS   |P2013_9             |TIMESTAMP' 2013-10-01 00:00:00'                                                 |   8116638
TBLEMPSTATISTICS   |P2014_01            |TIMESTAMP' 2014-02-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_02            |TIMESTAMP' 2014-03-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_03            |TIMESTAMP' 2014-04-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_04            |TIMESTAMP' 2014-05-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_05            |TIMESTAMP' 2014-06-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_06            |TIMESTAMP' 2014-07-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_07            |TIMESTAMP' 2014-08-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_08            |TIMESTAMP' 2014-09-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_09            |TIMESTAMP' 2014-10-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_10            |TIMESTAMP' 2014-11-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |PMAXDATE            |MAXVALUE

–Load the data from 1 to 14 NOV to actual table

INSERT INTO TBLEMPSTATISTICS
SELECT * FROM TBLEMPSTATISTICS_P2013_11;

–Clean temporary table

drop table TBLEMPSTATISTICS_P2013_11;

–Check INVALID objects

SELECT OBJECT_NAME FROM USER_OBJECTS
WHERE STATUS != 'VALID';

—cross check prd ddl

set long 10000
set pagesize 0
select dbms_metadata.get_ddl('TABLE','TBLEMPSTATISTICS') from dual;
select dbms_metadata.get_dependent_ddl('INDEX','TBLEMPSTATISTICS') from dual;

exposure on VLDB 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s