10 Replies Latest reply: Jan 29, 2013 1:46 PM by John Spencer RSS

    Data block information after deletion of data

    886282
      Hi , If I have deleted a row in a particular table then which meta data table in oracle will record that the no of bytes got deducted.

      I have checked in this table for perticular table before deletion and after deletion but there is no difference in number...DBA_SEGMENTS

      Edited by: 883279 on Jan 28, 2013 4:56 AM
        • 1. Re: Data block information after deletion of data
          BhaskarGouda
          Hi,

          Please check in the following way.and mark it ,if you helpful.

          SELECT object_name, droptime FROM user_recyclebin
          WHERE original_name = '*****';
          Thanks-Bhaskar
          • 2. Re: Data block information after deletion of data
            Karthick_Arp
            883279 wrote:
            Hi , If I have deleted a row in a particular table then which meta data table in oracle will record that the no of bytes got deducted.

            I have checked in this table for perticular table before deletion and after deletion but there is no difference in number...DBA_SEGMENTS

            Edited by: 883279 on Jan 28, 2013 4:56 AM
            When you delete a row in a table, oracle marks the block as free block and it does not actually frees the block. The subsequent insert statement uses this information and overwrites the block with new data.

            cehck this out, I have a table
            SQL> create table t
              2  as
              3  select *
              4    from all_objects;
             
            Table created.
            Now i run this
            SQL> set autotrace on
            SQL> 
            SQL> select count(*) from t;
             
              COUNT(*)
            ----------
                213321
             
            Statistics
            ----------------------------------------------------------
                     28  recursive calls
                      0  db block gets
                   2960  consistent gets
                   2879  physical reads
                      0  redo size
                    517  bytes sent via SQL*Net to client
                    488  bytes received via SQL*Net from client
                      4  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
             
            SQL> select count(*) from t;
             
              COUNT(*)
            ----------
                213321
            
            Statistics
            ----------------------------------------------------------
                      0  recursive calls
                      0  db block gets
                   2884  consistent gets
                      0  physical reads
                      0  redo size
                    517  bytes sent via SQL*Net to client
                    488  bytes received via SQL*Net from client
                      4  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            Check the consistent gets in the second qyery its 2884.

            Now i delete all the rows from table
            SQL> delete from t;
             
            213321 rows deleted.
            Now i again run the query
            SQL> select count(*) from t;
             
              COUNT(*)
            ----------
                     0
             
            Statistics
            ----------------------------------------------------------
                      0  recursive calls
                      0  db block gets
                   2884  consistent gets
                    306  physical reads
                      0  redo size
                    514  bytes sent via SQL*Net to client
                    488  bytes received via SQL*Net from client
                      4  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            You can see the consistent gets is same 2884, so oracle goes through all the data blocks even after
            we delete them.

            Now i truncate the table
            SQL> truncate table t;
             
            Table truncated.
             
            SQL> select count(*) from t;
             
              COUNT(*)
            ----------
                     0
             
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      1  db block gets
                      6  consistent gets
                      0  physical reads
                     52  redo size
                    514  bytes sent via SQL*Net to client
                    488  bytes received via SQL*Net from client
                      4  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            Now the HWM is reset and the blocks are released.
            • 3. Re: Data block information after deletion of data
              886282
              So do you mean to say that after a commit is processed the blocks will get released? and is it right that there is not perticular way to get the exact amout deleted?

              Because my actual requirement is that there is one application module which deals with user management data.

              In the application there is a option to delete the user from front end.

              But no one knows which are all the tables it is deleting.

              so I got an Idea to user reverse engineering and find out after deleting from front end what are all the tables the difference is making.

              So that I can concentrate on the backend tables having the relation.
              • 4. Re: Data block information after deletion of data
                Karthick_Arp
                883279 wrote:
                So do you mean to say that after a commit is processed the blocks will get released? and is it right that there is not perticular way to get the exact amout deleted?
                The block will be marked as free, but it will be there. The size of the table will be the same. Unless you rebuild the table which will reset the HWM (I would not suggest that).
                Because my actual requirement is that there is one application module which deals with user management data.
                In the application there is a option to delete the user from front end.
                But no one knows which are all the tables it is deleting.
                so I got an Idea to user reverse engineering and find out after deleting from front end what are all the tables the difference is making.
                So that I can concentrate on the backend tables having the relation.
                That is an incorrect way. I would suggest you get the details from the front end application.
                • 5. Re: Data block information after deletion of data
                  rp0428
                  >
                  Because my actual requirement is that there is one application module which deals with user management data.

                  In the application there is a option to delete the user from front end.

                  But no one knows which are all the tables it is deleting.
                  >
                  DELETE does NOT apply to users so you need to clarify what you are talking about.

                  Are you talking about a user that exists in your application? That is different from an Oracle user.

                  You use 'DROP MYUSER' to drop an Oracle user. That DROP statement does NOT drop the objects (tables, views, etc) owned by that user.

                  It sounds like you are talking about a user in your application that is NOT an Oracle user; just a user you have defined within an application. If you are NOT talking about an Oracle user (schema owner) then nothing said in this thread applies to your use case.

                  If that is the case reopen the thread and explain what you are really trying to do.

                  Another clarification: blocks are different from rows. Earlier a responder said that when you delete a row Oracle will mark the block as free. Blocks can contain multiple rows. Just because you delete one row doesn't mean the block is empty. Only if you delete the last row in a block is the block empty and put on the free list.
                  • 6. Re: Data block information after deletion of data
                    Sven W.
                    rp0428 wrote:
                    You use 'DROP MYUSER' to drop an Oracle user. That DROP statement does NOT drop the objects (tables, views, etc) owned by that user.
                    I think it does. Typo?

                    Everything else of cause is absolutly correct!
                    • 7. Re: Data block information after deletion of data
                      John Spencer
                      Sven W. wrote:
                      rp0428 wrote:
                      You use 'DROP MYUSER' to drop an Oracle user. That DROP statement does NOT drop the objects (tables, views, etc) owned by that user.
                      I think it does. Typo?

                      Everything else of cause is absolutly correct!
                      Sven:

                      Well, to be really nit picky, if the user owns objects, then drop user myuser will not drop the objects, it will throw an error
                      ORA-01922: CASCADE must be specified to drop 'MYUSER'
                      So technically correct :-)

                      John
                      • 8. Re: Data block information after deletion of data
                        rp0428
                        >
                        Well, to be really nit picky, if the user owns objects, then drop user myuser will not drop the objects, it will throw an error
                        >
                        Wow! Really? You consider that being 'nit picky'?
                        >
                        So technically correct
                        >
                        Technically correct and correct in every other way don't you think? I'm not sure how else I could have stated it other than saying what I did.
                        >
                        You use 'DROP MYUSER' to drop an Oracle user. That DROP statement does NOT drop the objects (tables, views, etc) owned by that user.
                        >
                        You're not still holding a grudge from my clarifications of one of your replies a few weeks ago are you? ;)
                        • 9. Re: Data block information after deletion of data
                          rp0428
                          Not a typo at all; standard Oracle functionally from the beginning.

                          See the SQL Language doc for DROP USER
                          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9008.htm

                          >
                          DROP USER Purpose

                          Use the DROP USER statement to remove a database user and optionally remove the user's objects.
                          . . .
                          user

                          Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

                          CASCADE
                          Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.

                          •If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.

                          •If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.

                          See Also:

                          Oracle Database Data Cartridge Developer's Guide for more information on these routines
                          •Oracle Database invalidates, but does not drop, the following objects in other schemas:

                          ◦Views or synonyms for objects in the dropped user's schema

                          ◦Stored procedures, functions, or packages that query objects in the dropped user's schema

                          •Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.

                          •Oracle Database drops all triggers in the user's schema.

                          •Oracle Database does not drop roles created by the user.

                          Caution:

                          Oracle Database also drops with FORCE all types owned by the user. See the FORCE keyword of DROP TYPE.
                          • 10. Re: Data block information after deletion of data
                            John Spencer
                            Well, my reply was to Sven who was questioning your statement
                            You use 'DROP MYUSER' to drop an Oracle user. That DROP statement does NOT drop the objects (tables, views, etc) owned by that user.
                            Since no one else had mentioned drop in the context of the thread before your post, I assumed that the "That DROP statement" referred to the drop statement you provided which will throw an error if the user owns objects in the database, so, technically correct. What I am unclear about is where drop user came from at all. From the OP's original post
                            883279 wrote:
                            Hi , If I have deleted a row in a particular table then which meta data table in oracle will record that the no of bytes got deducted.

                            I have checked in this table for perticular table before deletion and after deletion but there is no difference in number...DBA_SEGMENTS
                            It seems clear to me that they are looking for a way to find out which tables are affected by some (at that point unknown) operation. The OP goes on to clarify in a later post that you replied to:
                            Because my actual requirement is that there is one application module which deals with user management data.

                            In the application there is a option to delete the user from front end.

                            But no one knows which are all the tables it is deleting.

                            so I got an Idea to user reverse engineering and find out after deleting from front end what are all the tables the difference is making.

                            So that I can concentrate on the backend tables having the relation.
                            So, in essence, the OP's question is - People are doing things in the front-end and I don't know what it is doing in the database. Is there a way to find out by seeing what tables have rows deleted when they do that thing from the front-end?

                            And no, I don't hold grudges. Feel free to question or clarify anything I post.

                            John