Oracle create tablespace more datafiles,boy toy boxer shorts review,kitchen furniture planner - Review

This chapter describes tablespaces, the primary logical database structures of any Oracle database, and the physical datafiles that correspond to each tablespace. Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running. A database's data is collectively stored in the datafiles that constitute each tablespace of the database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database.
Through initialization parameters, you specify the file system directory to be used for a particular type of file.
When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed.
With Oracle-managed files, bigfile tablespaces make datafiles completely transparent for users. The system default is to create a smallfile tablespace, which is the traditional type of Oracle Database tablespace. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.
You can create a group of temporary tablespaces that let a user consume temporary space from multiple tablespaces. Bigfile tablespaces simplify management of datafiles in ultra large databases by reducing the number of datafiles needed. Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization. Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity.
Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.
In a database with a locally managed SYSTEM tablespace, dictionary managed tablespaces cannot be created. If the SYSAUX tablespace is unavailable, such as due to a media failure, then some database features may fail. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. During normal database operation, Oracle Database does not allow the SYSAUX tablespace to be dropped or renamed.
When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage.
Specify default temporary tablespaces when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.
A very small database may need only the SYSTEM tablespace; however, Oracle recommends that you create at least one additional tablespace to store user data separate from data dictionary information.
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The LOCAL clause of the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement is specified to create locally managed permanent or temporary tablespaces, respectively. When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed.
This keyword tells Oracle Database that you want to use bitmaps to manage the free space within segments. Locally managed tablespaces using automatic segment-space management can be created as smallfile (traditional) or bigfile tablespaces.
This keyword tells Oracle Database that you want to use free lists for managing free space within segments. If you created your database with Oracle9i, you could be using dictionary managed tablespaces. If you do not specify extent management when you create a tablespace, then the default is locally managed. In the initialization parameter file or server parameter file, you can configure subcaches within the buffer cache for each of these block sizes. Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. A database administrator can bring any tablespace other than the SYSTEM tablespace online (accessible) or offline (not accessible) whenever the database is open. A tablespace is usually online so that the data contained within it is available to database users.
When a tablespace goes offline, Oracle Database does not permit any subsequent SQL statements to reference objects contained in that tablespace. When a tablespace goes offline or comes back online, this is recorded in the data dictionary in the SYSTEM tablespace.
You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. Oracle Database automatically switches a tablespace from online to offline when certain errors are encountered.
The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database.
Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving requirements.
You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory.
Create temporary tablespaces by using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement. A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms. The first time a tablespace's datafiles are opened under Oracle Database with the COMPATIBLE initialization parameter set to 10 or higher, each file identifies the platform to which it belongs. In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. Oracle Database creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header. When a datafile is first created, the allocated disk space is formatted but does not contain any user data. The data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tablespace.
You can alter the size of a datafile after its creation or you can specify that a datafile should dynamically grow as schema objects in the tablespace grow. You can take tablespaces offline or bring them online at any time, except for the SYSTEM tablespace. When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified.
This enables fast tempfile creation and resizing; however, the disk could run out of space later when the tempfiles are accessed. The database control file is a small binary file necessary for the database to start and operate successfully. A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. Therefore, if you make a change to the physical structure of your database (using ALTER DATABASE statements), then you should immediately make a backup of your control file. As with redo log files, Oracle Database enables multiple, identical control files to be open concurrently and written for the same database. If all control files of a database are permanently lost during operation, then the instance is aborted and media recovery is required.
Scripting on this page enhances content navigation, but does not change the content in any way. Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects. An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Before you can create a tablespace, you must create a database to contain it, and the database must be open. If you are running the database in automatic undo management mode, then at least one UNDO tablespace must be online. If you are running the database in manual undo management mode, then at least one rollback segment other than the SYSTEM rollback segment must be online. 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. If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. You can specify only one datafile in the DATAFILE clause or one tempfile in the TEMPFILE clause. You must specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for the SYSAUX tablespace. Restrictions on the SYSAUX Tablespace You cannot specify OFFLINE or TEMPORARY for the SYSAUX tablespace.
Specify the datafiles to make up the permanent tablespace or the tempfiles to make up the temporary tablespace. For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.
On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed.
Restriction on BLOCKSIZE You cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.
Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels. Restriction on Forced Logging You cannot specify FORCE LOGGING for an undo or temporary tablespace. This clause lets you specify default storage parameters for all objects created in the tablespace and default compression of data for all tables created in the tablespace. For a dictionary-managed tablespace, the only storage parameter you can specify with this clause is COMPRESS. ONLINE  Specify ONLINE to make the tablespace available immediately after creation to users who have been granted access to the tablespace.
OFFLINE  Specify OFFLINE to make the tablespace unavailable immediately after creation.
The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline. The extent_management_clause lets you specify how the extents of the tablespace will be managed. After you have specified extent management with this clause, you can change extent management only by migrating the tablespace. UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte.
Restriction on Dictionary-managed Tablespaces You cannot specify DICTIONARY if the SYSTEM tablespace of the database is locally managed or if you have specified the temporary_tablespace_clause.
If you do not specify the extent_management_clause, then Oracle Database interprets the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management.
If you do not specify the DEFAULT storage_clause, then the database creates a locally managed autoallocated tablespace. If you specified the MINIMUM EXTENT clause, then the database evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0.
If you did not specify MINIMUM EXTENT clause, then the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If you specify LOCAL, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or the temporary_tablespace_clause. AUTO  Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap.
MANUAL Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists. If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks. If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.
Use this clause in conjunction with the ALTER DATABASE FLASHBACK clause to specify whether the tablespace can participate in FLASHBACK DATABASE operations. The FLASHBACK mode of a tablespace is independent of the FLASHBACK mode of an individual table. Use this clause to create a locally managed temporary tablespace, which is an allocation of space in the database that can contain transient data that persists only for the duration of a session. The transient data can be user-generated schema objects such as temporary tables or system-generated data such as temp space used by hash joins and sort operations. Specify an empty string (' ') to indicate that tablespace is not a member of any tablespace group.
Restrictions on Temporary Tablespaces The data stored in temporary tablespaces persists only for the duration of a session. Oracle Database always assigns an undo tablespace when you start up the database in automatic undo management mode. RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments.
The only clauses you can specify for an undo tablespace are the DATAFILE clause and the extent_management_clause to specify local extent management. Assuming that the default database block size is 2K, and that each bit in the map represents one extent, then each bit maps 2,500 blocks. The following example sets the default location for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location.
This statement creates a locally managed tablespace in which every extent is 128K and each bit in the bit map describes 64 blocks. The Oracle create tablespace statement can be quite complex if you look at it in the Oracle documentation. We will be looking at all three types of Oracle tablespace later on in this article, but first let’s cover some basics.
Top Tip: If you are going to have a large tablespace in your database try to have fewer physical datafiles which make the tablespace because when Oracle does a checkpoint, among other operations, it has to update each datafile header. DATAFILE – This next section lists out all of the physical datafiles you will be creating.
Note: Autoextend can actually cause tablespace to grow by up to 10% of the total size of the tablespace!
I would like to go into a little more detail for the extent management, uniform size and segment space management clauses which I will do in the next sections.
Extents are created as the segment grows and can span multiple datafiles but all extents for the same segment must be in the same tablespace.
In a locally managed tablespace, free and used blocks are managed by something called a bitmap in the datafile header. Note: In general, the only time that a used extent will be given back to the tablespace for use by another segment will be if the original segment is dropped.
Uniform size means that each extent will be the same size, like when building a house each brick will be the same size so will fit together nicely with no wasted space. Auto allocated extents, on the other hand, will usually start small and an internal algorithm will determine how large to make the next extent, and the one after that, etc. This type of extent is like a house made of odd shaped bricks – there will be gaps and finding a space to fit an odd shaped brick will be difficult!
With automatic segment space management (the default for locally managed tablespace) free space in each segment is managed very much in the same way that the extent management is; using bitmaps, but for the segment this information is kept in the first block allocated to the segment at creation time.
When using manual segment space management the free and used blocks are managed using free lists, which require more management when inserts, updates and deletes are run against the segment. Note: When using automatic segment space management, only the PCTFREE storage parameter is valid when creating a segment. Traditionally, Oracle only had one type of tablespace, which is now what is called a smallfile tablespace. Note: Unless you have changed the database default all of your tablespaces will be created as smallfile ones, but you can explicitly create bigfile tablespaces.
Temporary tablespaces are very important for ensuring that sorts, like index creations and SQL queries, can complete if they run out of PGA to sort the results in.
I have written another article about UNDO, what it is, how to monitor it, etc so have a read of that first if you are not familiar with the basic concepts. If you used the DBCA to create your database it should have automatically created an UNDO tablespace for you, if you created an 11g database.
If you didn’t use the DBCA and ran the CREATE DATABASE statement manually, the UNDO tablespace would have been created automatically if the UNDO_MANAGEMENT = AUTO parameter was configured. I hope that the information I have provided in the article has been of use to you, and as always please leave a comment if you have anything to say. While Oracle physically stores table and index data in datafiles, it manages the relationship between tables, indexes, and datafiles using a logical concept called the tablespace.
Note: While a tablespace may contain many datafiles, a datafile can only be part of one tablespace. You can see that the tablespace stands between the object (table, index, etc) that you have created, and the physical location where that object is stored.
The benefit you get from this relationship is the flexibility to add datafiles as the objects in a tablespace grow. 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.
This section provides background information about the various database storage structures.
Figure 6-3 shows the configuration of a database that has three 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 archive your redo log, you copy the 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 together 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 USERS. Some components and products that used the SYSTEM tablespace or their own tablespaces in releases prior to Oracle Database 10g now use the SYSAUX 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.
Locally managed tablespaces keep the space allocation information within the tablespace, not in the data dictionary, thus offering better performance. 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 make up a temporary tablespace are called tempfiles, as opposed to datafiles. 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 datafile property, not a tablespace property. 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.
This section provides instructions about viewing information about the various database storage structures with 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 datafile when it needs more storage space. You can use Oracle Enterprise Manager Database Control (Database Control), to modify a tablespace. 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. In the Status section, select Offline, make a selection from the Offline Mode list, and then click Apply.
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 datafiles.


After a tablespace has been dropped (deleted), the objects and data in it are no longer available. Database Control asks for confirmation that you want to delete the tablespace and delete the associated datafiles from the disk. 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. 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.
A recommendation for a segment can be either a shrink operation or a reorganization operation.
Each Segment Advisor recommendation includes a button that you can click to start 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. If the recommendation for any segments is to shrink, proceed with Step 7 through Step 12 for those segments. To reclaim space in a single segment, click Shrink under the Recommendation column for that segment. 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. You can create this file in the same directory, 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 redo log group and starts writing to the next available redo log group.
Beginning with Oracle Database Release 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 undo any uncommitted changes made to the database in the event that a rollback operation is necessary. 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 DBCA, it creates an autoextending undo tablespace named UNDOTBS1, with a maximum extension size of 35 GB. 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, you may need 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. For an undo tablespace of a fixed size, or a tablespace with autoextending disabled, rather than tuning the undo retention period to be slightly longer than the longest-running active query, the database dynamically tunes the undo retention period for the best possible retention, based on system activity and the undo tablespace size.
Another reason to change the undo tablespace to a fixed size is to prevent the tablespace from growing too large.
If you decide 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 want 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. If you decide to change the undo tablespace to a fixed size, use the Undo Advisor to help determine the minimum required size. 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, 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, 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. For users to access your database, you the database administrator must create user accounts and grant appropriate database access privileges to those accounts. User accounts are usually created by a database administrator or a person specifically assigned to create accounts and administer security. The administrative accounts provided by Oracle Database should be used only by authorized individuals. Most of the included accounts are administrative accounts, but Sample Schema accounts are also present. The online catalog (oc) subschema is a collection of object-relational database objects built inside the oe schema.
Oracle Database Sample Schemas for a description of the Sample Schemas that are used for examples in Oracle Database documentation and educational materials. System privileges, object privileges, and roles provide a basic level of database security.
Roles are groupings of privileges that you can use to create different levels of database access. You can grant privileges and roles to other users only when you possess the necessary privilege. CONNECT is a role that Enterprise Manager automatically grants to a user when you create a user as shown in "Creating Users". When you create an Oracle database, the user SYS is automatically created and granted the DBA role.
Ensure that most database users are never able to connect to Oracle Database with the SYS account. When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM user can create additional tables and views that display administrative information as well as internal tables and views used by various Oracle Database options and tools. SYSDBA and SYSOPER are administrative privileges required to perform basic database operations such as creating the database and instance startup and shutdown. The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. In the Available System Privileges list, double-click the privileges listed in Table 7-3 to add them to the Selected System Privileges list. A user profile establishes the password management policy for a user and sets limits the user's access to certain database resources.
In this section, you create a user named MYUSER, set the password, and assign MYUSER to the USERS tablespace.
Note that this profile does not allow the user to log in to Enterprise Manager, which would require the user to be assigned the DBA role.
For the Default Tablespace field, click the flashlight icon and select the USERS tablespace.
For the Temporary Tablespace field, click the flashlight icon and select the TEMP tablespace. If you later want to create other users similar to user MYUSER, or another existing user, Oracle provides a shortcut. The Actions list also provides shortcuts for other actions, as well as providing a means to display the SQL DDL used to create a user.
You can change the user attributes by navigating to the Users page (Figure 7-2) and clicking Edit. When you create a new user, the user is not able to save data until given a tablespace quota.
This user has 0 MB quota on all tablespaces, which means that MYUSER has no quota in any tablespace. From the Users page shown in Figure 7-2, select a user whose Account Status is shown as EXPIRED AND LOCKED.
You must follow the preceding steps individually for each account that you want to unlock and reset. To better understand the ramifications of unlocking and resetting accounts, see the other property pages available on the Edit user page.
In the Available Roles list, select the DBA and click Move to add it to the Selected Roles list. In the Selected Roles list, select the DBA role and click Remove to make it part of the Available Roles list.
You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
Oracle Database then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.
The size of a database is the collective size of the tablespaces that constitute the database.
You accomplish this by altering existing files or by adding files with dynamic extension properties. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. This allows Oracle Database to contain tablespaces made up of single large files rather than numerous smaller ones. In other words, you can perform operations on tablespaces, rather than the underlying datafile.
There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed. Tablespaces of different types are indistinguishable in terms of execution of SQL statements that do not explicitly refer to datafiles.
A tablespace group can also be specified as the default temporary tablespace for the database.
Smallfile tablespaces can contain up to 1024 files, but bigfile tablespaces contain only one file that can be 1024 times larger than a smallfile tablespace. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
Refer to your operating system specific documentation for information about maximum supported file sizes.
However, increasing the datafile size might increase time to restore a corrupted file or create a new datafile. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable. If the database contains many of these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace.
Many database components use the SYSAUX tablespace as their default location to store data.
It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.
You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap.
Earlier versions of Oracle Database may not include an undo tablespace and may instead use rollback segments.
However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases. A bigfile temporary tablespace, like all temporary tablespaces, uses tempfiles instead of datafiles.
This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles. Such recursive operations can occur in dictionary managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or rollback segment. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. For a tablespace that uses the data dictionary to manage its extents, Oracle Database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse.
The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle Database. However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes.
Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. If a tablespace is offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened. For example, Oracle Database switches a tablespace from online to offline when the database writer process, DBWn, fails in several attempts to write to a datafile of the tablespace. Oracle Database never updates the files of a read-only tablespace, and therefore the files can reside on read-only media such as CD-ROMs or WORM drives. Also, if you must recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user.
One sort segment exists for every instance that performs a sort operation in a given tablespace. The sort segment of a given temporary tablespace is created at the time of the first sort operation. You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases.
When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases.
These files have identical on disk formats for file header blocks, which are used for file identification and verification. When a datafile is created, the operating system under which Oracle Database runs is responsible for clearing old information and authorizations from a file before allocating it to Oracle Database. However, Oracle Database reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle Database. Note that a schema object does not correspond to a specific datafile; rather, a datafile is a repository for the data of any schema object within a specific tablespace. This functionality enables you to have fewer datafiles for each tablespace and can simplify administration of datafiles.
All of the datafiles of a tablespace are taken offline or brought online as a unit when you take the tablespace offline or bring it online, respectively. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
A control file is updated continuously by Oracle Database during database use, so it must be available for writing whenever the database is open. Control file information can be modified only by Oracle Database; no database administrator or user can edit a control file. The database name is taken from either the name specified by the DB_NAME initialization parameter or the name used in the CREATE DATABASE statement.
Every three seconds, the checkpoint process (CKPT) records information in the control file about the checkpoint position in the redo log. By storing multiple control files for a single database on different disks, you can safeguard against a single point of failure with respect to control files.
Media recovery is not straightforward if an older backup of a control file must be used because a current copy is not available.
Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add datafiles or tempfiles to it, or make it a read-only tablespace. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.
You must use the CREATE TABLESPACE statement to create the SYSAUX tablespace if you are upgrading from a release prior to Oracle Database 11g. 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. For Automatic Storage Management disk group files, the parameter must be set to a multiple file creation form of Automatic Storage Management filenames. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. 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. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects.
Please refer to Oracle Database Administrator's Guide for information on when to use this setting.
You must create an unencrypted tablespace and re-create the database objects in the unencrypted tablespace. The only clause of encryption_spec that is relevant for tablespace encryption is the USING clause.
All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. If they are equal, then the database creates a locally managed uniform tablespace with extent size = INITIAL. If you need a locally managed tablespace to store temporary objects, for example, if you will assign it as a user's temporary tablespace, then use the temporary_tablespace_clause. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps.
If you specify AUTO, then the database ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace.
Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management. This clause is useful if you have the database in FLASHBACK mode but you do not want Oracle Database to maintain Flashback log data for this tablespace. Oracle Database will save Flashback log data for this tablespace and the tablespace can participate in a FLASHBACK DATABASE operation. When a temporary tablespace, or a tablespace group of which this tablespace is a member, is assigned to a particular user, then Oracle Database uses the tablespace for sorting operations in transactions initiated by that user. Therefore, only a subset of the CREATE TABLESPACE clauses are relevant for temporary tablespaces.
When you run the database in automatic undo management mode, Oracle Database manages undo space using the undo tablespace instead of rollback segments. If no undo tablespace has been assigned to this instance, then the database uses the SYSTEM rollback segment. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions.
Oracle Database determines the size of each extent and the total number of extents allocated to satisfy the initial segment size. In this article I would like to try and break down the create tablespace command in to simpler terminology. There are a lot of options you have to consider when creating an Oracle tablespace, but let’s just use an example of how I would normally create one and then look at the statements in a little more detail. This is like a map of all blocks in the datafile, conceptually, it has a value of 1 if the block is used and a 0 if it is empty. The datafile bitmap will be scanned to see if there is a large enough chunk of disk space to create the new extent. Just deleting all rows in a segment will not release the extent back for use by another segment. They will most likely be larger each time if the segment is growing rapidly and smaller if it is not. It is also a single point of contention, so automatic management is generally accepted as the more efficient method to use. This is what percentage of free space should be left in each block for subsequent updates to existing rows. In an ideal world all the sorting would be done in memory because it’s much more efficient but the TEMP tablespace acts as an overspill area.
The UNDO tablespace is so fundamental to the smooth operation of the database that it should definitely not be overlooked. Physical structures are those that 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 flash 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. As long as 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 datafile to be restored from backup, then the recent data changes that are missing from the restored datafile can be obtained from the redo log, 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 datafiles of that tablespace. When you define the datafiles that make up a tablespace, you specify a storage location for these files.


Using SYSAUX reduces the load on the SYSTEM tablespace and reduces maintenance because there are fewer tablespaces to monitor and maintain. Similar to the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users. Oracle recommends creating locally managed tablespaces rather than dictionary-managed tablespaces. By default, Oracle Database sets all newly created tablespaces to be locally managed with automatic segment management, a feature that further improves 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. If you want to switch the undo tablespace used by the database instance, then you can create a new one and instruct the database to use it instead. 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. It is unlikely that you will create an Offline tablespace, but later you might change its status to Offline to perform maintenance on its datafiles. If you do not enable autoextend, you are alerted when the tablespace reaches its critical or warning threshold size. That is, when you create the datafiles that make up a tablespace, you indicate whether or not these datafiles automatically extend. These privileges enable a DBA to start up and shut down the database and perform other high-level administrative tasks. You can also click the name of a tablespace in the Tablespace column to display the attributes of the tablespace associated with the datafile.
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. For example, you can extend it (by increasing datafile sizes or adding another datafile), 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. 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 believe that particular segments may have reclaimable unused space, yet those segments do not appear among the Segment Advisor recommendations, 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 you created the tablespace for a segment as locally managed with automatic segment space management, which is the default, the Segment Advisor recommends shrinking, if the segment is not one of the few segment types that are not eligible for shrinking.
Although space reclamation is an online process, it can consume significant resources, so it is recommended that you reclaim space during off-peak hours.
This page lists the tablespace segments for which online segment shrink or reorganization is recommended. 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.
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. It is not required that redo log groups be symmetrical, but Oracle recommends that your groups all have the same number of members. You can force a log switch to make the current redo group inactive and available for redo log maintenance operations.
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 operation can be the result of a user who wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.
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. This best possible retention period for an undo tablespace of fixed size can be much longer than the longest-running active query, and can thus better 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.
After you create a user account, you must grant basic privileges to the account to enable the user to connect to the database and to view and create database objects, known as schema objects. To protect these accounts from unauthorized access, these accounts are initially locked with their passwords expired. Plan stability enables you to maintain the same execution plans for the same SQL statements.
They are designed to control user access to data and to limit the kinds of SQL statements that users can execute. For example, you can create a role for application developers that enable users to create tables and programs. It includes all system privileges and should only be granted to the most trusted and qualified of users. Depending upon the level of authorization you require, you must have one of these privileges granted to you.
For example, if you have the SYSDBA privilege, then you can connect to the database by specifying CONNECT AS SYSDBA. You can then grant the role to other roles or users depending on the level of data access required by the user. In this exercise, you add the basic system privileges shown in Table 7-3, which allow the creation of various objects, to the APPDEV role that you created previously.
When you create the user in "Creating Users", you assign the Oracle-supplied default profile.
You can read about the Database Resource manager in online Help and view its pages when you click the links in the Resource Manager section of the Database Administration page. You can create specific users to enable access to the database based on roles and privileges. If the account status to set to expired, then the user or the database administrator must change the password before the user can log in to the database.
While users can specify different defaults for each user, it is easiest to define a default permanent tablespace and a default temporary tablespace at the database level, instead of the user level. Locking an account is preferable to deleting a user, which removes all associated tables and data.
For example, you can specify user roles, privileges, quotas, consumer groups, and proxy users. To grant the user system administrator privileges, complete additional steps described in Chapter 3, "Getting Started with Oracle Enterprise Manager".
Because MYUSER belongs to the USERS tablespace, he must have quota to create tables and other schema objects in this tablespace. Specifically, click the Roles, System Privileges, and Object Privileges links to see the privileges of the user whose account you are enabling. For example, you can grant a user the DBA role, which allows the user to administer the database. You must exercise caution when dropping users, however, because this action drops all schema objects owned by the user including tables and indexes.To deny user access to the database, it is better to lock the user account or expire the user password. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles). This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files.
Bigfile tablespaces make the tablespace the main unit of the disk space administration, backup and recovery, and so on. This is useful with bigfile tablespaces, where you could need a lot of temporary tablespace for sorts.
The total tablespace capacity is the same for smallfile tablespaces and bigfile tablespaces.
Therefore, the SYSAUX tablespace is always created during database creation or database upgrade. Undo tablespaces are used only when the database is in automatic undo management mode (the default).
At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free. When an extent is allocated or freed for reuse, Oracle Database changes the bitmap values to show the new status of the blocks.
As more or less space becomes available in a data block, its new state is reflected in the bitmap.
Oracle Database also stores rollback information about each update of the dictionary tables.
Oracle Database saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance. Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs. As the data grows in a tablespace, Oracle Database uses the free space in the associated datafiles to allocate extents for the segment. Oracle Database allocates space for the data associated with a schema object in one or more datafiles of a tablespace. If for some reason the control file is not accessible, then the database cannot function properly. This information is used during database recovery to tell Oracle Database that all redo entries recorded before this point in the redo log group are not necessary for database recovery; they were already written to the datafiles. If a single disk that contained a control file crashes, then the current instance fails when Oracle Database attempts to access the damaged control file. You must have the SYSDBA system privilege to specify this clause, and you must have opened the database in MIGRATE mode. If this parameter is set, then the database creates a system-named 100 MB file in the default file destination specified in the parameter. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. 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. This clause lets you control free space fragmentation in the tablespace by ensuring that the size of 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. In this case, NOLOGGING is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in FORCE LOGGING mode.
You must also specify the ENCRYPT keyword as part of the DEFAULT storage_clause in this statement in order for the tablespace to be encrypted. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, then the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace. You must take the datafiles in this tablespace offline or drop them prior to any subsequent FLASHBACK DATABASE operation.
A tablespace group lets you assign multiple temporary tablespaces to a single user and increases the addressability of temporary tablespaces. If the tablespace group already exists, then Oracle Database adds the new tablespace to that group.
The only clauses you can specify for a temporary tablespace are the TEMPFILE clause, the tablespace_group_clause, and the extent_management_clause.
This clause is useful if you are now running in automatic undo management mode but your database was not created in automatic undo management mode. You can avoid this by creating an undo tablespace, which the database will implicitly assign to the instance if no other undo tablespace is currently assigned. I’d recommend something like using the database name, the user who will have objects in it and the extent size. This is one of the advantages of using locally managed tablespaces; the block management is done efficiently, and all blocks which are next to one another and free can be used.
When it finds which blocks to use for the new extent, the bitmap is updated to indicate that the blocks are now used and the segment will have some free space to use. With the increasing size of modern day databases, Oracle decided to add a bigfile tablespace to the mix.
Have a read about it in my article on monitoring TEMP space as it will give you some useful queries and tips. 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. 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 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 datafiles that are associated only with temporary tablespaces.
When the existing extents of a segment are full, the database allocates another extent for that segment. For example, you might specify a datafile location for a certain tablespace as a designated host directory (implying a certain disk volume) or designated 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. Dictionary-managed tablespaces is an older space management technique and not as efficient as locally managed tablespaces. 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 datafiles is considered to be an autoextending tablespace.
Other initialization parameters require the database to be restarted for the changes to take effect.
This password file is outside of the database itself, thereby enabling the authentication of a DBA when the database is not yet started. The Advanced and Record Section subpages give you more detailed information about your control files.
The 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, 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 need to extend a tablespace (if the alert is related to space available) or take it offline to recover it (if the alert is related to corrupted data or other serious errors). In many cases, 100 GB remaining would not be a critical situation, and the alert would not be useful. The sparsely populated objects that result can suffer performance degradation 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 already 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 a tablespace contains any segments for which a shrink operation or reorganization is recommended, the tablespace appears on the Segment Advisor Recommendations page. For example, you might want to drop the current redo group, but are not able to do so until the group is inactive.
However, if your installation uses Oracle Flashback operations, you may need 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. You choose the analysis period for this subpage by selecting from the Analysis Time Period list on the General subpage. As the database administrator, you are responsible for the unlocking and resetting of these accounts as described in "Unlocking Accounts and Resetting Passwords". At database creation, the administrative user SYS is created and granted all system privileges and predefined Oracle roles.
To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. The structure and functionality of the Roles page is similar to that of the Users page shown in Figure 7-2. This default profile is liberal in its resource specifications and does not provide tight restrictions on password usage. The structure and functionality of the Profiles page is similar to that of the Users page shown in Figure 7-2. For example, you might create a specific user MYUSER with the necessary privileges (which you can assign to roles) to develop applications. Consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. This action does not reset the password, so the user is still unable to log in to the database. You can also grant user-created roles, such as the APPDEV role you created in "Creating Roles". Bigfile tablespaces also simplify datafile management with Oracle-managed files and Automatic Storage Management by eliminating the need for adding new datafiles and dealing with multiple files.
However, because there is limit of 64K datafiles for each database, a database can contain 1024 times more bigfile tablespaces than smallfile tablespaces, so bigfile tablespaces increase the total database capacity by 3 orders of magnitude. A database can contain more than one undo tablespace, but only one can be in use at any time.
When upgrading to Oracle Database 11g you can migrate to automatic undo management by creating an undo tablespace and enabling automatic undo management mode.
Bitmaps enable Oracle Database to manage free space more automatically; thus, this form of space management is called automatic segment-space management.
Because dictionary tables and rollback segments are part of the database, the space that they occupy is subject to the same space management operations as all other data. When the tablespace is brought back online, Oracle Database applies the rollback data to the tablespace, if needed. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces. The first tablespace in any database is always the SYSTEM tablespace, so Oracle Database automatically allocates the first datafiles of any database for the SYSTEM tablespace during database creation.
However, when other copies of the current control file are available on different disks, an instance can be restarted without the need for database recovery. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. If you subsequently take the tablespace out of FORCE LOGGING mode, then the NOLOGGING default is once again enforced. If the tablespace group does not exist, then the database creates the group and adds the new tablespace to that group. This is ideal for allowing the database objects to grow without having to always manually add space. Each extent can vary in size or can be of uniform size, depending on how you have created the tablespace.
The DBA must understand the relationship between the physical and logical structures of a database. To protect against a failure involving the redo log itself, Oracle Database can multiplex the redo log so that two or more identical copies of the online redo log can be maintained on different disks.
Oracle also recommends that you configure the database to write archived redo log files to the flash 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. 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. Online segment shrink is permitted only on segments in a locally managed tablespace with automatic segment space management. 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.
If the segment is not eligible for online segment shrink, or if its tablespace is dictionary-managed or was created with manual segment space management, the Segment Advisor recommends reorganization. You may also want to force a log switch if the current redo group needs to be archived at a specific time before the members of the group are completely filled. User SYS can then grant privileges and roles to other users and also grant those users the right to grant specific privileges to others.
In other words, 8 exabytes is the maximum size of the Oracle database when bigfile tablespaces are used with the maximum block size (32 k).
Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database. Unless table striping is used (where data is spread across more than one disk), the database administrator and end users cannot control which datafile stores a schema object. SET ENCRYPTION WALLET clause to load information from the server wallet into memory for database access.
This will mean that each datafile will be listed on disk in order for any datafile from 001 to 999.
For databases created with Oracle Database Configuration Assistant (DBCA), three copies of the control file are automatically created and kept synchronized with each other. 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. Online segment shrink is an in-place operation; no additional free space in the tablespace is required. If you request recommendation details for a tablespace, Database Control displays the segments in that tablespace for which recommendations were generated. This option is useful in configurations with large redo log files that take a long time to fill. Also, you should not create any tables in the schema of user SYS, although you can change the storage parameters of the data dictionary settings if necessary. 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 datafiles or by adding another datafile to the tablespace.



Make your own outdoor furniture nz
Diy cabinets plans free australia




Comments