#!/bin/sh
 
source /etc/profile
 
rval=0
 
ORA_USER="AIS_NET"
ORA_PASS="mypass"
SQLPLUS_PATH="$ORACLE_HOME/bin/sqlplus"

if [ -n "$3" ]; then
       ORA_SID="$3"
       export ORACLE_SID=$ORA_SID
fi
 
sql=""
 
case $1 in
 
'job_state')
	if [ -n "$2" ]; then
		echo "
		SELECT to_char(N_JOB_STATE_ID, 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
		" | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} |
		awk '{  if ($1 == 2034) print "Running" 
			else if($1 == 1034) print "Waiting"
			else if($1 == 3034) print "Starting"
			else if($1 == 4034) print "Locked"
			else if($1 == 5034) print "Deleted"
			else if($1 == 6034) print "Error"
			else if($1 == 7034) print "Cant Start"
		}'
	else
		rval=1
		echo "No JOB_ID" >&2
	fi
	;;
'job_last_start')
	if [ -n "$2" ]; then
		sql="
		SELECT to_char((sysdate - D_LAST_START) * (86400), 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
		"
	else
		rval=1
		echo "No JOB_ID" >&2
	fi
	;;
*)
	echo "Hydra monitoring tool"
	echo "usage:"
	echo "    $0 job_state <JOB_ID> [SID]  -- Check job status."
	echo "    $0 job_last_start <JOB_ID> [SID]  -- Check job last start date/time."
	rval=1
	exit $rval
	;;
esac
 
if [ -n "$sql" ]; then
       echo "$sql" | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID}
fi
rval=$?

exit $rval
 

WITH TBLSP_TOTALSPACE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES/(1024*1024))) TOTALSPACE,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) TOTALMAXSPACE
  FROM   DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
),
TBLSP_USEDSPACE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) TOTALUSEDSPACE
  FROM   DBA_SEGMENTS
  GROUP BY TABLESPACE_NAME
)
SELECT TS.TABLESPACE_NAME "TABLESPACE",
       US.TOTALUSEDSPACE "Used MB",
      (TS.TOTALSPACE - US.TOTALUSEDSPACE) "Free MB",
       TS.TOTALSPACE "Total MB",
       TS.TOTALMAXSPACE "Total Max MB",
       ROUND(100*((TS.TOTALMAXSPACE - US.TOTALUSEDSPACE)/ TS.TOTALMAXSPACE)) "Pct. Free"
FROM   TBLSP_TOTALSPACE TS,
       TBLSP_USEDSPACE  US
WHERE  US.TABLESPACE_NAME = TS.TABLESPACE_NAME;

Ошибка при запуске JB_DATA_COLLECT_PKG.EX_DATA_COLLECT_ACCOUNTING
[ORA-01654: unable to extend index AIS_NET.EX_TRAFFIC_COL_C_FIRM_IDX by 8192
in tablespace HYDRA_INDEX]

SELECT COUNT(*)
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID  = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall')
AND    N_CDR_STATE_ID = SYS_CONTEXT('CONST', 'CDR_Status_Finished')
AND    D_BEGIN_ACC   >= TRUNC(SYSDATE, 'HH')
AND   (N_SUM_A IS NULL AND N_SUM_B IS NULL);

SELECT DECODE(MAX(D_LOG_CREATE), NULL, 'Never',
       TO_CHAR(MAX(D_LOG_CREATE), 'DD.MM.YYYY HH24:MI:SS')) Last_Date_Load
FROM   EX_V_CDR
WHERE  N_CDR_ID = (SELECT MAX(N_CDR_ID)
FROM   EX_V_CDR);

LAST_DATE_LOAD
-------------------
29.01.2013 11:05:54

LAST_DATE_LOAD
-------------------
Never

SELECT DECODE(MAX(D_LOG_CREATE), NULL, -1,
       TO_CHAR((SYSDATE-MAX(D_LOG_CREATE))*(86400), 'FM99999999999999990')) SEC
FROM   EX_V_CDR
WHERE  N_CDR_ID = (SELECT MAX(N_CDR_ID)
FROM   EX_V_CDR);

SQL> SELECT COUNT(*)
FROM SD_GOOD_MOVES;

CREATE USER &&username PROFILE DEFAULT IDENTIFIED BY &&password
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
/ 
GRANT SELECT ON V_$LOG_HISTORY TO &&username;
  
GRANT CONNECT TO &&username;
GRANT RESOURCE TO &&username;
  
GRANT SELECT ON SS_V_JOBS TO &&username;
GRANT SELECT ON SI_V_USERS TO &&username;
GRANT SELECT ON SS_V_JOB_SEANCES TO &&username;
  
GRANT EXECUTE ON SI_SUBJECTS_PKG_S TO &&username;
GRANT EXECUTE ON SI_OBJECTS_PKG_S TO &&username;
GRANT EXECUTE ON SI_REF_PKG_S TO &&username;
 
-- count.cdr; count.lastcdr
GRANT SELECT ON EX_V_CDR TO &&username;
-- count.gm
GRANT SELECT ON SD_GOOD_MOVES TO &&username;
-- count.ecrd
GRANT SELECT ON EX_CALL_DATA_REC TO &&username;
-- count.edc
GRANT SELECT ON EX_DATA_COLLECT TO &&username;
-- count.etcc
GRANT SELECT ON EX_TRAFFIC_COLLECT_C TO &&username;
-- count.active
GRANT SELECT ON SI_SUBJ_GOODS TO &&username;
-- tblspace.discovery
GRANT SELECT ON DBA_SEGMENTS TO &&username;
-- tblspace.pcf
GRANT SELECT ON DBA_DATA_FILES TO &&username;
GRANT SELECT ON DBA_SEGMENTS TO &&username;
-- checkactive
GRANT SELECT ON V_$INSTANCE TO &&username;
-- rcachehit
GRANT SELECT ON V_$SYSSTAT TO &&username;
-- activeusercount
GRANT SELECT ON V_$SESSION TO &&username;
-- dbsize
GRANT SELECT ON DBA_FREE_SPACE TO &&username;
GRANT SELECT ON DBA_TABLESPACES TO &&username;
-- lastarclog
GRANT SELECT ON V_$LOG TO &&username;
-- freebufwaits
GRANT SELECT ON V_$SYSTEM_EVENT TO &&username;
GRANT SELECT ON V_$EVENT_NAME TO &&username;
/
QUIT;