-- PARTITIONING - MADE A NEW TABLE FOR EACH PARTITION, ADDED THE SAME DATA TO IT
-- PARTITION BY RANGE
-- ADD THIS PART TO THE THE S_STAGE OR S_STUDENT TABLE, WHICHEVER ONE HAS A COL FOR
-- REMAINING_PLACES. LOOK AT THE BELOW PARTITIONS FOR EXAMPLE
PARTITION BY RANGE (remaining_places)
(
partition oneleft values less than (2),
partition twoleft values less than (3),
partition threeleft values less than (4),
partition fourleft values less than (5),
partition fiveleft values less than (6),
partition sixleft values less than (7),
partition sevenleft values less than (8),
partition eighteft values less than (9),
partition nineleft values less than (10),
partition tenleft values less than (11),
partition elevenleft values less than (12)
);


-- HASH PARTITION
create table programme_hash
(
prog_code varchar2(5) not null,
 prog_name varchar2(34),
 course_chairperson varchar2(25),
 total_s_students integer default 0,
primary key (prog_code)
)
PARTITION BY HASH(prog_name)
PARTITIONS 3 STORE IN (TS01, TS02, TS03)
enable row movement
;

-- LIST PARTITION
create table s_student_list
(
s_studentNo varchar2(9) not null,
prog_code varchar2(5) not null,
stage_code integer not null,
studentname varchar2(40),
studentAddress varchar2(60),
primary key (s_studentNo),
foreign key (prog_code,stage_code) references s_stage(prog_code,stage_code)
)
partition by list(prog_code)
(
partition dt228_students values ('DT228'),
partition dt211_students values ('DT211'),
partition dt249_students values ('DT249')
);


-- COMBO - LIST AND HASH
create table s_student_list_hash
(
s_studentNo varchar2(9) not null,
prog_code varchar2(5) not null,
stage_code integer not null,
studentname varchar2(40),
studentAddress varchar2(60),
primary key (s_studentNo),
foreign key (prog_code,stage_code) references s_stage(prog_code,stage_code)
)
PARTITION BY LIST (prog_code)

subpartition BY HASH(studentname)
subPARTITIONS 3 STORE IN (TS01, TS02, TS03)
(
partition dt228_students values ('DT228'),
partition dt211_students values ('DT211'),
partition dt249_students values ('DT249')
)ENABLE ROW MOVEMENT;


-- TESTING PARTITIONS - ENTER PARTITION TABLE NAME TO 'TABLE_NAME'
select * from all_part_tables where table_name='S_STUDENT_LIST';
select * from all_TAB_partitions where table_name='S_STUDENT_LIST';
select * from all_part_key_columns where name='S_STUDENT_LIST';;



-- RECOVERY PART
alter system set log_checkpoint_interval=0;
alter system set log_checkpoint_timeout=0;
alter system set fast_start_mttr_target=0;
alter system set fast_start_IO_target=0;

CREATE table TEST_ALL_OBJECTS as select * from all_objects;

select * from v$instance_recovery;
commit;

drop table test_all_objects;