Oracle 11g OCP Notes (5)--Oracle Storage architecture

1. Understand Oracle tablespace and data files.

Oracle storage model. Table spaces, segments, intervals, Oracle blocks, data files, and the corresponding relationships of operating system blocks.

Select Segment _type, COUNT (1) from Dba_segments Group by Segent_type. View type.

Table Heap Tables

Index indexes

Type2undo withdrawal paragraph??????? What's the difference with rollback??

Rollback ROLLBACK Segment

Table Partition Partitioned Tables

Index partition partitioned indexes

Lobsegment Lobindex LOB partition large Object type

Cluster aggregation

clusted Table Aggregation Tables

Select Tablespace_name,file_id,extent_id,block_id,block,butes from Dba_extents;
Select tablespace_name,file_name,file_id from Dba_data_files;

ALTER TABLE XX allocate extend (storage ' filename ')

File Storage technology:

Files on the local file system

Files on a clustered file system

Files on the original device

Files on ASM

？？？？？？？？

ASM can store only database files. Binary files cannot be stored. SPFile's solution.

Querying the view of the storage structure
Control the name and size of the file
Select Name,block_size*file_size_blks bytes from V$controlfile;
The name and size of the online redo log file member
Select Member,bytes from V$log join V$logfile using (group#);
The name and size of the data file and temporary file
Select Name,bytes from V$datafile;
Select Name,bytes from V$tempfile;

2. Create and manage table spaces

(1). CREATE TABLE Space
Create [Smallfile|bigfile] tablespace tablespace_name
DataFile '/u01/app/oracle/oradata/sid/***.dbf '
Size 100M autoextend on next 10M maxsize 200M
Logging
Extent Management Local
Segment Space Management Auto
Default nocompress;

Dba_spaces Dba_datafiles Db_free_space

(2) Change table space

Alter tablespace tablespaceoldname rename to Tablespacenewname; --Rename table space
Alter tablespace tablespacename offline [normal|immediate|temporary]; --Take the table space offline
Alter tablespace tablespacename [Read Only|read write]; --Table Space tag read-only and read-write
ALTER DATABASE datafile filename Resize n[m| G|                        T]; --Resize the tablespace data file
Alter tablespace tablespacename add datafile datafilename size n[m| G| T]; --Add data file into table space
ALTER DATABASE datafile filename autoextend on next 100M maxsize 4G; --Modifying the automatic extension of tablespace data files

(3). Renaming table spaces and data files
Alter tablespace tablespacename rename to Newtablespacename;
Alter tablespace tablespacename offline;
Host Rename xxxxx.dbf xxxxxxx.dbf
Alter DATABASE rename file ' Oldfilename ' to ' newfilename ';
Alter tablespace Tablespacename Online;


(4). Delete Table Space
Drop tablespace Tablespacenmae [including contents [and Datafiles]];


Use the OMF attribute to manage the creation of database files, setting the following parameters
Db_create_file_dest--The default location for data files
Db_create_online_log_dest_1--The default location for online redo logs
Db_create_online_log_dest_2
Db_create_online_log_dest_3
Db_create_online_log_dest_4
Db_create_online_log_dest_5
Db_recovery_file_dest--default location of the quick Recovery zone, archived log files, and backup files

3. Managing space in a table space

Divide the space into the table space, the space in the table space, and the space in the segment to the row.

sys.uet$ represents free space. sys.free$ represents the remaining space

Extend management: Dictionary-based space management and local space management (9i)

Uniform size is allocated at one time.

Select Tablespace_name,extend_management from dba_tablespace query management mode.

exec dbms_space_admin.tablespace_migeagte_to_local (' Tablespacename ')

Manage segment.

Automatic segment management. 9i introduced, 11g is the default.

Other

3. Size limits for data files:
(1) Data file for Smallfile table space:
ROWID consists of four parts: Object number + Relative file number + block number + line number (32bit object# + 10bit rfile# + 22bit block# + + bit row#), 10Bytes.
Select Dbms_rowid.rowid_object (ROWID) object_id,
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) Relative_fno,
Dbms_rowid.rowid_block_number (ROWID) Block_number,
Dbms_rowid.rowid_row_number (ROWID) Row_number,
From Mms.usr_mstr
where RowNum < 10;


Each file contains two numbers, one is the absolute file number file_id, the other is the relative file number relative_fno.
Select Dbms_rowid.rowid_relative_fno (ROWID) relative_fno,--relative file number (maximum 1024)
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (rowID, ' MMS ', ' usr_mstr ') absolute_fno--Absolute file number
From Mms.usr_mstr
where RowNum < 10;


In the data file of the Smallfile tablespace, Oracle uses 22 bits for block address storage, 22 bits can represent up to 2^22-1=4194303=4m data blocks, and if the block size is 8k, the data file has a maximum theoretical size of 32G. If the maximum chunk size is 32K, the theoretical size of the data file is capped at 128G. A table space can have only 1023 data files.
Therefore, the data file size is related to Db_block_size, and the data block size corresponds to the maximum theoretical value of the data file as follows:
2KB 8GB
4KB 16GB
8KB 32GB
16KB 64GB
32KB 128GB


(2) Data file for Bigfile table space
In the data file of the Bigfile tablespace, things will be different because a tablespace allows only one data file.
Select Dbms_rowid.rowid_object (ROWID) object_id,
DBMS_ROWID.ROWID_RELATIVE_FNO (rowID, ' bigfile ') relative_fno,
Dbms_rowid.rowid_block_number (ROWID) Block_number,
Dbms_rowid.rowid_row_number (ROWID) row_number
From Mms.usr_mstr
where RowNum < 10;


Bigfile table space can only have one data file, rowID file number is meaningless, is directly 1024. Since there is no relative_fno problem, there is no need to save up to 1024 of the value of RELATIVE_FNO in rowID. This saves 10 bits of bits to the data block location, and the same length of rowid can be used to address the block of 32-bit binary length. Each data file can contain a maximum of 2^32-1=4g blocks of data. If the data block size is 8K, the data file theory size is capped at 32TB. If the data block size is 32K, the data file theory size is capped at 128TB.

The size of the data file is also related to the limitations of the operating system.

Table-Space statistical information
Select D.status "Status",
D.tablespace_name "Name",
D.contents "Type",
D.extent_management "Extent Management",
NVL (a.bytes/1024/1024, 0) "Size (M)",
To_char (NVL (A.BYTES-NVL (f.bytes, 0), 0)/1024/1024, ' 99999999.999 ') "Used (M)",
ROUND (NVL (A.BYTES-NVL (f.bytes, 0)/a.bytes * 0), 2) "Used (%)"
From Sys.dba_tablespaces D,
(select Tablespace_name, sum (bytes) bytes from Dba_data_files Group by Tablespace_name) A,
(select Tablespace_name, sum (bytes) bytes from Dba_free_space Group by Tablespace_name) F
WHERE D.tablespace_name = a.tablespace_name (+)
and D.tablespace_name = F.tablespace_name (+)
And not (d.extent_management like ' LOCAL ' and d.contents like ' temporary ')
UNION All
Select D.status "Status",
D.tablespace_name "Name",
D.contents "Type",
D.extent_management "Extent Management",
NVL (a.bytes/1024/1024, 0) "Size (M)",
To_char (NVL (t.bytes,0)/1024/1024, ' 99999999.999 ') "Used (M)",
ROUND (NVL (T.bytes/a.bytes *, 0), 2) "Used (%)"
From Sys.dba_tablespaces D,
(select Tablespace_name, sum (bytes) bytes from Dba_temp_files Group by Tablespace_name) A,
(select Tablespace_name, sum (bytes_cached) bytes from V$temp_extent_pool Group by Tablespace_name) T
WHERE D.tablespace_name = a.tablespace_name (+)
and D.tablespace_name = T.tablespace_name (+)
and d.extent_management like ' LOCAL '
and d.contents like ' temporary '
ORDER by 2;


Select T.tablespace_name name, d.allocated, u.used, F.free, T.status, d.datafiles, Contents,
T.extent_management Extman, T.segment_space_management Segman
From Dba_tablespaces T,
(select Tablespace_name, sum (bytes) allocated, COUNT (file_id) datafiles from Dba_data_files Group by Tablespace_name) d,
(select Tablespace_name, sum (bytes) free from Dba_free_space Group by Tablespace_name) F,
(select Tablespace_name, sum (bytes) used from Dba_segments Group by Tablespace_name) u
where T.tablespace_name = D.tablespace_name (+)
and T.tablespace_name = F.tablespace_name (+)
and T.tablespace_name = U.tablespace_name (+);

Reference URL: 43452133

Oracle 11g OCP Notes (5)--Oracle Storage architecture