This article shows the table partition management and also how to export and import the table partitions.
Hope it helps ...
--SRI
SQL> CREATE TABLE "SH"."PART_TABLE"
2 ( "PARAMETER" VARCHAR2(32) NOT NULL ENABLE,
3 "TIMESTAMP" NUMBER NOT NULL ENABLE,
4 "VALUESUM" NUMBER NOT NULL ENABLE,
5 "VALUECOUNT" NUMBER DEFAULT 1 NOT NULL ENABLE,
6 CONSTRAINT "PK_INDEX01" PRIMARY KEY ("PARAMETER", "TIMESTAMP") ENABLE,
7 CONSTRAINT "VALUECOUNT_POS" CHECK ( valuecount > 0 ) ENABLE
8 ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
9 TABLESPACE "TS1"
10 STORAGE(
11 BUFFER_POOL DEFAULT)
12 PCTTHRESHOLD 50
13 PARTITION BY RANGE ("TIMESTAMP")
14 (PARTITION "PART_1" VALUES LESS THAN (1277596800001)
15 PCTFREE 10 INITRANS 2 MAXTRANS 255
16 STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18 TABLESPACE "TS1" );
Table created.
SQL> select segment_name,tablespace_name from dba_segments where segment_name='PART_TABLE';
no rows selected
SQL> select partition_name,tablespace_name from dba_segments where segment_name='PART_TABLE';
no rows selected
SQL> select table_name,tablespace_name from dba_tables where table_name='PART_TABLE';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART_TABLE
SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name='PART_1';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART_1
SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name='TS1';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT STATUS
------------------------------ ------------------------------ ------------------ --------
PK_INDEX01 PART_1 0 USABLE
SQL> select owner,table_name,index_name,status from dba_indexes where index_name='PK_INDEX01';
OWNER TABLE_NAME INDEX_NAME STATUS
---------- ------------------------------ ------------------------------
SH PART_TABLE PK_INDEX01 N/A
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
SQL> select segment_type,count(1) from dba_segments where tablespace_name='TS1' group by segment_type;
SEGMENT_TYSH COUNT(1)
------------------ ----------
INDEX PARTITION 1
SQL> ALTER TABLE SH.PART_TABLE ADD
2 PARTITION "PART_2" VALUES LESS THAN (1282910400001)
3 PCTFREE 10 INITRANS 2 MAXTRANS 255
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "TS1" ;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
PART_2
SQL> select segment_type,count(1) from dba_segments where tablespace_name='TS1' group by segment_type;
SEGMENT_TYSH COUNT(1)
------------------ ----------
INDEX PARTITION 2
Insert some data
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
700257971
$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n
Export: Release 10.2.0.4.0 - Production on Thu Aug 26 07:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported
About to export sSHcified tables via Conventional Path ...
. . exporting table PART_TABLE
. . exporting partition PART_2
...........................................................................
.....
809310 rows exported
Export terminated successfully without warnings.
SQL> alter table SH.PART_TABLE drop partition PART_2;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
699448661
SQL> alter table SH.PART_TABLE add
2 PARTITION "PART_2" VALUES LESS THAN (1282910400001)
3 PCTFREE 10 INITRANS 2 MAXTRANS 255
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "TS1" ;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
PART_2
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
699448661
$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none
Export: Release 10.2.0.4.0 - Production on Thu Aug 26 07:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SH, not by you
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH's objects into SH
. . importing partition "PART_TABLE":"PART_2"
...........................................................................
.....
809310 rows imported
Import terminated successfully without warnings.
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
700257971
2 ( "PARAMETER" VARCHAR2(32) NOT NULL ENABLE,
3 "TIMESTAMP" NUMBER NOT NULL ENABLE,
4 "VALUESUM" NUMBER NOT NULL ENABLE,
5 "VALUECOUNT" NUMBER DEFAULT 1 NOT NULL ENABLE,
6 CONSTRAINT "PK_INDEX01" PRIMARY KEY ("PARAMETER", "TIMESTAMP") ENABLE,
7 CONSTRAINT "VALUECOUNT_POS" CHECK ( valuecount > 0 ) ENABLE
8 ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
9 TABLESPACE "TS1"
10 STORAGE(
11 BUFFER_POOL DEFAULT)
12 PCTTHRESHOLD 50
13 PARTITION BY RANGE ("TIMESTAMP")
14 (PARTITION "PART_1" VALUES LESS THAN (1277596800001)
15 PCTFREE 10 INITRANS 2 MAXTRANS 255
16 STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18 TABLESPACE "TS1" );
Table created.
SQL> select segment_name,tablespace_name from dba_segments where segment_name='PART_TABLE';
no rows selected
SQL> select partition_name,tablespace_name from dba_segments where segment_name='PART_TABLE';
no rows selected
SQL> select table_name,tablespace_name from dba_tables where table_name='PART_TABLE';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART_TABLE
SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name='PART_1';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART_1
SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name='TS1';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT STATUS
------------------------------ ------------------------------ ------------------ --------
PK_INDEX01 PART_1 0 USABLE
SQL> select owner,table_name,index_name,status from dba_indexes where index_name='PK_INDEX01';
OWNER TABLE_NAME INDEX_NAME STATUS
---------- ------------------------------ ------------------------------
SH PART_TABLE PK_INDEX01 N/A
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
SQL> select segment_type,count(1) from dba_segments where tablespace_name='TS1' group by segment_type;
SEGMENT_TYSH COUNT(1)
------------------ ----------
INDEX PARTITION 1
SQL> ALTER TABLE SH.PART_TABLE ADD
2 PARTITION "PART_2" VALUES LESS THAN (1282910400001)
3 PCTFREE 10 INITRANS 2 MAXTRANS 255
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "TS1" ;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
PART_2
SQL> select segment_type,count(1) from dba_segments where tablespace_name='TS1' group by segment_type;
SEGMENT_TYSH COUNT(1)
------------------ ----------
INDEX PARTITION 2
Insert some data
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
700257971
$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n
Export: Release 10.2.0.4.0 - Production on Thu Aug 26 07:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported
About to export sSHcified tables via Conventional Path ...
. . exporting table PART_TABLE
. . exporting partition PART_2
...........................................................................
.....
809310 rows exported
Export terminated successfully without warnings.
SQL> alter table SH.PART_TABLE drop partition PART_2;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
699448661
SQL> alter table SH.PART_TABLE add
2 PARTITION "PART_2" VALUES LESS THAN (1282910400001)
3 PCTFREE 10 INITRANS 2 MAXTRANS 255
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "TS1" ;
Table altered.
SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='PART_TABLE' and PARTITION_NAME like 'PART_%' ORDER BY PARTITION_NAME;
PARTITION_NAME
------------------------------
PART_1
PART_2
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
699448661
$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none
Export: Release 10.2.0.4.0 - Production on Thu Aug 26 07:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SH, not by you
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH's objects into SH
. . importing partition "PART_TABLE":"PART_2"
...........................................................................
.....
809310 rows imported
Import terminated successfully without warnings.
SQL> select count(1) from SH.PART_TABLE;
COUNT(1)
----------
700257971
Hope it helps ...
--SRI
Post a Comment
Thank you for visiting our site and leaving your valuable comment.