Page History

WITH TBLSP_TOTAL AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) ALLOCATED_MB,
         ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024)) MAX_MB
  FROM   DBA_TEMP_FILES
  GROUP BY TABLESPACE_NAME),
TBLSP_FREE AS (
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES)/(1024*1024)) FREE_MB
  FROM   DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME
  UNION ALL
  SELECT TABLESPACE_NAME,
         ROUND(SUM(BYTES_FREE)FREE_SPACE/(1024*1024)) FREE_MB
  FROM   V$TEMPDBA_SPACE_HEADER
  GROUP BY TABLESPACE_NAMETEMP_FREE_SPACE)
SELECT TS.TABLESPACE_NAME           "TABLESPACE",
      (TS.ALLOCATED_MB-FS.FREE_MB)  "Used MB",
       FS.FREE_MB       "Free MB",
       TS.ALLOCATED_MB  "Total MB",
       TS.MAX_MB        "Total Max MB",
       ROUND(100*((TS.MAX_MB-(TS.ALLOCATED_MB-FS.FREE_MB))/TS.MAX_MB)) "Pct. Free"
FROM   TBLSP_TOTAL TS,
       TBLSP_FREE  FS
WHERE  FS.TABLESPACE_NAME = TS.TABLESPACE_NAME
ORDER BY TS.TABLESPACE_NAME;

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.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 V$TEMPDBA_TEMP_FREE_SPACE_HEADER 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;
/
QUIT;