Remote database and SELECT successful, CTAS and INSERT fails.
I have very weired problem on my oracle standard 10g Release2 installation. I am having a complex query involving many remote tables over database link and some local tables. I have used a driving_site hint into the sql.
While this is going to be a nightly refresh, I created a shell script with simple HEREDOC syntax on sqlplus as follows:
---------------------------------------------------------------------------------
sqlplus <login> <<EOF
CREATE TABLE ATABLE AS
SELECT /*+ driving_site (ptable) */
......
/
exit
EOF
---------------------------------------------------------------------------------
Idea was to run this under DBMS_SCHEDULER.
Now above query consistently fails. Giving
ORA-00904: "A1"."acolumn": invalid identifier
ORA-02063: preceding line from remote_DB