Re: Merge last partition -- is it possible?

Hi,

The following script shows how to use the MERGE PARTITION and EXCHANGE PARTITION clauses of the ALTER TABLE command to accomplish your goal. Note that this script avoids the work necessary to deal with related indexes, etc. of the partitioned table.

-- 
Hope this helps!
oraclehelp_at_dbdomain.cc
The Database Domain (http://www.dbdomain.com)
Online and CD Training for Oracle DBAs
Oracle Certification Training (OCA and OCP)

-- example script and output --
-- create a range-partitioned version of an orders table

CREATE TABLE orders (
id INTEGER
CONSTRAINT orders_pk
PRIMARY KEY,
c_id INTEGER NOT NULL
CONSTRAINT orders_customers_fk
REFERENCES customers,
orderdate DATE DEFAULT SYSDATE NOT NULL,
shipdate DATE,
paiddate DATE,
status CHAR(1) DEFAULT 'F')
PARTITION BY RANGE (orderdate) (
PARTITION beforejun2001
VALUES LESS THAN (TO_DATE('07-01-2001','MM-DD-YYYY')),
PARTITION afterjun2001
VALUES LESS THAN (MAXVALUE) );

Table created.

-- insert a row into the partitioned table for testing

INSERT INTO orders
VALUES (1,1,'18-JUN-02','18-JUN-02','30-JUN-02','F');

1 row created.

-- merge all table partitions into one

ALTER TABLE orders
MERGE PARTITIONS beforejun2001, afterjun2001
INTO PARTITION merged;

Table altered.

-- rename the original table

RENAME orders TO orders_partitioned;

Table renamed.

-- create a non-partitioned version of the orders table

CREATE TABLE orders (
id INTEGER
CONSTRAINT orders_pknew
PRIMARY KEY,
c_id INTEGER NOT NULL
CONSTRAINT orders_customers_fknew
REFERENCES customers,
orderdate DATE DEFAULT SYSDATE NOT NULL,
shipdate DATE,
paiddate DATE,
status CHAR(1) DEFAULT 'F');

Table created.

-- note that there are no rows in the new orders table

-- query the data dictionary to get some info about the data segments of 
interest

SELECT segment_name, partition_name, segment_type FROM user_segments;

SEGMENT_NAME            PARTITION_NAME        SEGMENT_TYPE
ORDERS 
TABLE
ORDERS_PARTITIONED MERGED                          TABLE PARTITION

... rows selected.

-- exchange the partition segment of the partitioned table with segment of 
the non-partitioned table

ALTER TABLE orders_partitioned

EXCHANGE PARTITION merged WITH TABLE orders;

Table altered.

-- query the orders table to prove that the segments were exchanged

SELECT * FROM orders;

ID C_ID ORDERDATE SHIPDATE PAIDDATE S

1 1 18-JUN-02 18-JUN-02 30-JUN-02 F



"Domenic" <domenicg_at_hotmail.com> wrote in message 
news:c7e08a19.0410010627.10098004_at_posting.google.com...


>I have a table that's partitioned that really shouldn't be

> partitioned.  I've merged the range partitions, two at a time into the

> higher ranging partition and now there is one partition left (ie ...

> values less than maxvalue).

>

> Can this partition now be eliminated? -- ie, converted into a regular

> table while preserving the rows, or does the table have to be

> re-created?

>

> I've gone through the syntax wire diagrams and can't find any syntax

> to "remove" the last partition and undo partitioning completely.

>

> Thanks in advance .../Domenic.