ALTER TABLE SPACE DOTSOFT
RENAME DATAFILE ‘filename’ to ‘filename’;
NOTE : The source file name should match the names stored in the control file.
All types of datafiles ALTER DATABASE RENAME FILE ‘filename’ to ‘filename’;
DROPPING TABLE SPACE : DROP ABLE SPACE DOTSOFT INCLUDING CONTENTS VIEWS : DBA_TABLE SPACES
DBA_DATA_FILES
Information from control files V$DATAFILE V$TABLE SPACES STORAGE STRUCTURE AND RELATIONSHIPS
Storage clause precedence :
Any storage parameter specified at the segment level overrides the corresponding option set at the TS levels except for the MINIMUM EXTENT TS Parameter
Coalescing Free Space : This happens
• When SMON initiates a space transaction to merge adjacent free extents (it happens when PCTINCREASE > 0)
• When oracle server needs to allocate an extent that needs space from more than are adjacent free extent
• On demand when requested by SBA.
ALTER TABLE SPACE DOTSOFT COALESCE : VIEW DBA_FREE_SPACE_COALESCED
INITRANS : default value = 1 for data segment
= 2 for Index segment
If transactions are more slot can be allocated from the free space in the block. MAXTRANS default value 255.
Data Dictionary Views :
DBA_TABLESPACES
DBA_SEGMENTS DBA_DATA FILES
Managing Rollback Segments :
RBS stores block information such as file and block ID and aho date (before modified image)
System
Types of RBS Private (Default)
Non-System
Public
Shrinkage of RBS : Deallocation of extents is not done as soon as transactions are completed.
The process of deallocating extents is performed only when head moves from one extent to the other. Extents are deallocated if
• The current size of RBS exceeds OPTIMAL
• There are contiguous inactive extents
Read Consistency : SNAPSHOT TOO OLD ERROR ORA_1555
Creating RBS with MINEXTENTS=20 is recommended to reduce the possibility of extension.
OPTIMAL > to size of initial size of RBS=Size of MINEXTENTS AVOID Setting MAXEXTENTS = UNLIMITED
ALTER ROLLBACK SEGMENT RBS01 ONLINE INIT_ORA parameters TRANSACTIONS
T (transactions) TRANSACTIONS_PER_ROLLBACK_SEGMENT N =
TPR N= No.of RBS
DBA_EXTENTS DBA_FREE_SPACE
Storage Setting :
ALTER ROLLBACK SEGMENT RBS01 STORAGE NEXT k/m MINEXTENT integer
MAXEXTENT integer / unlimited
OPTIMAL integer k/m/NULL
ALTER ROLLBACK SEGMENT RBS01 SHRINK TO 4m
SYS -- Private RBS
OWNER OF RBS
PUBLIC -- Public RBS
RBS Statistics DBA_ROLLBABK_SEGMENT
---V$ ROLL NAME V$ROLL STAT
Pending OFF LINE Info RBS – Current activity
V$SESSION V$TRANSACTION
Read Consistency errors can be minimized with
• Higher MINEXTENTS
• Larger EXTENT SIZES
• Higher OPTIMAL VALUE Blocking Sessions :
V$ROLLSTAT, V$SESSION, V$TRANSACTION Get SID, SERIA# and kill the session
username V$SESSION
Session information
V$TRANSACTION – Transactions currently using RBS V$ROLL STAT – statistics of RBS
Managing Tables Structure of a row :
ROWHEADER -- no.of columns in row chaining the info, row lock status
ROW DATA -- column length (1byte, 3 bytes), column value (250 bytes, more than 250 bytes)
ROWID Data type : Pseudo column
Data object no Relative File no Block No Row No
Size in bytes 32 10 22 16
ROWID needs 10 bytes of storage – 80 bits Displayed using 64 bit encoding scheme Creating a table :
• If MINEXTENT has been defined for the TS, the extent sizes for the table will get rounded up to next higher multiple of MINIMUM EXTENT value
• If MINEXTENT > 1 and TS contains more than one data file, the extent will be spread across the diff. Files in the TS.
- TO improve full table scans – Init.ora DB_FILE_MULTIBLOCK_READ_COUNT Setting PCTFREE, PCT USED :
(average row size – initial row size)
PCTFREE = --- x 100 Average Row size
Average row size x 100 PCT USED = 100 – PCTFREE
---Available Data space
000000 FFF BBBBBB RRR
Changing Storage and block utilization parameters :
Any of the block utilization parameters and some of storage parameters can be modified.
Storage
INITIAL - Cannot be modified
NEXT - will be rounded to a value that is a multiple of the block size greater than or equal to the value specified
PCTINCREASE - Subsequent allocated extents
MINEXTENTS - can be changed to < present used extents in the table MAXEXTENTS - can be changed to > present used extents in the table Block utilization :
• PCTFREE - affects future inserts
• PCTUSED - affects all the blocks in the table
• INITRANS - affects only new blocks
• MAXTRANS - affects all blocks in the table Manual allocation of extents :
ALTER TABLE Scott.employees ALLOCATE EXTENT (SIZE 500 k
DATAFILE ‘ /DISK3/DOTSOFT3.DBF’);
If size is omitted, the default value NEXT EXTENT table from DBA_TABLES to allocate new extent
High Water Mark : HWM is stored in the segment header of table Finding the HWM – DBMS_SPACE_UNUSED_SPACE
Created when scripts dbmstuil_sql, prvutil.plb are invoked by catproc.sql Deallocation of unused space
ALTER TABLE EMP DEALLOCATE UNUSED (KEEP int {k/M}) Above HWM
To release all the space below the HWM even HWM is below MINEXTENTS use KEEP = 0 Truncating a table : the DELETE will not fire while truncating
• Specify REUSE clause to retain all the space used by the table
• The effect of REUSE or DROP cascades to indexes.
Validating Table Structure : ANALYSE command uses
• Deleting statistics from DD
• Validating structure of the table
• Identifying migrated and chained rows of the table
Init.ora : DB_BLOCK_CHECKSUM = TRUE to calculate the checksum and store in the header of every data block writing it to disk
ANALYSE TABLE EMP
VALIDATE STRUCTURE CASCADE NOTE : Use following PL/SQL procedures to analyse objects :
• DBMS_DDL.ANALYSE_OBJECT – for analyzing OBJECTS
• DBMS_UTILITY.ANALYSE_SCHEME -- for analyzing OBJECTS OWNED by user
• DBMS_UTILITY.ANALYSE_DATABASE -- for analyzing ALL OBJECTS IN DB Detecting Row Migration : Use analyse command to find it
ANALYSE TABLE EMP
[COMPUTE STATISTICS / ESTIMATE STATISTICS [SAMPLE integer [ROWS / PERCENT 3]
Retrieving Table Information :
DBA_OBJECTS DBA_TABLES DBA_SEGMENTS
Info about EXTENTS DBA_EXTENTS
DBMS_ROWID Package : available when run dbmsutil.sql Temporary Table Space
Init.ora SORTAREA_SIZE
CREATE TABLESPACE DOTSOFT TEMPORARY DATAFILE ‘filespec’
- if a permanent TS is used for sorting an instance may have more than one temporary segment in TS.