ITPub博客

The following statements sometimes require the use of a temporary segment: ■ CREATE INDEX ■ SELECT ... ORDER BY ■ SELECT DISTINCT ... ■ SELECT ... GROUP BY ■ SELECT . . . UNION ■ SELECT ... INTERSECT ■ SELECT ... MINUS Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments. 1. 以上7种情况可能用到临时段2. Oracle一条SQL最多

When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes. 1. 临时段经常用做SQL解析和执行2. 典型的情况是临时段用做

今天邂逅ORA-12638错误 , 身份证明检索失败.C:>oerr ora 12638ORA-12638: Credential retrieval failedCause: The authentication service failed to retrieve the credentials of a user.Action: Enable tracing to determine the exact error.credential外交使节所递的国书, 信任状不明白 , 百度一把 , 原因是由于Oracle不能应用OS认证而导致凭证检索失败,处理方法:修改%oracle_home%/network/admin下的sqlnet.ora，将SQLNET.AUTHENTICATION_SERVICES=(NTS)屏蔽即ok但是为什么会有这个错误 , 屏蔽了这个参数对后面有什么影响还得继续

Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data. Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it

PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.[@more@]

This parameter allows you to select a subset of rows from a set of tables when doinga table mode export. The value of the query parameter is a string that contains aWHERE clause for a SQL SELECT statement that will be applied to all tables (or tablepartitions) listed in the TABLE parameter.If a table is missing the columns specified in the QUERY clause, an error messagewill be produced, and no rows will be exported for the offending table.Restrictionsn The parameter QUERY cannot be specified for

A single data segment in an Oracle database holds all of the data for one of the following:■ A table that is not partitioned or clustered■ A partition of a partitioned table■ A cluster of tablesOracle creates this data segment when you create the table or cluster with the CREATE statement.1. 三者的并集是全集啊2. 当使用create语句创建表时 , Oracle创建数据段[@more@]

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table’s data segment, and for each index, Oracle allocates one or more extents to form its index segment.1. 包含某指定的逻辑存储结构的所有数据3., 构成, (使

When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment in a temporary tablespace of the user issuing the statement and then returns the extents to the tablespaces.Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are

Oracle periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is, it has too many extents), then Oracle automatically deallocates one or more extents from the rollback segment.Oracle定期检查会管段是否增长超过它们的optimal size , 假如超过 , Oracle从回滚段中自动回收一个或者多个extents.[@more@]

All extents allocated to an index segment remain allocated as long as the index exists. When you drop the index or associated table or cluster, Oracle reclaims the extents for other uses within the tablespace. 只要索引存在 , 索引段还保留着 . 删除了索引对应的table or cluster后 , 索引段可供表空间内其他用户使用.[@more@]

Oracle deallocates the extents of materialized views and materialized view logs in the same manner as for tables and clusters.回收extents的方式和table , cluster相同[@more@]

Clustered tables store information in the data segment created for the cluster. Therefore, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are deallocated. You can also truncate clusters (except for hash clusters) to free extents. 在cluster中删除一张表, 数据段还是保留给其他在该cluster中的表 , 除了hash cluster , 可以用truncate clusters来使extents free. [@more@]

As long as a nonclustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table. Oracle inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Oracle does not reclaim the data blocks for use by other objects in the tablespace. After you drop a nonclustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents of the table’s data and ind

The Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object.In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement). Exceptions to this include the following: ■ The owner of a table or cluster, or a user with the DELETE ANY privilege, can trunc

Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed. With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile’s bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.[@more@]

A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation. ■ For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage t

specifies whether or not the export utility uses the set transaction read onlystatement to ensure that the data exported is consistent to a single point in time anddoes not change during the export. if object_consistent is set to y, each objectis exported in its own read-only transaction, even if it is partitioned. in contrast, ifyou use the Consistent parameter, then there is only one read-only transaction.与Consistent参数的区别:1. Object_Consistent是每个导出的对象都有每个对象的consistent2.

Export provides two methods for exporting table data:■ Conventional path Export■ Direct path ExportConventional path Export uses the SQL SELECT statement to extract data fromtables. Data is read from disk into a buffer cache, and rows are transferred to theevaluatingbuffer.Thedata,afterpassingexpressionevaluation,istransferredtotheExport client, which then writes the data into the export file.Direct path Export is much faster than conventional path Export because data isread from disk into the

Specifies whether or not Export uses the SET TRANSACTION READ ONLYstatement to ensure that the data seen by Export is consistent to a single point intime and does not change during the execution of theexp command. You shouldspecifyCONSISTENT=y when you anticipate that other applications will beupdating the target data after an export has started.If you useCONSISTENT=n, each table is usually exported in a single transaction.However, if a table contains nested tables, the outer table and each inne