ORACLE_HOME=/oravl01/oracle/12.1.0.2
export ORACLE_HOME
ORACLE_SID=PRDCM2
export ORACLE_SID
NLS_LANG=AMERICAN_AMERICA
export NLS_LANG
unset ORA_NLS33
unset ORA_NLS10
unset ORA_NLS11
$ORACLE_HOME/bin/sqlplus -S -L "/ AS SYSDBA" << EOF

set pages 0
set linesize 200
set long 99999999
set head off
SELECT dbms_xmlgen.getxml('WITH SEGMENT_IGNORED AS (
							SELECT
								distinct
								c.OWNER,
								d.OBJECT_NAME
								FROM
								DBA_AWS c
								JOIN 
								DBA_OBJECTS d 
									ON c.owner = d.OWNER 
									AND ''AW\$''||c.AW_NAME = d.OBJECT_NAME
								WHERE 
								d.OBJECT_TYPE like ''%PARTITION%''
							UNION ALL
							SELECT 
								DISTINCT
								e.OWNER,
								g.OBJECT_NAME
							FROM
								DBA_AWS e
								JOIN DBA_LOBS f 
									ON e.OWNER = f.OWNER 
									AND ''AW\$''||e.AW_NAME = f.table_name
								JOIN DBA_OBJECTS g 
									ON f.OWNER = g.OWNER 
									AND f.SEGMENT_NAME = g.OBJECT_NAME
							WHERE 
								g.object_type like ''%PARTITION%''
							UNION ALL
									SELECT 
									DISTINCT
									e.OWNER, 
									g.object_name
								FROM
									DBA_AWS e
									JOIN DBA_INDEXES f on e.OWNER = f.table_owner and ''AW\$''||e.aw_name = f.table_name
									JOIN DBA_OBJECTS g on f.owner = g.OWNER and f.INDEX_NAME = g.OBJECT_NAME
								WHERE
									g.OBJECT_TYPE like ''%PARTITION%''			
							),
							SEGMENTS AS (
								SELECT OWNER, OBJECT_NAME FROM DBA_RECYCLEBIN WHERE TYPE NOT LIKE ''%Partition%'' AND  (OWNER, OBJECT_NAME) in (select OWNER, OBJECT_NAME from DBA_RECYCLEBIN where TYPE like ''%Partition%'')
								UNION ALL 
								SELECT OWNER, OBJECT_NAME FROM DBA_RECYCLEBIN WHERE TYPE like ''%Partition%''
							)
							SELECT
							''Partitioning'' as "Option",
							(select COUNT(*) from V\$OPTION where PARAMETER=''Partitioning'' and value=''TRUE'') as "Installed",
							(
								SELECT 
									COUNT(*) 
								FROM 
									SEGMENTS a 
									LEFT OUTER JOIN SEGMENT_IGNORED i
										ON a.OWNER = i.OWNER
										AND a.OBJECT_NAME = i.OBJECT_NAME 
								WHERE 
									a.OWNER NOT IN (''APPLSYS'', ''APPS'', ''AR'', ''BIL'', ''BOM'',
									''ENG'', ''EGO'', ''SH'', ''SYSTEM'', ''SYS'', ''WIRELESS'', ''CLKRT'', ''HR'', ''ISC'', ''MSC'', ''AP'',
									''JTF'', ''OPI'', ''OSM'', ''PJI'', ''PA'', ''PO'', ''AUDSYS'')
									AND i.OWNER IS NULL
								) as "Used"
							FROM DUAL') xml from dual;

EOF