Alter Index Rebuild to Another Tablespace
Rebuild Index in Oracle To move an index is to rebuild a whole index either in the original tablespace or another tablespace with some options… Read More »Alter Index Rebuild to Another Tablespace
User and Schema
How to Resolve ORA-00980: synonym translation is no longer valid
ORA-00980 We usually use synonyms to point to a base object that we don't own it. Let's see a case. SQL> conn oe/oe@orclpdb1 Connected. SQL>… Read More »How to Resolve ORA-00980: synonym translation is no longer valid
How to Resolve ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ORA-02243 The root cause of ORA-02243 may vary in different operations, so the case in this post may not fit your situation. Tried to move… Read More »How to Resolve ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
How to Get DDL of Sequence from ALL_SEQUENCES
You don't have to export and import sequences by data pump, you can compose the creation statements by querying ALL_SEQUENCES: [oracle@primary01 oracle]$ vi compose_create_sequence.sql set… Read More »How to Get DDL of Sequence from ALL_SEQUENCES
How to Get DDL of Synonym from ALL_SYNONYMS
You don't have to export and import public synonyms by data pump, you can compose the creation statements by querying ALL_SYNONYMS:: [oracle@primary01 oracle]$ vi compose_create_or_replace_synonym.sql… Read More »How to Get DDL of Synonym from ALL_SYNONYMS
How to Check Oracle Table Creation Date
We usually retrieve table information from ALL_TABLES, but it has no table creation date. This is because table is also an object type, so we… Read More »How to Check Oracle Table Creation Date
How to Resolve ORA-01502 Error Message
ORA-01502: index or partition of such index is in usable state ORA-01502 means that the index that the optimizer want to use is in UNUSABLE… Read More »How to Resolve ORA-01502 Error Message
How to Resolve ORA-01439: column to be modified must be empty to change datatype
ORA-01439 ORA-01439 means that the data type of a column you want to change is not empty, you have to make it empty first, then… Read More »How to Resolve ORA-01439: column to be modified must be empty to change datatype
How to Resolve ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster
ORA-25188 There're some restrictions on using index-organized tables (IOT). This is one case of them. SQL> alter table ERPAPP.PAY_EXT_P drop constraint XM500P0; alter table ERPAPP.PAY_EXT_P… Read More »How to Resolve ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster
How to Change Column Order and Position
One developer reported that some statements like the following failed to complete. SQL> insert into finapp.invoices_history select * from finapp.invoices_month where invoice_date < sysdate -30;… Read More »How to Change Column Order and Position