Not even via DBMS_REDEFINITION? Well then, I guess the Oracle9i document
about DBMS_REDEFINITION is wrong. ;-)
Of course you do have to create a new table as part of DBMS_REDEFINITION,
but depending on your point of view, it's possible to partition an existing
table, even when it's online.
Here's an excerpt from Oracle9i documentation:
==============================================
The steps in this redefinition are illustrated below.
1) Verify that the table is a candidate for online redefinition.
-------------------------------------------------------------
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
dbms_redefinition.cons_use_pk);
END;
/
2) Create an interim table hr.int_admin_emp.
-------------------------------------------------------------
CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
3) Start the redefinition process.
-------------------------------------------------------------
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/
4) Create any triggers, indexes and constraints on hr.int_admin_emp. During
the final step of redefinition, these are transferred back to the original
table. Any referential constraints involved on hr.int_admin_emp should be
disabled. You can define any grants associated with the interim table. These
replace the grants on the original table after the redefinition.
-------------------------------------------------------------
ALTER TABLE hr.int_admin_emp ADD CONSTRAINT admin_dept_fkey2
FOREIGN KEY (deptno) REFERENCES hr.departments (department_id);
ALTER TABLE hr.int_admin_emp MODIFY CONSTRAINT admin_dept_fkey2
DISABLE KEEP INDEX;
The disabled constraint, admin_dept_fkey2, will be enabled automatically as
part of the finish redefinition process and will then involve the newly
redefined admin_emp table.
5) Optionally, synchronize the interim table hr.int_admin_emp.
-------------------------------------------------------------
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
6) Complete the redefinition.
-------------------------------------------------------------
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/
The table hr.admin_emp is locked in the exclusive mode only for a small
window toward the end of this step. After this call the table hr.admin_emp
is redefined such that it has all the attributes of the hr.int_admin_emp
table.
7) Drop the interim table.
-------------------------------------------------------------
-----Original Message-----
From: siva
[mailto:oracledba-ezmlmshield-x30382523.[Email address protected]
Sent: Wednesday, June 29, 2005 8:26 AM
To: LazyDBA Discussion
Subject: Re: PARTITIONING A EXISTING TABLE
You can't partition existing table.
1.rename the table
2.You have to create a new table with partitioning option and create all
the constraints , indexes etc.
3. and transfer the data
"j" <oracledba-ezmlmshield-x91521124.[Email address protected]
06/29/2005 05:16 PM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
PARTITIONING A EXISTING TABLE
Dear All,
Is it possible to do a table partition on an existing table containing 50
lacs records. If yes, how can we do so.
Thanks and regards,
Jay
Notice: The information contained in this e-mail message and/or
attachments to it may contain confidential or privileged information. If
you are not the intended recipient, any dissemination, use, review,
distribution, printing or copying of the information contained in this
e-mail message and/or attachments to it are strictly prohibited. If you
have received this communication in error, please notify us by reply
e-mail or telephone and immediately and permanently delete the message and
any attachments. Thank you
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
**************************************************************************************
The information contained in this email is privileged and confidential information intended only for the use of the individual or entity named as recipient. If the reader is not the intended recipient, please be notified that any dissemination, distribution, or copy of this communication is strictly prohibited. If you have received this communication in error, please do not disclose this communication to any other person. Please notify us immediately by telephone and return the original to us at the address indicated above. Thank you
Oracle LazyDBA home page