| |
 |
|
Oracle Tips by Burleson |
Monitoring Tablespace Data
Files
As the DBA, you should also monitor the size and location of the
data files associated with the tablespaces under your control. If
for no other reason than to prevent yourself from placing index
tablespace data files alongside those that deal with table data, you
need to have an accurate map of data files. A script to document
tablespace data files is shown in Source 11.17; it produces the
report in Listing 11.14 that provides this data file map.
SOURCE 11.17 Script to document tablespace data files.
REM
REM Name: dbfiles.sql
REM FUNCTION: Document file sizes and locations
REM Use: From SQLPLUS
REM MRA 05/16/99 Added autoextend monitoring
REM MRA 10/14/99 Added temp file monitoring 9i
REM
CLEAR COMPUTES
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'AE?'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SET LINES 130 PAGES 47 VERIFY OFF FEEDBACK OFF
START title132 'DATABASE DATA FILES'
SPOOL rep_out\&db\datafile
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT
tablespace_name,file_name,
bytes/1048576 meg,
status,autoextensible,
maxbytes/1048576 maxmeg,
increment_by
FROM
dba_data_files
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
