Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)
/

Useful Queries for Oracle Applications DBA'S

Script to check if datainstall / hrglobal was effective in completing its actions.


select
  substr(APPLICATION_SHORT_NAME,1,11) APPLICATION,
  substr(LEGISLATION_CODE,1,3) LEG,
  decode(nvl(STATUS,'NULL')
  ,'I','Installed'
               ,' ','Not Installed') STATUS,
  decode(action,'F','Force Install'
                ,'C','Clear'
                ,'U','Upgrade'
                ,'I','Install') ACTION,
  last_update_date
from hr_legislation_installations
where (status is not null or action is not null) order by ACTION, STATUS, legislation_code;


Sample Output

APPLICATI LEG    STATUS        ACTION        LAST_UPDATE_DAT
--------- ------ ------------- ------------- ---------------
PAY       AE     Installed                   18-OCT-15
PER       AE     Installed                   18-OCT-15
PAY       AU     Installed                   18-OCT-15
PER       AU     Installed                   18-OCT-15
PER       CA     Installed                   18-OCT-15
PAY       CA     Installed                   18-OCT-15
PER       CN     Installed                   18-OCT-15
PAY       CN     Installed                   18-OCT-15
PER       ES     Installed                   18-OCT-15
CM        GB     Installed                   18-OCT-15
PAY       GB     Installed                   18-OCT-15
PER       GB     Installed                   18-OCT-15
PER       IE     Installed                   18-OCT-15
PAY       IE     Installed                   18-OCT-15
PER       NL     Installed                   18-OCT-15
PAY       SA     Installed                   18-OCT-15
PER       SA     Installed                   18-OCT-15
PER       SG     Installed                   18-OCT-15
PAY       SG     Installed                   18-OCT-15
GHR       US     Installed                   18-OCT-15
CM        US     Installed                   18-OCT-15
PAY       US     Installed                   18-OCT-15
PER       US     Installed                   18-OCT-15
PER              Installed                   18-OCT-15

24 rows selected.


Script to Check size of fnd_lobs table



SELECT sum( bytes)/1024/1024 size_in_MB
FROM user_segments
WHERE (segment_name = 'FND_LOBS'
OR segment_name in (
SELECT segment_name
FROM user_lobs
WHERE table_name = 'FNS_LOBS'
UNION
SELECT index_name
FROM user_lobs
WHERE table_name = 'FND_LOBS'

) );


Sample Output:


   SIZE_GB SEGMENT_NAME                                                                      SEGMENT_TYPE
---------- --------------------------------------------------------------------------------- ------------------
.873779297 SYS_LOB0000034032C00004$$                                                         LOBSEGMENT

Take Backup of a Package and Package Body

Login to sqlplus as the user who owns the package/package body.

SQL>spool package_name.bkp

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE';

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE BODY';

SQL>Spool off;