61 lines
2.0 KiB
SQL
61 lines
2.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display row count in table or partition
|
|
* Parameters : 1 - OWNER (% - wildchar, \ - escape char)
|
|
* 2 - Table Name (% - wildchar, \ - escape char)
|
|
* 3 - Parition Name (% - wildchar, \ - escape char)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Oct-13 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE partition_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE partition_name="&&3"
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _partition_name NEW_VALUE partition_name NOPRINT
|
|
|
|
set term off
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
, DECODE('&&partition_name','','',' partition (&&partition_name )') "_partition_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT Object Name - &&owner..&&table_name &&partition_name
|
|
PROMPT **************************************************************
|
|
|
|
COLUMN rowcount HEADING "Row Count" FORMAT 999,999,999,999,999
|
|
|
|
SELECT /*+ full(a) parallel(a,16) */
|
|
count(1) rowcount
|
|
FROM &&owner..&&table_name &&partition_name a
|
|
;
|
|
|
|
|
|
@@footer
|