Ora-00942

Hello,

The system is responding with the ORA-00942 error message when I ran the following code. Please help me..

---------------------------------------------------

DECLARE
Tname varchar2(30);
CURSOR C1 IS SELECT table_name FROM dba_tables where chain_cnt > 0;

BEGIN
Open C1;
Loop
Fetch C1 into Tname;
dbms_output.put_line( 'Analying the table: ' || Tname );
execute immediate 'analyze table Tname list chained rows';
EXIT WHEN C1%NOTFOUND;
End Loop;
CLOSE C1;
End;
/

---------------------------------------------------
Here is the error message:

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 10

---------------------------------------------------

Thanks,
Seenu

You are missing the table owner out, and ...

execute immediate 'analyze table Tname list chained rows';

... should be ...

execute immediate 'analyze table '||Towner||'.'||Tname||' list chained rows';

Thank you.

-Seenu

Hello,

I have one last quick question.
The system is responding with the ORA-00904 error message when I ran the following code:
--------------------------------------------------------
DECLARE
Tname varchar2(30);
CURSOR C1 IS SELECT table_name FROM dba_tables where chain_cnt > 0;

BEGIN
Open C1;
Loop
Fetch C1 into Tname;

Execute Immediate 'CREATE table migrated_rows as SELECT orig.* FROM ' || Tname || ' orig, chained_rows cr WHERE orig.rowid = cr.head_rowid AND cr.table_name = upper('||Tname||')';

EXIT WHEN C1%NOTFOUND;
End Loop;
CLOSE C1;
End;
/

--------------------------------------------------------
Error Message:

ERROR at line 1:
ORA-00904: invalid column name
--------------------------------------------------------

Kind regards,
Seenu

select orig.*? Interesting technique; however, this is not in Oracle's functional skill set.

Hello steven,

The CREATE table migrated_rows as SELECT orig.* FROM command works when I execute it individually at he SQL prompt.

Thanks,
Seenu