Partitioned Tables in Oracle8.1.6 on HPUX11.0

[Reynaldo]

Hi
Does anybody know why when I create a partitioned table it doesnt belong to any tablespace.

export ORACLE_SID=objdev
sqlplus objdev/objdev
CREATE TABLE Hierarchy (ObjectId NUMBER(12) NOT NULL,ParentId NUMBER(12) NOT NULL,TypeCd VARCHAR2(8) NOT NULL,Category NUMERIC(3) NOT NULL,Path VARCHAR2(128) NULL,Name VARCHAR2(66) NULL,Flag NUMBER(1) NOT NULL) PARALLEL (DEGREE DEFAULT) NOLOGGING partition by range (category)
(partition PH values less than (194),
partition LOC values less than (196),
partition FE values less than (197));
ALTER TABLE Hierarchy ADD(PRIMARY KEY(ObjectId)) PARALLEL (DEGREE DEFAULT) NOLOGGING;

QL> select table_name,tablespace_name,owner,PARTITIONED from dba_all_tables where table_name ='HIERARCHY';

TABLE_NAME TABLESPACE_NAME OWNER PAR
HIERARCHY SYSTEM YES
HIERARCHY OBJDEV YES

Thanks in advance
Reynaldo

[Printaporn_1]

Hi,
Partition table can span in many tablespace.

see following statement.

CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE ( sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN ( 1998, 04, 01 )
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN ( 1998, 07, 01 )
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN ( 1998, 10, 01 )
TABLESPACE tsc,
PARTITION sales q4 VALUES LESS THAN ( 1999, 01, 01 )
TABLESPACE tsd);
--------------------
Creating a partitioned table is very similar to creating a table or index: you must use
the CREATE TABLE statement with the PARTITION by clause. Also, you must
specify the tablespace name for each partition.

[Steven Gillard_2 · Accepted solution]

That information is not store in the dba_tables view, have a look at dba_tab_partitions. It contains information about the table -> tablespaces relationships.

Cheers,
Steve