Oracle Storage Management

Oracle Storage Management

Logically, data is stored in segments (usually tables); physically, data is stored in datafiles. A table space entity is an image of the two. A table space may contain multiple segments and consists of multiple data files. The CIDR block has no direct relationship with the data file. A data file can exist as a file in the file system. It can also be used as a file on an Automatic Storage Management (ASM) device starting from version 10.
Www.2cto.com
I. Oracle Data Storage Model

Separating logical and physical storage is a necessary part of a relational database instance. The relational database example shows that programmers only process the logical structure and allow the database to manage the ing to the physical structure. This means that physical storage can be reorganized, or the entire database can be moved to a completely different hardware and operating system without the application being aware of any changes.
For example, the Oracle storage model is displayed. The logical structure is left and the physical structure is right.
Www.2cto.com


1. Logical Structure of Oracle Database
(1) DATABASE: a DATABASE can be divided into multiple logical storage units called tablespaces.

(2) TABLESPACE: Only one database, including one or more files.

(3) SEGMENT: it exists in a tablespace and contains one or more partitions.
Including: Table segment, table partition segment, index segment, index partition segment, temporary segment, undo segment, BLOB, CLOB
High Water Level
PCTFREE and PCTUSED
Row migration and row Link
INITRANS and MAXTRANS

(4) EXTENT: composed of adjacent data blocks, which means that each partition can only exist in one data file.

(5) BLOCK: The smallest I/O unit in the database, db_block_size
 

2. Physical Structure of Oracle Database
(1) OS files
A. It only belongs to one tablespace.
B. It is the basic file that forms the tablespace.

(2) OS Block
A. tune2fs-l/dev/sda1
B. Sector: The size of one IO of the 512-byte Operating System

3. The relationships between tablespaces, tables, areas, blocks, and file numbers are displayed in the dba_extents view.
Gyj @ OCM> create tablespace tp1 datafile '/u01/app/oracle/oradata/ocm/tp1.dbf' size 100 M, '/u01/app/oracle/oradata/ocm/tp2.dbf' size 100 m extent management local uniformsize 1 M;
Gyj @ OCM> alter user gyj defaulttablespace tp1;
Gyj @ OCM> conn gyj/gyj
Gyj @ OCM> create table t10 (id int, namevarchar2 (100 ));
Gyj @ OCM> begin
2 for I in 1 .. 200000 loop
3 insert into t10values (I, 'gyj' | I );
4 commit;
5 end loop;
6 end;
7/
Gyj @ OCM> col tablespace_name for a10
Gyj @ OCM> col segment_name for a10
Gyj @ OCM> selecttablespace_name, segment_name, extent_id, file_id, block_id, bytes, blocks fromdba_extents where segment_name = 't10 'order by extent_id;

TABLESPACE SEGMENT_NA EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
----------------------------------------------------------------------
TP1 T10 0 0 6 128 1048576 128
TP1 T10 1 7 128 1048576 128
TP1 T10 2 6 256 1048576 128
TP1 T10 3 7 256 1048576 128
TP1 T10 4 4 6 384 1048576 128

Ii. Table space type
1. Common tablespace: System tablespace and non-system tablespace

2. Temporary tablespace

3. roll back the tablespace

3. Create a space
Create tablespace TP25 DATAFILE '/u01/app/oracle/oradata/ocp/tp2501.dbf' SIZE 50 M,
'/U01/app/oracle/oradata/ocp/tp2502.dbf' size50M
Autoextend on next 10 m maxsize 2G,
'/U01/app/oracle/oradata/ocp/tp2503.dbf' size50M
-- EXTENT MANAGEMENT DICTIONARY
Autoextend on next 10 m maxsize 2G
Extent management local uniform size 2 M
Segment space management auto;

Create tablespace TP21 DATAFILE '/u01/app/oracle/oradata/ocp/tp2501.dbf' SIZE 50 M
Extent management dictionary;

Create tablespace tp22 datafile '/u01/app/oracle/oradata/ocp/tp22_1.dbf' size10M, '/u01/app/oracle/oradata/ocp/tp22_2.dbf' size 10 m extent management local;

4. Delete tablespaces
1. Delete tablespaces without objects
Drop tablespace tp1;

2. Delete tablespaces with objects, but do not delete physical files
Drop tablespace tp1 including contents;

3. Delete tablespaces with objects and delete physical files
Drop tablespace tp20 including contents and datafiles cascadeconstraints;

5. Manage the space in the tablespace
1. Zone Management (the default space management for 8i is DMT, and the default space management for 9I is LMT)
(1) data dictionary management
SYS. UET $-row with description of used zone
SYS. FET $-row with description Zone

(2) Local Management
A. Unified
B. Automatic
Alter system dump datafile 5 block 2;
Alter system dump datafile 5 block 3;

(3) advantages of local tablespace Management
A. Recursive space management is not required.
B. do not record the free space in the data dictionary table to reduce contention
C. Automatic Tracking of adjacent idle space without merging idle Space
D. the system automatically determines the size of the local management area.
E. do not generate Restoration Information for changes to the location map. For such changes, tables in the data dictionary are not updated (except for special situations such as the tablespace quota information)

(4) You can use the following PL/SQL program to convert any dictionary-managed tablespace to local management:
Executedbms_space_admin.tablespace_migrage_to_local ('tablespacename ');

2. segment Management
(1) SSM: Automatic Management (BMB segment: BitmapManagedSegments), using the data block free space in the bitmap management segment, large concurrency.

(2) MSSM: manual management (FreeList). The Free List is used to manage the free space of data blocks in a segment. Serial operations may cause contention of segment headers.
-- Dump field header information

(3) Management of OMF (OracleManaged Files)
① Set parameters
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
② Create a tablespace
Altersystem set DB_CREATE_FILE_DEST = '/u01/oradata ';
CREATETABLESPACE tbs_1;

Vi. Temporary tablespace
1. Default temporary tablespace of the database
Sys @ OCM> select * fromdatabase_properties where property_name = 'default _ TEMP_TABLESPACE ';
Sys @ OCM> alter databasedefault temporary tablespace temp1;

2. Create a temporary tablespace or a temporary tablespace Group
(1) create a temporary tablespace
Create temporary tablespace temp1 TEMPFILE '/u01/app/oracle/oradata/ocp/temp1.dbf' SIZE 10 m;

(2) create a temporary tablespace Group
Create temporary tablespace temp1 TEMPFILE '/u01/app/oracle/oradata/ocp/temp1.dbf' SIZE 10 m tablespace group group1;
Create temporary tablespace temp2 TEMPFILE '/u01/app/oracle/oradata/ocp/temp2.dbf' SIZE 10 m tablespace group group1;

3. Shrink temporary tablespace and temporary files
(1) Method 1: shrink the temporary tablespace
Gyj @ OCM> alter tablespace TEMP shrink space keep 10 M;

Tablespace altered.

(2) method 3: shrink a file in the temporary tablespace
Sys @ OCM> alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ocm/temp01.dbf 'keep 10 M;

Tablespace altered.

(3) method triple new definition of temporary file size
Gyj @ OCM> alter database tempfile '/u01/app/oracle/oradata/ocm/temp01.dbf' resize 100 M;

Database altered.

3. Delete temporary tablespace
(1) query temporary tablespace
Sys @ OCM> selecttablespace_name, file_name, bytes/1024/1024 file_size, autoextensible fromdba_temp_files;

TABLESPACE_NAME FILE_NAME FILE_SIZE AUT
---------------------------------------------------------------------------------------------
TEMP/u01/app/oracle/oradata/ocm/temp01.dbf 10.9921875 YES

(2) Permanently delete temporary tablespace
Sys @ OCM> drop tablespace temp includingcontents and datafiles cascade constraints;

(3) Delete a file in the temporary tablespace
Sys @ OCM> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop ;-

VII. Monitoring tablespace
Gyj @ OCM> set line 200 pagesize 9999
Gyj @ OCM> select B. tablespace_name, round (sum (B. bytes)/1024/1024, 0) sum_MB, round (sum (nvl (a. bytes, 0)/1024/1024, 0)
2 free_MB, round (sum (B. bytes)-sum (nvl (a. bytes, 0)/sum (B. bytes), 4) * 100use_precent
3 from (selecttablespace_name, file_id, sum (bytes) bytes from dba_free_space group bytablespace_name, file_id) a, dba_data_files B
4 where a. file_id (+) = B. file_idand a. tablespace_name (+) = B. tablespace_name
5 group by B. tablespace_name;
Order by use_precent;

TABLESPACE SUM_MB FREE_MB USE_PRECENT
---------------------------------------------------------
TP1 200 193 3.5
UNDOTBS1 145 18 87.84
SYSAUX 520 29 94.45
USERS 5 1 81.25
SYSTEM 680 4 99.39
EXAMPLE 100 21 78.75

8. Ways to expand Databases
1. Create a New tablespace

2. Add files in the tablespace
Sys @ OCM> alter tablespace tp1 add datafile '/u01/app/oracle/oradata/ocm/tp3.dbf' size 10 M;

Tablespace altered

3. Increase the file size
Sys @ OCM> alter database datafile '/u01/app/oracle/oradata/ocm/tp3.dbf' resize 20 M;

Database altered.
Www.2cto.com
4. dynamically expand data files
(1) automatic expansion of data file query
Sys @ OCM> col file_name for a50;
Sys @ OCM> select file_name, autoextensiblefrom dba_data_files;

FILE_NAME AUT
-----------------------------------------------------
/U01/app/oracle/oradata/ocm/users01.dbf YES
/U01/app/oracle/oradata/ocm/undotbs01.dbf YES
/U01/app/oracle/oradata/ocm/sysaux01.dbf YES
/U01/app/oracle/oradata/ocm/system01.dbf YES
/U01/app/oracle/oradata/ocm/example01.dbf YES
/U01/app/oracle/oradata/ocm/tp1.dbf NO
/U01/app/oracle/oradata/ocm/tp2.dbf NO
/U01/app/oracle/oradata/ocm/tp3.dbf NO
 
(2) Change the tp3.dbf file to automatic extension
Sys @ OCM> alter database datafile '/u01/app/oracle/oradata/ocm/tp3.dbf' autoextend on next 2 M maxsize 2048 M;

Database altered.

(3) confirm again whether the tp3.dbf file is automatically extended to www.2cto.com
Sys @ OCM> select file_name, autoextensiblefrom dba_data_files;

FILE_NAME AUT
-----------------------------------------------------
/U01/app/oracle/oradata/ocm/users01.dbf YES
/U01/app/oracle/oradata/ocm/undotbs01.dbf YES
/U01/app/oracle/oradata/ocm/sysaux01.dbf YES
/U01/app/oracle/oradata/ocm/system01.dbf YES
/U01/app/oracle/oradata/ocm/example01.dbf YES
/U01/app/oracle/oradata/ocm/tp1.dbf NO
/U01/app/oracle/oradata/ocm/tp2.dbf NO
/U01/app/oracle/oradata/ocm/tp3.dbf YES