Oracle Database Growth Test

Password – Password of the specified database user

This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

alternate view – In large environments, where the volume of transactions to the Oracle database server is generally very high, this test may take time to execute and retrieve the desired results. To ensure that the test is faster and is resource-efficient, administrators of such environments can create an alternate ‘view’ on the target Oracle database server, and grant select privileges to the view to the special database user mentioned above. Once the view is created, the test should be configured to use the alternate view for metrics collection; to achieve this, specify the name of the view in the alternate view text box. By default, this text box is set to none, which implies that the alternate view is not used by default.

This alternate ‘view’ should be created with the following structure:

CREATE OR REPLACE VIEW <VIEW_NAME> (

TABLESPACE_NAME,

FILE_ID,

BLOCK_ID,

BYTES,

BLOCKS,

RELATIVE_FNO

) AS

select /*+ use_hash (tsfi, fet2) */ tsfi.tablespace_name,

tsfi.file_id,

fet2.block_id,

tsfi.blocksize * fet2.blocks,

fet2.blocks,

tsfi.relfile#

from (select /*+ use_hash (ts, fi) */ ts.name tablespace_name,

fi.file# file_id,

ts.BLOCKSIZE,

fi.relfile#,

ts.ts#

from sys.ts$ ts,

sys.file$ fi

where ts.ts# = fi.ts#

and ts.online$ in (1,4)) Tsfi,

(select f.block# block_id,

f.length blocks,

f.file# file_id,

f.ts#

from sys.fet$ f

union all

select f.ktfbfebno block_id,

f.ktfbfeblks blocks,

f.ktfbfefno,

ktfbfetsn

from sys.x$ktfbfe f) Fet2

where fet2.file_id = tsfi.relfile#

and fet2.ts# = tsfi.ts# ;