ALTER SESSION SET SQL_TRACE = TRUE
ALTER SYSTEM SET TIMED_STATISTICS = TRUE
ALTER SYSTEM SET SORT_AREA_SIZE = 131072 DEFERRED
- The MAX_DUMP_FILE_SIZE and
USER_DUMP_DEST Parameters are dynamic initialization parameters - Check the ALERT file periodically to
Detect internal errors (ORA_600) and block _corruption error Monitor db operations
View the non-default initialization parameter DD Views :
- Created during creation db – by the script sql.bsq - The DD is located in SYSTEM TS and owned by SYS
Base tables
Data Dictionary views
After the db creation _ catlog.sql & catproc_sql must be run as the user SYS dbmsspool.sql – enables to display the sizes of the objects in the shared pool and mark then for keeping or unkeeping in order to reduce shared pool fragmentation.
Redo log files :
Used to minimize the loss of data.
Oracle server needs minimum of 2 online redo log file groups
Each member in a group has identical log sequence numbers which are incremented at log switch.
The log.seg no is stored in control file and header of all data files.
Create db command :
MAXLOGFILES -- maximum limit is 255
MAXLOGMEMBERS determines max of members per group.
Init.ora : LOG_FILES should not exceed MAXLOGFILES*MAXLOGMEMBERS
Log switch occurs :
• When a commit occurs
• When a redo log buffer pool becomes 1/3 full
• When a LGWR time out occurs
• Before DBWR writes dirty buffers in the buffer cache to datafiles.
When a log switch occurs checkpoint is initiated.
A checkpoint occurs
• At every log switch
• When an instance has been shutdown with Normal, transactional or immediate option.
when forced by setting of init.ora parameters LOG_CHECKPOINT_INTERNAL interms OS blocks
LOG_CHECKPOINT_TIMEOUT
• When manually requested by DBA.
Information about each check point goes to ALERT file if LOG_CHECKPOINTS_TO_ALERT
= TRUE in Init.ora
There are two ways in which online redo log file can be archived - manually
- automatically
Init.ora parameter LOG_ARCHIVE_START=TRUE automatic
=FALSE Manual Obtaining information about archiving:
SVRMGR>ARCHIEVE LOG LIST
Or query the V$DATABASE
- NAME
- LOGMODE
or V$INSTANCE
- ARCHIEVER
Information about log groups V$THREAD
GROUPS
CURRENT GROUP#
SEQUENCE # Info about Groups and members V$LOG
GROUP #
MEMBERS
SEQUENCE #
STATUS
BYTES
V$LOGFILE
GROUP
STATUS
MEMBER
STATUS - UNUSED
- CURRENT
- ACTIVE
- INACTIVE
LOG SWITCHES AND CHECKPOINTS
SVRMGR>ALTER SYSTEM SWITCH LOGFILE;
- The init_ora LOG_CHECKPOINT_INTERVAL=0 makes ckpt to initiate frequently since a request will be initiated even single redo log buffer has been written since the last request.
- LOG_CHECKPOINT_TIMEOUT=0 makes disabling timed ckpt of redo buffer.
ADDING GROUPS ALTER DATABASE ADD LOG FILE (‘ /DISK3/Log3a_rdo’
(‘ /DISK4/Log3b_rdo’) SIZE 1m MEMBERS ALTER DATABASE ADD LOGFILE MEMBERS
‘ file spec’, ‘ TO GROUP 1,
‘ file spec’, ‘ TO GROUP 2;
DROPPING ALTER DATABASE DROP LOGFILE GROUP 3;
When an online redo log group is dropped, the OS files are not deleted.
ALTER DATABASE DROP LOGFILE MEMBER ‘ /DISK4/Log2b_dbf’
CLEARING ALTER DATABASE CLEAR LOG FILE ‘/DISK4/Log2a.rdo’;
This command is equal to ADDING AND DROPPING A REDO LOG FILE.
“ “ can be issued even if there are only 2 log groups
SIZE Minimum size of redo log file is 50K and max size is OS specific.
Indexes
• Logical
- Single column
- Concatenated / composite - Unique or non unique
• Physical
- Partitioned - Non-partitioned - B-Tree or Bitmap
- Normal or reverse key (B-Tree only) Type : B-Tree
Reverse key Bitmap
B-Tree Format of leaf: Header Key column-length value pairs Row ID
No.of columns size of column followed by Row ID of the Row & locking info value of column
- Max no.of folumns in a composite key index is 32.
- However, the combined size of all columns cannot exceed 1/3 of block size.
- There will not any index to a row if all key columns are NULL.
- Reverse key indexes – NOSORT is used while creating index.
- Reverse key indexes – are not used for range values.
- PCTFREE - Maximum for index on range values - Low for system generated sequences.
• BITMAP INDEX
Init.ora parameter CREATE_BITMAP_AREA_SIZE gives the area size to store BITMAP INDEX IN MEMORY – Default value is 8 MB.
As a general rule for high cardinality value is more in MB’s for low cardinality value is less in KB.
ALLOCATING AND DEALLOCATING INDEX SPACE ALTER INDEX SCOTT.ord.
ALLOCATE EXTENT (SIZE 200K DATAFILE ‘ /DISK6/indx01 dst’);
ALTER INDEX SCOTT.ORD DEALLOCATE UNUSED;
Views : DBA_INDEXES
DBA_IND_COLUMNS
DBA_OBJECTS
Managing Table spaces and Data Files
- Except for SYSTEMS TS or TS having with active RBS, TSs can be taken off line leaving the db running.
- TSs can be switched between RW_and Readonly status Creating TS :
CREATE TABLESPACE dotsoft
DATAFILE ‘C:\DOTSOFT\dotsoft1.dbf’ SIZE 100m
‘D:\DOTSOFT\dotsoft2.dbf’ SIZE 200m MINIMUM EXTENT 500k
DEFAULT STORAGE (INITIAL 500k NEXT 500k MAXEXTENTS 500 PCTINCREASE 0);
- The max no TSs in database 64k, but no.of TSs cannot exceed the no.of data files.
- The max no.of datafile per TS is 1023
- The MINIMUM EXTENT OPTION can only specified for a TS, not for the storage of individual objects with this option the DBA controls fragmentation.
n-2 PCTINCREASE
SIZE of the extent = NEXT x 1 +
n 100
PCTINCREASE : DEFAULT VALUE 50
Min value = 0
- If MINEXTENTS is specified for a TS, the extents allocated in this TS are multiples of this parameter.
ADDING DATAFILES ALTER TABLE SPACE app_data
ADD DATAFILE ‘ d:\dotsoft.dbf’ SIZE 200 M
TWO Ways of enlarging TS 1 way
2nd way - change the size of the data file which exist, already - for existing file ALTER DATABASE DOTSOFT
DATA FILE ‘D:\DOTSOFT2.DBF’
AUTOEXTEND ON NEXT 10M MAX SIZE 500M
- for new file ALTER TABLE SPACE DOTSOFT
ADD DATA FILE ‘/DOTSOFT2.DBF’ SIZE 200M AUTOEXTEND ON NEXT 10M
MAXSIZE 500M
Manual RESIZING ALTER DATABASE DATA FILE
‘D:\DOTSOFT2.DBF’ RESIZE 200M
OFFLINE - NORMAL
- TEMPORARY
- IMMEDIATE