Page History

-- Не были тарифицированы или были тарифицированы неуспешно
SELECT COUNT(*)
FROM (
 EX_V_CDR
WHERE   SELECT EC.N_CDR_ID
  FROM EX_CDR EC
  WHERE NOT EXISTS (
  SELECT 1
  FROM SS_CDR_DETAILS SCD
  WHERE SCD.N_CDR_ID = EC.N_CDR_TYPE_ID
  AND SCD.C_ACTIVE = 'Y')
  AND EC.D_END >= SYSDATE - 1/24
  AND EC.N_CDR_STATE_ID =   SYS_CONTEXT('CONST',   'CDR_TYPEStatus_PhoneCallFinished')
  AND EC.N_DURATION_SEC != 0
  AND EC.C_ACTIVE = 'Y'

  UNION ALL

  SELECT EC.N_CDR_ID
  FROM EX_CDR EC
  INNER JOIN SS_CDR_DETAILS SCD
  ON SCD.N_CDR_ID = EC.N_CDR_ID
  AND SCD.N_RATING_STATE_ID  =   SYS_CONTEXT('CONST',   'CDR_StatusRATING_FinishedSTATE_Fail')
AND  WHERE  EC.D_END          >= SYSDATE - 1/24
AND  AND (EC.N_SUMCDR_ASTATE_ID IS= NULL AND N_SUM_B IS NULL)
AND    SYS_CONTEXT('CONST',   'CDR_Status_Finished')
  AND EC.N_DURATION_SEC != 0
  AND EC.C_ACTIVE = 'Y'
);

SELECT DECODE(MAX(D_LOG_CREATE), NULL, 'Never',
       TO_CHAR(MAX(D_LOG_CREATE), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_LOAD_DATE
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall')_TEL;

SELECT DECODE(MAX(D_LOG_CREATE), NULL, -1, TO_CHAR((SYSDATE-MAX(D_LOG_CREATE))*(60*60*24), 'FM99999999999999990')) N_LAST_LOAD_SEC
FROM   EX_V_CDR
WHERE  N_CDR_TYPE_ID = SYS_CONTEXT('CONST', 'CDR_TYPE_PhoneCall')_TEL;

SELECT DECODE(MAX(D_ENDFINISH), NULL, 'Never',
       TO_CHAR(MAX(D_ENDFINISH), 'DD.MM.YYYY HH24:MI:SS')) VC_LAST_UPDATE
FROM   EXEP_V_CDR
WHERE  N_CDR_TYPE_ID IN (SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WithCharging'), SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WOCharging'))SESSIONS;

SELECT DECODE(MAX(D_ENDFINISH), NULL, -1, TO_CHAR((SYSDATE-MAX(D_ENDFINISH))*(60*60*24), 'FM99999999999999990')) N_LAST_UPDATE_SEC
FROM   EXEP_V_CDR
WHERE  N_CDR_TYPE_ID IN (SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WithCharging'), SYS_CONTEXT('CONST', 'CDR_TYPE_PPP_WOCharging'))SESSIONS;

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 SELECT ON V_$PARAMETER 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 SELECT ON SS_V_MANAGER_JOBS 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.q*
GRANT SELECT ON SS_V_EVENTS_QUEUE to &&username;
-- count.dbc
GRANT SELECT ON V_$DATABASE_BLOCK_CORRUPTION TO &&username;
-- count.uretenop
GRANT SELECT ON V_$UNDOSTAT 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_FREE_SPACE TO &&username;
GRANT SELECT ON DBA_TEMP_FILES TO &&username;
GRANT SELECT ON DBA_TEMP_FREE_SPACE TO &&username;
GRANT SELECT ON V_$TEMP_EXTENT_POOL TO &&username;
GRANT SELECT ON DBA_UNDO_EXTENTS 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;
GRANT SELECT ON EX_V_PAYMENTS TO &&username;
GRANT SELECT ON SS_SESSION_LOGS TO &&username;
-- dpiStmt_execute
GRANT SELECT ON V_$TEMP_EXTENT_POOL TO &&username;
GRANT SELECT ON DBA_UNDO_EXTENTS TO &&username;
/
QUIT;