1 2 3 Previous Next 33 Replies Latest reply on Aug 31, 2018 8:46 AM by Sven W. Go to original post
      • 15. Re: Lock Table but only for external Sessions
        _Dylan_

        One solution would to not grant your users UPDATE ANY TABLE.

         

        Ugh, I'm sounding like my DBAs!!!

        • 16. Re: Lock Table but only for external Sessions

          So in this case your answer is that we can not solve it.. ?

          You can solve it the way I suggested.

           

          You can NOT solve it the way you wish to.

          • 17. Re: Lock Table but only for external Sessions

            There should be a solution for it.. that I can protect my table from other DMLs during a migration! I can not believe it!

            NO ONE should be performing DML on a table that you are migrating.

             

            Make the table READ ONLY for everyone.

             

            Most migrations I have worked with use steps like the following:

             

            1. backup the database (or tables being migrated)

            2. do the migration from the backup

            3. when you are ready to 'switch' to the new, migrated system - query the NEW changes only from the existing system and apply them to the new system.

            • 18. Re: Lock Table but only for external Sessions
              Sven W.

              HansMueller69 wrote:

               

              The table has more than 3'000'000'000 Rows in the Table.

              The Table is partitioned.

               

              I want to update columns but pro partition and commit. -> In this case we do not need very large undo tablespace.

               

              ...


              More of a side comment.

              Since UNDO is one of the main issues, then consider to add a where clause to your update statement.

              1. 'UPDATE /*+ PARALLEL(16) ENABLE_PARALLEL_DML */'                                 || CHR(10) 
              2.                   || '       OMG.OMG_BUSINESS_PLAN PARTITION (' || rec_par.partition_name || ') PKL'  || CHR(10) 
              3.                   || '   SET x_transaktion = TO_CHAR(n_transaktion)'
              4.                   || '   WHERE (x_transaktion != TO_CHAR(n_transaktion) or n_transaktion is null or x_transaktion is null)'

               

              This depends of cause if there are rows that already have the correct x_transaction set.

               

              Also can you elaborate, why it is a problem if users update partition 1234, while you are changing partition 6789?

              After the migration is done they would be allowed to change it, right? So where is the difference?

               

              And on a second thought. Maybe you can add an before statement level trigger to the table. That can be made so, that it raises an error "DML not allowed currently. Migration running" for other sessions, but for the one that does the update. The main difference compared to a lock is: A lock means the other sessions will WAIT. Here they get an error message.


              However it would require to install this trigger. And later to drop the trigger again.

               

              -- edit:
              added "OR ... IS NULL" to the where clause.
              Maybe LNNVL is also worth a try. But the NULL complexity in the where clause depends on existing constraints. So might not be worth investigating, if for example the n_transaction is already declared as mandatory.

              1 person found this helpful
              • 19. Re: Lock Table but only for external Sessions
                _Dylan_

                Adding to what rp0428 and Sven mentioned, if you're blowing up your logs with a large update, and you don't want changes made to the underlying table, consider setting the table to read-only, do a nologging CTAS with the corrections applied in the select (if you're update logic isn't too funky), and then swap the tables when you're done. I've regularly had to "update" very wide fact tables with billions of rows. In tests, creating a new copy, rebuilding indexes, and swapping was almost always faster than an actual update.

                • 20. Re: Lock Table but only for external Sessions
                  L. Fernigrini

                  Maybe this works:

                   

                  1) Rename the table from "tablename" to "othername"

                  2) Create a view:

                  CREATE OR REPLACE VIEW tablename AS SELECT * FROM othername WITH READ ONLY CONSTRAINT DML_DISABLED_TEMPORARY;

                  3) Do your work on "othername"

                  4) Drop the view

                  5) Rename the table from "othername" to "tablename"

                   

                  It's not very smooth but should do the work

                  1 person found this helpful
                  • 21. Re: Lock Table but only for external Sessions
                    HansMueller69

                    Hi Sven

                     

                    n_transaktion : (n_ = Number)

                    x_transaktion : (x_ = TeXt so Varchar2)

                     

                    x_transaction is empty.

                     

                    But you have mentioned a very good solution. This is the creating of a Trigger on the table.

                    If there is a change only on the n_transaktion which will be to x_transaktion migrated, then the other processes can be stopped.

                     

                    Thank you for your effort. Super Solution.

                    • 22. Re: Lock Table but only for external Sessions
                      HansMueller69

                      Hi Lisandro

                       

                      Your solution is also very good.

                       

                      Thank you.

                      • 23. Re: Lock Table but only for external Sessions
                        HansMueller69

                        The best solution with the VIEW would be that I create a view on the table and make the view updateable and after that I create an INSTEAD OF trigger on the view for update all the fields except the one I want to migrate.

                         

                        Sven you were the closest with the idea of the trigger to the solution. So in this case I sign your answer as the solution.

                         

                        Lisandro you led me to a very good solution with the creating of the View.

                         

                        Thank You all for Your efforts! You are all very nice!

                         

                        Regards, David

                        • 24. Re: Lock Table but only for external Sessions
                          Billy~Verreynne

                          HansMueller69 wrote:

                           

                          We have a central ADMINISTRATION -User and other Application users who have the privileges for example UPDATE ANY TABLE

                          .These packages/functions under the Administration User or under these Applications user can be executeed during the migration.

                          I can not revoke these privilege (UPDATE ANY TABLE) from the administration user...

                           

                          There should be a solution for it.. that I can protect my table from other DMLs during a migration! I can not believe it!

                           

                          Yep. I cannot believe it either - that you are blaming your lack of knowledge and utter ignorance, on Oracle.

                           

                          And granting UPDATE ANY TABLE!? That is mostly an idiotic approach, and creates a very serious security flaw.

                           

                          Oracle supports the ACID principles exceedingly well - better than most other relational database products. So stop pretending to be so indignant about Oracle not automagically fulfilling your wishes, and read and comprehend - starting with the Database Concepts guide.

                          • 25. Re: Lock Table but only for external Sessions
                            Billy~Verreynne

                            HansMueller69 wrote:

                             

                            I wanted to say with that that the Table is for the other sessions in READ-ONLY Mode.

                             

                            I do not want a particular user to be able to do a dml but every user and packages etc.. I want to save my table during the migration.

                             

                            There are many users who have the privileges to modify the table content. I can not eliminate these rights. because of this the whole application can not be stoped.

                            So there are privileges granted to users - that gives them the right to update a table for example.

                             

                            And you want this privilege to cease working when you feel like it - without revoking the privilege from these users.

                             

                            Kind of like wanting liquid water, but the water must not be wet...

                            • 26. Re: Lock Table but only for external Sessions
                              HansMueller69

                              Hi Billy

                               

                              Thank you for your answer.

                              Please read the forum posting Etiquette :

                              https://community.oracle.com/docs/DOC-920700

                              As you are a Grant Titan you should show the right behaviour.

                              Your comments are not technical but personal.

                               

                              "And granting UPDATE ANY TABLE!? That is mostly an idiotic approach, and creates a very serious security flaw."

                               

                              It shows everything... Probably you teach Oracle SQL but you do not work in a real environment..

                              In a real environment you can not get always that what you can read in the Documentation...

                               

                              Please stop filling the forum with not technical answers. Thanks.

                              • 27. Re: Lock Table but only for external Sessions
                                Sven W.

                                HansMueller69 wrote:

                                 

                                Hi Sven

                                 

                                ...

                                If there is a change only on the n_transaktion which will be to x_transaktion migrated, then the other processes can be stopped.

                                ...

                                Hi Hans,

                                 

                                that is not how the trigger should work. The most easiest way probably is to use a package variable which defaults to FALSE. When any session tries to update your table, this variable will be checked (by a before statement insert,update, delete trigger) and if it is FALSE an error will be raised. Which in turn stops the update.

                                 

                                Now your session, that does the migration would set this package variable to TRUE, before running the update. In that case the check in the trigger would not stop the update. Since package variables are session specific, only your session will see the TRUE value.

                                1 person found this helpful
                                • 28. Re: Lock Table but only for external Sessions
                                  HansMueller69

                                  Hi Sven

                                   

                                  BRILLIANT IDEA! The Trigger Solution with your idea is unbeatable till this time.

                                   

                                  Thank you very much I think this solution is simple and efficient.

                                  • 29. Re: Lock Table but only for external Sessions
                                    Billy~Verreynne

                                    I always have an issue when someone with a self-made problem, does not own the problem, but instead blame the product, or others.

                                     

                                    UPDATE ANY TABLE is a very dangerous priv to grant to application schemas. And is the root cause of why you have a problem "protecting" a table against other sessions' DML, during "migration".

                                     

                                    Adding a  trigger is a hack - it is not a robust approach as identifying the single valid "migration" session requires using session attributes that can easily be duplicated, or even spoofed.

                                     

                                    I am quite correct in my analysis that the UPDATE ANY TABLE priv is an idiotic approach to application security.

                                     

                                    And neither have you mentioned how you are going to maintain data integrity during a migration process that uses distinct transactions, when this process can fail partially through the migration, with committed transactions that cannot be undone.

                                     

                                    Shooting from the hip? Expect some shots to go wild and through your foot instead.