Re: Merge last partition -- is it possible?

Wow, I'm impressed. Fantastic -- thanks a lot!!!!

Domenic.

"Oracle Help" <oraclehelp_at_dbdomain.cc> wrote in message news:<Wai7d.626$Vm1.252_at_newsread3.news.atl.earthlink.net>...
> 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.
Received on Sat Oct 02 2004 - 10:04:10 CDT