You can use online redefinition to copy nonpartitioned Collection Tables to partitioned Collection Tables and Oracle Database inserts rows into the appropriate partitions in the Collection Table. Example 4-30 illustrates how this is done for nested tables inside an Objects column; a similar example works for Ordered Collection Type Tables inside an XMLType table or column. During the copy_table_dependents operation, you specify 0 or false for copying the indexes and constraints, because you want to keep the indexes and constraints of the newly defined collection table. However, the Collection Tables and its partitions have the same names as that of the interim table (print_media2 in Example 4-30). You must take explicit steps to preserve the Collection Table names.
Example 4-30 Redefining partitions with collection tables
CONNECT / AS SYSDBA
DROP USER eqnt CASCADE;
CREATE USER eqnt IDENTIFIED BY eqnt;
GRANT CONNECT, RESOURCE TO eqnt;
-- Grant privleges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO eqnt;
GRANT ALTER ANY TABLE TO eqnt;
GRANT DROP ANY TABLE TO eqnt;
GRANT LOCK ANY TABLE TO eqnt;
GRANT CREATE ANY TABLE TO eqnt;
GRANT SELECT ANY TABLE TO eqnt;
-- Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO eqnt;
GRANT CREATE ANY INDEX TO eqnt;
CONNECT eqnt/eqnt
CREATE TYPE textdoc_typ AS OBJECT ( document_typ VARCHAR2(32));
/
CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
/
-- (old) non partitioned nested table
CREATE TABLE print_media
( product_id NUMBER(6) primary key
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab
( (document_typ NOT NULL)
STORAGE (INITIAL 8M)
)
;
-- Insert into base table
INSERT INTO print_media VALUES (1,
textdoc_tab(textdoc_typ('xx'), textdoc_typ('yy')));
INSERT INTO print_media VALUES (11,
textdoc_tab(textdoc_typ('aa'), textdoc_typ('bb')));
COMMIT;
-- Insert into nested table
INSERT INTO TABLE
(SELECT p.ad_textdocs_ntab FROM print_media p WHERE p.product_id = 11)
VALUES ('cc');
SELECT * FROM print_media;
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
1 TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))
11 TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))
-- Creating partitioned Interim Table
CREATE TABLE print_media2
( product_id NUMBER(6)
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab2
( (document_typ NOT NULL)
STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);
EXEC dbms_redefinition.start_redef_table('eqnt', 'print_media', 'print_media2');
DECLARE
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('eqnt', 'print_media', 'print_media2',
0, true, false, true, false,
error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
EXEC dbms_redefinition.finish_redef_table('eqnt', 'print_media', 'print_media2');
-- Drop the interim table
DROP TABLE print_media2;
-- print_media has partitioned nested table here
SELECT * FROM print_media PARTITION (p1);
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
1 TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))
SELECT * FROM print_media PARTITION (p2);
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
11 TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))