For a range interval partitioned table, you can specify multiple tablespaces like:
CREATE TABLE range_part_interval_table(col1 NUMBER, col2 NUMBER)
PARTITION BY RANGE (col1)
INTERVAL (10) STORE IN (ts2, ts3, ts4)
(PARTITION VALUES LESS THAN (100) TABLESPACE ts1);
But I can't find where the interval tablespaces are stored in the data dictionary (here, ts2, ts3, ts4). Is this information available somewhere?
This should get you the info you need:
SELECT table_owner, table_name, partition_name, tablespace_name
FROM dba_tab_partitions
WHERE table_name = <table_name>;
The following is the table description:
%> desc dba_tab_partitions
Name Null Type
------------------------------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG()
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(18)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
Your interval partitions don't exist until you have some data in them....
SQL> CREATE TABLE range_part_interval_table(col1 NUMBER, col2 NUMBER)
2 PARTITION BY RANGE (col1)
3 INTERVAL (10) STORE IN (ts2, ts3, ts4)
4 (PARTITION VALUES LESS THAN (100) TABLESPACE ts1);
Table created.
SQL> SELECT table_owner, table_name, partition_name, tablespace_name
2 FROM dba_tab_partitions
3 WHERE table_name = 'RANGE_PART_INTERVAL_TABLE'
4 /
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
----------- ------------------------------ -------------- ---------------
APC RANGE_PART_INTERVAL_TABLE SYS_P55 TS1
SQL>
The empty table has only the defined partition. But if we insert some data for different intervals....
SQL> insert into range_part_interval_table values (90, 8888)
2 /
1 row created.
SQL> insert into range_part_interval_table values (110, 8888)
2 /
1 row created.
SQL> insert into range_part_interval_table values (310, 8888)
2 /
1 row created.
SQL> insert into range_part_interval_table values (120, 8888)
2 /
1 row created.
SQL> SELECT table_owner, table_name, partition_name, tablespace_name
2 FROM dba_tab_partitions
3 WHERE table_name = 'RANGE_PART_INTERVAL_TABLE'
4 /
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
----------- ------------------------------ -------------- ---------------
APC RANGE_PART_INTERVAL_TABLE SYS_P58 TS2
APC RANGE_PART_INTERVAL_TABLE SYS_P55 TS1
APC RANGE_PART_INTERVAL_TABLE SYS_P56 TS4
APC RANGE_PART_INTERVAL_TABLE SYS_P57 TS3
SQL>
I've only know so far the following: SELECT dbms_metadata.get_ddl('TABLE', 'T') FROM dual;
Did you find better?