Oracle Table SpaceThe biggest difference between a SQL Server database and an Oracle database is a tablespace design. Oracle database pioneered the design concept of tablespaces, which made an indelible contribution to the high performance of Oracle databases. As you can say, many of the optimizations in Oracle are based on the design philosophy of the tablespace.1 Overview
Basic Concepts of Oracle Tablespace
The Oracle database is partitioned into logical areas called the table spaces--forming the logical structure of the Oracle database. An Oracle database can have one or more table spaces, while one table space corresponds to one or more physical database files . The
tablespace is the smallest unit of Oracle Database recovery , accommodating many database entities such as tables, views, indexes, clusters, fallback segments, and temporary segments.
Each Oracle database has a system table space, which is created automatically when the database is created. The
system tablespace must always be online because it contains the basic information required to run the database ( data dictionary for the entire database, online help mechanism, all fallback segments, temporary and bootstrap segments, all user database entities, Tables required by other Oracle software products).
an Oracle database for a small application typically includes only the system table space, whereas a slightly larger application with an Oracle database with multiple table spaces can be more convenient for database use.
the role of the Oracle table spaceThe role of Tablespaces helps DBA users to do the following: 1. Determine the spatial allocation of database entities; 2. Set the space share of database users; 3. Control the availability of data in the database; 4. Distribute data between different devices to improve performance; 5. Back up and restore data. When a user creates its database entity, it must have the appropriate power in a given tablespace, so for a user to manipulate data in an Oracle database, it should: 1. is granted the resource privilege in one or more tablespaces; 2. The default tablespace is specified; 3. The allocated storage space usage share of the specified tablespace; 4. The default temporary segment table space is specified, and a different tablespace is set up, setting the maximum storage capacity.2 controlling Oracle Space
In some large database applications, we need to control the disk space occupied by a user or a group of users. This is like having a disk quota set for each user in the file server to prevent hard disk space exhaustion. So, in the database, we also need to limit the amount of disk space that the user can use. To achieve this, we can do this through the tablespace. We can create different tablespaces in the Oracle database, set the maximum storage capacity for them, and then attribute the user to the table space. In this case, the user's storage capacity is limited by the size of the table space. Of course, the advantages of table space is not only these, the higher the enterprise performance requirements for the database, or the larger the database capacity, the greater the advantages of table space. Below, we'll look at how table spaces are handled in the Oracle database to see if there is enough performance and security to contend with the SQL Server database.
Oracle Space[1] When designing a database, we recommend that the database administrator set the tablespace in the following order. Step one: Establish an Oracle user tablespace. When designing a database, you first need to design the table space. We need to consider whether to build a table space, or to create multiple table spaces, where each table space is stored, disk quotas, and so on. In the end, the design of how many table space is reasonable, there is no unified statement, which is mainly based on the actual needs of enterprises to judge. If the enterprise needs to control the user's disk quota, it will need to set the table space according to the number of users. When the enterprise's data capacity is large, and the performance of the database is higher, it is necessary to set up different table spaces according to different types of data to improve its input and output performance. Step two: Establish the Oracle user and specify the user's default tablespace. When creating a user, we recommend that the database administrator specify the user's default tablespace. Because we are creating database objects, such as database tables, by using the CREATE statement, it defaults to the current default space stored in the database. If you do not specify a user default tablespace, each time a user creates a database object, it is stored in the database default tablespace, and if you want to store it in a table space of your own, it is a good idea to specify your own table space as the default table space. Also note that different table spaces have different permissions controls. The user has full control over tablespace A, and may have only query permissions for table space B, not even the permissions of the connection. Therefore, reasonable for the user to configure the table space access rights, but also to improve the security of the database a method.3Oracle Table Space Recovery Scenario
User Table SpaceOracle tablespace Error Behavior: ora-01157,ora-01110 or OS-level error when starting database (e.g. ORA-07360, shutting down the database (using shutdown normal or shutdown immediate) will cause error ora-01116,ora-01110 and OS-level error ORA-07368 resolution: The following two solutions: scenario one, the user's tablespace can be easily rebuilt by the exported object is available or the object in the tablespace can be easily rebuilt etc. In this case, the simplest method is to offline and delete the data file, delete the tablespace, and rebuild the table space and all the objects. svrmgrl> startup mountsvrmgrl> ALTER DATABASE datafile filename offline drop;svrmgrl> ALTER DATABASE open;svrmgr L> drop tablespace tablespace_name including contents; Rebuild table space and all objects. Scenario two, the user's table space can not be easily rebuilt in most cases, rebuilding the tablespace is impossible and hard work. The method is to back up and do media recovery. If your system is running in Noarchivelog mode, only the lost data is in the online redo Log China can be restored. Steps are as follows: 1) Restore The lost datafile from a backup2) svrmgrl> startup mount3) svrmgrl> Select v1.group#,member,sequence# , first_change# from V$log v1,v$logfile v2 where v1.group#=v2.group#;4) if the database is running in noarchivelog mode:svrmgrl> Select File #,change# from V$recover_file; if the change# is greater than the minimum first_change#, the data file can be restored. If the change# is less than the minimum first_change#, the data file is unrecoverable. Restores the most recent full backup or adoption scenario one. 5) svrmgrl> recover datafile filename;6) Confirm Recovery Success 7) svrmgrl> ALTER DATABASE Open resetlogs; read-only table space requires no media recovery, as long as the backup is restored. The only exception is that the tablespace is changed to the Read-write mode after the last backup the table space is changed to Read-only mode after the last backup. In this case, media recovery is required.Temporary table SpaceThe temporary table space does not contain real data, and the recovery method is to delete the temporary tablespace and rebuild it.system table SpaceIf the backup is not available, you can only use the method of rebuilding the databaseRoll back table space
