How do I invalidate a table in Oracle 11g on purpose?

Question

I'm writing a small query to find invalid tables in Oracle :

select * from user_tables where status != 'VALID'

For testing, I thought it would be good to create a table and invalidate it on purpose. Is there a way to do this?

Invalidating a view is easy, just drop one of the underlying tables.

Any hint welcome.

Answer 1

You won't see status INVALID in user_tables, however, you would see that in [USER|ALL|DBA]_OBJECTS view.

One simple way is to create the table using an object type, and force the object type attribute to invalidate.

For example,

SQL> CREATE OR REPLACE TYPE mytype AS OBJECT(col1 VARCHAR2(10))
  2  /

Type created.

SQL>
SQL> CREATE TABLE t(col1 NUMBER,col2 mytype)
  2  /

Table created.

SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name='T'
  2  /

OBJECT_NAME OBJECT_TYPE             STATUS
----------- ----------------------- -----------
T           TABLE                   VALID

SQL>

So, the table is now in VALID status. Let's make it INVALID:

SQL> ALTER TYPE mytype ADD ATTRIBUTE col2 NUMBER INVALIDATE
  2  /

Type altered.

SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name='T'
  2  /

OBJECT_NAME OBJECT_TYPE             STATUS
----------- ----------------------- -----------
T           TABLE                   INVALID

SQL>