Hi All ,
Could you help me with this scenario where we see indexes marked as unusable post our application migration to a higher version.
We have recently migrated our application to a higher version , which comprise of sql scripts ran against the DB .... which leave few indexes unusable and has to rebuilt manually.
Environment :
DB - 11.2.0.4
Tables - Non partitioned
[ select owner, table_name, partitioning_type, subpartitioning_type from all_part_tables ; .., SELECT * FROM dba_part_tables; ., SELECT * FROM dba_part_index; select partition_name from user_tab_partitions where table_name = 'xxxxxxxxxxxxxxx';]
To be precise , what the sql's consist and does is ...
- adds new columns to fewer tables
- adding indees if not exist
- add new constraint to table if it does not exist
- add new sequence if it does not exist
- create unique indexes
- creating foreign keys for all of the tables
Glance of Syntax's of the scripts used :
==============================
ALTER TABLE xxxxxxxxxxx
CREATE INDEX xxxxxxxxxxxx
ON xxxxxxxxxxxxxxx(
xxxxxxxxxxxxxx,
xxxxxxxxxxx,
xxxxxxxxxxxx
)TABLESPACE xxxxxxxxxxxxxxx;
ALTER TABLE xxxxxxxxxxxxx
ADD (CONSTRAINT xxxxxxxxxxxxxxxxxxxxx PRIMARY KEY (id)) ;
CREATE UNIQUE INDEX xxxxxxxxxxxxxxxxxxxx
ON xxxxxxxxx(
xxxxxxxxxxxxx,
xxxxxxxxxxxxxx
)TABLESPACE xxxxxxxxxxxxxx;
ALTER TABLE xxxxxxxxxxxxxxxx
ADD CONSTRAINT xxxxxxxxxxxxxxxxxxx FOREIGN KEY (xxxxxx)
REFERENCES xxxxx (id)
ON DELETE SET NULL;
ALTER TABLE xxxxxxxxxxxxxxx
ADD CONSTRAINT xxxxxxxxxxxxxxxxxx FOREIGN KEY (xxxxxxxxxxxxx)
REFERENCES xxxxxxxxxxxx(id)
ON DELETE CASCADE ;
Thanks