Partitioning – 1

--- CREATE TABLESPACE
CREATE TABLESPACE "USERS02" DATAFILE
 'C:\ORACLE\ORADATA\ORCL10\USERS02_1.DBF' SIZE 5242880
 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "USERS03" DATAFILE
 'C:\ORACLE\ORADATA\ORCL10\USERS03_1.DBF' SIZE 5242880
 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
--- PARTITIONED TABLE
CREATE TABLE CURSO.TESTE01
 (COD NUMBER,
 TEXTO VARCHAR2(100))
 PARTITION BY RANGE(COD)
 (PARTITION P1 VALUES LESS THAN (1) TABLESPACE USERS,
 PARTITION P2 VALUES LESS THAN (2) TABLESPACE USERS02,
 PARTITION P3 VALUES LESS THAN (3) TABLESPACE USERS,
 PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS02);
INSERT INTO CURSO.TESTE01 VALUES (0,'P1');
 INSERT INTO CURSO.TESTE01 VALUES (1,'P23');
 INSERT INTO CURSO.TESTE01 VALUES (3,'P23');
 INSERT INTO CURSO.TESTE01 VALUES (4,'P4');
 INSERT INTO CURSO.TESTE01 VALUES (99,'P99');
COMMIT;
--- CHECKING BLOCK AND PARTITIONS
SELECT
 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK,
 COD, TEXTO FROM CURSO.TESTE01;
--- CHECKING ROWS X PARTITIONS
select
 owner,
 segment_name     Name,
 segment_type     Tipo,
 block_id         c3,
 PARTITION_NAME,
 TABLESPACE_NAME
 from
 dba_extents
 where
 997 >= block_id
 and
 997 <= block_id+blocks AND SEGMENT_TYPE = 'TABLE PARTITION'
--- PARTITION BY HASH
CREATE TABLE CURSO.TESTE02
 (COD NUMBER, TEXTO VARCHAR2(100))
 PARTITION BY HASH (COD)
 PARTITIONS 2
 STORE IN (USERS,USERS02);
CREATE TABLE CURSO.TESTE03
 (COD NUMBER, TEXTO VARCHAR2(100))
 PARTITION BY HASH (COD)
 PARTITIONS 4
 STORE IN (USERS,USERS02);
 (USING 2 TABLESPACES)
--- PARTITION WITH LOB
CREATE TABLE curso.teste_lob
 ( idx NUMBER, txt CLOB )
 LOB ( txt ) STORE AS s_lob
 ( TABLESPACE users )
 PARTITION BY RANGE ( idx )
 ( PARTITION s_10 VALUES LESS THAN ( 10 )
 TABLESPACE users02
 LOB ( txt ) STORE AS st_10
 ( DISABLE STORAGE IN ROW
 TABLESPACE users )
 , PARTITION s_20 VALUES LESS THAN ( 20 )
 TABLESPACE users02
 ) ;
ALTER TABLE CURSO.TESTE01 ENABLE ROW MOVEMENT;
INSERT INTO CURSO.TESTE02 VALUES (0,'P1');
 INSERT INTO CURSO.TESTE02 VALUES (1,'P2');
 INSERT INTO CURSO.TESTE02 VALUES (2,'P3');
 INSERT INTO CURSO.TESTE02 VALUES (3,'P4');
COMMIT;
--- TEST WITH INDEX (LOCAL X GLOBAL)
-- LOCAL = PARTITIONED INDEX
 DROP INDEX CURSO.IX_TESTE01;
 CREATE INDEX CURSO.IX_TESTE01 ON CURSO.TESTE01(COD) TABLESPACE USERS
 LOCAL
 (PARTITION IX_01 TABLESPACE USERS,
 PARTITION IX_02 TABLESPACE USERS02,
 PARTITION IX_03 TABLESPACE USERS,
 PARTITION IX_04 TABLESPACE USERS02);
-- GLOBAL INDEX
 CREATE INDEX CURSO.IX_TESTE02 ON CURSO.TESTE01(texto) TABLESPACE USERS;
--- RENAMING PARTITION:
ALTER TABLE CURSO.TESTE02 RENAME PARTITION SYS_P21 TO HASH_01;
 ALTER TABLE CURSO.TESTE02 RENAME PARTITION SYS_P22 TO HASH_02;
--- MOVING PARTITION TO OTHER TABLESPACE
ALTER TABLE CURSO.TESTE01 MOVE PARTITION P2 TABLESPACE USERS;
--- MOVING INDEX PARTITION TO OTHER TABLESPACE
ALTER INDEX CURSO.IX_TESTE01 REBUILD PARTITION IX_02 TABLESPACE USERS;
--- TESTING ADD PARTITION
1.) ERROR TRYING ADDING A NEW PARTITION:
SQL> ALTER TABLE CURSO.TESTE01
 2  ADD PARTITION P4 VALUES LESS THAN (4) TABLESPACE USERS02;
 ADD PARTITION P4 VALUES LESS THAN (4) TABLESPACE USERS02
 *
 ERRO na linha 2:
 ORA-14074: limite da partição deve intercalar mais que o da última partição
2.) TRYING SPLIT PARTITION
SQL> ALTER TABLE CURSO.TESTE01
 2  split partition p99
 3  at (maxvalue) into
 4  (partition p4 ,
 5   partition p_max
 6  tablespace users);
 split partition p99
 *
 ERRO na linha 2:
 ORA-14080: partição não pode ser dividida ao longo do limite superior especificado
SOLUTION:
A.) EXPORT PARTITION
exp dbaprod/**** file=p99 tables=curso.teste01:p99 rows=y
B.) DROP PARTITION
alter table curso.teste01 drop partition p99;
C.) ADD NEW PARTITION
alter table curso.teste01 add partition p4 values less than (4)
 tablespace users02;
D.) CREATE AGAIN THE PARTITION
alter table curso.teste01 add partition p99 values less than (maxvalue)
 tablespace users02
E.) IMPORT
imp dbaprod/**** file=p99.DMP fromuser=curso touser=curso ignore=y rows=y indexes=n constraints=n grants=n
*** Merge | Coalesce ***
--Merge:
 alter table curso.teste01 merge partitions p2,p3 into partition p23;
-- COALESCE is usefull for HASH partitions the rows are equalized between the partitions
 Coalesce: ALTER TABLE PART COALESCE;
*** Split Partition
--- The partition P23 has values (1,2), divided in 2 parts:
ALTER TABLE curso.teste01 SPLIT
 PARTITION p23
 AT ( 2 ) INTO
 ( PARTITION p2
 , PARTITION p23
 TABLESPACE users ) ;
SQL> select * from curso.teste01 partition (p23);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 2 P3
 2 P3
SQL> select * from curso.teste01 partition (p2);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 1 P2
 1 P2
 1 P23
*** Merge Partitions
ALTER TABLE curso.teste01 MERGE
 PARTITIONS p2,p23
 INTO PARTITION p23
 TABLESPACE users;
select * from curso.teste01 partition (p23);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 1 P2
 1 P2
 1 P23
 2 P3
 2 P3
*** Exchange Partition
1.) the non-partitoned table must have the same structure
create table curso.exchange_table
 as
 select * from curso.teste01 where rownum<1;
2.)
ALTER TABLE curso.teste01
 EXCHANGE PARTITION p23
 WITH TABLE curso.exchange_table
 WITHOUT VALIDATION ;
3.)
ANALYZE TABLE curso.teste01
 PARTITION (p23)
 VALIDATE STRUCTURE
 INTO INVALID_ROWS ;
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