BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring Tablespace Autoextend Settings

Oracle Tips by Burleson Consulting

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 Remote 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
WHERE
  a.ts#=b.ts# and b.file#=c.file# and b.file#=d.file#
/

This script creates the view Remote DBA_FILE_DATA, which will look like this when queried:

Name                                 Null?         Type
-----------------------------      --------      ------------
TABLESPACE                         NOT NULL      VARCHAR2(30)
MIN_EXTENTS                        NOT NULL      NUMBER
MAX_EXTENTS                        NOT NULL      NUMBER
INIT                               NOT NULL      NUMBER
NEXT                               NOT NULL      NUMBER
PCT_INCREASE                       NOT NULL      NUMBER
DATAFILE                                         VARCHAR2(257)
DATAFILE_SIZE                      NOT NULL      NUMBER
MAX_EXTEND                         NOT NULL      NUMBER
EXT_INCR                           NOT NULL      NUMBER

Using this view, it is then easy to create a simple SELECT that gets autoextend and data file location information from a single view, along with all of the pertinent sizing information. The Oracle Administrator Storage Manager does show this information under the data files section. In Oracle8i, the view Remote DBA_DATA_FILES contains the columns AUTOEXTENSIBLE (YES or NO), MAXBYTES, MAXBLOCKS, and INCREMENT_BY, which allow for ease of report generation and monitoring of the autoextension capabilities of all data files.

Monitoring Tablespace Data Files

As the Remote 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
     Remote DBA_data_files
UNION
SELECT
     tablespace_name,file_name,
     bytes/1048576 meg,
     status,autoextensible,
     maxbytes/1048576 maxmeg,
     increment_by
FROM
     Remote DBA_temp_files
ORDER BY
     tablespace_name
/
SPOOL OFF
SET VERIFY ON feedback ON
TTITLE OFF
CLEAR COLUMNS
CLEAR COMPUTES
PAUSE Press Enter to continue

LISTING 11.14 Example of output from data file report.

Date: 10/14/01                                                                               Page:   1   
Time: 12:59 PM                                 DATABASE DATA FILES                           Remote DBAUTIL
                                                galinux1 database

                                                                                         Max     Inc
Tablespace    File Name                                   Megabytes Status     AE? Megabytes      By
------------- ------------------------------------------ ---------- ---------- --- --------- -------
CWMLITE       /var/oracle/oradata/galinux1/cwmlite01.dbf      20.00 AVAILABLE  YES    32,768      80
*************                                            ----------                                 
sum                                                           20.00
                                                                                             
                                    
Remote DBAUTIL_DATA  /opt/oracle/oradata/galinux1/dbtl_dat1.dbf      10.00 AVAILABLE  NO          0       0
*************
sum                                                           10.00
          
Remote DBAUTIL_INDEX /opt/oracle/oradata/galinux1/dbtl_idx1.dbf      10.00 AVAILABLE  NO          0       0
*************
sum                                                           10.00

DRSYS         /var/oracle/oradata/galinux1/drsys01.dbf        20.00 AVAILABLE  YES    32,768      80
*************                                            ----------
sum                                                           20.00

EXAMPLE       /var/oracle/oradata/galinux1/example01.dbf      36.25 AVAILABLE  YES    32,768      80
*************                                            ----------sum                                                           36.25

INDX          /var/oracle/oradata/galinux1/indx01.dbf         25.00 AVAILABLE  YES    32,768     160
*************                                            ----------sum                                                           25.00

SYSTEM        /var/oracle/oradata/galinux1/system01.dbf      465.00 AVAILABLE  YES    32,768   1,280
*************                                            ----------sum                                                          465.00

TEMP          /var/oracle/oradata/galinux1/temp01.dbf         40.00 AVAILABLE  NO          0       0
              /var/oracle/oradata/galinux1/temp02.dbf         50.00 AVAILABLE  NO          0       0
*************                                            ----------
sum                                                           90.00
                           
TOOLS         /var/oracle/oradata/galinux1/tools01.dbf        10.00 AVAILABLE  YES    32,768      40
*************                                            ----------sum                                                           10.00

UNDOTBS       /var/oracle/oradata/galinux1/undotbs01.dbf     200.00 AVAILABLE  YES    32,768     640
*************                                            ----------sum                                                          200.00
                           
UNDO_TBS2     /opt/oracle/oradata/galinux1/undo_tbs2.dbf      20.00 AVAILABLE  YES    32,768       1
*************                                            ----------sum                                                           20.00

UNDO_TBS3     /opt/oracle/oradata/galinux1/undo_tbs3.dbf      20.00 AVAILABLE  YES        30       1
*************                                            ----------
sum                                                           20.00
                         
USERS         /var/oracle/oradata/galinux1/users01.dbf        25.00 AVAILABLE  YES    32,768     160
*************                                            ----------sum                                                           25.00
                                                         ----------
sum                                                          951.25

Monitoring Tablespace Extent Mapping

A final set of freespace information that is handy to have around (OEM and many third-party tools will provide a GUI-based map) is the location of the freespace in a tablespace and the size of the fragments themselves. The script in Source 11.18 provides this data. The output from the script in Source 11.18 is shown in Listing 11.15.

SOURCE 11.18 Script to document freespace extents inside a tablespace.

rem
rem Name: mapper.sql
rem Function: create an extent map for a specific tablespace
rem  Based on a technique from Remote DBA Handbook
rem Mike Ault 7/19/96
rem
SET PAGES 47 LINES 132 VERIFY OFF feedback OFF
COLUMN file_id       HEADING 'File|id'
COLUMN value         NEW_VALUE dbblksiz NOPRINT
COLUMN meg            FORMAT 9,999.99 HEADING 'Meg'
COLUMN partition_name FORMAT a30      HEADING 'Partition|Name'
SELECT value FROM v$parameter WHERE name='db_block_size';
START title132 '&&ts Mapping Report'
SPOOL rep_out/&db/ts_map
SELECT
     'free space' owner, '      ' object,'Not Part.' partition
     file_id, block_id, blocks,
     (blocks*&dbblksiz)/(1024*1024) meg
FROM
     Remote DBA_free_space
WHERE
     tablespace_name=UPPER('&&ts')
UNION
SELECT
     SUBSTR(owner,1,20), SUBSTR(segment_name, 1,32),partition_name
     file_id, block_id, blocks,
     (blocks*&dbblksiz)/(1024*1024) meg
FROM
     Remote DBA_extents
WHERE
     tablespace_name = UPPER('&&ts')
ORDER BY 3,4;
SPOOL OFF
UNDEF ts
SET PAGES 22 LINES 80 VERIFY ON feedback ON
CLEAR COLUMNS
TTITLE OFF

LISTING 11.15     Example of output of the mapper script.

Date: 06/13/97                                         Page:   1
Time: 07:29 PM        raw_data Mapping Report             SYSTEM
                         ORTEST1 database            
                                                       
                              File
OWNER      OBJECT               id  BLOCK_ID    BLOCKS       MEG
---------- --------------     ----  --------    ------    ------
TELE_Remote DBA   LOAD_TEST            11         2    102655    401.00
TELE_Remote DBA   LOAD_TEST            11    102657     25600    100.00
TELE_Remote DBA   LOAD_TEST            11    128257     25600    100.00
SYSTEM     PARTITION_TEST       11    153857       260      1.02
SYSTEM     PARTITION_TEST       11    154117       260      1.02
SYSTEM     PARTITION_TEST       11    154377       260      1.02
free space                      11    154637     24564     95.95


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter