CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
Moribundus
Moribundus

Reputation: 71

Create Table with CTAS and Partition

i have created a table per CTAS and PARTITION, looks all good but not partition has any rows. can someone tell what i am doing wrong?

table tickets:

region number(1),
ticket_number varchar2(10)

and then the CTAS with partition and index

CREATE TABLE tickets2 
PARTITION BY list (region)
 (
    PARTITION R1 VALUES (1),
    PARTITION R2 VALUES (2),
    PARTITION R3 VALUES (3),
    PARTITION RX VALUES (DEFAULT)
   ) AS
SELECT * FROM tickets
;
CREATE INDEX idx1 ON tickets2 (region, ticket_number) NOLOGGING

all runs without any problems or errors but if i checked the partitions in TOAD under Table Partitions there are no rows for the partitions.

Upvotes: 0

Views: 575

Answers (1)

chandru
chandru

Reputation: 46

Did you try running this command and see ?

select * from user_tab_partitions where table_name = 'tickets'; 

select count(*) from tickets partition (r1);
select count(*) from tickets partition (r2);
select count(*) from tickets partition (r3);
select count(*) from tickets partition (rx);

This command should display the rows in each partitions created for the table.

Upvotes: 1

Related Questions