Dear Readers, Please check the below articles to get good knowledge on Oracle Partitioning. SNO Article Name Article Link 1 Partition https://www.ktexperts.com/partition/ 2 Oracle : Partitioning using online Redefinition method which requires minimal downtime https://www.ktexperts.com/oracle-partitioning-using-online-redefinition-method-which-requires-minimal-downtime/ 3 Oracle : High Level steps for Partitioning a non… Read More
Implementing Interval Partition with Enable Row Movement In this article , we will learn how to implement interval partition with enable row movement. Table Creation with interval partition (without interval Row Movement. Create user
|
1 2 3 |
SYS>>grant dba to ajay identified by ajay; Grant succeeded. |
Now create table with interval partition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SYS>>CREATE TABLE AJAY.MIC_INS_DD ( ID VARCHAR2(32 CHAR) NOT NULL, NAME VARCHAR2(35 CHAR) NOT NULL, KEY VARCHAR2(30 CHAR) NOT NULL, MIC_TAG_ID DATE default (to_date('01-01-1999', 'MM-DD-YYYY')) ) TABLESPACE MIC_DATA RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( MAXSIZE UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) PARTITION BY RANGE (MIC_TAG_ID) INTERVAL( NUMTODSINTERVAL(1, 'DAY')) ( PARTITION VALUES LESS THAN (TO_DATE('01-01-1999', 'MM-DD-YYYY', 'NLS_CALENDAR=GREGORIAN')) LOGGING NOCOMPRESS TABLESPACE MIC_DATA PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 8M NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; |
Now try to… Read More
PARTITIONING PARTITIONS : Dividing the large tables into small tables known as partitions Partition of single table can go in two different tablespaces Sorting can be done by partitioning thus we not require temporary tablespace Partition can be independently managed Backup and recovery… Read More
spool DEPT_NonPart_to_Part.logset timing onset time onset echo on conn SCOTT/PW –> Very Importantshow user –> Create DEPT_PART table (with partitions) EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name = ‘DEPT’; EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT_PART’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name =… Read More
Please check my upcoming articles (Tomorrow and Day after Tomorrow) for detailed steps with examples.
