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;