59 lines
1.7 KiB
SQL
59 lines
1.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tablespace usage
|
|
* Parameters : 1 - group_name (Use % as wildcard, Default value '%')
|
|
* 2 - tablespace_name (Use % as wildcard, Default value '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 24-Jun-16 Vishal Gupta Created
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE group_name
|
|
UNDEFINE tablespace_name
|
|
DEFINE group_name="&&1"
|
|
DEFINE tablespace_name="&&2"
|
|
|
|
set term off
|
|
COLUMN _group_name NEW_VALUE group_name NOPRINT
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&group_name' ,'','%','&&group_name')) "_group_name"
|
|
, UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * Tablespace Groups
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Group Name = '&&group_name'
|
|
PROMPT * - Tablespace Name = '&&TABLESPACE_NAME'
|
|
PROMPT *****************************************************************
|
|
|
|
COLUMN group_name HEADING "GroupName" FORMAT a30
|
|
COLUMN tablespace_name HEADING "TablespaceName" FORMAT a30
|
|
|
|
BREAK ON REPORT ON group_name
|
|
|
|
SELECT group_name
|
|
, tablespace_name
|
|
FROM dba_tablespace_groups
|
|
WHERE group_name like '&&group_name'
|
|
AND tablespace_name like '&&tablespace_name'
|
|
ORDER BY group_name
|
|
, tablespace_name
|
|
;
|
|
|
|
@@footer
|