ITPub博客

A database administrator creates undo tablespaces individually, using the CREATE UNDO TABLESPACE statement. It can also be created when the database is created, using the CREATE DATABASE statement. A set of files is assigned to each newly created undo tablespace. Like regular tablespaces, attributes of undo tablespaces can be modified with the ALTER TABLESPACE statement and dropped with the DROP TABLESPACE statement.An undo tablespace cannot be dropped if it is being used by any instance or cont

The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.

Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.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

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.database->tablespaces->segments->extents->blocks(contiguous)[@more@]

The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.You can enlarge a database in three ways:1 Add a datafile to a tablespace2 Add a new tablespace3 Increase the size of a datafileWhen you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. Figure 3–2 illustrates this kind of space i

Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures: 1 Tablespaces 2 Redo log files 3 Control files Through initialization parameters, you specify the file system directory to be used for a particular type of

oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.datafiles objects(physical structures associated with only one tablespace)objects (stored in tablespaces-may span several datafiles)databases, tablespaces, and datafiles are closely related, but they have important differences:1 an oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database??s data. 2 each tabl

在查看SQL语句执行计划时 ， 在Sqlplus下使用set autot traceonlyset longsize 800set autot traceonlyselect">test@ora10g>select * from t_test where tonecode like '501%';已选择5103行。执行计划--------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost(%CPU)|----------------------------------------------------------------------------------| 0

Purpose Use the RENAME statement to rename a table, view, sequence, or private synonym. a. Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object. b. Oracle Database invalidates all objects that depend on the renamed object, such asviews, synonyms, and stored procedures and functions that refer to a renamed table.Prerequisites The object must be in your own schema.Syntax ren

show">sys@ora10g>show parameter optimizer_modeNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_mode string all_rows optimizer_mode establishes the default behavior for choosing an optimizationapproach for the instance.Values:■ first_rows_nThe optimizer uses a cost-based approach and optimizes with a goal of bestresponse time to return the first n rows (where n =

10g默认没有创建role : plustrace , 非sys用户在使用set autot语句时出错set">test@ora10g>set autot traceonlySP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色SP2-0611: 启用 STATISTICS 报告时出错解决:%ohome%sqlplusadmin>sqlplus / as sysdba @plustrcegrant plustrace to test;test用户重连后即可正常使用。[@more@]

Specifies the type of database optimizer statistics to generate when theexported data is imported. Options are ESTIMATE,COMPUTE, and NONE.In some cases, Export will place the precalculated statistics in the export fileas well as the ANALYZE statements to regenerate the statistics.However, the precalculated optimizer statistics will not be used at export timeif a table has columns with system-generated names.The precalculated optimizer statistics are flagged as questionable at exporttime if:■ Th

Monitor transaction and undo information with V$TRANSACTION and V$ROLLSTAT. For automatic undo management, the information in V$ROLLSTAT reflects the behaviors of the automatic undo management undo segments.The V$UNDOSTAT view displays a histogram of statistical data to show how well the system is working. You can see statistics such as undo consumption rate, transaction concurrency, and lengths of queries run in the instance. Using this view, you can better estimate the amount of undo space req

The TABLESPACES parameter specifies that all tables in the tablespace beexported to the Export dump file. This includes all tables contained in the listof tablespaces and all tables that have a partition located in the list oftablespaces. Indexes are exported with their tables, regardless of where theindex is stored. You must have the EXP_FULL_DATABASE role to use TABLESPACES toexport all tables in the tablespace.When TABLESPACES is used in conjunction withTRANSPORT_TABLESPACE=y,you can specify

Oracle Database 10g automatically tunes a parameter called the undo retentionperiod.The undo retention period indicates the amount of time that must pass before oldundo information—that is, undo information for committed transactions—can beoverwritten. The database collects usage statistics and tunes the undo retentionperiod based on these statistics and on undo tablespace size. Provided that automaticundo management is enabled, the database automatically tunes the undo retentionperiod as foll

In automatic undo management mode, the system controls exclusively theassignment of transactions to undo segments, and controls space allocation for undo segments. An ill-behaved transaction can potentially consume much of the undo space, thus paralyzing the entire system. The Resource Manager directive UNDO_POOL is a more explicit way to control large transactions. This lets database administrators groupusers into consumer groups, with each group assigned a maximum undo space limit. When the to

Undo mode provides a more flexible way to migrate from manual undo management to automatic undo management. A database system can run in either manual undo management mode or automatic undo management mode. In manual undo management mode, undo space is managed through rollback segments. In automatic undo management mode, undo space is managed in undo tablespaces. To use automatic undo management mode, the database administrator needs only to create an undo tablespace for each instance and set th

Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following:■ Rollback an active transaction■ Recover a terminated transaction■ Provide read consistency■ Recovery from logical corruptionsAutomatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being o

Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments. Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table. Oracle drops segments for a transaction-speci

Oracle allocates temporary segments as needed during a user session in one of the temporary tablespaces of the user issuing the statement. Specify these tablespaces with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause. If no temporary tablespace is defined for the user, then the default temporary tablespace is the SYSTEM tablespace. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment. Oracle drops