global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.
create index idx_fk_opt_run_case on opt_run (case_id) local;
Should do the trick.
If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.
an example:
RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
); 2 3 4 5 6 7 8
Table created.
RONR SQL>create unique index pk_parent
on parent (id) local;
2
Index created.
RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX; 2 3 4
Table altered.
RONR SQL>create table child
( parent number not null
, y varchar2(10)
,
constraint fk_p_c
foreign key (parent) references parent(id))
partition by reference (fk_p_c); 2 3 4 5 6 7
Table created.
RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.
RONR SQL>
1 row created.
RONR SQL>select * from parent;
ID X
---------- ----------
1 boe
2 oeps
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (2,'ppp');
1 row created.
RONR SQL>select * from child;
PARENT Y
---------- ----------
1 ggg
1 ggg
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_1
PARENT PAR_2
CHILD PAR_1
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_1 USABLE
PK_PARENT PAR_2 USABLE
RONR SQL>alter table parent drop partition par_1;
Table altered.
RONR SQL>select * from parent;
ID X
---------- ----------
2 oeps
RONR SQL>select * from child;
PARENT Y
---------- ----------
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_2
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_2 USABLE
I see no unusable index.
What I do see is that I can drop a parent partition and this cascades to the child partition.
does this help?
Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup)
After the clone, the skipped tablespaces have datafiles with status RECOVER.
see RMAN DUPLICATE DATABASE: Options
In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.
It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.
Best Answer
Just set the "long" in the sql sheet window big enough to hold the desired ddl.
Example:
-- Then just hit Run Script button or press f5.