DATABASE VIEW NOTES

 

Views

DATABASE VIEW NOTES:

Owner - Owner of the view

View Name - Name of the view

Created - Date/time view was created

Status - Status of the view

select  OWNER,
 OBJECT_NAME,
 to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
 status
from   dba_objects
where OWNER not in ('SYS','SYSTEM')
and OBJECT_TYPE='VIEW'
order by OWNER,OBJECT_NAME

DATABASE TRIGGER NOTES

 

Triggers

DATABASE TRIGGER NOTES:

Table Owner - Owner of the table

Table Name - Name of the table

Trigger Name - Name of the trigger

Trigger Name - When the trigger fires (BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, AFTER STATEMENT)

Triggering Event - Statement that fires the trigger (INSERT, UPDATE, DELETE)

Status - Whether the trigger is enabled (ENABLED or DISABLED)

select  TABLE_OWNER,
 TABLE_NAME,
 TRIGGER_NAME,
 TRIGGER_TYPE,
 TRIGGERING_EVENT,
 STATUS
from  dba_triggers
order  by TABLE_NAME, TRIGGER_NAME

DATABASE SYNONYM NOTES

 

Synonyms

DATABASE SYNONYM NOTES:

Owner - Owner of the synonym

Name - Name of the synonym

Table Owner - Owner of the table

Table Name - Name of the table

DB Link - Name of the database link

select OWNER,
 SYNONYM_NAME,
 TABLE_OWNER,
 TABLE_NAME,
 DB_LINK
from   dba_synonyms
where owner not in ('SYS','SYSTEM','PUBLIC','DBSNMP')
order  by OWNER,SYNONYM_NAME

DATABASE SNAPSHOT NOTES

 

Snapshots

DATABASE SNAPSHOT NOTES:

Owner - Owner of the snapshot

Name - The view used by users and applications for viewing the snapshot

Table Name - Table the snapshot is stored in, has an extra column for the master rowid

Master View - View of the master table, owned by the snapshot owner, used for refreshes

Master Owner - Owner of the master table

Master - Name of the master table of which this snapshot is a copy

Master Link - Database link name to the master site

Can Use Log - If NO, this snapshot is complex and will never use a log

Updatable - If NO, the snapshot is read only

Last Refresh - SYSDATE from the master site at the time of the last refresh

Error - The number of failed automatic refreshes since last successful refresh

Type - The type of refresh (complete, fast, force) for all automatic refreshes

Next Refresh - The date function used to compute next refresh dates

Refresh Group - GROUP All snapshots in a given refresh group get refreshed in the same transaction

select 
OWNER,
 NAME,
 TABLE_NAME,
 MASTER_VIEW,
 MASTER_OWNER,
 MASTER,
 MASTER_LINK,
 CAN_USE_LOG,
 UPDATABLE,
 LAST_REFRESH,
 ERROR,
 TYPE,
 NEXT,
 REFRESH_GROUP
from   dba_snapshots
order  by OWNER,NAME

DATABASE SEQUENCE NOTES

 

Sequences

DATABASE SEQUENCE NOTES:

Owner - Owner of the sequence

Name - Name of the sequence

MinValue - Minimum value of the sequence

MaxValue - Maximum value of the sequence

Increment - Value by which sequence is incremented

Cycle - Does sequence wrap around on reaching limit?

Order - Are sequence numbers generated in order?

Cache Size - Number of sequence numbers to cache

Last Number - Last sequence number written to disk

select  SEQUENCE_OWNER,
 SEQUENCE_NAME,
 MIN_VALUE,
 MAX_VALUE,
 INCREMENT_BY,
 CYCLE_FLAG,
 ORDER_FLAG,
 CACHE_SIZE,
 LAST_NUMBER
from   dba_sequences
where SEQUENCE_OWNER not in ('SYS','SYSTEM')
order  by SEQUENCE_OWNER,SEQUENCE_NAME

DATABASE PROCEDURE/PACKAGE NOTES

 

Procedures/Packages

DATABASE PROCEDURE/PACKAGE NOTES:

Owner - Owner of the object

Name - Name of the object

Type - Type of object

select OWNER,
 NAME,
 TYPE
from   dba_source
group by OWNER,NAME,TYPE
order  by OWNER,NAME,TYPE

DATABASE LINK NOTES

 

Links

DATABASE LINK NOTES:

Owner - Owner of the database link

DBLink - Name of the database link

Username - Name of user to log in as

Host - SQL*Net string for connect

Created - Creation time of the database link

select  OWNER,
 DB_LINK,
 USERNAME,
 HOST,
 to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created
from   dba_db_links
order by OWNER,DB_LINK

DATABASE JOB NOTES

 

Jobs

DATABASE JOB NOTES:

Log User - USER who was logged in when the job was submitted.

Schema - Default schema used to parse the job. For example, if the SCHEMA_USER is SCOTT and you submit the procedure HIRE_EMP as a job, Oracle looks for SCOTT.HIRE_EMP.

Job# - Identifier of job. Neither import/export nor repeated executions change it.

Interval - A date function, evaluated at the start of execution, becomes next NEXT_DATE.

Next Execution - Date/time that this job will next be executed.

Broken - If Y, no attempt is made to run this job. See DBMS_JOBQ.BROKEN (JOB).

What - Body of the anonymous PL/SQL block that this job executes.

select  LOG_USER,
 SCHEMA_USER schema,
 JOB job#,
 INTERVAL,
 to_char(NEXT_DATE,'MM/DD/YYYY HH24:MI:SS') next_execution,
 BROKEN,
 substr(WHAT,1,100) what
from  dba_jobs
order  by LOG_USER

DATABASE CLUSTER NOTES

 

Clusters

 DATABASE CLUSTER NOTES:

Owner - Owner of the table/cluster

Tablespace - Name of the tablespace containing the cluster

Cluster Name - Name of the cluster

Table Name - Clustered table name

Table Column - Key column in the table

Cluster Column - Key column in the cluster

select  a.OWNER,
 TABLESPACE_NAME,
 a.CLUSTER_NAME,
 TABLE_NAME,
 TAB_COLUMN_NAME,
 CLU_COLUMN_NAME
from  dba_clusters a, dba_clu_columns b
where a.CLUSTER_NAME = b.CLUSTER_NAME
order  by a.OWNER,TABLESPACE_NAME,a.CLUSTER_NAME,TABLE_NAME

FREE, LARGEST, & INITIAL NOTES

Freespace/Largest Ext

FREE, LARGEST, & INITIAL NOTES:

Tablespace - Name of the tablespace

Total Free Space - Total amount (bytes) of freespace in the tablespace

Largest Free Extent - Largest free extent (bytes) in the tablespace

select  TABLESPACE_NAME,
 sum(BYTES) Total_free_space,
    max(BYTES) largest_free_extent
from  dba_free_space
group  by TABLESPACE_NAME

OBJECTS IN SYSTEM TABLESPACE NOTES

Objects in SYSTEM TS

OBJECTS IN SYSTEM TABLESPACE NOTES:

Owner - Owner of the object

Object Name - Name of object

Object Type - Type of object

Tablespace - Tablespace name

Size - Size (bytes) of object

Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

select OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 BYTES
from  dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order  by OWNER, SEGMENT_NAME

SYSTEM TABLESPACE USAGE NOTES

 

Users Default (SYSTEM)

SYSTEM TABLESPACE USAGE NOTES:

Username - Name of the user

Created - User creation date

Profile - Name of resource profile assigned to the user

Default Tablespace - Default tablespace for data objects

Temporary Tablespace - Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select  USERNAME,
 CREATED,
 PROFILE,
 DEFAULT_TABLESPACE,
 TEMPORARY_TABLESPACE
from  dba_users
order  by USERNAME

TABLESPACE USAGE NOTES

 

Usage

TABLESPACE USAGE NOTES:

1. Tablespace Name - Name of the tablespace
2. Bytes Used - Size of the file in bytes
3. Bytes Free - Size of free space in bytes
4. Largest - Largest free space in bytes
5. Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

select a.TABLESPACE_NAME,
 a.BYTES bytes_used,
 b.BYTES bytes_free,
 b.largest,
 round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from  
 (
  select  TABLESPACE_NAME,
   sum(BYTES) BYTES 
  from  dba_data_files 
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   sum(BYTES) BYTES ,
   max(BYTES) largest 
  from  dba_free_space 
  group  by TABLESPACE_NAME
 )
 b
where  a.TABLESPACE_NAME=b.TABLESPACE_NAME
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc

WAIT STATISTIC NOTES

 

Coalesced Exts

WAIT STATISTIC NOTES:

Tablespace Name - Name of tablespace

Total Extents - Total number of free extents in tablespace

Extents Coalesced - Total number of coalesced free extents in tablespace

% Extents Coalesced - Percentage of coalesced free extents in tablespace

Total Bytes - Total number of free bytes in tablespace

Bytes Coalesced - Total number of coalesced free bytes in tablespace

Total Blocks - Total number of free oracle blocks in tablespace

Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace

% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace

select TABLESPACE_NAME,
 TOTAL_EXTENTS,
 EXTENTS_COALESCED,
 PERCENT_EXTENTS_COALESCED,
 TOTAL_BYTES,
 BYTES_COALESCED,
 TOTAL_BLOCKS,
 BLOCKS_COALESCED,
 PERCENT_BLOCKS_COALESCED
from  dba_free_space_coalesced
order  by TABLESPACE_NAME

TABLESPACE INFORMATION NOTES

 

Information

TABLESPACE INFORMATION NOTES:

Tablespace Name - Name of the tablespace

Initial Extent - Default initial extent size

Next Extent - Default incremental extent size

Min Extents - Default minimum number of extents

Max Extents - Default maximum number of extents

PCT Increase - Default percent increase for extent size

Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.

select TABLESPACE_NAME,
 INITIAL_EXTENT,
 NEXT_EXTENT,
 MIN_EXTENTS,
 MAX_EXTENTS,
 PCT_INCREASE,
 STATUS,
 CONTENTS
from  dba_tablespaces
order  by TABLESPACE_NAME 

DATABASE PRODUCT AND VERSION NOTES

 

 Versions

DATABASE PRODUCT AND VERSION NOTES:

Version number of core library components in the Oracle Server

select BANNER product_versions
from v$version

LICENSE INFORMATION NOTES

 

License

LICENSE INFORMATION NOTES:

Sessions Max - Maximum number of concurrent user sessions allowed for the instance.

Sessions Warn - Warning limit for concurrent user sessions for the instance.

Sessions Current - Current number of concurrent user sessions.

Sessions Highwater - Highest number of concurrent user sessions sine the instance started.

Users Max - Maximum number of named users allowed for the database.

select SESSIONS_MAX,
 SESSIONS_WARNING,
 SESSIONS_CURRENT,
 SESSIONS_HIGHWATER,
 USERS_MAX
from v$license

INIT.ORA PARAMETER NOTES

init.ora

INIT.ORA PARAMETER NOTES:

Parameter - Init.ora parameter name

Value - Current value

Is Default - Whether the parameter value is the default

Session Modifiable - TRUE=the parameter can be changed with ALTER SESSION / FALSE=the parameter cannot be changed with ALTER SESSION

System Modifiable - IMMEDIATE=the parameter can be changed with ALTER SYSTEM / DEFERRED=the parameter cannot be changed until the next session / FALSE=the parameter cannot be changed with ALTER SYSTEM

Is Modified - Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.

ALWAYS_ANTI_JOIN - Set the parameter to HASH to allow parallel processing of the NOT IN clause (a real resource hog). If you set the parameter to NESTED_LOOPS, the NOT IN is evaluated the same way it was as Oracle7.2 and earlier (which will not please you). MERGE uses the SORT_MERGE algorithm to process the NOT IN, which is faster than NESTED_LOOPS but not as fast as HASH.
DEFAULT: NULL
VALUES: NESTED_LOOPS, MERGE, HASH

COMPATIBLE - This parameter specifies the release with which the Oracle Server must maintain compatibility. Set to the release of Oracle RDBMS that you are currently running to take full advantage of any new features. Allows you to use the current version while at the same time guaranteeing backward compatibility with an earlier release. This is in case it becomes necessary to revert to the earlier release. Some features of the current release may be restricted. Oracle 7.1.5 introduced the ability to bypass the buffer cache (direct reads) for table scans and sorts (COMPATIBLE=7.1.5). Oracle 7.3 introduced temporary tablespaces that improve the performance of sorts and hash joins (COMPATIBLE=7.3.1)
DEFAULT: NULL
VALUES: Any Oracle RDBMS (e.g., Oracle7.2.3)

CONTROL_FILES - Specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the OS level.
DEFAULT: OS Dependent
VALUES: 1 - 8 filenames

CURSOR_SPACE_FOR_TIME - Turns waiting for application cursors on (TRUE) or off (FALSE). If it is set to TRUE, the database uses more space for cursors to save time. It affects both the shared SQL area and the client's private SQL area. Shared SQL areas are kept pinned in the shared pool when this parameter's value is TRUE. As a result, shared SQL areas are not aged out of the pool as long as there is an open cursor that references them. Because each active cursor's SQL area is present in memory, execution is faster. Because the shared SQL areas never leave memory while they are in use, however, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously. Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. This saves cursor allocation and initialization time.
DEFAULT: FALSE
VALUES: TRUE/FALSE

DB_BLOCK_BUFFERS - Sets the size, in database blocks, of the data block buffer cache in the SGA. The larger the data block buffer cache is, the more memory will be available for sharing data already in memory amoung users - reducing the need for physical reads. You can determine the effectiveness of the data block buffer cache by measuring the hit ratio of the database.
DEFAULT: 32 Buffers
VALUES: 4 to unlimited

DB_BLOCK_SIZE - Defined when the database is created; it determines the size of each block within the database. You CANNOT change the block size of an existing database; the only method available for increasing the block size is to perform a full database Export, recreate the database with a different DB_BLOCK_SIZE value, and Import the database. In most environments, the default value for DB_BLOCK_SIZE is 2048 bytes (2KB). If you operating environment permits, you should increase the value to 4KB, 8KB, or higher. The performance gains obtained by using a larger block size are significant for both OLTP and batch applications. In general, each doubling of the database block size will reduce the time required for I/O-intensive batch operations be around 40 percent. As the database block size increases, your overall memory requirements may increase.
DEFAULT: OS Dependent
VALUES: 1024-32768

DB_FILE_MULTIBLOCK_READ_COUNT parameter helps determine how many blocks are read at a time by the database during full table scans. You should set the DB_BLOCK_MULTIBLOCK_READ_COUNT parameter to a value that takes the greatest advantage of the operating system's buffer during reads. For example, suppose the OS buffer available for reads is 64KB in size. If your database block size is 2KB, you should set DB_FILE_MULTIBLOCK_READ_COUNT to 32; if the block size is 4KB, set DB_FILE_MULTIBLOCK_READ_COUNT to 16. In some operating systems, the available buffer size is configurable.
DEFAULT: OS Dependent but usually a function of DB_BLOCK_BUFFERS and PROCESSES
VALUES: OS Dependent (normally 1 to either the DB_BLOCK_BUFFERS / 4 or the OS maximum I/O size / DB_BLOCK_SIZE)

DB_FILE_SIMULTANEOUS_WRITES - Number of write batches written by the database writers. If you are experiencing problems getting buffers written to your disks during checkpoints, try increasing the value. It is applicable only to the operation systems that support more than one write to your disk devices.
DEFAULT: 4
VALUES: 1 to 24

DB_WRITERS - Once the database block size and memory areas are properly established, tune the way in which Oracle writes data from memory. If your OS permits using multiple DBWR processes, set a value greater than '1' for the DB_WRITERS parameter. If you start more than on DBWR process, you may be able to reduce contention for blocks within the database block buffer cache. If there is only one DBWR process available, it becomes a possible bottleneck during I/O operations, even if the data is distributed among multiple devices.

ENQUEUE_RESOURCES - An enqueue is a sophisticated locking mechanism which permits several concurrent processes to share known resources to varying degrees. Any object which can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.
DEFAULT: Derived
VALUES: 10 - 65535

HASH_AREA_SIZE - This parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: 2 times the value of SORT_AREA_SIZE
VALUES: Any integer

HASH_JOIN_ENABLED - This parameter enables or disables the hash join feature. Set this parameter to TRUE to use hash joins. Set this parameter to FALSE to disable hash joins. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: TRUE
VALUES: TRUE/FALSE

HASH_MULTIBLOCK_IO_COUNT - This parameter specifies how many blocks a hash join reads and writes at once. When operating in multi-threaded server mode, however, this parameter is ignored (that is, the default value of 1 is used even if you set the parameter to another value). The value of DB_BLOCK_SIZE multiplied by the value of HASH_MULTIBLOCK_IO_COUNT should be less than 64 KB. This parameter strongly affects performance because it controls the number of partitions into which the input is divided.
DEFAULT: 1
VALUES: 1 - (65,536/DB_BLOCK_SIZE)

LOG_ARCHIVE_BUFFER_SIZE - The size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications. This parameter, with LOG_ARCHIVE_BUFFERS, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
DEFAULT: OS Dependent
VALUES: 1 - OS Dependent (in O/S blocks)

LOG_ARCHIVE_DEST - Applicable only if using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition. To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.
DEFAULT: OS Dependent
VALUES: Any valid path or device name, except raw partitions

LOG_ARCHIVE_START - Applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.) In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
DEFAULT: FALSE
VALUES: TRUE/FALSE

LOG_BUFFER Sets the size, in bytes, of the redo log buffer area in the SGA. The default is set to four times the maximum database block size for the OS. For an OLTP application in which many users perform transactions, the LOG_BUFFER parameter needs to be increased beyond its default value. If the 'redo log space requests' statistic in V$SYSSTAT is non-zero, you should increase LOG BUFFER to support the transaction load without forcing transactions to wait for accesses to the redo log buffer.
DEFAULT: OS Dependent
VALUES: OS Dependent

LOG_CHECKPOINT_INTERVAL - The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs. The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic dbwr checkpoints, which is displayed in the System Statistics Monitor of the Server Manager. Additional cache statistics include background checkpoints started and background checkpoints completed.
DEFAULT: OS Dependent
VALUES: Unlimited (OS blocks, not database blocks)

LOG_SIMULTANEOUS_COPIES - The maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch. If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: CPU_COUNT
VALUES: 0 - unlimited

OPTIMIZER_MODE - When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.) You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time. The goal of cost-based optimization can also be set within a session by using ALTER SESSION SET OPTIMIZER_MODE.
DEFAULT: CHOOSE
VALUES: RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

PARALLEL_MAX_SERVERS - Each instance must either have a value of zero or the same value as the other instances. Maximum number of query servers or parallel recovery processes for an instance.
DEFAULT: OS Dependent
VALUES: 0 - 256

PARALLEL_MIN_SERVERS - Minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.
DEFAULT: 0
VALUES: 0 - PARALLEL_MAX_SERVERS

PROCESSES - For a multiple-process operation, this parameter specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle7 Server. Should include up to 6 for the background processes (or more if GC_LCK_PROCS is non-zero or if you use the dispatcher configuration) plus one for login; so a value of 20 would permit 13 or 14 concurrent users. The default values of DB_FILE_MULTIBLOCK_READ_COUNT and SESSIONS are derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of these derived parameters.
DEFAULT: 25
VALUES: 6 - OS Dependent

ROLLBACK_SEGMENTS - One or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is not overridden, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
DEFAULT: NULL (the instance uses public rollback segments by default if you do not specify this parameter
VALUES: Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM

ROW_CACHE_CURSORS - The number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
DEFAULT: 10
VALUES: 10 - 3300

SHARED_POOL_RESERVED_MIN_ALLOC - This parameter controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
DEFAULT: 5000
VALUES: 5000 - SHARED_POOL_RESERVED_SIZE (in bytes)

SHARED_POOL_RESERVED_SIZE - This parameter controls the amount of SHARED_POOL_SIZE reserved for large allocations. SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC to create a reserved list. The default value of 0 represents no reserved shared pool area. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value will be sufficient if you have already tuned the shared pool.
DEFAULT: 0
VALUES: From SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)

SHARED_POOL_SIZE - Sets the size, in bytes, of the shared pool in the SGA. If your application is OLTP-oriented, and you use packages and other procedural objects, you'll need a large shared SQL area. In environments using a large number of procedural objects, the size of your shared pool may exceed your data block buffer cache. If you have many users, you should increase the SHARED_POOL_SIZE parameter everytime you increase the DB_BLOCK_BUFFERS parameter.
DEFAULT: 3,500,000
VALUES: 300 Kbytes - OS Dependent

SORT_AREA_RETAINED_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory retained after a sort. This memory is released back to the PGA, not to the operating system, after the last row is fetched from the sort space. If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter. Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE.
DEFAULT: The value of SORT_AREA_SIZE
VALUES: From the value equivalent to one database block to the value of SORT_AREA_SIZE

SORT_AREA_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. After the sort is complete and all that remains to do is to fetch the rows out, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched out, all memory is freed. The memory is released back to the PGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. The default is usually adequate for most database operations. Only if very large indexes are created might you want to adjust this parameter. For example, if one process is doing all database access, as in a full database import, then an increased value for this parameter may speed the import, particularly the CREATE INDEX statements.
DEFAULT: OS Dependent
VALUES: The value equivalent to two database blocks (minimum)

SORT_DIRECT_WRITES - SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. When set to the default value of AUTO, and if the value of SORT_AREA_SIZE is greater than ten times the buffer size, SORT_DIRECT_WRITES automatically configures the SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS parameters. When SORT_DIRECT_WRITES is in AUTO mode, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE have no effect. When SORT_DIRECT_WRITES is set to TRUE, each sort allocates additional buffers in memory to write directly to disk. When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
DEFAULT: AUTO
VALUES: AUTO/TRUE/FALSE

SORT_READ_FAC - SORT_READ_FAC is a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate.
DEFAULT: OS Dependent

SORT_WRITE_BUFFER_SIZE - This parameter sets the size of the sort buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 32768
VALUES: Any integer

SORT_WRITE_BUFFERS - This parameter sets the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 2
VALUES: Any integer

SQL_TRACE - Disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: FALSE
VALUES: TRUE/FALSE

TIMED_STATISTICS - By default (when set to FALSE), the Server Manager statistics related to time (from the buffer manager) always are zero and the Server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Should normally be set to FALSE.
DEFAULT: FALSE
VALUES: TRUE/FALSE

USER_DUMP_DEST - The pathname for a directory where the server will write debugging trace files on behalf of a user process.
DEFAULT: OS Dependent
VALUES: Valid local pathname, directory, or disk

select NAME,
 VALUE,
 ISDEFAULT,
 ISSES_MODIFIABLE,
 ISMODIFIED
from  v$parameter
order   by NAME

DATABASE FILE NOTES

 

Files

DATABASE FILE NOTES:

Filename - Name of the file(s)

Location - Location of the file(s)

select  'Archived Log Directory' "Filename",
 value "Location"
from  v$parameter
where  name = 'log_archive_dest'
UNION
select  'Control Files' "Filename",
 value "Location"
from  v$parameter
where   name = 'control_files'
UNION
select  'Datafile' "Filename",
 name "Location"
from    v$datafile
UNION
select  'LogFile Member' "Filename",
 member "Location"
from   v$logfile

Size DATAFILE NOTES

 

Size

DATAFILE NOTES:

File Name - Name of the datafile

Tablespace - Name of the tablespace

Datafile Size - Size of datafile (bytes)

Bytes Used - Amount of datafile used

Percent Used - Percent of datafile used

Bytes Free - Amount of datafile free

select  FILE_NAME,
 d.TABLESPACE_NAME,
 d.BYTES datafile_size,
 nvl(sum(e.BYTES),0) bytes_used,
 round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
 d.BYTES - nvl(sum(e.BYTES),0) bytes_free
from  DBA_EXTENTS e,
 DBA_DATA_FILES d
where   d.FILE_ID = e.FILE_ID (+)
group   by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
order   by d.TABLESPACE_NAME,d.FILE_ID

DATABASE INFORMATION NOTES

 

Database Information

DATABASE INFORMATION NOTES:

Database Name - Name of the database

Created - Date/time the database was created

Log Mode - Archive log mode: NOARCHIVELOG or ARCHIVELOG

Checkpoint Change# - Last SCN checkpointed

Archive Change# - Last SCN archived

select  NAME,
 CREATED,
 LOG_MODE,
 CHECKPOINT_CHANGE#,
 ARCHIVE_CHANGE#
from  v$database

Cached Tables

 

Cached Tables

CACHED TABLE NOTES:

Owner - Owner of the table

Table Name - Name of the table

Cache - Cached?

Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads.

select  OWNER,
 TABLE_NAME,
 CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME

Recently Analyzed Tables

 

Recently Analyzed Tables

LAST ANALYZED TABLE NOTES:

Owner - Owner of the table

Table Name - Name of the table

Last Analyzed - Last analyzed date/time

select  OWNER,
 TABLE_NAME,
 to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from  dba_tab_columns
where  OWNER not in ('SYS','SYSTEM')
and  LAST_ANALYZED is not null
and COLUMN_ID=1
and  (SYSDATE-LAST_ANALYZED) < 30
order by (SYSDATE-LAST_ANALYZED)

Analyzed Tables

 

Analyzed Tables

ANALYZED TABLE NOTES:

Owner - Owner of the table

Analyzed - Number of analyzed tables

Not Analyzed - Number of tables that have not be analyzed

Total - Total number of tables owned by user

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.

A COMPUTE will cause a table-level lock to be placed on the table during the operation.

select OWNER,
 sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
 sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
 count(TABLE_NAME) total
from  dba_tables
where  OWNER not in ('SYS', 'SYSTEM')
group  by OWNER

TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM

 

Extents reaching maximum

TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :

Owner - Owner of the segment

Segment Name - Name of the segment

select owner "Owner",
       segment_name "Segment Name",
       segment_type "Type",
       tablespace_name "Tablespace",
       extents "Ext",
       max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3) 
and owner not in ('SYS','SYSTEM')
order by owner, segment_name

OBJECTS WITH MORE THAN 50% OF MAXEXTENTS

 

Segment Fragmentation

OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:

Owner - Owner of the object

Tablespace Name - Name of the tablespace

Segment Name - Name of the segment

Segment Type - Type of segment

Size - Size of the object (bytes)

Extents - Current number of extents

Max Extents - Maximum extents for the segment

Percentage - Percentage of extents in use

As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.

To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7

Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017

Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.

select  OWNER,
 TABLESPACE_NAME,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 BYTES,
 EXTENTS,
 MAX_EXTENTS,
 (EXTENTS/MAX_EXTENTS)*100 percentage
from  dba_segments
where  SEGMENT_TYPE in ('TABLE','INDEX')
and  EXTENTS > MAX_EXTENTS/2
order  by (EXTENTS/MAX_EXTENTS) desc

TABLES THAT CANNOT EXTEND

 

Object Extent Warning

TABLES THAT CANNOT EXTEND NOTES:

Owner - Owner of the object

Object Name - Name of the object

Object Type - Type of object

Tablespace - Name of the tablespace

Next Extent - Size of next extent (bytes)

select  OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 NEXT_EXTENT
from (
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   t.NEXT_EXTENT
 from  dba_segments seg,
   dba_tables t
 where  (seg.SEGMENT_TYPE = 'TABLE'
 and    seg.SEGMENT_NAME = t.TABLE_NAME
 and    seg.owner = t.OWNER
 and    NOT EXISTS (
   select  TABLESPACE_NAME
    from  dba_free_space free
    where  free.TABLESPACE_NAME = t.TABLESPACE_NAME
    and  BYTES >= t.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   c.NEXT_EXTENT
 from  dba_segments seg,
   dba_clusters c 
 where   (seg.SEGMENT_TYPE = 'CLUSTER'
 and      seg.SEGMENT_NAME = c.CLUSTER_NAME
 and      seg.OWNER = c.OWNER
 and     NOT EXISTS (
   select  TABLESPACE_NAME
   from  dba_free_space free
   where  free.TABLESPACE_NAME = c.TABLESPACE_NAME
   and  BYTES >= c.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   i.NEXT_EXTENT
 from  dba_segments seg,
   dba_indexes  i
 where   (seg.SEGMENT_TYPE = 'INDEX'
 and      seg.SEGMENT_NAME = i.INDEX_NAME
 and      seg.OWNER        = i.OWNER
 and      NOT EXISTS (
   select  TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = i.TABLESPACE_NAME
   and  BYTES >= i.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   r.NEXT_EXTENT
 from  dba_segments seg,
   dba_rollback_segs r
 where   (seg.SEGMENT_TYPE = 'ROLLBACK'
 and      seg.SEGMENT_NAME = r.SEGMENT_NAME
 and      seg.OWNER        = r.OWNER
 and      NOT EXISTS (
   select TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and  BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order  by OWNER,SEGMENT_NAME

INCONSISTENT COLUMN DATATYPE

 

Inconsistent Column Names

INCONSISTENT COLUMN DATATYPE NOTES:

Owner - Owner of the table

Column - Name of the column

Table Name - Name of the table

Datatype - Datatype of the column

select  OWNER,
 COLUMN_NAME,
 TABLE_NAME,
 decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from  dba_tab_columns 
where   (COLUMN_NAME, OWNER) in
  (select COLUMN_NAME, 
   OWNER
    from  dba_tab_columns
    group by COLUMN_NAME, OWNER
     having min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
    max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and  OWNER not in ('SYS', 'SYSTEM')
order by COLUMN_NAME,DATA_TYPE 

FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE

 

FK Index Problems

FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:

Owner - Owner of the table

Constraint Name - Name of the constraint

Column Name - Name of the column

Position - Position of the index

Problem - Nature of the problem

It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

select  acc.OWNER,
 acc.CONSTRAINT_NAME,
 acc.COLUMN_NAME,
 acc.POSITION,
 'No Index' Problem
from    dba_cons_columns acc, 
 dba_constraints ac
where   ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and    ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE' 
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

FOREIGN KEY CONSTRAINTS NOTES

 

FK Constraints

FOREIGN KEY CONSTRAINTS NOTES:

Table Owner - Owner of the table

Table Name - Name of the table

Constraint Name - Name of the constraint

Column Name - Name of the column

Referenced Table - Name of the referenced table

Reference Column - Name of the referenced column

Position - Position of the column

select  c.OWNER,
 c.TABLE_NAME,
 c.CONSTRAINT_NAME,
 cc.COLUMN_NAME,
 r.TABLE_NAME,
 rc.COLUMN_NAME,
 cc.POSITION
from  dba_constraints c, 
 dba_constraints r, 
 dba_cons_columns cc, 
 dba_cons_columns rc
where  c.CONSTRAINT_TYPE = 'R'
and  c.OWNER not in ('SYS','SYSTEM')
and  c.R_OWNER = r.OWNER
and  c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and  c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and  c.OWNER = cc.OWNER
and  r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and  r.OWNER = rc.OWNER
and  cc.POSITION = rc.POSITION
order  by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION