Create tablespace oracle 11g xe,country projects lesson plans ontario,how to make a door knob not lock,diy kitchen cabinet doors mdf - PDF Review

This chapter discusses using Oracle Enterprise Manager Database Control (Database Control) to view and manage the storage structures of your database. Logical structures are created and recognized by Oracle Database and are not known to the operating system. To view a database storage structure, go to the Storage section of the Server subpage and click on any of the links to access the storage pages. This section provides background information about the various database storage structures. Figure 6-2 shows the configuration of a database that has three online redo log groups and two members in each group.
The database log writer process (LGWR) writes redo records from the memory buffer to a redo log group until the log files in that group reach their storage size limit, or until you request a log switch operation. When you enable archiving of the online redo logs, Oracle Database copies the online redo log files to another location before they are overwritten.
These archived redo log files extend the amount of redo data that can be saved and are used for recovery.
Rollback segments were database structures used to track undo information for the database in earlier releases of Oracle Database. After the database has been created, it is not possible to change the default block size without re-creating the database. A database is divided into logical storage units called tablespaces, which group related logical structures (such as tables, views, and other database objects).
When you create an Oracle database, some tablespaces already exist, such as SYSTEM and SYSAUX. The SYSAUX tablespace contains data for some components and products, reducing the load on the SYSTEM tablespace.
Components that use SYSAUX as their default tablespace during installation include Automatic Workload Repository, Oracle Streams, Oracle Text, and Database Control Repository. Space management within a tablespace involves keeping track of available (free) and used space, so that space is allocated efficiently during data insertion and deletion. A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace.
Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sort operations. The physical files that comprise a temporary tablespace are called tempfiles, as opposed to data files.
The TEMP tablespace is typically used as the default temporary tablespace for users who are not explicitly assigned a temporary tablespace. If the tablespace is created Read Only, then the tablespace cannot be written to until its status is changed to Read Write. You can set a tablespace to automatically extend itself by a specified amount when it reaches its size limit. Although it is common to refer to tablespaces as autoextending, automatic extension is a data file property, not a tablespace property.
Encrypted tablespaces primarily protect your data from unauthorized access by means other than through the database.
Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files. Initialization parameters are used by the Oracle instance at startup to determine the run-time properties and resources for the database. A database can use a password file to authenticate administrative users with SYSDBA or SYSOPER connection privileges. When you invoke DBCA as part of the Oracle Database installation process, DBCA creates a password file with one entry: the SYS user. Oracle Database can also use operating system authentication to authenticate users with the SYSDBA or SYSOPER privileges. Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data. To assist you in managing the storage structures within your database, this section provides instructions for viewing information about the various database storage structures using Oracle Enterprise Manager Database Control (Database Control). You use Database Control to view status and multiplexing information about online redo log files. You use Database Control to view configuration, size, and status information about tablespaces. You can create additional tablespaces to store user data, so that not all data is stored in the USERS tablespace. For certain users, groups of users, or applications, it may be convenient to keep all application data in a separate tablespace or set of tablespaces for backup and recovery or maintenance reasons.
Some applications, such as those with large partitioned tables, may benefit from distributing data across multiple tablespaces. To create a tablespace that is similar to an existing tablespace, in the Select column, select the tablespace whose attributes you want to reproduce. This is the amount of disk space that is added to the data file when it needs more storage space. This section explains how to set a tablespace to automatically extend when it reaches its size limit. You receive an alert in Database Control when a space usage threshold for a tablespace is reached.
When space used becomes greater than or equal to a percentage of total space, an alert is issued. For both warning and critical alerts for a tablespace, you can enable either the space used threshold or the free-space threshold, or you can enable both thresholds. Select Specify Thresholds, and then enter a Warning (%) threshold and a Critical (%) threshold.
Select Specify Thresholds, and then enter a Warning (MB) threshold and a Critical (MB) threshold. To bring the tablespace back online, return to this page, and then, under the Status section, click Read Write. Oracle Database Administrator's Guide for more information about taking tablespaces offline and for information about renaming or relocating data files. After a tablespace has been dropped (deleted), the objects and data in it are no longer available. Database Control asks for confirmation to delete the tablespace and gives you the option to also delete the associated data files from the disk. A confirmation is displayed and the deleted tablespace no longer appears on the Tablespaces page.
This section provides background information about reclaimable unused space in the database, and provides instructions about how to reclaim this space.
Over time, performing insert, update, and delete operations (also referred to as DML operations) on objects within a tablespace can create pockets of empty space that individually are not big enough to be reused.
Online segment shrink operates on table segments and on the segments of the dependent objects of the table, such as indexes and partitions. Online segment shrink is permitted only on segments in a locally managed tablespace with automatic segment space management. Reorganization relocates the table and its dependent objects in a different part of the tablespace.
The Segment Advisor generates recommendations for shrinking or reorganizing segments that have a significant amount of reclaimable unused space. The Segment Advisor identifies database objects that have unused space that you can reclaim. During each run of the Automatic Segment Advisor, only a subset of the segments in the database are analyzed. If a tablespace contains any segments for which a shrink operation or reorganization is recommended, then the tablespace appears on the Segment Advisor Recommendations page. The recommendations provided by the Segment Adviser depend on the characteristics of the tablespace in which the segment is located.
In addition, the Automatic Segment Advisor evaluates tables that are 10MB or larger and have at least three indexes to determine the amount of space that could be saved if the tables are compressed with the OLTP compression method.
Each Segment Advisor recommendation includes a button that you can click to begin the space reclamation process. In the Space Summary section, click the numeric link adjacent to Segment Advisor Recommendations. The Segment Advisor Recommendations page appears, showing recommendations from the most recent automatic and manual runs of the Segment Advisor.
This page lists the tablespace segments for which online segment shrink or reorganization is recommended.
If the recommendation for any segments is to reorganize, then start reorganization for those segments by clicking the Reorganize button, or select multiple segments and then click the Reorganize button above the table. If the recommendation for any segments is to shrink, then proceed with Step 7 through Step 13 for those segments. To reclaim space in a single segment, click Shrink under the Recommendation column for that segment. To shrink one or more segments, select the segments, and then click the Shrink button above the table. Choose Compact Segments if you believe that you may have long-running queries currently in operation that involve the segments being shrunk.
Because the shrink operation can be resource intensive, you can also select Later and schedule the operation for an off-peak period.
See Oracle Database Administrator's Guide for information about running the Segment Advisor manually. If the Storage Type is set to File System, then in the File Name field, enter a file name for the new redo log member. In the File Directory field, enter the directory where you want the data file to be stored on disk. You can create this file in the same directory as the other member of the redo log file group, but it is recommended that you store members on separate disk drives.
At the top of the page, click the Redo Log Groups link to return to the Redo Log Groups page.
When a log switch occurs, the log writer (LGWR) process stops writing to the current online redo log group and starts writing to the next available redo log group. You can force a log switch to make the current redo group inactive and available for redo log maintenance operations.
Beginning with Oracle Database 11g, for a default installation, Oracle Database automatically manages the undo data. When a transaction modifies data, Oracle Database copies the original data before modifying it. To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data.
To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time. When you create the database using Database Configuration Assistant (DBCA), it creates an autoextending undo tablespace named UNDOTBS1, with a maximum extension size of 32,767 MB. Oracle Database automatically ensures that undo data that is in use by an active transaction is never overwritten until that transaction has been committed. Even after a transaction has been committed, it is useful to retain (not overwrite) its undo data, to ensure the success of Oracle Flashback features and for read consistency for long-running queries. Although by default Oracle Database manages undo data and the undo tablespace automatically, if your installation uses Oracle Flashback features, then you may have to perform some undo management tasks to ensure the success of these operations.
Oracle Flashback operations resulting in snapshot too old errors indicate that you must intervene to ensure that sufficient undo data is retained to support these operations. Set the minimum undo retention period for the autoextending tablespace to be as long as the longest expected Oracle Flashback operation.
For an autoextending undo tablespace, Oracle Database always automatically tunes the undo retention period to be slightly longer than the longest-running active query. To change the undo tablespace to a fixed size, you must choose a tablespace size that is sufficiently large. DML could fail because there is not enough space to accommodate undo data for new transactions. Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.
Oracle Enterprise Manager Database Control (Database Control) includes an Undo Advisor to help you determine the minimum size for the fixed size of the undo tablespace. If you must change the undo tablespace to a fixed size, then use the Undo Advisor to help determine the minimum required size. To configure the undo tablespace to have a fixed size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Decide whether you want to compute the minimum size of the undo tablespace based on statistics gathered over a designated time period (such as the last 7 days), or based on an undo retention period of a duration that you choose. The automatically gathered statistics include the duration of the longest-running query and the undo generation rate.
If you prefer to choose and enter the duration of a desired undo retention period, then the duration must be based on your expectations of the duration of future long-running queries or Oracle Flashback operations.
In the Analysis Period section, in the Analysis Time Period list, select the desired analysis time period. If you select Customize Time Period, then a page appears that enables you to enter the starting and ending date and time for the period. In the Analysis Period section, select Specified manually to allow for longer duration queries or flashback. In the Duration field, enter the desired duration of the undo retention period in seconds, minutes, hours, or days.
You can base your determination on the longest-running query recorded during a previous workload period. The Duration field changes to match the selected undo retention period, and the Minimum Required Undo Tablespace Size field above the graph changes to reflect the matching required size. Running an analysis or setting the minimum undo retention with the Undo Advisor does not change the size of the undo tablespace. You change the undo tablespace to a fixed size to prevent the tablespace from growing too large or to better support Oracle Flashback operations. On the Automatic Undo Management page, after determining the minimum required undo tablespace size, click Edit Undo Tablespace. To create transportable tablespace sets from RMAN backups, instead of the live datafiles of the source database. The limitations on creating transportable tablespace sets described in Oracle Database Administrator's Guide apply to transporting tablespaces from backup, with the exception of the requirement to make the tablespaces read-only. You must have a backup of all needed tablespaces (including those in the auxiliary set) and archived redo log files available for use by RMAN that can be recovered to the target point in time for the TRANSPORT TABLESPACE operation.
If RMAN is not part of the backup strategy for your database, you can still use RMAN TRANSPORT TABLESPACE, as long as the needed datafile copies and archived redo logs are available on disk.
You also have the option of using RMAN to back up your database specifically to create backups for use in creating a transportable tablespace set from backup. Because the RMAN process for creating transportable tablespaces from backup uses the Data Pump Export and Import utilities, you cannot use this process if the tablespaces to be transported use XMLTypes. Because RMAN creates the automatic auxiliary instance used for restore and recovery on the same node as the source instance, there is some performance overhead during the operation of the TRANSPORT TABLESPACE command. If you drop a tablespace, then you cannot later use TRANSPORT TABLESPACE to include that tablespace in a transportable tablespace set, even if the SCN for TRANSPORT TABLESPACE is earlier than the SCN at which the table was dropped.
If you renname a tablespace, you cannot use TRANSPORT TABLESPACE to create a transportable tablespace set as of a point in time before the tablespace was renamed.
You cannot TRANSPORT tables without their associated constraints, or constraints without their associated tables.
If not using a recovery catalog and transporting tablespaces as of a point in time in the past, then the set of tablespaces with undo segments at the time TRANSPORT TABLESPACE is executed must be the same as the set of tablespaces with undo segments at the time selected for transport. Tablespaces including undo segments as of the target SCN for TRASNPORT TABLESPACE must be part of the auxiliary set.
If the database has re-used the control file records for the RMAN repository that contained information about backups required for the TRANSPORT TABLESPACE process, then the process fails because RMAN cannot locate the required backups.
Optional argument that specifies a database directory object where Data Pump Export outputs are created.
Optional argument that specifies the filename for the sample input script generated by RMAN for use in plugging in the transported tablespace at the destination database. Argument that specifies the directory where the datafiles for the transported tablespaces are left at the end of the tablespace transport operation. Scripting on this page enhances content navigation, but does not change the content in any way. Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its data files or temp files. You cannot use this statement to convert a dictionary-managed tablespace to a locally managed tablespace. If you have the ALTER TABLESPACE system privilege, then you can perform any ALTER TABLESPACE operation.
The tablespace must not be involved in an open backup, because the end of a backup updates the header file of all data files in the tablespace. Performing this function in restricted mode may help you meet these restrictions, because only users with RESTRICTED SESSION system privilege can be logged on.
If you specify the DEFAULT clause, then you must specify at least one of the clauses table_compression, inmemory_clause, ilm_clause, or storage_clause. You cannot make the SYSTEM tablespace read only or temporary and you cannot take it offline. For locally managed temporary tablespaces, the only clause you can specify in this statement is the ADD clause. The clauses table_compression, inmemory_clause, ilm_clause, and storage_clause have the same semantics in CREATE TABLESPACE and ALTER TABLESPACE. Restriction on MINIMUM EXTENT You cannot specify this clause for a locally managed tablespace or for a dictionary-managed temporary tablespace.
For each data file in the tablespace, this clause combines all contiguous free extents into larger contiguous extents. If tablespace is read only, then Oracle Database does not update the data file headers to reflect the new name. If you re-create the control file, and if the data files that Oracle Database uses for this purpose are restored backups whose headers reflect the old tablespace name, then the re-created control file will also reflect the old tablespace name. If tablespace has been designated as the undo tablespace for any instance in an Oracle Real Application Clusters (Oracle RAC) environment, and if a server parameter file was used to start up the database, then Oracle Database changes the value of the UNDO_TABLESPACE parameter for that instance in the server parameter file (SPFILE) to reflect the new tablespace name. The RENAME clause does not change the value of the UNDO_TABLESPACE parameter in the running instance.
Restriction on Renaming Tablespaces You cannot rename the SYSTEM or SYSAUX tablespaces. Use these clauses to move all data files in a tablespace into or out of online (sometimes called hot) backup mode.
Specify BEGIN BACKUP to indicate that an open backup is to be performed on the data files that make up this tablespace. You cannot specify this clause for a read-only tablespace or for a temporary locally managed tablespace. While the backup is in progress, you cannot take the tablespace offline normally, shut down the instance, or begin another backup of the tablespace. Restriction on Ending Tablespace Backup You cannot use this clause on a read-only tablespace. Specify ADD to add to the tablespace a data file or temp file specified by file_specification. For locally managed temporary tablespaces, this is the only clause you can specify at any time.


If you omit file_specification, then Oracle Database creates an Oracle Managed File of 100M with AUTOEXTEND enabled. You can add a data file or temp file to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline.
Restriction on Adding Data Files and Temp Files You cannot specify this clause for a bigfile (single-file) tablespace, as such a tablespace has only one data file or temp file.
On some operating systems, Oracle does not allocate space for a temp file until the temp file blocks are actually accessed. Specify DROP to drop from the tablespace an empty data file or temp file specified by filename or file_number. Cannot be in a read-only tablespace that was migrated from dictionary managed to locally managed. Use these clauses to take all data files or temp files in the tablespace offline or put them online.
Specify LOGGING if you want logging of all tables, indexes, and partitions within the tablespace. When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). If the tablespace is in FORCE LOGGING mode, then you can specify NOLOGGING in this statement to set the default logging mode of the tablespace to NOLOGGING, but this will not take the tablespace out of FORCE LOGGING mode. Use this clause to put the tablespace in force logging mode or take it out of force logging mode.
Restriction on Force Logging Mode You cannot specify FORCE LOGGING for an undo or a temporary tablespace.
Specify an empty string (' ') to remove tablespace from the tablespace_group_name tablespace group.
Restriction on Tablespace Groups You cannot specify a tablespace group for a permanent tablespace or for a dictionary-managed temporary tablespace. Before taking a tablespace offline for a long time, consider changing the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace.
Restriction on Taking Tablespaces Offline You cannot take a temporary tablespace offline.
OFFLINE NORMAL Specify NORMAL to flush all blocks in all data files in the tablespace out of the system global area (SGA). OFFLINE TEMPORARY If you specify TEMPORARY, then Oracle Database performs a checkpoint for all online data files in the tablespace but does not ensure that all files can be written.
OFFLINE IMMEDIATE If you specify IMMEDIATE, then Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. Specify READ WRITE to indicate that write operations are allowed on a previously read-only tablespace.
Specify PERMANENT to indicate that the tablespace is to be converted from a temporary to a permanent tablespace. Specify TEMPORARY to indicate that the tablespace is to be converted from a permanent to a temporary tablespace. If tablespace was not created with a standard block size, then you cannot change it from permanent to temporary. Use this clause to specify whether this tablespace should participate in any subsequent FLASHBACK DATABASE operation.
For you to turn FLASHBACK mode off, the database must be mounted, either open READ WRITE or closed. Altering a tablespace logging attribute has no affect on the logging attributes of the existing schema objects within the tablespace. This statement prepares a database for initial use and erases any data currently in the specified files.
In this release of Oracle Database and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts. Use the CREATE DATABASE statement to create a database, making it available for general use.
This statement erases all data in any specified datafiles that already exist in order to prepare them for initial database use.
After creating the database, this statement mounts it in either exclusive or parallel mode, depending on the value of the CLUSTER_DATABASE initialization parameter and opens it, making it available for normal use. You cannot use special characters from European or Asian character sets in a database name. If you omit the database name from a CREATE DATABASE statement, then Oracle Database uses the name specified by the initialization parameter DB_NAME. If you do not specify these clauses, then Oracle Database creates default passwords change_on_install for user SYS and manager for user SYSTEM.
Specify CONTROLFILE REUSE to reuse existing control files identified by the initialization parameter CONTROL_FILES, overwriting any information they currently contain. You cannot use this clause if you also specify a parameter value that requires that the control file be larger than the existing files. If you omit this clause and any of the files specified by CONTROL_FILES already exist, then the database returns an error.
Specify the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. The number of datafiles accessible to your instance is also limited by the initialization parameter DB_FILES.
Specify the maximum number of instances that can simultaneously have this database mounted and open. Restriction on CHARACTER SET You cannot specify the AL16UTF16 character set as the database character set.
Specify the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.
Use the database_logging_clauses to determine how Oracle Database will handle redo log files for this database. The redo_log_file_spec clause specifies a redo log file group containing one or more redo log file members (copies). In all these cases, the parameter settings must correctly specify operating system filenames or creation form Automatic Storage Management filenames, as appropriate.
If no values are set for any of these parameters, then the database creates a log file in the default location for the operating system on which the database is running. Specify the maximum number of redo log file groups that can ever be created for the database.
This parameter is useful only if you are using Oracle Database in ARCHIVELOG mode with Oracle Real Application Clusters (RAC). Specify ARCHIVELOG if you want the contents of a redo log file group to be archived before the group can be reused.
Specify NOARCHIVELOG if the contents of a redo log file group need not be archived before the group can be reused. Use the tablespace clauses to configure the SYSTEM and SYSAUX tablespaces and to specify a default temporary tablespace and an undo tablespace. When you create a locally managed SYSTEM tablespace, you cannot change it to be dictionary managed, nor can you create any other dictionary-managed tablespaces in this database.
If you specify this clause, then the database must have a default temporary tablespace, because a locally managed SYSTEM tablespace cannot store temporary segments. If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, then you can omit the default_temp_tablespace clause.
If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the default_temp_tablespace clause and explicitly specify a datafile for that tablespace. If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, then you can omit the undo_tablespace clause. If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the undo_tablespace clause and explicitly specify a datafile for that tablespace. Use this clause to determine the default type of subsequently created tablespaces and of the SYSTEM and SYSAUX tablespaces.
A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks.
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
You must specify this clause if you have specified one or more datafiles for the SYSTEM tablespace using the DATAFILE clause.
If you have enabled Oracle-managed files and you omit the SYSAUX clause, then the database creates the SYSAUX tablespace as an online, permanent, locally managed tablespace with one datafile of 100 MB, with logging enabled and automatic segment-space management.
The syntax for specifying datafiles for the SYSAUX tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.
The DATAFILE clause and extent_management_clause have the same semantics they have in a CREATE TABLESPACE statement. Specify BIGFILE or SMALLFILE to determine whether the default temporary tablespace is a bigfile or smallfile tablespace. The syntax for specifying tempfiles for the default temporary tablespace is the same as that for specifying tempfiles during temporary tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.
On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. The extent_management_clause clause has the same semantics in CREATE DATABASE and CREATE TABLESPACE statements. If you have opened the instance in automatic undo mode (the UNDO_MANAGEMENT initialization parameter is set to AUTO, which is the default), then you can specify the undo_tablespace to create a tablespace to be used for undo data. The syntax for specifying datafiles for the undo tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device. If you specify this clause, then Oracle Database creates an undo tablespace named tablespace, creates the specified datafile(s) as part of the undo tablespace, and assigns this tablespace as the undo tablespace of the instance. If you have specified a value for the UNDO_TABLESPACE initialization parameter in your initialization parameter file before mounting the database, then you must specify the same name in this clause. If you omit this clause, then Oracle Database creates a default database with a default smallfile undo tablespace named SYS_UNDOTBS and assigns this default tablespace as the undo tablespace of the instance.
The syntax for specifying datafiles for the SYSTEM tablespace is the same as that for specifying datafiles during tablespace creation using the CREATE TABLESPACE statement, whether you are storing files using Automatic Storage Management or in a file system or raw device.
If you are running the database in automatic undo mode and you specify a datafile name for the SYSTEM tablespace, then Oracle Database expects to generate datafiles for all tablespaces. By specifying a displacement from UTC (Coordinated Universal Time—formerly Greenwich Mean Time).
Oracle Database normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. Physical structures can be seen and operated on from the operating system, such as the physical files that store data on a disk. This figure also shows recovery-related structures that are optionally kept in the fast recovery area.
Oracle Enterprise Manager Database Control (Database Control) provides a Web-based graphical user interface (GUI) to enable easier management and monitoring of your database. It is the root file that the database uses to find all the other files used by the database.
If you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. If a failure requires a data file to be restored from backup, then the recent data changes that are missing from the restored data file can be obtained from the online redo log files, so work is never lost.
Archived redo log files are required to recover a backup of the database from the time of the backup to the current time. It is created automatically when the database is created, and is always brought online at instance startup. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. For example, a table is stored in a table segment, and an index is stored in an index segment.
It is possible, however, to create a tablespace with a block size different than the default block size.
For example, all application objects can be grouped into a single tablespace to simplify maintenance operations. Database objects assigned to a tablespace are stored in the physical data files of that tablespace.
When you define the data files that comprise a tablespace, you specify a storage location for these files. Every database using Oracle Database 10g release 1 (10.1) or later must have a SYSAUX tablespace. Similar to the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users.
Locally managed tablespaces keep the space allocation information within the tablespace, not in the data dictionary, thus offering better performance. Oracle Database uses undo data to roll back transactions, to provide read consistency, to help with database recovery, and to enable features such as Oracle Flashback Query. It is unlikely that you would create a Read Only tablespace, but you might change it to that status after you have written data to it that you do not want modified. You might change the status of a tablespace to Offline before performing maintenance or recovery on the data files associated with that tablespace. If you do not enable autoextend, then you are alerted when the tablespace reaches its critical or warning threshold size. That is, when you create the data files that comprise a tablespace, you indicate whether these data files automatically extend. For example, when encrypted tablespaces are written to backup media for travel from one Oracle database to another or for travel to an off-site facility for storage, they remain encrypted.
The Advanced and Record Section subpages give you more detailed information about your control files. For example, suppose you must recover all application data from backup due to a hardware or software failure, and you want to perform an offline recovery. This approach allows the optimal use of the available storage because frequently accessed data can be placed on high performance disks, and infrequently retrieved data can be placed on less expensive storage.
Bigfile tablespaces are used with very large databases that use Automatic Storage Management or other logical volume managers that support striping, RAID, and dynamically extensible logical volumes. In the File Directory and File Size fields, enter appropriate values for your data file location and initial size. For example, you can extend it by increasing data file sizes or adding another data file, set it to automatically extend, change its space usage alert thresholds, or change its status to Offline.
The following instructions assume that the tablespace was previously not an autoextending tablespace.
There are two types of space usage alerts that you can enable: warning, for when tablespace space is somewhat low, and critical, for when the tablespace is almost completely full and action must be taken immediately. For example, for a 10 TB tablespace, setting the percentage full critical alert to as high as 99 percent means that the database would issue an alert when there is still 100 GB of free space remaining.
Online segment shrink is an in-place operation; no additional free space in the tablespace is required. It performs its analysis by examining usage and growth statistics and by sampling the data in the object.
Results from the advisor are summarized on the Space Summary section of the Database Home page as Segment Advisor Recommendations. If you suspect that segments have reclaimable space, but these segments do not appear among the Segment Advisor recommendations, then the Automatic Segment Advisor may not have chosen them for analysis. Only tablespace names appear on the Segment Advisor Recommendations page in Database Control. If the tablespace was created with default options, then the Segment Advisor typically recommends shrinking. Although space reclamation is an online process, it can consume significant resources, so you should reclaim space during off-peak hours. Follow the directions in the succeeding pages to generate a reorganization script for the selected segments. If you do not want to release the freed space to the tablespace, then choose Compact Segments. Long-running queries that were started before the shrink operation completes might attempt to read from blocks that have been reclaimed, which results in an error. You do so when you want to analyze objects not selected for analysis by the Automatic Segment Advisor, or when you want more up-to-date recommendations on a tablespace.
Every transaction in the database updates the redo logs, regardless of whether archiving is enabled. It is not required that online redo log groups be symmetrical, but Oracle recommends that your groups all have the same number of members. After a log switch, the current online redo log group becomes inactive, and the next available online redo log group becomes the current online redo log group.
Forcing a log switch is useful in configurations with large redo log files that take a long time to fill. The status of the group that had been Current changes to Active, and the status of the next group in the list changes from Inactive to Current. A rollback can be needed because a user wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.
With read consistency, a user session does not see uncommitted changes made in other user sessions (sometimes referred to as dirty reads). After the transaction has been committed, the space occupied by that undo data can be reused, or overwritten. However, this autotuned retention period may be insufficient to accommodate Oracle Flashback operations. Then, you can use the Undo Advisor to determine the best size to configure the undo tablespace to allow for future long-running queries and Oracle Flashback operations.
Computing the minimum undo tablespace size based on these statistics is advisable if you do not use Oracle Flashback features or if you do not expect future long-running queries to exceed the duration of previous long-running queries. This information is available from the System Activity subpage of the Automatic Undo Management page.
You must follow the instructions in "Changing the Undo Tablespace to a Fixed Size" to change the size of the undo tablespace. Also, to create transportable tablespace sets that are recovered to a point in time in the past instead of the present time. If the target platform has a different endian format, then you must use the RMAN CONVERT command to perform the separate step of converting the endian format of the datafiles in the transportable set.
Use the RMAN CATALOG command to record the datafile copies and archived logs in the RMAN repository. In such a case, you must use the process documented in Oracle Database Administrator's Guide.
The RMAN repository in the control file only contains a record of tablespaces that include undo segments at the current time.
You may be able to use CATALOG to add the needed backups to the RMAN repository if they are still available, but if the database is already overwriting control file records you may lose records of other needed backups.
If not specified, files will be created in the location specified by TABLESPACE DESTINATION. If not specified, the export dump file is named dmpfile.dmp and stored in the location specified by the DATAPUMP DIRECTORY clause or in the tablespace destination. If omitted, the export log is named explog.log and stored in the location specified by the DATAPUMP DIRECTORY clause or in the tablespace destination. If specified, RMAN restores and recovers the tablespaces at the auxiliary instance to their contents at that past point in time before export. For this reason, the SYSTEM tablespace can never be made read only, because it contains the SYSTEM rollback segment. For complete information on these clauses, refer to DEFAULT Clause in the documentation on CREATE TABLESPACE.


The MINIMUM EXTENT clause lets you control free space fragmentation in the tablespace by ensuring that every used or free extent in a tablespace is at least as large as, and is a multiple of, the value specified in the size_clause.
However, after the database is fully recovered, the control file will reflect the new name. If a single-instance database is using a parameter file (pfile) instead of an spfile, then the database puts a message in the alert log advising the database administrator to change the value manually in the pfile. If the UNDO_TABLESPACE parameter is set to the new tablespace name in the pfile or spfile, then the parameter will be set correctly when the instance is next restarted. Use the datafile_tempfile_spec form of file_specification (see file_specification) to list regular data files and temp files in an operating system file system or to list Oracle Automatic Storage Management disk group files.
This delay in space allocation results in faster creation and resizing of temp files, but it requires that sufficient disk space is available when the temp files are later used.
This clause causes the data file or temp file to be removed from the data dictionary and deleted from the operating system. If tablespace is SYSTEM, or an undo tablespace, or the default temporary tablespace, then the database must not be open. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.
Use this clause to add tablespace to or remove it from the tablespace_group_name tablespace group. If tablespace_group_name does not already exist, then Oracle Database implicitly creates it when you alter tablespace to be a member of it. While the tablespace is offline, such users cannot allocate space for objects or sort areas in the tablespace. Files that are offline when you issue this statement may require media recovery before you bring the tablespace back online. In this state, existing transactions can complete (commit or roll back), but no further DML operations are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace. Use this clause to enable or disable autoextension of the single data file in the tablespace. The KEEP clause is omitted, so the database will attempt to shrink the tablespace as much as possible as long as other tablespace storage attributes are satisfied. An initialization parameter file with the name of the database to be created must be available, and you must be in STARTUP NOMOUNT mode.
If you specify the database name as a quoted identifier, then the quotation marks are silently ignored. The DB_NAME initialization parameter must be set in the database initialization parameter file, and if you specify a different name from the value of that parameter, then the database returns an error.
Normally you use this clause only when you are re-creating a database, rather than creating one for the first time.
These parameters are MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle Database control file to expand automatically so that the datafiles section can accommodate more files. The supported character sets and default value of this parameter depend on your operating system.
Use the redo_log_file_spec form of file_specification to create regular redo log files in an operating system file system or to create Automatic Storage Management disk group redo log files. All redo log files specified in a CREATE DATABASE statement are added to redo log thread number 1. Oracle Database uses this value to determine how much space to allocate in the control file for the names of redo log files. Specify the maximum number of archived redo log files for automatic media recovery of Oracle RAC.
After creating the database, you can change between ARCHIVELOG mode and NOARCHIVELOG mode with the ALTER DATABASE statement.
Oracle Database will log all changes in the database except for changes in temporary tablespaces and temporary segments.
If you shut down and restart the database, then the database is still in FORCE LOGGING mode. Please refer to Oracle Database Administrator's Guide for information on when to use this setting. Oracle Database will create a default temporary tablespace called TEMP with one datafile of size 10M with autoextend disabled. Specify either BIGFILE or SMALLFILE to set the default type of subsequently created tablespaces as a bigfile or smallfile tablespace, respectively.
Use this clause if you are not using Oracle-managed files and you want to specify one or more datafiles for the SYSAUX tablespace. If you are using Oracle-managed files and you omit this clause, then the database creates the SYSAUX datafiles in the default location set up for Oracle-managed files. Oracle Database creates a smallfile tablespace and subsequently will assign to this tablespace any non-SYSTEM users for whom you do not specify a different permanent tablespace.
Oracle Database will assign to this temporary tablespace any users for whom you do not specify a different temporary tablespace. If you have not specified a value for this parameter, then the TEMPFILE clause is required.
This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. A bigfile tablespace contains only one datafile, which can be up to 8 exabytes (8 million terabytes) in size. A smallfile tablespace is a traditional Oracle Database tablespace, which can contain up to approximately 4 million (222) blocks. If you have not specified a value for this parameter, then the DATAFILE clause is required.
If these names differ, then Oracle Database will return an error when you open the database. This undo tablespace allocates disk space from the default files used by the CREATE DATABASE statement, and it has an initial extent of 10M. Therefore, to avoid ambiguity, if your intention is to specify a datafile for the SYSTEM tablespace with this clause, then do not specify it immediately after an undo_tablespace clause that does not include the optional DATAFILE clause. To see a listing of valid region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.
Doing so can improve performance, especially across databases, as no conversion of time zones will be required.
If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server. Therefore no file specification is needed for the DEFAULT TEMPORARY TABLESPACE and UNDO TABLESPACE clauses.
The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure. Because of the importance of the control file, Oracle recommends that the control file be multiplexed, or have multiple identical copies.
Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database using the remaining control files. The online redo log files are used to recover a database after hardware, software, or media failure. The LGWR process performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records. Archiving can be either enabled or disabled for the database, but Oracle strongly recommends that you enable archiving.
Tempfiles are a special class of data files that are associated only with temporary tablespaces.
When the existing extents of a segment are full, the database allocates another extent for that segment.
The operating system recognizes only files and operating system blocks, not the number of data blocks in an Oracle Database file. For example, you might specify a data file location for a certain tablespace as a designated host directory (implying a certain disk volume) or designated Oracle Automatic Storage Management disk group. Oracle documentation and educational materials contain examples based on the sample schemas. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for a particular database. Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. Otherwise, user objects will be created in the SYSTEM tablespace, which is not good practice. By default, Oracle Database sets all newly created tablespaces to be locally managed with automatic segment management, a feature that further improves performance. You would create another temporary tablespace if you were creating a temporary tablespace group.
The critical and warning threshold parameters have default values that you can change at any time. A tablespace that has autoextending data files is considered to be an autoextending tablespace. Also, encrypted tablespaces protect data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.
All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.Tablespace encryption is completely transparent to your applications, so no application modification is necessary. Other initialization parameters require the database to be restarted for the changes to take effect. These privileges enable a DBA to start and shut down the database and perform other high-level administrative tasks.
The online redo log group with status Current is the one currently being written to disk by the log writer. If the application data is kept in a separate tablespace, then you can take just that tablespace offline and recover it, without affecting the operation of other database applications.
When you get a critical or warning alert, you might have to extend a tablespace if the alert is related to a space limitation or you might have to take the tablespace offline for recovery if the alert is related to corrupted data or other serious errors. Usually, 100 GB remaining would not be a critical situation, and the alert would not be useful. The sparsely populated objects that result can degrade performance during queries and DML operations. Free space can either be returned to the tablespace or kept in the segment for future insert operations. For this operation to succeed, however, the tablespace must have free space equal to the size of the table and its dependent objects.
By default, it is configured to run automatically at regular intervals, during all maintenance windows (time periods). If you request recommendation details for a tablespace, then Database Control displays the segments in that tablespace for which recommendations were generated. If the segment is not eligible for online segment shrinking, or if it is in a dictionary-managed tablespace or a tablespace created with manual segment space management, then the Segment Advisor recommends reorganization instead. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.
However, if your installation uses Oracle Flashback operations, then you may have to perform some undo management tasks to ensure the success of these operations. In this case, that undo data could be overwritten if space in the undo tablespace becomes scarce.
Committed undo data whose age is less than the undo retention period is retained for use by queries or Oracle Flashback operations. If the undo tablespace has autoextending disabled, or has a fixed size, then Oracle Database uses a different method for tuning the undo retention period to better accommodate Oracle Flashback operations.
You choose the analysis period for this subpage by selecting from the Analysis Time Period list on the General subpage. If the set of tablespaces with undo segments was different at the target SCN, then TRANSPORT TABLESPACE fails. See Oracle Database Backup and Recovery Advanced User's Guide for details on the interactions among the different methods of naming the auxiliary instance files.
See Oracle Database Utilities for more details on Data Pump Export and database directory objects.
Additionally, because the rollback segments of a read-only tablespace are not accessible, Oracle recommends that you drop the rollback segments before you make a tablespace read only.
Use K, M, G, or T to specify the size in kilobytes, megabytes, gigabytes, or terabytes, respectively. In the optional KEEP clause, the size_clause defines the lower bound that a tablespace can be shrunk to. Otherwise, if an instance failure or SHUTDOWN ABORT occurs, then Oracle Database assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance startup.
To avoid potential problems, before you create or resize a temp file, ensure that the available disk space exceeds the size of the new temp file or the increased size of a resized temp file.
In the optional KEEP clause, the size_clause defines the lower bound that the temp file can be shrunk to. Each filename must fully specify a data file using the conventions for filenames on your operating system. To enable or disable autoextension of a newly added data file or temp file in smallfile tablespaces, use the autoextend_clause of the database_file_clauses in the ALTER DATABASE statement.
Refer to "Schema Object Naming Guidelines" for additional rules to which database names should adhere. When you create a database for the first time, Oracle Database creates a control file in the default destination, which is dependent on the value or several initialization parameters. When using a form of ASM_filename, you cannot specify the autoextend_clause of redo_log_file_spec. The database uses this value to determine how much space to allocate in the control file for the names of archived redo log files. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects. However, if you re-create the control file, then Oracle Database will take the database out of FORCE LOGGING mode unless you specify FORCE LOGGING in the CREATE CONTROLFILE statement.
If you do not specify this clause, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.
If you do not specify this clause, and if the database does not create a default temporary tablespace automatically in the process of creating a locally managed SYSTEM tablespace, then the SYSTEM tablespace is the default temporary tablespace. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. However, if you want undo space management to be handled by way of rollback segments, then you must omit this clause.
The DATAFILE clause of this clause has the same behavior as described in "DATAFILE Clause". Use the datafile_tempfile_spec form of file_specification to create regular datafiles and tempfiles in an operating system file system or to create Automatic Storage Management disk group files.
If you do so, then Oracle Database will interpret the DATAFILE clause to be part of the undo_tablespace clause. However, if you are not using Oracle-managed files and you specify this clause, then you must also specify the undo_tablespace clause and the default_temp_tablespace clause. If the operating system time zone is not a valid Oracle Database time zone, then the database time zone defaults to UTC.
The database administrator (DBA) must understand the relationship between the physical and logical structures of a database.
For databases created with Oracle Database Configuration Assistant (DBCA), three copies of the control file are automatically created and kept synchronized with each other.
To protect against a failure involving the online redo log file itself, Oracle Database can multiplex the online redo log file so that two or more identical copies of the online redo log file can be maintained on different disks. Oracle also recommends that you configure the database to write archived redo log files to the fast recovery area.
Any schema objects assigned to that tablespace then get located in the specified storage location.
It also contains various tables and views that contain administrative information about the database.
In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. In the preconfigured database, USERS is designated as the default tablespace for all new users.
These parameters also cause alerts to be generated for autoextending tablespaces that are approaching their specified size limit.
This password file is outside of the database itself, thereby enabling the authentication of a DBA when the database is not yet started. For this tablespace, it might be better to use a free-space threshold, which you could set to issue a critical alert when 5 GB of free space remains.
Reorganization is the only permitted operation for dictionary-managed tablespaces or for locally managed tablespaces with manual segment space management. See Oracle Database Administrator's Guide for information about how the Automatic Segment Advisor selects tablespaces and segments for analysis.
The excess space should allow for anticipated increases in disk space use by unrelated operations as well. However, Oracle recommends that you use the transportable tablespaces feature for tablespace recovery. You can also omit this clause if you have set a value for the UNDO_TABLESPACE initialization parameter. If Oracle Database is unable to create the undo tablespace, then the entire CREATE DATABASE operation fails. These are all contained in the SYS schema, and can be accessed only by the SYS user or other administrative users with the required privilege. If no temporary tablespace is specified when a user account is created, then Oracle Database assigns this tablespace to the user. If you have an extremely large database, then you might configure additional temporary tablespaces. If you omit the KEEP clause, then the database will attempt to shrink the tablespace as much as possible as long as other tablespace storage attributes are satisfied. If you omit the KEEP clause, then the database will attempt to shrink the temp file as much as possible as long as other storage attributes are satisfied. If you subsequently issue an ALTER DATABASE statement that explicitly specifies a database name, then Oracle Database verifies that name with the name in the control file. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. If that parameter has been set, and if you specify this clause, then tablespace must be the same as that parameter value. The backup and recovery features of Oracle Database enable you to back up or recover at the tablespace level. You do so by increasing the size of one or more of the tablespace data files or by adding another data file to the tablespace. You can examine the GROUP value for a redo log file group through the dynamic performance view V$LOG.



Free wood bunk bed plans
Build your own cabin kits arizona




Comments