| |
 |
|
Oracle Tips by Burleson |
Monitoring Tablespace
Autoextend Settings
In addition to space usage and fragmentation monitoring, if you use
the autoextend feature, you need to monitor database autoextend
status and data file locations. Source 11.16 shows the SQL to create
a view that monitors autoextend data for pre-Oracle8. In pre-Oracle8
versions, the only way to get this information was to query the SYS
table FILEXT$, which, unfortunately, looks like this:
Name Null? Type
-------------------------------- -------------- ------
FILE# NOT NULL NUMBER
MAXEXTEND NOT NULL NUMBER
INC NOT NULL NUMBER
This structure means that, in order to get back to the actual
filename and tablespace, you need to join to several other tables,
namely, FILE$, TS$, and V$DBFILE. A script to create a data file
view is shown in Source 11.16.
SOURCE 11.16 Script to create a data file view.
CREATE VIEW dba_file_data AS
SELECT
a.name tablespace,a.dflminext min_extents,
a.dflmaxext max_extents,
a.dflinit init,a.dflincr next,
a.dflextpct pct_increase, d.name datafile,
b.blocks datafile_size, c.maxextend max_extend,
c.inc ext_incr
FROM ts$ a, file$ b, filext$ c, v$dbfile d
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. |
