Partitioning Tables  «Prev 

Partitioning a table with LOB data

  1. We have started the CREATE TABLE command for you. This is a table for storing a historical library of music. Type in the last column definition, the BLOB column, in the open line:
    MUSIC BLOB)
  2. We have added the storage line for the table, and now you must enter the storage line for the LOB. Type this information and press Enter:
    LOB (MUSIC) STORE AS (CHUNK 4096)
  3. Now, just like the other types of partitioned tables, you specify that you will be creating range partitioning based on a certain column. In this example, the partitions are divided by date. Type in the following information and press Enter:
    PARTITION BY RANGE(PUBLISH_DATE)
  4. This line begins the definition of the first partition. For our example, the oldest music will be stored in the ELVIS_ERA partition. Type in the following information and press Enter:
    (PARTITION ELVIS_ERA
  5. The ELVIS_ERA partition will store music that was published earlier than 1960. To specify the values of the publish date for the partition, type this line and press Enter:
    VALUES LESS THAN (TO_DATE('01-JAN-1960', 'DD-MON-YYYY'))
  6. Let's assume that we have set up some special tablespaces just for this table. One of them is called ELVIS_TS, and it is going to store all the table data (except the LOB) for the ELVIS_ERA partition. Tell Oracle to use this tablespace by typing the following line and pressing Enter:
    TABLESPACE ELVIS_TS
    
  7. Another special tablespace, called ELVIS_MUSIC_TS was created earlier to store the LOB data related to the ELVIS_ERA partition. Tell Oracle to use this tablespace by typing the following line and pressing Enter:
    LOB (MUSIC) store as (TABLESPACE ELVIS_MUSIC_TS),
  8. The comma at the end of the previous line tells us that the definition of the first partition is now complete. Begin the second partition definition now. This partition is called BEATLES_ERA and stores music of the 1960s. Type the following line and press Enter:
    PARTITION BEATLES_ERA
  9. We have filled in the remaining specifications for the BEATLES_ERA partition. Notice the tablespace names in this partition. Now begin the third and final partition definition. This partition is called BEEGEES_ERA and stores music of the 1970s and beyond. Type the following line and press Enter:
    PARTITION BEEGEES_ERA
  10. We have filled in the remaining specifications for the BEEGEES_ERA partition. The closing parenthesis at the end of the partition definition marks the final partition. Execute the command by typing a forward slash (/) and pressing Enter.
  11. You have successfully created a partitioned table containing LOB data.