11 Replies Latest reply: Aug 19, 2013 3:11 AM by MatthiasHoys RSS

    ORA-00942: table or view does not exist

    Sivamani
      Hi all,

      I am trying to take backup of whole apex application and i tried using system user and i am getting the following user.

      /usr/java/jre1.6.0_43/bin/java oracle.apex.APEXExport -db localhost:1541:test -user system -password welcome -instance


      Exception in thread "main" java.sql.SQLException: ORA-00942: table or view does not exist

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:677)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2371)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2660)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:387)
      at oracle.apex.APEXExport.ExportFiles(APEXExport.java:66)
      at oracle.apex.APEXExport.main(APEXExport.java:326)


      Could any one please help me to get resolved .

      Thanks
        • 1. Re: ORA-00942: table or view does not exist
          sybrand_b
          As there is an utility in the Apex GUI to export the complete repository, one wonders why you do this.
          Also system doesn't own the Apex objects and would need to have synonyms for every object, which will be a nightmare, as soon as you upgrade to a higher release, as the schema will change.
          That said: why don't you just use available tools, or connect as the apex owner?

          --------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: ORA-00942: table or view does not exist
            Sivamani
            Hi thanks for the update.

            i) I want to schedule the apex backup using shell script to run at regular basis.
            ii) I tried using the following

            /usr/java/jre1.6.0_43/bin/java oracle/apex/APEXExport -db localhost:1541:test -user APEX_040200 -password apex -instance
            Exporting Application 100:'Sample Application'
            Completed at Fri Apr 19 09:46:53 EDT 2013
            Exporting Application 101:'Sample Database Application'
            Completed at Fri Apr 19 09:46:54 EDT 2013
            Exporting Application 102:'Maintain Delegate Time Entry Rights'
            Completed at Fri Apr 19 09:46:54 EDT 2013
            Exporting Application 103:'Employee Asset Management'
            Completed at Fri Apr 19 09:46:55 EDT 2013
            Exporting Application 104:'IT Projects'
            Completed at Fri Apr 19 09:46:55 EDT 2013
            Exporting Application 105:'New App Template'
            Completed at Fri Apr 19 09:46:55 EDT 2013
            Exporting Application 106:'Field Resource Management'
            Completed at Fri Apr 19 09:46:57 EDT 2013
            Exporting Application 107:'cBOM'
            Completed at Fri Apr 19 09:46:57 EDT 2013
            Exporting Application 108:'Change Management'
            Completed at Fri Apr 19 09:46:58 EDT 2013
            Exporting Application 109:'PDR Inv Management'
            Completed at Fri Apr 19 09:46:59 EDT 2013
            Exporting Application 110:'HR Performance Appraisal'
            Completed at Fri Apr 19 09:47:00 EDT 2013
            Exporting Application 111:'AP Extensions'
            Completed at Fri Apr 19 09:47:00 EDT 2013
            Exporting Application 112:'P-Track'
            Completed at Fri Apr 19 09:47:04 EDT 2013
            Exporting Application 113:'APEX Application Archive'
            Completed at Fri Apr 19 09:47:05 EDT 2013
            Exporting Application 114:'Labor'
            Completed at Fri Apr 19 09:47:06 EDT 2013
            Exporting Application 116:'AP Workflow'
            Completed at Fri Apr 19 09:47:06 EDT 2013
            Exporting Application 118:'Sample Charts'
            Completed at Fri Apr 19 09:47:07 EDT 2013
            Exporting Application 901:'New App Template'
            Completed at Fri Apr 19 09:47:07 EDT 2013
            Exporting Application 905:'XXPDR Inv Management'
            Completed at Fri Apr 19 09:47:08 EDT 2013
            Exporting Application 7000:'Online Marketing Campaign Calendar'
            Exception in thread "main" java.sql.SQLException: ORA-20987: APEX - Application 7000 not found logged in as database user APEX_040200. - Contact your application administrator.
            ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 622
            ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 903
            ORA-06512: at "APEX_040200.WWV_FLOW_UTILITIES", line 9947
            ORA-06512: at line 1

            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
            at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)
            at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)
            at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)
            at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:888)
            at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2076)
            at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1986)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2697)
            at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)
            at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:531)
            at oracle.apex.APEXExport.ExportFile(APEXExport.java:173)
            at oracle.apex.APEXExport.ExportFiles(APEXExport.java:79)
            at oracle.apex.APEXExport.main(APEXExport.java:326)

            getting errors.Please advise.

            Thanks
            • 3. Re: ORA-00942: table or view does not exist
              Sivamani
              I want to take a full instance backup so which user i should use.
              • 4. Re: ORA-00942: table or view does not exist
                TexasApexDeveloper
                This is NOT really and APEX specific question.. Why not ask in the SQL and Pl/SQL support forum?

                Thank you,

                Tony Miller
                Ruckersville, VA
                • 5. Re: ORA-00942: table or view does not exist
                  Sivamani
                  Please check the log messages.I'm trying to take backup of apex application .
                  • 6. Re: ORA-00942: table or view does not exist
                    user8989724

                    You must use the APEX_0nnnnn user to do an instance level export. You will need to unlock it and change the password. The sys or APEX_PUBLIC_USER accounts will not work

                     

                    Ignore the previous "replies" - they are both incorrect.

                    a) using the GUI to export apps is only sensible in a small environment for occasional backups. The command line option is essential for anything else (don't rely on database backups only....)

                    b) this is a question specifically addressed at APEXExport - not SQL or PL/SQL - it is an Apex question

                    • 7. Re: ORA-00942: table or view does not exist
                      aorosar

                      Hi,

                       

                      I'm getting also the same error, the procedure I followed is the one below:

                       

                      setenv CLASSPATH /opt/oracle11.2/database/oui/jlib/classes12.jar:${ORACLE_HOME}/jdbc/lib/ojdbc5.jar:./:$CLASSPATH

                      cd /opt/oracle11.2/database/apex/utilities

                      java oracle/apex/APEXExport -db localhost:1521:test -user APEX_040200 -password apex -workspaceid 3317222166520935

                       

                      some applications were exported to the utilities directory, but at some point I get:

                       

                      Exception in thread "main" java.sql.SQLException: ORA-20987: APEX - Application 7000 not found logged in as database user APEX_040200. - Contact your application administrator.

                      ORA-06512: en "APEX_040200.WWV_FLOW_ERROR", línea 622

                      ORA-06512: en "APEX_040200.WWV_FLOW_ERROR", línea 903

                      ORA-06512: en "APEX_040200.WWV_FLOW_UTILITIES", línea 9947

                      ORA-06512: en línea 1

                       

                       

                              at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)

                              at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)

                              at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)

                              at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)

                              at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:888)

                              at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2076)

                              at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1986)

                              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2697)

                              at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)

                              at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:531)

                              at oracle.apex.APEXExport.ExportFile(APEXExport.java:173)

                              at oracle.apex.APEXExport.ExportFiles(APEXExport.java:127)

                              at oracle.apex.APEXExport.main(APEXExport.java:326)

                       

                      Checking the database I found the following:

                       

                      SQL> select owner,application_id,alias,workspace_id,workspace from apex_applications where application_id=7000 order by application_id;

                       

                       

                       

                      OWNER               APPLICATION_ID     ALIAS    WORKSPACE_ID       WORKSPACE

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

                      APEX_040200       7000                        F7000    11                              COM.ORACLE.APEX.REPOSITORY

                       

                       

                      If I run the same command with "-instance" instead of "-workspaceid 3317222166520935" I get the same error.

                       

                      I also have tried using the user SYSTEM but I got the same error.

                       

                      I have Oracle 11.2.0.3 installed over Red Hat 5, and my Apex versión is Apex 4.2.1. The listener is configured as a service in WebLogic.

                       

                      I have no clue and I don't find anything, could you help me? And I also don't understand why is it taking an application from another workspace.

                       

                      Thanks a lot.

                       

                      Best regards,

                       

                      Ángel

                      • 8. Re: ORA-00942: table or view does not exist
                        aorosar

                        Hi again,

                         

                        I'm sorry, my problem is fixed, I don't know why Oracle did what it did... The case is that mi APEXExport tool hadn't been completely copied at installation time so when I used it despite of the options I gave it, it always tried to export all the applications and failed. I have copied now the APEXExport.class from the original path (APEX installer) and no everything works OK, I hope this helps someone in case the have this weird problem too.

                         

                        Regards,

                         

                        Ángel

                        • 9. Re: ORA-00942: table or view does not exist
                          MatthiasHoys

                          Hi Ángel,

                           

                          I have the same problem trying to export a workspace with all applications, but I don't understand what you did to solve it.

                          Could you explain it a bit better? Can you share the complete command you used and your environment variables (CLASSPATH)?

                           

                          Thanks,

                          Matthias

                          • 10. Re: ORA-00942: table or view does not exist
                            aorosar

                            Hi, Matthias,

                             

                            Sorry for answering this late.

                             

                            Actually my problem was that when I installed APEX the APEXExport.class file wasn't completely copied so its performance was defficient, check the size of both file (the one you have in your APEX directory, and the one given in the installation package, if they are not the same replace the one in the Apex directory with the one given in the downloaded package).

                             

                            Just in case that I hadn't explained my problem well, I tried to export the applications in my workspace, the should be just f100.sql and f101.sql but the error came trying to export some others, so the CLASSPATH had nothing to do with it, but anyway I write what I do:

                             

                             

                            cd ${ORACLE_HOME}/apex/utilities

                            setenv CLASSPATH /opt/oracle11.2/database/oui/jlib/classes12.jar:${ORACLE_HOME}/jdbc/lib/ojdbc5.jar:./:$CLASSPATH

                            setenv JAVA_HOME /usr/java/jdk1.6.0_35/bin/

                            java oracle/apex/APEXExport -db ${MACHINE}:${PORT}:${ORACLE_SID} -user APEX_040200 -password apex1234 -workspaceid ${WOR

                            KSPACE}

                             

                             

                            Is it the same problem? I hope it helps you, if not, can you elaborate a bit more?

                             

                            Regards,

                             

                            Ángel

                            • 11. Re: ORA-00942: table or view does not exist
                              MatthiasHoys

                              Hi,

                               

                              Yes, it's now working for me.

                              The problem was the "cd" into the apex/utilities folder. If I didn't do that before calling the class file, the export failed after a while, strangely enough...

                               

                              Thanks for the feedback,

                              Matthias