How would I create a table in Oracle 11g R2 from a SELECT AS and partition the table by range-list?

Question

I am trying to create a new table called Titles2 from an existing table called Titles. I have to use a SELECT AS statement to create the columns in Titles2 from Titles. I also have to partition Titles2 by RANGE and then by LIST.

The code used to create Titles:

SQL> CREATE TABLE Titles
  2    (
  3    Title_id         char(3)     ,
  4    Title            varchar2(40),
  5    Genre            varchar2(10),
  6    Pages            number      ,
  7    Price            number(5,2) ,
  8    Sales            number      ,
  9    Pub_id           char(3)     ,
 10    Pubdate          date        ,
 11    Advance          number(9,2) ,
 12    Royalty_rate     number(5,2) ,
 13    CONSTRAINT Titles_pk PRIMARY KEY (title_id),
 14    CONSTRAINT Titles_Publishers_fk FOREIGN KEY (Pub_id)
 15                             REFERENCES Publishers (pub_id)
 16    )
 17    PARTITION BY RANGE (Pubdate) (
 18    PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY')) TABLESPACE TSLab8ben1,
 19    PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) TABLESPACE TSLab8ben2,
 20    PARTITION P3 VALUES LESS THAN (MAXVALUE) TABLESPACE TSLab8ben3
 21    );

Table created.

The following code is what I have so far for creating a Titles2 table from Titles:

CREATE TABLE Titles2 AS
SELECT Title_id AS TID, Title, Genre, Sales, (Sales * Price) AS Revenue, Pub_id AS P#
FROM Titles
PARTITION BY RANGE (Revenue)
SUBPARTITION BY LIST (Genre)
SUBPARTITION TEMPLATE (
SUBPARTITION G1 VALUES ('history', 'biography'),
SUBPARTITION G2 VALUES ('computer','children'),
SUBPARTITION G3 VALUES (DEFAULT)) (
PARTITION P1 VALUES LESS THAN (100000),
PARTITION P2 VALUES LESS THAN (500000),
PARTITION P3 VALUES LESS THAN (1000000),
PARTITION P4 VALUES LESS THAN (MAXVALUE));

However, the code for creating Titles2 doesn't execute. Would anybody be able to help me get the Titles2 code to execute? Thanks!

Answer 1

Create table Titles2 using just create table statement(not CTAS) with appropriate partitions just like you did with Titles. And then use insert statement to populate Titles2 with data from Titles

insert into Titles2 
  select <<columns>>
    from Titles  

Or you can rewrite your last create table statement as follows:

CREATE TABLE Titles2
  PARTITION BY RANGE (Revenue)(
    SUBPARTITION BY LIST (Genre)
    SUBPARTITION TEMPLATE (
    SUBPARTITION G1 VALUES ('history', 'biography'),
    SUBPARTITION G2 VALUES ('computer','children'),
    SUBPARTITION G3 VALUES (DEFAULT)) (
    PARTITION P1 VALUES LESS THAN (100000),
    PARTITION P2 VALUES LESS THAN (500000),
    PARTITION P3 VALUES LESS THAN (1000000),
    PARTITION P4 VALUES LESS THAN (MAXVALUE))
   ) AS
SELECT Title_id AS TID
     , Title
     , Genre
     , Sales
     , (Sales * Price) AS Revenue
     , Pub_id AS P#
FROM Titles