3 Replies Latest reply: Jul 2, 2013 2:30 PM by ThAn RSS

    Inserted data in table lost after shut down and login again with  scott tiger account

    6b8ef1d9-b329-4889-a122-e3f5539859fc

      I have installed oracle 9i on my system. and using scott tiger account to login in sql plus.

      My problem is that after creating a table and inserting data tinto the table when i shutdown the computer and start it again

      it shows me the table columns only.

      ie on using select query it say's no row selected.

      on checking the table it show the columns only. But does not show the previously inserted rows in the table.

      What could be the problem.

      Please advice the solution.

      Thanks in advance

        • 1. Re: Inserted data in table lost after shut down and login again with  scott tiger account
          ThAn

          Hi,

           

          did you commit your changes before you finished your sqlplus session?

          As create table statements are of type DDL which issue an implict
          commit those actions are preserved after leaving sqlplus.

          But for DML statements such as insert, update and delete you'll have
          to issue "commit" command at least once before you close your
          sqlplus session (at least if autocommit is off - which seems to be
          in your case) to make data changes persistent.

          If you don't (and if autocommit in sqlplus is off) all changes are lost
          when closing the session.

           

           

          Regards

          • 2. Re: Inserted data in table lost after shut down and login again with  scott tiger account
            6b8ef1d9-b329-4889-a122-e3f5539859fc

            Hello,

             

            Thanks for your valuable and quick reply.

            I have learned on oracle few years back and started using it now.

            I have not faced this problem on that time. Thanks again.

            Please tell me the syntex how to set autocommit ON in sqlplus before exiting the sqlplus.

            And should i need to execute it after every DML Command or executing it once only before exiting sqlplus

             

            Thanks & Regards

            • 3. Re: Inserted data in table lost after shut down and login again with  scott tiger account
              ThAn

              Hi,

              just issue

              SET AUTOCOMMIT ON

              in sqlplus session and it will be set to on from this point and for future use.

              Regarding your question on when to issue a commit: it depends on your use case.
              If your changes should be seen in other (users) sessions immediately after the change, then
              issue a commit for the relevant statements.

              If your changes should only be seen in your session (your code) then it should be ok to
              issue one commit at the end of the session. But at this stage there might be situations,
              where it's better to rollback all changes - just think of a situation, when all DML statements
              belong to a logical transaction. If one fails for any reason, then all related changes need to
              be rolled back (with rollback command).

              But - as already stated - this depends on your use case / business case, so that there's
              not a "single truth".

               

              Regards

               

              P.S.: If your question can be considered as answered, don't forget to mark it as "answered" as
              this helps other forum users distinguishing open from answered questions