daily pastebin goal
9%
SHARE
TWEET

Untitled

a guest Apr 21st, 2017 47 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- PARTITIONING - MADE A NEW TABLE FOR EACH PARTITION, ADDED THE SAME DATA TO IT
  3. -- PARTITION BY RANGE
  4. -- ADD THIS PART TO THE THE S_STAGE OR S_STUDENT TABLE, WHICHEVER ONE HAS A COL FOR
  5. -- REMAINING_PLACES. LOOK AT THE BELOW PARTITIONS FOR EXAMPLE
  6. PARTITION BY RANGE (remaining_places)
  7. (
  8. partition oneleft values less than (2),
  9. partition twoleft values less than (3),
  10. partition threeleft values less than (4),
  11. partition fourleft values less than (5),
  12. partition fiveleft values less than (6),
  13. partition sixleft values less than (7),
  14. partition sevenleft values less than (8),
  15. partition eighteft values less than (9),
  16. partition nineleft values less than (10),
  17. partition tenleft values less than (11),
  18. partition elevenleft values less than (12)
  19. );
  20.  
  21.  
  22. -- HASH PARTITION
  23. create table programme_hash
  24. (
  25. prog_code varchar2(5) not null,
  26.  prog_name varchar2(34),
  27.  course_chairperson varchar2(25),
  28.  total_s_students integer default 0,
  29. primary key (prog_code)
  30. )
  31. PARTITION BY HASH(prog_name)
  32. PARTITIONS 3 STORE IN (TS01, TS02, TS03)
  33. enable row movement
  34. ;
  35.  
  36. -- LIST PARTITION
  37. create table s_student_list
  38. (
  39. s_studentNo varchar2(9) not null,
  40. prog_code varchar2(5) not null,
  41. stage_code integer not null,
  42. studentname varchar2(40),
  43. studentAddress varchar2(60),
  44. primary key (s_studentNo),
  45. foreign key (prog_code,stage_code) references s_stage(prog_code,stage_code)
  46. )
  47. partition by list(prog_code)
  48. (
  49. partition dt228_students values ('DT228'),
  50. partition dt211_students values ('DT211'),
  51. partition dt249_students values ('DT249')
  52. );
  53.  
  54.  
  55. -- COMBO - LIST AND HASH
  56. create table s_student_list_hash
  57. (
  58. s_studentNo varchar2(9) not null,
  59. prog_code varchar2(5) not null,
  60. stage_code integer not null,
  61. studentname varchar2(40),
  62. studentAddress varchar2(60),
  63. primary key (s_studentNo),
  64. foreign key (prog_code,stage_code) references s_stage(prog_code,stage_code)
  65. )
  66. PARTITION BY LIST (prog_code)
  67.  
  68. subpartition BY HASH(studentname)
  69. subPARTITIONS 3 STORE IN (TS01, TS02, TS03)
  70. (
  71. partition dt228_students values ('DT228'),
  72. partition dt211_students values ('DT211'),
  73. partition dt249_students values ('DT249')
  74. )ENABLE ROW MOVEMENT;
  75.  
  76.  
  77. -- TESTING PARTITIONS - ENTER PARTITION TABLE NAME TO 'TABLE_NAME'
  78. select * from all_part_tables where table_name='S_STUDENT_LIST';
  79. select * from all_TAB_partitions where table_name='S_STUDENT_LIST';
  80. select * from all_part_key_columns where name='S_STUDENT_LIST';;
  81.  
  82.  
  83.  
  84. -- RECOVERY PART
  85. alter system set log_checkpoint_interval=0;
  86. alter system set log_checkpoint_timeout=0;
  87. alter system set fast_start_mttr_target=0;
  88. alter system set fast_start_IO_target=0;
  89.  
  90. CREATE table TEST_ALL_OBJECTS as select * from all_objects;
  91.  
  92. select * from v$instance_recovery;
  93. commit;
  94.  
  95. drop table test_all_objects;
RAW Paste Data
Top