ORA-12986 troubleshooting

Today in the deletion of a large table column, accidentally DBA restart DB, and then execute the query, it reported the ORA-12986 error, first come to understand this error:

ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

Cause: An attempt was made to access a table with columns in partially dropped state (I. e., drop column operation was interrupted ).

Action: Submit alter table drop columns continue to complete the drop column operation before accessing the table.

We can see that the cause of this error is that you are deleting the column, but when you delete the column halfway, the process is unexpectedly terminated, resulting in the table being partially deleted, so what error is reported? All you need to do is to continue deleting. See the following example:

1. Set the column to unused

Alter table test1 set unused column Tablespace_name;

2. Delete the unused Column

Alter table test1 drop unused column checkpoint 5;

Before the execution is completed, use shutdown abort to forcibly close the database. (If shutdown immediate is used, the database will not be closed until the execution is completed)

3. Restart the database and view the test1 table. An error is returned.

4. Continue to delete unfinished Columns

Alter table test1 drop columns continue

5. After the execution is completed, query the test1 table again.

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store