Logical Structure of Oracle 10 Database

2.1.1unlike other database products, Oracle has its own unique system architecture. The Oracle system architecture is the framework of the entire ORACLE Server System and the basis and core for managing and applying ORACLE data servers. The Oracle system architecture consists of three parts: logical structure, physical structure, and instance. The instance is the core of the physical and logical structures, as shown in Figure 2-1 and Figure 2-2. Figure 2-1 shows the relationship between the three-level database mode and its physical files. Figure 2-1 database mode and its physical file Relationship Diagram 2-2 The Oracle system architecture and functions, whether in Oracle Application Development or database management, use instances as the starting point. However, Oracle application development mainly focuses on the logical objects of databases (such as tables, indexes, and views), while database management focuses on all the contents of databases. An Oracle database consists of various files that constitute a physical structure, such as data files, control files, and redo log files; an instance is a collection of SGA and server background processes allocated by Oracle in the memory. The Oracle Database Server is a combination of databases and instances. 2.1.2oracle Logical Structure Oracle logical structure is a hierarchical structure. It consists of the following concepts: tablespace, segment, partition, and data block. The logical structure is user-oriented. The logical structure is used to develop applications using Oracle. The hierarchical structure of database storage and its components. The structure objects also form different levels of granularity relationships from data blocks to tablespaces, as shown in Figure 2-3 and figure 2-4. Figure 2-3 Oracle 10 Gb database Hierarchy Diagram 2-4 relationships between segments, zones, and data blocks 1. Data BlockOracle data block is a continuous set of operating system blocks. The size of the allocated database block is set when the Oracle database is created, and the data block is the basic unit for Oracle read/write. The size of a data block is generally an integer multiple of the size of the operating system block, which can avoid unnecessary system I/O operations. From Oracle9i, the data block sizes of different tablespaces in the same database can be different. Data blocks are the most basic storage units of Oracle, while tablespaces, segments, and ranges are members of logical organizations. Each block in the database buffer is a data block. A data block cannot span multiple files. The data block structure mainly includes: · title: including general block information, such as block address and segment type. · Table Directory: contains the row information about the table in the data block. · Oracle architecture Chapter 1 row Directory: contains information about the row address in the data block. · Row data: including table or index data. A row can span multiple data blocks. · Free space: allocate free space to insert new rows and update rows that require additional space. The space management parameter pctfree can be used to control the use of free space. Free space management can be automatic or manual. In data operations, there are two types of statements that can increase the free space of the database block: one is the delete statement, and the other is the update to update the existing row. The released idle space can be used for the insert statement. If the insert statement is in the same transaction as the statement that generates the idle space, and then runs it, the insert statement can directly use the generated free space. If the insert statement is in a transaction that is separated from the statement that generates free space, the insert statement can be used after other transactions are committed and when it requires space, use the previously generated free space. The space released in the data block may be continuous or discontinuous. Oracle merges the free space of a data block only when the following conditions occur: When an insert or update statement uses a data block, the free space of the data block is sufficient to store a new row, in addition, the free space is fragmented, and the continuous space in the data block cannot be inserted into a row. In addition, Oracle also needs to compress the data block space when the system performance declines. There are two parameters that can affect the free space: pctfree and pctused. For manually managed tablespaces, you can use two space management parameters pctfree and pctused to control the use of idle space by insert and update. You can specify these two parameters when creating or modifying a table. You can specify the pctfree parameter when creating or modifying an index with its own index segment. The pctfree parameter is the minimum percentage of free space reserved for updates to the block row. The default value is 10. For example, if pctfree is set to 20 in the create table statement, 20% of each data block in the Data Segment of the table is used as available free space, used to update data rows that already exist in a data block. The remaining 80% is used to insert a new data row until it reaches 80%. Obviously, the smaller the pctfree value, the less space reserved for the existing row update. Therefore, if pctfree is set too high, it will increase I/O during full table scan, wasting disk space. If pctfree is set too low, it will cause row migration. The pctused parameter sets whether the data block is idle. When the used space of a data block is lower than the value of pctused, this data block indicates that it is idle and this free space is only used to insert new rows. If the data block has reached the upper boundary determined by pctfree, Oracle considers that the data block cannot be inserted into new rows. For example, if pctused is set to 40 in the create table statement, the data block is available if it is less than or equal to 39. Therefore, you can fill in more data blocks to save space, but increase processing overhead, because the free space of data blocks is always occupied by updated rows, therefore, data blocks need to be reorganized frequently. Low pctused increases the free space of the database, but reduces the overhead of update operations. Therefore, if pctused is set too high, the disk utilization will be reduced, leading to row migration. If pctused is set too low, disk space will be wasted and I/O output during full table scan will be added. Pctused is a parameter relative to pctfree. So, how do I select the pctfree and pctused values? There is a formula for reference. Obviously, the sum of pctfree and pctused cannot exceed 100. If the sum of the two is less than 100, the optimal balance between space utilization and system I/O is: The sum of pctfree and pctused is equal to 100% minus the size of a row as a percentage of the block space. For example, if the block size is 2048 bytes, it requires 100 bytes of overhead, and the row size is 390 bytes (20% of the available block ). To make full use of the space, the sum of pctfree and pctused should be 80%. How can we determine the size of data blocks? There are two factors to consider: first, the database environment type. For example, is it a DSS environment or an OLTP environment? In the data warehouse environment (OLAP or DSS), you need to perform many queries that have been running for a long time, so you should use big data blocks. In the OLTP system, users can process a large number of small transactions and use smaller data blocks to achieve better results. The second is the size of SGA. The size of the database buffer is determined by the data block size and the db_block_buffers parameter of the initialization file. It is best to set it to an integer multiple of the operating system I/O. 2 . ZoneA zone (extent) is a group of continuous data blocks. When a table, rollback segment, or temporary segment is created or requires additional space, the system always allocates a new data zone for it. A Data zone cannot span multiple files because it contains continuous data blocks. The purpose of the use area is to save data of a specific data type, which is also the basic unit of data growth in the table. In Oracle databases, the allocated space is in the Data zone. An Oracle object contains at least one data zone. Set the storage parameters of a table or index to include the size of its data zone. 3 .Segments are composed of multiple data zones. segments are specific database objects (such as table segments, index segments, rollback segments, and temporary segments) A series of data zones. The data areas contained in the segments can be discontinuous and span multiple files. The purpose of the use segment is to save a specific object. An Oracle database has four types of segments: · data segments: Also known as table segments, which contain data and are related to tables and clusters. When a table is created, the system automatically creates a data segment named after the table. · Index segment: contains the index used to improve system performance. Once an index is created, the system automatically creates an index segment named after the index. · Rollback segment: Contains rollback information and is used during database recovery to provide database read consistency and rollback of uncommitted transactions, that is, the data space for rolling back and forth transactions. When a transaction starts to be processed, the system allocates a rollback segment for it. The rollback segment can be dynamically created and revoked. The system has a default rollback segment, which can be managed automatically or manually. · Temporary segment: it is the segment created by Oracle during running. When an SQL statement requires a temporary workspace, Oracle creates a temporary segment. Once the statement is executed, the interval of the temporary segment is returned to the system. 4 . TablespaceOracle Database (tablespace) is composed of several tablespaces. Any database objects must be stored in a tablespace. The tablespace corresponds to several disk files, that is, the tablespace is composed of one or more disk files. A tablespace is equivalent to a folder in the operating system and a ing between the logical structure of the database and physical files. Each database has at least one tablespace, And the tablespace size is equal to the total size of all data files belonging to it. There are several special tablespaces in Oracle 10 GB: (1) system tablespaces (system tablespace) are required by each Oracle database. The function is to store information required for database management in the system tablespace, such as the tablespace name and data files contained in the tablespace. The name of the system tablespace cannot be changed. The system tablespace must be available at any time, which is also a necessary condition for database operation. Therefore, the system tablespace cannot be offline. System tablespaces include data dictionaries, stored procedures, triggers, and system rollback segments. To avoid storage fragmentation and competition for system resources in the system tablespace, an independent tablespace should be created to separately store user data. (2) The sysaux tablespace is created with the database creation. It acts as the auxiliary tablespace of the system and stores objects other than the data dictionary. Sysaux is also the default tablespace of many Oracle databases. It reduces the number of tablespaces managed by databases and DBAs, and reduces the load on system tablespaces. (3) compared with other tablespaces, temporary tablespaces (temp tablespace) are mainly used to store temporary data generated during the running of Oracle databases. A database can create multiple temporary tablespaces. When the database is closed, all data in the temporary tablespace is cleared. All other tablespaces except temporary tablespaces are permanent tablespaces. (4) The undo tablespace is used to save the Oracle database revocation information. That is, the tablespace that saves the user rollback segment is called the rollback tablespace (or RBS undo tablespace )). In Oracle8i, It is rollback tablespace, Which is changed from Oracle9i to undo tablespace. In Oracle 10 Gb, only six tablespace sysaux, system, temp, undotbs1, example, and users are initially created. Temp is a temporary tablespace, while undotbs1 is an undo tablespace. Figure 2-5 shows the relationship between tablespaces and databases and data files. Figure 2-5 relationship between tablespaces and databases and data files 2.1.3 The following principles should be observed when managing tablespaces: 1) use multiple tablespaces. Using multiple tablespaces makes database operations more flexible. It is mainly reflected in the following aspects: · separating user data from Data Dictionary data and storing data files in different tablespaces on different disks can reduce I/O competition. · Separating the data of one application from other applications can prevent multiple applications from being affected when the tablespace is offline. · A single tablespace can be taken offline as needed to achieve better availability. · Optimize tablespaces by reserving tablespaces for different types of databases, such as high update, read-only, or temporary Segment Storage. · Back up a single tablespace. 2) Specify the tablespace quota for the user. To create, manage, and use tablespaces, you must first log on to the database as sys and as sysdba. Unlike Oracle9i, in Oracle 10g, the account and password for SQL * Plus startup do not require quotation marks. The command format is sqlplus sys/<sys password> As sysdba. The data dictionary and permissions used to create and manage tablespaces in Oracle 10 Gb can be summarized as follows. 1 . Data Dictionary related to the tablespaceMetadata related to table spaces can be queried and used in the following data dictionaries. It mainly includes: region, dba_users, region, user_tablespaces, region, user_extents, user_segments, user_free_space, region, dba_extents, region, dba_segments, region, V _ $ backup_datafile, _ $ database_block_upload uption, V _ $ datafile, V _ $ datafile_copy, V _ $ datafile_header, V _ $ rollstat, V _ $ segment_statistics, and V _ $ undostat. Data dictionaries starting with V _ $ are saved as dynamic information. 2 . System permissions related to using tablespacesThe primary system permissions related to tablespaces include create tablespace, alter tablespace, drop tablespace, manage tablespace, and unlimited tablespace. Unlimited tablespace allows users to access all tablespaces without restriction. For security reasons, exercise caution when granting this permission to users. If the user does not need this system permission, it is recommended to revoke this permission. Otherwise, the user will use this permission to deliberately create a large number of objects or copy data, so as to fill the tablespace and cause the database server to crash. 3 . Create a permanent tablespaceCommand syntax: SQL> Create [undo] tablespace [datafile filespec [autoextend_clause] [, filespec [autoextend_clause]...] [{minimum extent integer [k | M] | blocksize integer [k] | {logging | nologging} | default storage_clause | {online | offline} | {permanent | temporary} | extent_management_clause | principal} [minimum extent integer [k | M] | blocksize integer [k] | {logging | nologging} | default storage_clause | {online | offline} | {permanent | temporary} | extent_management_clause | segment_management_clause]...]; [Example 2-1] Create a tablespace named dalianrenSQL> Create tablespace dalianren nologgingdatafile 'd:/Oracle/product/10.2.0/oradata/dalianren/dalianren01.ora 'size 50 m blocksize 8192 extent management local uniform size 256 ksegment space management auto; 4 . Offline a tablespaceCommand syntax: SQL> alter tablespace <tablespace_name> offline; [Example 2-2] Remove the tablespace dalianren offlineSQL> alter tablespace dalianren offline; Note:The system tablespace cannot be offline. 5 . Bring a tablespace onlineCommand syntax: SQL> alter tablespace <tablespace_name> online; [Example 2-3] connecting the tablespace dalianrenSQL> alter tablespace dalianren online; 6 . Read-Only tablespaceCommand syntax: SQL> alter tablespace <tablespace_name> read only; [Example 2-4] Change the tablespace dalianren to read-only.SQL> alter tablespace dalianren read only; 7 . Make the tablespace readable and writableCommand syntax: SQL> alter tablespace <tablespace_name> Read Write; [Example 2-5] Change the tablespace dalianren to read/writeSQL> alter tablespace dalianren read write; 8 . Create temporary tablespaceCommand syntax: SQL> create temporary tablespace <tablespace_name> tempfile '<data_file_path_and_file_name>' size <megabytes> M autoextend <on | off> extent management local uniform size <extent_size>; [Example 2-6] create temporary tablespace tempSQL> create temporary tablespace temptempfile 'd:/Oracle/product/10.2.0/oradata Dalian/temp01.ora 'size 500 m autoextend offextent management local uniform size 512 K; note that although the statement alter tablespace contains the temporary keyword, you cannot use the alter tablespace statement with the temporary keyword to convert a locally managed permanent tablespace to a locally managed temporary tablespace. You must use the create temporary tablespace statement to directly create a locally managed temporary tablespace. 9 . Add temporary tablespace data filesCommand syntax: SQL> alter tablespace <tablespace_name> Add tempfile '<path_and_file_name>' size <n> m; [Example 2-7] add data files for temporary tablespace temp_renSQL> alter tablespace temp_ren add tempfile 'd:/Oracle/product/10.2.0/oradata/Dalian/temp_ren.dbf' size 100 m; 10 . Adjust the data file of the temporary tablespaceCommand syntax: SQL> alter database tempfile '<path_and_file_name>' resize <pai_bytes> m; [Example 2-8] adjust the data file size of the temporary tablespaceSQL> alter database tempfile 'd:/Oracle/product/10.2.0/oradata/test/temp_ren.ora 'resize 20 m; 11 . Take the tablespace data file or temporary file offlineCommand syntax: SQL> alter database datafile '<path_and_file_name>' offline; or SQL> alter database tempfile '<path_and_file_name>' offline; [Example 2-9] Remove the tablespace data file or temporary fileSQL> alter database datafile 'd:/Oracle/product/10.2.0/oradata/Dalian/temp_ren.ora 'offline; or SQL> alter database tempfile 'd: /Oracle/product/10.2.0/oradata/Dalian/temp_ren.ora 'offline; 12. Bring temporary tablespace onlineCommand syntax: SQL> alter database tempfile '<path_and_file_name>' online; [Example 2-10] online SQL> alter database tempfile 'd: /Oracle/product/10.2.0/oradata/Dalian/temp_ren.ora 'online; 13. Delete a tablespace without deleting its files.Command syntax: SQL> drop tablespace <tablespace_name>; [Example 2-11] Delete the table space dalianren, but do not delete its files.SQL> drop tablespace dalianren; 14. Delete the tablespace that contains the directory contentCommand syntax: SQL> drop tablespace <tablespace_name> including contents; [Example 2-12] Delete the tablespace dalianren and its contentsSQL> drop tablespace dalianren including contents; 15 . Delete tablespaces including directory content and data filesCommand syntax: SQL> drop tablespace <tablespace_name> including contents and datafiles; [Example 2-13] Delete the tablespace dalianren, its contents, and data files. SQL> drop tablespace dalianren including contents and datafiles; 16 When there is a reference constraint, delete the tablespace that contains the directory content and data files.Command syntax: SQL> drop tablespace <tablespace_name> including contents and datafiles cascade constraints; [Example 2-14] Delete the tablespace dalianren, its contents, data files, and related constraints.SQL> drop tablespace dalianren including contents and datafiles cascade constraints; 17 . Tablespace renameYou cannot rename the tablespace directly in Oracle9i. You can rename Permanent and Temporary tablespaces in Oracle 10 Gb. However, the system and sysaux tablespaces cannot be renamed. Command syntax: SQL> alter tablespace <old_tablespacename> Rename to <new_tablespacename>; [Example 2-15] Change the tablespace users to newusers.SQL> alter tablespace users Rename to newusers; in Oracle 10 Gb, if an instance that uses pfile to undo a tablespace is renamed, a message is warned to be written to the log file, remind the user to change the parameter value of undo_tablespace. Note: When drop tablespace is used to accidentally delete a tablespace, you can check the alert file to determine the time of misoperation. The file is located in the ORACLE_HOME/admin/<Sid>/bdump directory named Alert _ <Sid>. log, for example, the alert_test.log file in the Directory D:/Oracle/product/10.2.0/admin/test/bdump. 18 . Multiple temporary tablespaceThe concept of a tablespace group is added to Oracle 10 Gb. By using a tablespace group, you can use more than one temporary tablespace storage segment. A tablespace group is automatically and implicitly created by the system when the first tablespace is specified to this group. For example, create a tablespace group by adding an existing tablespace. SQL> alter tablespace temp tablespace group temp_ts_group; Add a new tablespace TO THE created tablespace group. SQL> create temporary tablespace temp2tempfile 'd:/Oracle/product/10.2.0/oradata/test/temp201.dbf' size 20 m tablespace group temp_ts_group; the table space specified for the group can be queried in the view. SQL> select * From dba_tablespace_groups; group_nametablespace_name -------------------------------------------------------- temp_ts_grouptemp_ts_grouptemp22 rows selected. SQL> once a tablespace group is created, you can specify it to the user or use it as the default temporary tablespace, just like a tablespace. · Assign a tablespace group to the user as a temporary tablespace. SQL> alter user Scott temporary tablespace temp_ts_group; · use a tablespace group as the default temporary tablespace. SQL> alter database default temporary tablespace temp_ts_group; · The tablespace can also be removed from the tablespace group. SQL> alter tablespace temp2 tablespace group; · query a table space group. SQL> select * From dba_tablespace_groups; group_nametablespace_name limit temp_ts_grouptemp1 row selected. SQL> theoretically, there is no maximum limit on the number of tablespaces in a tablespace group, but at least one tablespace must be included. When the last tablespace is deleted, the tablespace group is also implicitly deleted. If the tablespace group is still referred to as a temporary tablespace, the last tablespace member in the group cannot be deleted. At the same time, the tablespace group cannot have the same name as the tablespace. 2.1.4 related queries of tablespaces list tablespaces and tablespaces, allocated space, idle space, and the next idle partition, as shown below. Set linesize 132 set pagesize 60col tablespace_name format a12col file_name format a38col tablespace_kb heading 'tablespace | Total kb 'col kbytes_free heading 'total free | Kbytes' select DDF. tablespace_name, DDF. file_name, DDF. bytes/1024 tablespace_kb, sum (FS. bytes)/1024 kbytes_free, max (FS. bytes)/1024 next_freefrom sys. dba_free_space FS, sys. dba_data_files ddfwhere DDF. tablespace_n Ame = FS. tablespace_namegroup by DDF. tablespace_name, DDF. file_name, DDF. bytes/1024 order by DDF. tablespace_name, DDF. file_name; List data files, table space names, and sizes, as shown below. Col file_name format a50col tablespace_name format a10select file_name, tablespace_name, round (Bytes/1024000) mbfrom dba_data_filesorder by file_name; lists the percentage of tablespace, size, free space, and free space, as shown below. Select DDF. tablespace_name, sum (DDF. bytes) total_space, sum (DFS. bytes) free_space, round (nvl (sum (DFS. bytes), 0)/sum (DDF. bytes) * 100), 2) pct_freefrom dba_free_space DFS, dba_data_files DDF where DDF. tablespace_name = DFS. tablespace_name (+) group by DDF. tablespace_nameorder by DDF. tablespace_name: calculate the actual minimum space and the corresponding file name of each data file in the tablespace. The size is different from that shown in the disk operating system, as shown below. This statement takes a long time to run. Select substr (DF. file_name, 1, 70) filename, max (de. block_id * (de. bytes/de. blocks) + de. bytes)/1024 min_sizefrom dba_extents de, dba_data_files dfwhere de. file_id = DF. file_idgroup by DF. file_name;