Check How Much Table data has Changed Since the Last Analyze

Useful Query

How to find out total allocated, Used and free space in all the datafiles in oracle

If you want to find out total allocated space, used space and free space available in a datafile please use below query:

SQL>SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))

used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM dba_data_files df, dba_free_space dfs

WHERE df.FILE_ID = dfs.file_id(+)

GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes

UNION ALL

select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) - (user_bytes/1024/1024)) "free_space_mb" from dba_temp_files;

Output:

FILE_NAME                                                   ALLOCATED_MB    USED_MB             FREE_SPACE_MB

D:/oracle/ABC/abc_01.dbf                                20480                                  1                      20479

Check How Much Table data has Changed Since the Last Analyze

--

-- Check How Much Table data has Changed Since the Last Analyze.

--

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SET VERIFY OFF

-- *************************************************************************

-- * Enter a Valid Table Owner and a Valid Table Name.  (% for ALL Tables).

-- * Change Factor = High the Number, Greater the Change

-- *************************************************************************

COL table_name FOR A30

COL table_owner FOR A20

SELECT *

  FROM ( SELECT m.table_owner

              , m.table_name

              , t.last_analyzed

              , m.inserts

              , m.updates

              , m.deletes

              , t.num_rows

              , ( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END "Change Factor"

           FROM dba_tab_modifications m

              , dba_tables t

           WHERE t.owner = m.table_owner

             AND t.table_name = m.table_name

             AND m.inserts + m.updates + m.deletes > 1

             AND m.table_owner='&Enter_Table_Owner'

             AND m.table_name like '&Enter_Table_Name'

           ORDER BY "Change Factor" DESC

       )

/

Display the Time a Row was Last Updated

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SET VERIFY OFF

SELECT

   SCN_TO_TIMESTAMP ( ora_rowscn )

FROM

  &table_name

WHERE

  &where_clause

/