Patch:
++++
SELECT
DISTINCT RPAD(a.bug_number,11)
|| RPAD(e.patch_name,11)
|| RPAD(TRUNC(c.end_date),12)
|| RPAD(b.applied_flag,4)
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('16278172','16502567') ORDER BY 1 DESC;
$ORACLE_HOME/OPatch/opatch lsinventory |grep -i ^Patch
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE'
export PATH=$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep -i "PATCH SET UPDATE"
$ export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
$ date
$ opatch lsinventory|grep ^Patch
export PATH=$PATH:$ORACLE_HOME/OPatch
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc -bugs_fixed | grep -i 'PATCH SET UPDATE'
export PATH=$PATH:$ORACLE_HOME/OPatch
URL:
++++
select home_url from apps.icx_paratmeter;
Redo Details:
+++++++++
set lines 200 pages 200
col GROUP for a10
col TYPE for a20
col MEMBER for a100
col IS_ for a70
select * from v$log;
select * from v$logfile order by GROUP#;
Invalid Object:
++++++++++
spool /tmp/md/invalid_obj_after.txt
set pages 10000 lines 150
col owner for a30
col object_name for a50
col object_type for a30
select owner, object_name, object_type,status
from dba_objects
where status like 'INVALID'
order by 1,2,3 asc ;
exit ;
============
Child request:
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
============
Manager name:
select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a,
apps.fnd_concurrent_queues_vl b, apps.fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id ='&request_id';
To get Hash Values from SQLID:
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM dba_hist_snapshot ss
WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
AND q.sql_id IN ( '&SQLID');
Schema Size:
set linesize 150
set pagesize 5000
col owner for a155
select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;
Find OPP Log File Using Concurrent Request ID:
Select
fcpp.concurrent_request_id req_id,
fcp.node_name,
fcp.logfile_name
FROM
fnd_conc_pp_actions fcpp,
fnd_concurrent_processes fcp
where
fcpp.processor_id = fcp.concurrent_process_id
AND
fcpp.action_type = 6
AND
fcpp.concurrent_request_id ='&1';
To check Indexes are on a Table:
SELECT index_name
FROM all_indexes
WHERE table_name = `&table_name`;
To check which Columns are Indexed on a Table:
SELECT column_name, index_name
FROM all_ind_columns
WHERE table_name = `&table_name`;
U = unique index
N = non-unique index
To Check Triggers on a Table:
SELECT trigger_name
FROM all_triggers
WHERE table_name = `&table_name`;
To check the maximum number of allowed connections to an Oracle database:
SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions';
Currently, 2311 out of 6024 connections are used.
SQL>
To get Forms run away process:
ps -ef|grep 26346
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
SQL> SQL> SQL> 2 3 4 5 Enter value for spid: 26346
old 4: and b.spid='&spid'
new 4: and b.spid='26346'
no rows selected
SQL>
===========
DPSERVER_STATUS
set heading off echo off feedback off
SELECT DECODE(Cdr_Exe_Service.getSrvcLocLockStatus(LOCATION_NAME,0) ,1,'Running' ,0,'Down') DPSERVER_STATUS_RUNNING FROM (select csrvloc.location_name from apps.cdr_service_locations csrvloc , apps.fnd_nodes fnod,apps.cdr_services csrv where upper(csrvloc.machine) like (upper(fnod.node_name)||'%') and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID) where rownum=1;
select * from (select ':'||csrvloc.machine||':' from apps.cdr_service_locations csrvloc , apps.fnd_nodes fnod, apps.cdr_services csrv where upper(csrvloc.machine) like (upper(fnod.node_name)||'%')and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID) where rownum=1;
select * from (select csrv.ROOT_DIRECTORY from cdr_services csrv,apps.cdr_service_locations csrvloc, fnd_nodes fnod where csrv.SERVICE_LOCATION_ID=csrvloc.SERVICE_LOCATION_ID and upper(csrvloc.machine) like (upper(fnod.node_name)||'%') and csrv.ROOT_DIRECTORY is not null) where rownum=1;
SAS-------------------------
set heading off echo off feedback off
SELECT DECODE(Cdr_Exe_Service.getSrvcLocLockStatus(LOCATION_NAME,0) ,1,'Running' ,0,'Down') DPSERVER_STATUS_RUNNING FROM (select csrvloc.location_name from apps.cdr_service_locations csrvloc ,apps.cdr_services csrv where LOCATION_NAME like '%SAS%' and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID)where rownum=1;
select * from (select ':'||machine||':' from apps.cdr_service_locations where upper(LOCATION_NAME) like '%SAS%') where rownum=1;
select * from (select csrv.ROOT_DIRECTORY from cdr_services csrv,apps.cdr_service_locations csrvloc where csrv.SERVICE_LOCATION_ID=csrvloc.SERVICE_LOCATION_ID and upper(csrvloc.LOCATION_NAME) like '%SAS%' and csrv.ROOT_DIRECTORY is not null) where rownum=1;
++++
SELECT
DISTINCT RPAD(a.bug_number,11)
|| RPAD(e.patch_name,11)
|| RPAD(TRUNC(c.end_date),12)
|| RPAD(b.applied_flag,4)
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in ('16278172','16502567') ORDER BY 1 DESC;
$ORACLE_HOME/OPatch/opatch lsinventory |grep -i ^Patch
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE'
export PATH=$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep -i "PATCH SET UPDATE"
$ export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
$ date
$ opatch lsinventory|grep ^Patch
export PATH=$PATH:$ORACLE_HOME/OPatch
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc -bugs_fixed | grep -i 'PATCH SET UPDATE'
export PATH=$PATH:$ORACLE_HOME/OPatch
URL:
++++
select home_url from apps.icx_paratmeter;
Redo Details:
+++++++++
set lines 200 pages 200
col GROUP for a10
col TYPE for a20
col MEMBER for a100
col IS_ for a70
select * from v$log;
select * from v$logfile order by GROUP#;
Invalid Object:
++++++++++
spool /tmp/md/invalid_obj_after.txt
set pages 10000 lines 150
col owner for a30
col object_name for a50
col object_type for a30
select owner, object_name, object_type,status
from dba_objects
where status like 'INVALID'
order by 1,2,3 asc ;
exit ;
Instance:
+++++++++
set lines 1000
set pages 60
col host_name format a25
select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,to_char(STARTUP_TIME,'DD-MON-RRRR HH:MM:SS') "STARTUP_TIME",STATUS,LOGINS from gv$instance order by 1;
select name,open_mode,controlfile_type from gv$database;
select database_role from gv$database;
Node Details:
+++++++++++++
set lines 200
col host_name for a20
col node_name for a20
col database for a10
col concmgr for a9
col forms for a7
col webserver for a11
col admin for a7
select
NODE_NAME,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'YES','NO') ConcMgr,
decode(SUPPORT_FORMS,'Y','YES', 'NO') Forms,
decode(SUPPORT_WEB,'Y','YES', 'NO') WebServer,
decode(SUPPORT_ADMIN, 'Y','YES', 'NO') Admin,
decode(SUPPORT_DB, 'Y','YES', 'NO') Database
from apps.fnd_nodes
where node_name != 'AUTHENTICATION' order by NODE_NAME;
>>>>>
set lines 1000
set pages 60
col host_name format a25
select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,to_char(STARTUP_TIME,'DD-MON-RRRR HH:MM:SS') "STARTUP_TIME",STATUS,LOGINS from gv$instance order by 1;
select node_name,to_char(CREATION_DATE,'DD-MON-RRRR HH:MM:SS') "Created on", to_char(LAST_UPDATE_DATE,'DD-MON-RRRR HH:MM:SS') "Updated On", SUPPORT_CP "CP", SUPPORT_FORMS "FORMS",SUPPORT_WEB "WEB",SUPPORT_ADMIN "ADMIN",STATUS from apps.fnd_nodes order by 2;
select home_url from apps.icx_parameters;
select release_name from apps.fnd_product_groups;
Password Creation:
+++++++++++++
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password>12 entries=5
tuning related
++++++++++
var v_num number;
exec:v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'2ym9kd2wcbr77',plan_hash_value => 4127271796);
==================
select s.INST_ID,s.SID,s.serial#,s.event,s.last_call_et,s.SQL_ID,r.PHASE_CODE,r.STATUS_CODE,r.PARENT_REQUEST_ID,r.ACTUAL_START_DATE,r.ACTUAL_COMPLETION_DATE from gv$session s,apps.fnd_concurrent_requests r where s.audsid=r.oracle_session_id and r.request_id=16624610;
select table_name, stale_stats,last_analyzed from dba_tab_statistics where table_name='&1';
select owner, index_name, last_analyzed, global_stats from dba_indexes where index_name='&1';
select * from table(dbms_xplan.display_awr('d6xu9cxq57ant'));
Killing in RAC ::::::::::
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where osuser = 'xxwwexp' and machine in ('ccixapp-prod1-128','ccixapp-prod2-155');
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where sql_id in ('az41pugxbqdha','cmndhqbj6h5dj','d6xu9cxq57ant');
=======================
select s.snap_id,to_char(s.begin_interval_time,'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, q.EXECUTIONS_DELTA "executions"
, q.VERSION_COUNT "VersionCount"
, q.INVALIDATIONS_DELTA "Invalidation"
, round(disk_reads_delta/executions_delta) "Phy IO/Exec"
, round(buffer_gets_delta/executions_delta) "LogIO/Exec"
, round(elapsed_time_delta*0.0000001/executions_delta,2) "Elapsed_Time_In_Sec/Exec"
, round(cpu_time_delta*0.0000001/executions_delta,2) "CPU_Time_In_Sec/Exec"
, round(rows_processed_delta/executions_delta) "Rows/Exec"
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sql_id')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and q.executions_delta > 0
and begin_interval_time BETWEEN (SYSDATE - nvl(to_number('&num_days'),1)) AND SYSDATE
order by q.sql_id,s.snap_id desc;
Password Creation:
+++++++++++++
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password>12 entries=5
tuning related
++++++++++
var v_num number;
exec:v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'2ym9kd2wcbr77',plan_hash_value => 4127271796);
==================
select s.INST_ID,s.SID,s.serial#,s.event,s.last_call_et,s.SQL_ID,r.PHASE_CODE,r.STATUS_CODE,r.PARENT_REQUEST_ID,r.ACTUAL_START_DATE,r.ACTUAL_COMPLETION_DATE from gv$session s,apps.fnd_concurrent_requests r where s.audsid=r.oracle_session_id and r.request_id=16624610;
select table_name, stale_stats,last_analyzed from dba_tab_statistics where table_name='&1';
select owner, index_name, last_analyzed, global_stats from dba_indexes where index_name='&1';
select * from table(dbms_xplan.display_awr('d6xu9cxq57ant'));
Killing in RAC ::::::::::
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where osuser = 'xxwwexp' and machine in ('ccixapp-prod1-128','ccixapp-prod2-155');
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where sql_id in ('az41pugxbqdha','cmndhqbj6h5dj','d6xu9cxq57ant');
=======================
select s.snap_id,to_char(s.begin_interval_time,'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, q.EXECUTIONS_DELTA "executions"
, q.VERSION_COUNT "VersionCount"
, q.INVALIDATIONS_DELTA "Invalidation"
, round(disk_reads_delta/executions_delta) "Phy IO/Exec"
, round(buffer_gets_delta/executions_delta) "LogIO/Exec"
, round(elapsed_time_delta*0.0000001/executions_delta,2) "Elapsed_Time_In_Sec/Exec"
, round(cpu_time_delta*0.0000001/executions_delta,2) "CPU_Time_In_Sec/Exec"
, round(rows_processed_delta/executions_delta) "Rows/Exec"
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sql_id')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and q.executions_delta > 0
and begin_interval_time BETWEEN (SYSDATE - nvl(to_number('&num_days'),1)) AND SYSDATE
order by q.sql_id,s.snap_id desc;
============
Child request:
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
============
Manager name:
select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a,
apps.fnd_concurrent_queues_vl b, apps.fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id ='&request_id';
To get Hash Values from SQLID:
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM dba_hist_snapshot ss
WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
AND q.sql_id IN ( '&SQLID');
Schema Size:
set linesize 150
set pagesize 5000
col owner for a155
select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;
Find OPP Log File Using Concurrent Request ID:
Select
fcpp.concurrent_request_id req_id,
fcp.node_name,
fcp.logfile_name
FROM
fnd_conc_pp_actions fcpp,
fnd_concurrent_processes fcp
where
fcpp.processor_id = fcp.concurrent_process_id
AND
fcpp.action_type = 6
AND
fcpp.concurrent_request_id ='&1';
To check Indexes are on a Table:
SELECT index_name
FROM all_indexes
WHERE table_name = `&table_name`;
To check which Columns are Indexed on a Table:
SELECT column_name, index_name
FROM all_ind_columns
WHERE table_name = `&table_name`;
U = unique index
N = non-unique index
To Check Triggers on a Table:
SELECT trigger_name
FROM all_triggers
WHERE table_name = `&table_name`;
To check the maximum number of allowed connections to an Oracle database:
SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions';
Currently, 2311 out of 6024 connections are used.
SQL>
To get Forms run away process:
ps -ef|grep 26346
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
SQL> SQL> SQL> 2 3 4 5 Enter value for spid: 26346
old 4: and b.spid='&spid'
new 4: and b.spid='26346'
no rows selected
SQL>
===========
DPSERVER_STATUS
set heading off echo off feedback off
SELECT DECODE(Cdr_Exe_Service.getSrvcLocLockStatus(LOCATION_NAME,0) ,1,'Running' ,0,'Down') DPSERVER_STATUS_RUNNING FROM (select csrvloc.location_name from apps.cdr_service_locations csrvloc , apps.fnd_nodes fnod,apps.cdr_services csrv where upper(csrvloc.machine) like (upper(fnod.node_name)||'%') and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID) where rownum=1;
select * from (select ':'||csrvloc.machine||':' from apps.cdr_service_locations csrvloc , apps.fnd_nodes fnod, apps.cdr_services csrv where upper(csrvloc.machine) like (upper(fnod.node_name)||'%')and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID) where rownum=1;
select * from (select csrv.ROOT_DIRECTORY from cdr_services csrv,apps.cdr_service_locations csrvloc, fnd_nodes fnod where csrv.SERVICE_LOCATION_ID=csrvloc.SERVICE_LOCATION_ID and upper(csrvloc.machine) like (upper(fnod.node_name)||'%') and csrv.ROOT_DIRECTORY is not null) where rownum=1;
SAS-------------------------
set heading off echo off feedback off
SELECT DECODE(Cdr_Exe_Service.getSrvcLocLockStatus(LOCATION_NAME,0) ,1,'Running' ,0,'Down') DPSERVER_STATUS_RUNNING FROM (select csrvloc.location_name from apps.cdr_service_locations csrvloc ,apps.cdr_services csrv where LOCATION_NAME like '%SAS%' and csrvloc.SERVICE_LOCATION_ID in csrv.SERVICE_LOCATION_ID)where rownum=1;
select * from (select ':'||machine||':' from apps.cdr_service_locations where upper(LOCATION_NAME) like '%SAS%') where rownum=1;
select * from (select csrv.ROOT_DIRECTORY from cdr_services csrv,apps.cdr_service_locations csrvloc where csrv.SERVICE_LOCATION_ID=csrvloc.SERVICE_LOCATION_ID and upper(csrvloc.LOCATION_NAME) like '%SAS%' and csrv.ROOT_DIRECTORY is not null) where rownum=1;
================
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/ts_free_space.sql
-- Author : Tim Hall
-- Description : Displays a list of tablespaces and their used/full status.
-- Requirements : Access to the DBA views.
-- Call Syntax : @ts_free_space.sql
-- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql
-- 22-SEP-2017 - LINESIZE set.
-- -----------------------------------------------------------------------------------
Identify Tablespaces with Free Space:
SET PAGESIZE 140 LINESIZE 200
COLUMN used_pct FORMAT A11
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
ORDER BY tablespace_name;
SET PAGESIZE 14
