44 lines
1.2 KiB
MySQL
44 lines
1.2 KiB
MySQL
|
@@header
|
||
|
|
|
||
|
|
/*
|
||
|
|
*
|
||
|
|
* Author : Vishal Gupta
|
||
|
|
* Purpose : Display SYSAUX occupant's tablespace space usage.
|
||
|
|
* Parameter : None
|
||
|
|
*
|
||
|
|
*
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ -----------------------------------------
|
||
|
|
* 20-May-15 Vishal Gupta Created
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT ######################
|
||
|
|
PROMPT SYSAUX Space Usage
|
||
|
|
PROMPT ######################
|
||
|
|
|
||
|
|
COLUMN occupant_name HEADING "Occupant Name" FORMAT a24
|
||
|
|
COLUMN schema_name HEADING "Schema Name" FORMAT a20
|
||
|
|
COLUMN space_usage HEADING "Space|Usage|(MB)" FORMAT 99,999,999
|
||
|
|
COLUMN move_procedure HEADING "Move Procedure" FORMAT a35
|
||
|
|
COLUMN move_procedure_desc HEADING "Move Procedure Desc" FORMAT a40
|
||
|
|
COLUMN occupant_desc HEADING "Occupant Desc " FORMAT a60
|
||
|
|
|
||
|
|
BREAK ON REPORT
|
||
|
|
COMPUTE SUM LABEL "Total" OF space_usage ON REPORT FORMAT 99,999,999
|
||
|
|
|
||
|
|
SELECT occupant_name
|
||
|
|
, round( space_usage_kbytes/1024) space_usage
|
||
|
|
, schema_name
|
||
|
|
, move_procedure
|
||
|
|
--, move_procedure_desc
|
||
|
|
, occupant_desc
|
||
|
|
FROM v$sysaux_occupants
|
||
|
|
ORDER BY space_usage desc
|
||
|
|
;
|
||
|
|
|
||
|
|
@@footer
|