Script for Compiling INVALID objects

SET ECHO        OFF
SET FEEDBACK    OFF
SET HEADING     OFF
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

spool compile.sql

SELECT  'alter ' ||
       decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
       ' ' ||
       object_name||
       ' compile' ||
       decode(object_type, 'PACKAGE BODY', ' body;', ';')
FROM   dba_objects
WHERE  status = 'INVALID'
/

spool off

SET ECHO        off
SET FEEDBACK    off
SET HEADING     off
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     on
SET TIMING      off
SET TRIMOUT     on
SET TRIMSPOOL   on
SET VERIFY      off

@compile

SET FEEDBACK    6
SET HEADING     ON

TOP SEGMENTS IN ORACLE DATABASE BY SIZE

The following script lists out the top segments in your database by size grouping by segment type. 



COLUMN segment_type          FORMAT A20                HEADING 'Segment_Type'
COLUMN owner                        FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment_Name'
COLUMN partition_name         FORMAT A30                HEADING 'Partition_Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace_Name'
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

BREAK ON segment_type SKIP 1

COMPUTE sum OF bytes ON segment_type

SELECT
    a.segment_type      segment_type
  , a.owner             owner
  , a.segment_name      segment_name
  , a.partition_name    partition_name
  , a.tablespace_name   tablespace_name
  , a.bytes             bytes
  , a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

Segment_Type         Owner    Segment_Name     Partition_Name      Tablespace_Name     Bytes          Extents
-----------------               --------    ---------------------    ----------------------     -------------------------     --------           ----------
INDEX                         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOB PARTITION      xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOBSEGMENT         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE               xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE PARTITION xxx         xxxxxxxx           xxxxxxxx               xxxxxxxx                 xxxx               xxxxx

MONITORING TABLESPACE FOR GROWTH,USED and FREE SPACE

As a DBA one of the routine tasks we should do is keeping an eye on the growth of the tablespaces in our databases. It is also important to check which tablespace  has been using how much of the storage space allocated to it and how much is left for use out of it? The point here is to make sure that the database is not in danger of freezing from shortage of space as segments keep on increasing in size. I have posted the following scripts along with respective sample outputs. 

The following script is used to display the tablespace growth over a period of time

set linesize 175 
set pagesize 2000
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
 ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
 max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
 DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name 
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname, days;

DAYS       TSNAME                         CUR_SIZE_GB     USEDSIZE_GB
----------       -------------------            -----------                  -----------
01-11-2012 EMPDATA                        1119.87                1117.33
17-10-2014 EMPDATA                        6145.67                6074.91
18-10-2014 EMPDATA                        6145.67                6078.53
19-10-2014 EMPDATA                        6145.67                6078.54
20-10-2014 EMPDATA                        6145.67                6078.54
21-10-2014 EMPDATA                        6145.67                6078.94
22-10-2014 EMPDATA                        6145.67                6079.16
23-10-2014 EMPDATA                        6145.67                6080.6
24-10-2014 EMPDATA                        6145.67                6086.11
25-09-2014 EMPDATA                        6129.67                6000.02
25-10-2014 EMPDATA                        6145.67                6086.11
26-09-2014 EMPDATA                        6133.67                6043.16
27-09-2014 EMPDATA                        6133.67                6001.93
28-09-2014 EMPDATA                        6133.67                6007.77
29-09-2014 EMPDATA                        6133.67                6018.81

30-09-2014 EMPDATA                        6145.67                6073.52


The following script checks tablespaces for percentage of used and free spaces.


SET LINESIZE 175
SET PAGESIZE 5000
SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
 FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Tablespace                       Size (MB)            Free (MB)            % Free          % Used
------------------------------            ----------                ----------             - ---------       ----------
UNDOTBS1                         32767.9844         21782.875             66             34
TEMP                                  391                        197                       50             50
SYSAUX                             8228                    4052.25                 49             51
SYSTEM                             6530                    2898.1875             44             56