| Invalid Table [message #169538] |
Thu, 27 April 2006 09:06  |
kjm0001
Messages: 11
Registered: February 2006
|
Junior Member |
|
|
|
I was trying to do a full db export and discovered some errors referencing exp-00056 and upon further investigation discovered ora-04063 errors on the tables...which I discovered that there was a column with no datatype which shows the table as being invalid after querying dba_objects...If one can not create a table without datatypes then how do these tables exist?
|
|
|
|
|
|
| Re: Invalid Table [message #169577 is a reply to message #169546] |
Thu, 27 April 2006 11:24  |
kjm0001
Messages: 11
Registered: February 2006
|
Junior Member |
|
|
|
i realize what the error means, i wanted to know how the table object could exist if one can not create a table without specifying a datatype...
|
|
|
|
|
|
| Re: Invalid Table [message #169582 is a reply to message #169580] |
Thu, 27 April 2006 11:37 |
kjm0001
Messages: 11
Registered: February 2006
|
Junior Member |
|
|
QUERY USED:
select a.owner, a.object_name, a.object_type, a.created, a.status, b.column_name, b.data_type
from dba_objects a, (select owner,table_name,column_name, data_type from dba_tab_columns
where data_type is null) b
where object_type = 'TABLE'
and status = 'INVALID'
and a.owner = b.owner
and a.object_name = b.table_name
OUTPUT:
OBJECT_NAME OBJECT_TYPE CREATED STATUS COLUMN_NAME DATA_TYPE
------------------------------ ------------ --------- ------- ------------- ----------
QS_ORDERS_PR_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_ORDERS_SQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_WS_ORDERS_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_WS_ORDERS_PR_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_ES_ORDERS_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_ES_ORDERS_PR_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_OS_ORDERS_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_OS_ORDERS_PR_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_CBADM_ORDERS_MQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_CBADM_ORDERS_SQTAB TABLE 12-MAY-02 INVALID USER_DATA
QS_CS_ORDER_STATUS_QT TABLE 12-MAY-02 INVALID USER_DATA [Updated on: Thu, 27 April 2006 11:42] by Moderator Report message to a moderator |
|
|
|
| Re: Invalid Table [message #169584 is a reply to message #169538] |
Thu, 27 April 2006 12:03 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Do you remember what actually the datatype was of column "USER_DATA", if it was any user defined object/type then first check the status of that. Is there anything weird in alert.log?? According to me these should be the first steps to approach this. Also give us the output of "DESC" command on these tables.
Daljit Singh
|
|
|
|
|
|
| Re: Invalid Table [message #169590 is a reply to message #169584] |
Thu, 27 April 2006 12:18 |
kjm0001
Messages: 11
Registered: February 2006
|
Junior Member |
|
|
|
the problem is that, this is one of our test db, so we don't really keep up with it, as far as maintaining the validity of objects and reading the alert log, maintaing backups or archiving...it just happens that the developers now want a copy of the db on another server...i will go ahead and check these steps to see if something shows up...i am guessing that these tables at one time were valid and somehow the column referencing a non-existent datatype did have a datatype...i was just curious as to what might have possibly caused this situation...
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
