Re: Create Partitioned Table

From: Marc Blum <marc_at_marcblum.de>
Date: Fri, 19 Jul 2002 21:25:08 +0200
Message-ID: <krpgju49urudt7uo5csm890dfncghiupb9@4ax.com>

On 19 Jul 2002 11:44:56 -0700, nilanjan_sarkar_at_hotmail.com (nilanjan) wrote:

>Oracle Guru's :
>
>Is there a way to CREATE a PARTITIONED table from a non-partitioned
>one using the CREATE TABLE ..AS SELECT.. syntax ? I couldnt find it in
>the manuals..
>
>( the few tables in question are about 20 Gigs in size each )

sure:

SQL> CREATE TABLE t
  2 AS
  3 SELECT ROWNUM AS ROW#,text
  4 FROM dba_source;

Table created.

SQL>
SQL> DESC t

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ROW#                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL>
SQL> CREATE TABLE t_part
  2 PARTITION BY RANGE (ROW#)
  3 (

  4  PARTITION p1000 VALUES LESS THAN (1001),
  5  PARTITION p2000 VALUES LESS THAN (1002),
  6  PARTITION p3000 VALUES LESS THAN (1003),

  7 PARTITION powerflow VALUES LESS THAN (MAXVALUE)   8 )
  9 AS
 10 SELECT * FROM t;

Table created.

SQL>
SQL> DESC t_part

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ROW#                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL>
SQL> SELECT table_name,partition_name
  2 FROM dba_tab_partitions
  3 WHERE table_name = 'T_PART';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_PART                         P2000
T_PART                         P3000
T_PART                         POWERFLOW
T_PART                         P1000

SQL>
SQL> Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Fri Jul 19 2002 - 14:25:08 CDT