? Describe the storage of table row data in a block? Create and manage table spaces? Get tablespace information
When you create a table, you also create one that holds its data.segment。 A table space contains a collection of segments. Logically, the table contains many row and column values. Rows are eventually stored in a database block as a row fragment. The reason for calling itLine Fragment, because in some cases the full row may not be stored in one place.when the inserted row is too large andWhen a single block cannot be mounted (a link line), or when an update causes an existing row to exceed the available free space for the current block (migratingline), this happens. Row fragments are also used when there are more than 255 columns in a table. In this case, the line fragments may be in the same block (within the block chain) or in more than one block.
? Size: header contains segment types (such as tables or indexes), block addresses, table directories, row directories, and transaction slots. Each slot is approximately 23 bytes in size, and these slots are used when modifying a row in a block. The size of the top-down growth.? Row data: This is the actual data for a row in a block. The row data space grows from the bottom up.? Free space: Free space is in the middle of the block, allowing header and row data space to grow as necessary. When you insert a new row or update the columns of an existing row with a larger value, the row data consumes free space. Examples of events that result in block-size growth include the following:-row directories require more row entries-More transaction slots are required than the initial configured number initially, free space in the blocks is contiguous. However, the delete and update operations may cause free space in the block to become fragmented. When needed, the Oracle server engages free space in the block.
- Browse the storage structure
Logical data structures are stored in the physical files of the database. Enterprise Manager (EM) makes it easy to see the logical structure of a database. You can get more information about each structure by clicking the link in the Storage (storage) area of the server page.
1. Click the Server tab, and then click tablespaces (Table Space) under the Storage (storage) heading. 2. Click Create. Note: If you want to create a tablespace that is similar to an existing table space, select an existing table space, and then choose Create like (create similar) from the Actions menu. Click Go (Execute). The Create Tablespace page is displayed. 3. Enter a name for the table space. 4. Under the Extent Management heading, select locally Managed (locally administered). Oracle DB servers can effectively manage the extents of locally managed tablespaces in a tablespace. For a dictionary-managed tablespace, you must be more proactive in the admin area, and you need data dictionary access to track these areas. The "dictionary-managed (dictionary Managed)" option is provided for backwards compatibility only, and Oracle does not recommend this option. 5. Under the "Type" heading, select "Permanent (Permanent)". Persistent tablespace is used to store system or user-created persistent database objects. 6. Under the "Status" heading, select "Read Write". The read-write status indicates that the table space can be read and written by the user after the tablespace is created. This is the default setting. 7. In the Datafiles (data files) area of the page, click Add to append the data file to the tablespace.A table space must have at least one file. Depending on the environment, select the appropriate storage type. Large file table spaces are used for very large databases in which ASM or other logical volume managers support striped or redundant array of independent disks (RAID), and support dynamically scalable logical volumes. 8. On the Add Datafiles page, select the desired storage type and enter the required information. For ASM, select the disk group that you want. For file system, enter a file name and directory for the data file. 9. Enter the desired file size. 10. In the Storage (storage) area, select Automatically extenddatafile when full (autoextend) (Auto-expand (autoextend) after the data file fills out), and then in the Specify a quantity in the Increment (increment) field. This will automatically expand each time the data file goes out of space. Of course, the data file is limited by the physical media on which it resides. Leave "Maximum file Size" as "Unlimited (unlimited)" or enter a maximum value for it. Click Continue (Continue) to return to the Create Tablespace table Space page. The data file that you just created is visible when you return to the data file. 11. After returning to the Create Tablespace page, click the Storage tab and modify the storage options for that tablespace as needed. In most cases, accept all the default values on the Storage (storage) page. Click OK to create the tablespace. Note: These steps describe how to quickly create a table space in most cases. You may need to change some options to meet your specific needs.
Zone allocation: You can assign extents in the local management table space by one of the following two methods:? Automatic: Also known as auto-Assign, this method specifies that the size of the extents in the tablespace is managed by the system.You cannot specify "Automatic (Auto)" For a temporary table space. ? Unified: This approach specifies that the table space is managed using the uniform area size that you specify. The default size is 1 MB. All zones of a temporary tablespace are uniform.You cannot specify Uniform (unified) for the Restore table space. Segment Space management: How to manage the section space in a locally managed tablespace can be specified as:? Automatic: Oracle DB usageBitmap Management SegmentsFree space in the. Bitmaps describe the state of each block of data in a segment, which is related to the amount of space in a block that can be inserted into a row. When the amount of free space in a block increases or decreases, the new state of the data block is reflected in the bitmap. By using bitmaps, Oracle DB can increase the degree of automation in managing free space. Thus, this type of space management is calledAutomatic segment Space Management (ASSM). ? Manual: This method specifies that you want to use theFree listTo manage the free space in a segment. A free list is a list of blocks of data that have space to insert rows. This approach is referred to as "manual segment space Management" because this way of managing the segment space requires specifying and optimizing the pctused, freelists, and freelist groups storage parameters for the schema objects created in the table space. This method is supported for backwards compatibility and is recommended for use with ASSM. Compression options: By default, the data segment compression function isDisabledState. Enabling data segment compression can save disk space usage, reduce memory usage in the buffer cache, and speed query execution when reading. However, CPU overhead is incurred during data loading and DML. This feature is particularly useful in online analytical processing (OLAP) systems where long-timeread-only Operation, but it can also be used in online transaction processing (OLTP) systems. Event Logging:The event record clause sets the default event record value for all segments created in the tablespace. Changes made to objects in the tablespace are written to the redo log. If event logging is not enabled, any direct loading using sql*loader and the direct load insert operation is not written to the redo log, so if data loss occurs, the objects will not be recoverable. If an object is created without event logging enabled, only those objects can be backed up for recovery. Choosing not to enable event logging can have a significant impact on the ability to recover objects later. Please use it carefully. Note: If the database is enabled for theForce LOGGINGmode, which takes precedence over table space event logging settings. You can put the database into force logging mode when you create the database, or you can use it after you create the databaseALTER DATABASE force loggingcommand to place the database in this mode. Block information: This area shows the block size used by the tablespace you created. The value shown here is a read-only value. If any other block-size initialization parameters (Db_nk_cache_size) are set, other values are listed here as options.
- Table spaces in a preconfigured database
? SYSTEM? Sysaux? TEMP? UNDOTBS1? USERS? EXAMPLE (optional)The following tablespaces are created in the preconfigured database:? The System:oracle server uses the system table space to manage the database. This table space contains data dictionaries and tables that contain information about the management of the database. All of the above information is included in the SYS scenario, and only the SYS user or other administrative user with the required permissions can access the information. Sysaux: This is the secondary table space for the system table space. Some of the components and products in earlier versions of Oracle DB that use the system tablespace or its own table space now use the Sysaux table space instead. Each Oracle database 10g (or later) databases must have Sysaux tablespace. In Enterprise Manager (EM), you can view a pie chart of the contents of this table space. To do this, click tablespaces (table Space) on the Administration page. Select Sysaux, and click Edit. Then click the occupants (possessive) tab. Once created, you can use EM to monitor the space usage of each possessor in the Sysaux table space. If you detect that a component occupies too much space in the Sysaux table space, or if you anticipate that the component will take up too much space, you can move the selected occupants to a different tablespace by selecting one of the occupants and then clicking Change tablespace (Alter Tablespace). Temp: If the SQL statement you are executing needs to create a temporary segment (such as a mass sort or create an index), theneed touse temporary table spaces. Just as you assign a default tablespace to each user to store the data objects that you create, each user is also assigned a temporary table space. It is a best practice to define a default temporary tablespace for the database and assign this tablespace to all new users (unless otherwise specified). In a preconfigured database, the temp table space is specified as the default temporary tablespace. This means that if you do not specify a temporary tablespace when creating a user account, Oracle DB allocates the tablespace to the user as a temporary tablespace. UNDOTBS1: This is the restore table space that the database server uses to store the restore information. If the database uses automatic restore management, the database can use only one restore tablespace at any given time. This tablespace was created when the database was created. Users: This table space is used to store user objects and data. If you do not specify a default tablespace when you create a user, the users table space becomes the default tablespace for all objects created by that user. For SYS and system users, the default permanent table space is system. EXAMPLE: This tablespace contains sample scenarios that you can install when you create a database. These sample scenarios provide a common platform for a variety of examples. The Oracle documentation and courseware include examples built on these sample scenarios. Note: For ease of administration, a separate tablespace is typically used for the index.
After you create a tablespace, you can change the table space in several ways, depending on the needs of your system changes. Rename: Enter a new name for the tablespace, and then click Apply. ALTERtablespace"USERS"RENAMEto"USER" Change state: There are three different states of a tablespace, which can be in one of these states. Because table space availability depends on the type of tablespace, the following three states are not necessarily available:? Read/write: The table space is online and can be read and written. Read Only: Specifies read-only to place the tablespace in read-only mode. In this state, existing transactions can be completed (committed or rolled back), but data manipulation language (DML) operations are not allowed to be further performed on objects in the tablespace. The tablespace is online, but is in a read-only state. The system or Sysaux table space cannot be in read-only mode.
ALTER tablespace"USERS"READ onlyNote: You cannot make the restore table space and the temporary tablespace a read-only table space. Offline: You can take the online table space offline so that this part of the database is temporarily unavailable for general use. The remainder of the database is open and allows users to access the data in it. When the tablespace is offline, the following options are available:-Normal (OK): ALTERtablespace"USERS"OFFLINENORMAL
If no error state exists in any of the data files in the tablespace, the table space can be taken offline in a normal way. When Oracle DB makes the table space offline, all data files for the tablespace areset up checkpointsTo ensure that all data is written to disk. -Temporary (temporary): ALTERtablespace"USERS"OFFLINEtemporary If one or more files in the tablespace have an error state, you can also temporarily take the tablespace offline. When Oracle DB makes data files (not yet offline) offline, checkpoints are set for those data files. If no files are taken offline, but a temporary clause is used, no media recovery is required when the tablespace is re-linked. However, if one or more of the tablespace's files are offline due to a write error, and the tablespace is set up temporarily offline, the tablespace needs to be restored before it can be brought back online. -Immediate (Immediate): ALTERtablespace"USERS"OFFLINEIMMEDIATE
Oracle DB enables the table space to be taken offline immediately, whileyou do not need to set checkpoints for any data files.。 If you specify Immediate (immediate), you must perform a media restore of the tablespace before you can bring the table space online. If the database is running in Noarchivelog mode, you cannot immediately take the table space offline. -For Recover (for recovery): ALTERtablespace"USERS"OFFLINEforRECOVER
The for recover setting has been deprecated. This syntax is supported for backwards compatibility. Note:You cannot take the system table space offline. Change size:you can increase the space of an existing table space by adding data files to the tablespace or by changing the size of existing data files. ? To add a new data file to the Tablespace, click Add, and then in the Add DataFile page, enter information about the data file. ALTERtablespace"USERS"ADDdatafile'/u01/app/oracle/oradata /TEST0924/USER02.DBF 'SIZE20MautoextendonNEXT1024K MAXSIZEUNLIMITED
ALTER tablespace "USERS" OFFLINE for RECOVER
Note: You cannot continue to add additional data files to the large file tablespace. ? To change the size of an existing data file, click the name of the data file, select the data file in the Datafiles (data file) area of the Edit Tablespace page, or select the data file, and then click Edit. Next, on the Edit DataFile page, you can change the size of the data file. Table spaces can be enlarged or reduced. However, you cannot make the data file less than the space used in the file, and if you attempt to do so, you receive the following error: Ora-03297:file contains used data beyond requested RESIZE value ALTERDATABASEdatafile'/u01/app/oracle/oradata/test0924/users01.dbf ' RESIZE 30M Storage options: Click Storage to change the event logging behavior of the tablespace. Threshold: Click Thresholds (Threshold) to change the value of the tablespace's space usage when the warning or severity level is reached. A total of three options:? Use database default thresholds (using the DB defaults): Use default values for presets, and provide options for setting these default values. Specify thresholds (Specify threshold): You can set the threshold for this particular tablespace. Disable Thresholds (Disable threshold): Closes the space use alert for this tablespace. Note: By default, the space usage is checked every 10 minutes, so it may take several minutes before a threshold alert is registered.
Using the Actions menu, you can perform various tasks on the tablespaces. Select a table space, and then select the action you want to perform. Add DataFile: Adds a data file to the table space, which increases the tablespace. Create like (create similar): Use a tablespace as a template, creating another table space. Generate DDL (Generate DDL): Generates a Data definition language (DDL) statement that is used to create a tablespace. This statement can then be copied and pasted into a text file for use as a script or document. Make locally Managed (for local administration): If the table space is currently a dictionary-managed tablespace, you can convert the tablespace to a locally managed tablespace.This conversion can only be done in one direction, and the tablespace cannot be converted to a dictionary-managed table space. If required, you can use the PL/SQL package dbms_space_admin. Tablespace_migrate_from_local to convert to a dictionary-managed tablespace. Make Readonly (read-only): Stops all write operations to the tablespace. Allows the current transaction to be completed, but does not allow new DML activity or other write activity to be started on the tablespace. This option is displayed only if the current state of the tablespace is not read-only. Make writable: Allows you to initiate DML activities and other write activities on objects in the tablespace. This option is displayed only if the current state of the tablespace is not writable. Place online: Bring the currently offline Tablespace online. Reorganize (Reorganization): Launches the Reorganization Wizard, which allows you to move objects around the table space to reclaim space and make them available. This task should be performed when the object in the tablespace is in a non-usage peak. Run Segment Advisor (running segment Guide): Starts the Segment Advisor (segment guidance), which allows you to determine whether an object has recyclable space based on the level of space fragmentation in the object. Suggestions are generated for each segment in the tablespace at the table space level. Show Dependencies (Display dependency): Displays the object that this tablespace depends on, or the object that depends on this tablespace. Show Tablespace Contents (show tablespace content): Displays information about all segments in the tablespace, including graphics for all extents. Take Offline (offline): Makes the currently online tablespace unavailable. The tablespace is not deleted at this time, but the tablespace is not available.
If you no longer need the tablespace and its contents (the segments contained in the table space), you can delete the tablespace and its contents from the database. You must have the drop tablespace system permission to delete a table space. When you delete a table space, it is deletedfile pointers in the associated database control file。 If you are using Oracle managed files (OMF), the underlying operating system files are also deleted. If you do not use OMF, you can instruct the Oracle server to delete the operating system files (data files) that are contained in the deleted table space, as needed. If you do not instruct the Oracle server to delete the data files while deleting the tablespace, you must use the appropriate command for the operating system when you want to delete the files later. Cannot delete the containingtable space for the active segment。 For example, if a table in a table space is currently in use, or if the tablespace contains the restore data that is required to roll back uncommitted transactions, you cannot delete the table space. The status of a tablespace can be online or offline, but it is best to take it offline before deleting the tablespace.
- viewing tablespace information
Click View to view information about the selected table space. On the View tablespace page, you can also change the tablespace by clicking Edit (Edit). You can also get table space and data file information by querying the following:? Table space information:-Dba_tablespaces-v$tablespace? Data file information:-dba_data_files-v$datafile Note: The V$dbfile view displays all data files in the database. This view is retained for backwards compatibility. It is recommended to use V$datafile.? Temporary file information:-Dba_temp_files-v$tempfile
On the Tablespaces home page or on a specific tablespace page, select Show Tablespace Contents (show Table space content) from the Actions drop-down list, and then click Go. The show tablespace Contents (show Table space content) page displays detailed information about the tablespace, including a list of the middle sections of the table space, the type of each segment, the segment size, and the number of extents in each segment. Using any of these four values, you can sort the list by clicking the column header or filter the list by entering a value in the search area. For a dictionary-managed tablespace, the following additional columns are displayed:? Max Extents (maximum number of zones)? Next (Next)? Percent increase (Increase percentage) to view a list of extents, click the link in the Extents column. To view the extents graphically, expand Extent Map, and then move the cursor over each area. The following information is displayed:? The name of the segment to which the zone belongs? Zone ID? Block ID? Area size (in blocks)? The data file used for the storage area
- Oracle-managed files (OMF)
Specify file operations by database object instead of file name. SQL> ALTER SYSTEM SET db_create_file_dest = ' +data '; Sql> CREATE tablespace tbs_1; oracle managed files (OMF) If you use Oracle-managed files, you do not need to directly manage the operating system files in Oracle DB. You can specify an action by database object instead of by file name. The database will use the standard file system interface internally to create or delete files for the following database structures, as needed:? Table space? Redo log files? Control file? Archive logs? Block change trace file? Flashback log? The RMAN backup database can contain either Oracle-managed files or files that are not Oracle-managed. The file system directory specified by either of these parameters must already exist, and the database will not create the directory. The directory must also have the appropriate permissions to allow the database to create files in it. example shows that after setting db_create_file_dest, you can omit the datafile clause in the CREATE tablespace statement. The data file is created at the location specified by Db_create_file_dest. When you create a tablespace as shown, the default values are assigned to all parameters. oracle managed files have a specific naming format. For example, use the following format in Linux and UNIX-based systems: <DESTINATION_PREFIX>/O1_MF_%T_%U_.DBF do not rename files that are managed by Oracle. The database identifies Oracle-managed files by name. Renaming a file causes the database to fail to recognize it as an Oracle-managed file, which prevents it from being managed correctly. The following example sets the default location for creating data files to/u01/oradata, and then creates a tablespace tbs_1 that stores data files at that location. SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/oradata '; sql> CREATE tablespace tbs_1; By default, Oracle-managed data files, including Oracle-managed data files in the system and Sysaux table spaces, are 100MB in size and can be automatically scaled. Note: ASM uses OMF files by default, but if you specify the ASM data file when you create a table space or when you add an ASM data file to an existing table spaceAlias, the ASM data file will not be in OMF format.
You can expand the database as follows:? Create a new tablespace? Add a data file to an existing small file table space? Increase the size of the data file? Dynamically extending data files expanding the database you can use enterprise Manager or SQL statements to perform these activities. The database size can be described as the sum of the sizes of all the table spaces in the database.
Source: http://blog.csdn.net/rlhua/article/details/12572003
