Display the Time a Row was Last Updated

--

-- 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

/

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

       )

/

To determine Tablespace, File, Size, Used, Free, Maxbytes, Auto Extend of all the tablespces in oracle

To determine Tablespace, File, Size, Used, Free, Maxbytes, Auto Extend of all the tablespces in oracle:



SQL>set line 100
SQL> set pagesize 80

SQL>column tablespace format A10

SQL>column file format A20 word_wrapped


SQL>SELECT Substr(df.tablespace_name,1,20) "Tablespace",
Substr(df.file_name,1,40) "File",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Round(df .MAXBYTES/1024/1024,2) "Maxbytes (M)",
Substr(df.AUTOEXTENSIBLE,1,20) "Autoextensible"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;

To determine total space, used space, free space and percentage of used and free space in all the tablespaces in oracle

To determine total space, used space, free space and percentage of used and free space in all the tablespaces in oracle:


If yo want to determine tolat space, used space, free space and percentage of used and free space in all the tablespaces in a database user below query:

SQL> SET LINESIZE 100
SQL> COLUMN TABLESPACE FORMAT A15

SQL>select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace
as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2)
as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group
by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f
group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;


TABLESPACE   Totalspace(MB)     Used Space(MB)    Freespace(MB)     % Used     % Free
---------------        --------------------------     ----------------------   --------------------  ------------   -----------
LAGOBLO            4096                      1                              4095                    .02           99.98
LAGODAT            173052                  167101                    5951                96.56             3.44
LAGOHIST           4324                      4087                        237                   94.52            5.48
LAGOIDX             223204                  202645                    20559               90.79            9.21
LAGOTHN            4096                      1                              4095                     .02          99.98
SYSAUX               7850                      1643                        6207                 20.93          79.07
SYSTEM               1660                      1654                        6                        99.64              .36
UNDOTBS1          5690                      463                          5227                    8.14          91.86
USERS                   5                           2                              3                        40               60

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

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

-------------------------                    -----------------      --------------         -------------------

+BP_DATA/bplagp1/dat                 31744            29985                  1759

afile/lagoidx.269.88

9205885

+BP_DATA/bplagp1/dat                 24576            24576                     0

afile/lagodat.260.88

9205001

+BP_DATA/bplagp1/dat                31744            31526                     218

afile/lagoidx.274.88

9206041

How much disk space percentage is used in each individual datafile

How much disk space percentage is used in each individual datafile

If you are interested in investigating how much disk space percentage is used in each individual datafile:

SQL>set line 100
SQL>column TABLESPACE_NAME format A15
SQL>column Filename format A20 word_wrapped
SQL>set pagesize 80
SQL>column Used% format A5;

SQL>SELECT *

FROM (SELECT TABLESPACE_NAME,

FILE_NAME "Filename",

ROUND (BYTES / 1024 / 1024) "Size MB",

ROUND (MAXBYTES / 1024 / 1024) "MaxMB Set by DBA",

AUTOEXTENSIBLE "Autoextend Yes/No ?",

ROUND (

( ( (BYTES / 1024 / 1024) / (MAXBYTES / 1024 / 1024))),

2)

* 100

|| '%'

"Used%"

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'YES'

UNION

SELECT TABLESPACE_NAME,

FILE_NAME "Filename",

ROUND (BYTES / 1024 / 1024) "Size MB",

(CASE WHEN MAXBYTES = 0 THEN ROUND (BYTES / 1024 / 1024) END),

(CASE

WHEN AUTOEXTENSIBLE = 'NO'

THEN

'No:MaxOut per DBA setting'

END)

"Autoextensible",

'100%'

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'NO')

WHERE tablespace_name = 'EXAMPLE'  ---replace EXAMPLE with your real tablespace_name

ORDER BY 1 ASC, 5 DESC;

Output:

TABLESPACE_NAME Filename     Size MB MaxMB Set by DBA Autoextend Yes/No? Used%        
------------------------   -----------------            ---------- --------------------------  -------------------------   ----
EXAMPLE               +EX/bp1/blo.2       4096         31744                     YES                      13%
             

How much of free space is available in individual tablespace in oracle

How much of free space is available in individual tablespace in oracle

If you are interested in investigating how much of free space is available in individual tablespace, here is the SQL query for this:

SQL>SELECT TABLESPACE_NAME,SUM(maxmb-cursize) FREE_MB

from

(

SELECT *

FROM (SELECT TABLESPACE_NAME,

FILE_NAME,

ROUND (BYTES / 1024 / 1024) cursize,

ROUND (MAXBYTES / 1024 / 1024) MaxMB,

AUTOEXTENSIBLE "Autoextend",

ROUND (

( ( (BYTES / 1024 / 1024) / (MAXBYTES / 1024 / 1024))),

2)

* 100

|| '%'

"Used"

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'YES'

UNION

SELECT TABLESPACE_NAME,

FILE_NAME,

ROUND (BYTES / 1024 / 1024) cursize,

(CASE WHEN MAXBYTES = 0 THEN ROUND (BYTES / 1024 / 1024) END),

(CASE

WHEN AUTOEXTENSIBLE = 'NO'

THEN

'No:MaxOut per DBA setting'

END)

"Autoextensible",

'100%'

FROM SYS.DBA_DATA_FILES

WHERE AUTOEXTENSIBLE = 'NO')

WHERE tablespace_name = 'LAGOBLO'  ---replace LAGOBLO with your real tablespace_name

ORDER BY 1 ASC, 6 DESC

)

group by tablespace_name;

Output:

TABLESPACE_NAME                   FREE_GB
------------------------------                    -------------------
LAGOBLO                                             27