Oracle SQL, how to access latest queries and their data

oracle

I have SQL Developer, + Oracle client 11g.

I wrote a program (MFC Visual C++) that read and writes data to a certain table.
usually i put logs in the project so I could see what I insert/Update/Delete from the table.
Now I am about to delete multiple rows. and I would like to count the lines I deleted.

How can I know the latest queries that used in the DB, what they affected and get some data on activities that were held on the server ?

thanks !

Best Answer

To count the rows deleted just after the delete statement use SQL%ROWCOUNT. Here is a demonstration.

--Setup.
drop table t1;
create table t1 as (select level x from dual connect by level <=5);

set serveroutput on size 1000000 format wrapped
DECLARE
   vCount Number(3);
BEGIN
   DELETE FROM T1 WHERE rownum<=2;
   vCount := SQL%ROWCOUNT;  --<<---<<---<<---<<---<<---<<---<<---<<---
   DBMS_Output.Put_Line('RowCount:' || vCount);
END;
/

For your broader question you can look into tracing, auditing, flashback database, flashback query, logminer, materialized views, streams, and change data capture (not necessarily in that order). We would need more details about what data you need and why before directing you any more specifically.

Related Solutions

Oracle delete technique for table’s segment size (data compactness)

How do you define the "tablespace size"? Are you interested in the total size of the data files on disk that comprise the tablespace? Or are you interested in the total size of all the segments that are part of the tablespace?

Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under either definition. Both the size of the table's segment and the size of the tablespace's data files will remain constant. Of course, there will now be additional free space in many of the table's blocks that can be used by subsequent INSERT and UPDATE operations.

Issuing a TRUNCATE, on the other hand, will decrease the size of the table's segment. That won't affect the size of the tablespace's data files. But it will affect the total size of all the segments that are part of the tablespace. So there may be a difference depending on your definition of the size of a tablespace. A TRUNCATE, being DDL, will not be transactional so it cannot be rolled back. Assuming that you are deleting a large fraction of the rows in the table, it will also tend to be much more efficient than issuing a DELETE because it generates much less UNDO and REDO.

If you are stating in your last paragraph that the size of the segment is increasing much faster than the rate at which new data is being added, assuming that the new rows are roughly the same size as the old rows and that the old rows are not growing over time due to updates, is it possible that the new rows are being added via direct-path inserts which will always go above the current high-water mark for the segment and will thus never reuse the space in blocks that is freed up by a DELETE? If so, is that intentional? If the table is small, you might see similar differences because of the granularity of extent allocation-- you might insert 100 rows without requiring a new extent, the 101st insert requires Oracle to allocate a new extent, and that new extent might be sufficient for thousands of new rows to be added, but you'll only see the size of the segment change after the 101st insert. But that is less likely if this is a reasonably large table unless you've chosen a particularly large extent size.

Delete Old Archivelogs from missing database

We finally deleted the files manually and OEM is fine.

We ran this query to get the delete scripts:

SELECT 'alter diskgroup ' 
       || gname 
       || ' drop file ''+' 
       || gname 
       || Sys_connect_by_path(aname, '/') 
       || ''';' a 
FROM   (SELECT b.name            gname, 
               a.parent_index    pindex, 
               a.name            aname, 
               a.reference_index rindex, 
               a.system_created, 
               a.alias_directory 
        FROM   v$asm_alias a, 
               v$asm_diskgroup b 
        WHERE  a.group_number = b.group_number) 
WHERE  alias_directory = 'N' 
       AND system_created = 'Y' 
START WITH ( MOD(pindex, Power(2, 24)) ) = 0 
           AND rindex IN (SELECT a.reference_index 
                          FROM   v$asm_alias a, 
                                 v$asm_diskgroup b 
                          WHERE  a.group_number = b.group_number 
                                 AND ( MOD(a.parent_index, Power(2, 24)) ) = 0 
                                 AND a.name = '&DATABASENAME') 
CONNECT BY PRIOR rindex = pindex;

Replace &DATABASENAME with your old database name and then run the scripts.

Important note: you would normally not do this. I'm sure my database has been deleted and that the remaining files are completely useless. If your intent is to clean obsolete archive logs, use RMAN instead. It's safe and really easy.

Best Answer

Related Solutions

Oracle delete technique for table’s segment size (data compactness)

Delete Old Archivelogs from missing database

Related Question