How to check rows that got inserted today

I got a reaquest where a user wanted to check all the records created today or between a date range. This was not achievable from the application perspective as it was not having any column like that.

Luckily I got reference to one of the reply from Pelle in a forum regarding this -

If you are running 10g or higher, you can use the ORA_ROWSCN pseudocolumn, which can be translated to a timestamp using the SCN_TO_TIMESTAMP function.

select ID from SCHEMA.TABLE where scn_to_timestamp( ora_rowscn ) - 0 > sysdate - 1;

But there can be a situation where we insert the data imported from another instance. Since it will have an invalid SCNs, it will raise an exception. So, here is a small function that returns NULL whenever there's an imported row with an invalid SCN.

CREATE OR REPLACE function rowdate( rowscn in number )
return date
as
l_rowts timestamp;
bad_scn exception;
pragma exception_init( bad_scn, -8181 );
begin
l_rowts := scn_to_timestamp( rowscn );
return cast( l_rowts as date);
exception when bad_scn then
return null;
end;
/

and then you can use the following SQL to see the records added to the table but be advised that this will give you not only rows inserted today, but also the updated ones.

select ID from SCHEMA.TABLE where rowdate( ora_rowscn ) > trunc( sysdate );

Monitoring Indexes

Databases, over the period of time can have many indexes. Some indexes might be in use but others may not be. Since these indexes eat up al lot of space and adds to a total cost, we should try to check what all indexes are in use and what not so that we can remove the unwanted indexes from the system.

Way to acheive this is that, we can turn on the Monitioring on the indexes for a while like -

ALTER INDEX INDEX_NAME MONITORING USAGE;

and once the information has been gathered we can turn off the monitoring like -

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

This will record all the information under the table OBJECT_USAGE data dictionary view.
Under this view there are following four column (OBJ#,FLAGS,START_MONITORING,
END_MONITORING)


OBJ# is the OBJECT_ID and can be correlated with DBA_OBJECTS. The OBJECT_ID is a unique identifier of the object in the database. Each object is assigned a unique number to recognize it in the database. So, no two object of two different schemas will be same. That is why Oracle might have kept only one master table (X$) and one master view (OBJECT_USAGE) to know which objects were actually get used and which were not.

FLAGS column of OBJECT_USAGE view can have two values, 1 (USED) and 0 (NOT USED).


After turning on/off the monitoring we can run a simple query like the following to get the Objects that are being in use -

SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE
FROM DBA_OBJECTS A, OBJECT_USAGE B
WHERE A.OBJECT_ID=B.OBJ#
AND B.FLAGS=1
/

If you want to check the space used by all INDEXS that are NOT in use then you can check it by running this query -

SELECT SUM(BYTES/1024/1024/1024) "TOTAL SPACE (GB)"
FROM DBA_SEGMENTS A, DBA_OBJECTS B, OBJECT_USAGE C
WHERE A.OWNER=B.OWNER
AND A.SEGMENT_NAME=B.OBJECT_NAME
AND B.OBJECT_ID=C.OBJ#
AND B.OBJECT_TYPE='INDEX'
AND C.FLAGS=0
/


This can save us not only space but can enhance the performance of the query also.