Database Partition Systems – Cluster Architecture

CREATE PARTITION AND TABLE SPACES:

CREATE BIGFILE TABLESPACE bigtbs_00
DATAFILE ‘/u01/imig/clusterdb/PARTITION_0.dat’
SIZE 250M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_01
DATAFILE ‘/u01/imig/clusterdb/PARTITION_1.dat’
SIZE 250M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_02
DATAFILE ‘/u01/imig/clusterdb/PARTITION_2.dat’
SIZE 250M AUTOEXTEND ON;

CREATE SAMPLE TABLE FOR DEMO :

CREATE TABLE SAMPLE_DEMO (
CHUNK_ID NUMBER NOT NULL,
NAME VARCHAR2(30) NOT NULL,
DESCRIPTION VARCHAR2(4000),
PARTITION_ID NUMBER GENERATED ALWAYS AS         (MOD(TO_NUMBER(“CHUNK_ID”),3)) VIRTUAL
)
PARTITION BY LIST (PARTITION_ID)
(
PARTITION PARTITION_0 VALUES (0) TABLESPACE  bigtbs_00,
PARTITION PARTITION_1 VALUES (1) TABLESPACE  bigtbs_01,
PARTITION PARTITION_2 VALUES(2) TABLESPACE  bigtbs_02
);

INSERT SAMPLE VALUES TO TABLE :

INSERT INTO SAMPLE_DEMO VALUES(12,’Pepe Ortiz’,’Demo Execution 1′,default);
INSERT INTO SAMPLE_DEMO VALUES(13,’Ashoka Bhat’,’Demo Execution 2′,default);
INSERT INTO SAMPLE_DEMO VALUES(14,’Shivakumar’,’Demo Execution 3′,default);

RETRIEVE THE VALUE FROM TABLE :

SELECT * FROM SAMPLE_DEMO

VIEW THE LIST OF AVAILABLE TABLESPACES :

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;

select * from database_properties where property_name like ‘%TABLESPACE’;

select * from user_part_tables;

DROP TABLE SPACES AND PARTITIONS :

DROP TABLESPACE bigtbs_00
INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE bigtbs_01
INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE bigtbs_02
INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE PARTITION_0
INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE PARTITION_1
INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE PARTITION_2
INCLUDING CONTENTS AND DATAFILES;