ITPub博客

You can assign a default value to a column of a table so that when a new row isinserted and a value for the column is omitted or keyword DEFAULT is supplied, adefault value is supplied automatically. Default column values work as though anINSERT statement actually specifies the default value.The datatype of the default literal or expression must match or be convertible to thecolumn datatype.If a default value is not explicitly defined for a column, then the default for the columnis implicitly se

A null is the absence of a value in a column of a row. Nulls indicate missing,unknown, or inapplicable data. A null should not be used to imply any other value,such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY integrityconstraint has been defined for the column, in which case no row can be insertedwithout a value for that column.Nulls are stored in the database if they fall between columns with data values. In thesecases they require 1 byte to store the length of the column (z

Compression occurs while data is being bulk inserted or bulk loaded. These operationsinclude:■ Direct path SQL*Loader■ CREATE TABLE and AS SELECT statements■ Parallel INSERT (or serial INSERT with an APPEND hint) statementsExisting data in the database can also be compressed by moving it into compressedform through ALTER TABLE and MOVE statements. This operation takes an exclusivelock on the table, and therefore prevents any updates and loads until it completes. Ifthis is not acceptable, then

Oracle's table compression feature compresses data by eliminating duplicate values ina database block. Compressed data stored in a database block (also known as diskpage) is self-contained. That is, all the information needed to re-create theuncompressed data in a block is available within that block. Duplicate values in all therows and columns in a block are stored once at the beginning of the block, in what iscalled a symbol table for that block. All occurrences of such values are replaced wit

The column order is the same for all rows in a given table. Columns are usually storedin the order in which they were listed in the CREATE TABLE statement, but this is notguaranteed. For example, if a table has a column of datatype LONG, then Oracle alwaysstores this column last. Also, if a table is altered so that a new column is added, thenthe new column becomes the last column stored.In general, try to place columns that frequently contain nulls last so that rows take lessspace. Note, though,

The rowid identifies each row piece by its location or address. After they are assigned,a given row piece retains its rowid until the corresponding row is deleted or exportedand imported using Oracle utilities. For clustered tables, if the cluster key values of arow change, then the row keeps the same rowid but also gets an additional pointerrowid for the new values.Because rowids are constant for the lifetime of a row piece, it is useful to referencerowids in SQL statements such as SELECT, UPDA

Each row piece, chained or unchained, contains a row header and data for all or someof the row’s columns. Individual columns can also span row pieces and, consequently,data blocks.The row header precedes the data and contains information about:■ Row pieces■ Chaining (for chained row pieces only)■ Columns in the row piece■ Cluster keys (for clustered data only)A row fully contained in one block has at least 3 bytes of row header. After the rowheader information, each row contains column leng

Oracle stores each row of a database table containing data for less than 256 columns asone or more row pieces. If an entire row can be inserted into a single data block, thenOracle stores the row as one row piece. However, if all of a row’s data cannot beinserted into a single data block or if an update to an existing row causes the row tooutgrow its data block, then Oracle stores the row using multiple row pieces. A datablock usually contains only one row piece for each row. When Oracle must s

When you create a table, Oracle automatically allocates a data segment in a tablespaceto hold the table’s future data. You can control the allocation and use of space for atable’s data segment in the following ways:■ You can control the amount of space allocated to the data segment by setting thestorage parameters for the data segment.■ You can control the use of the free space in the data blocks that constitute the datasegment’s extents by setting the PCTFREE and PCTUSED parameters for the

Tables are the basic unit of data storage in an Oracle database. Data is stored in rowsand columns. You define a table with a table name (such as employees) and set ofcolumns. You give each column a column name (such as employee_id, last_name,and job_id), a datatype (such as VARCHAR2, DATE, or NUMBER), and a width. Thewidth can be predetermined by the datatype, as in DATE. If columns are of the NUMBERdatatype, define precision and scale instead of width. A row is a collection of columninformatio

A schema is a collection of logical structures of data, or schema objects. A schema isowned by a database user and has the same name as that user. Each user owns a singleschema. Schema objects are logical data storage structures. Schema objects do not have aone-to-one correspondence to physical files on disk that store their information.However, Oracle stores a schema object logically within a tablespace of the database.The data of each object is physically contained in one or more of the tables

今天测了一下脉搏 , 一分钟70次 .在学校里面一分钟可是低于60次啊 , 看样子得加紧锻炼身体了 .[@more@]

翻了一下pub上面2002年的老帖子Index growth could be due to the manner in which Oracle splits the blocks as the index is populated. Indexes are created as b*trees. The only balancing occurs as a 50:50 block splits. Each leaf block is allocated a range of key values. When a value is deleted the space is not immediately available for any new key values - only for ones that fall in the existing range for the block. A block is only placed back on the free list when all the index entries for that block

Transaction control statements in an autonomous PL/SQL block apply only to thecurrently active autonomous transaction. Examples of such statements are:SET TRANSACTIONCOMMITROLLBACKSAVEPOINTROLLBACK TO SAVEPOINTSimilarly, transaction control statements in the main transaction apply only to thattransaction and not to any autonomous transaction that it calls. For example, rollingback the main transaction to a savepoint taken before the beginning of an autonomoustransaction does not undo the autonom

You can call autonomous transactions from within a PL/SQL block. Use the pragmaAUTONOMOUS_TRANSACTION. A pragma is a compiler directive. You can declare thefollowing kinds of PL/SQL blocks to be autonomous:■ Stored procedure or function■ Local procedure or function■ Package■ Type method■ Top-level anonymous blockWhen an autonomous PL/SQL block is entered, the transaction context of the caller issuspended. This operation ensures that SQL operations performed in this block (orother blocks cal

Autonomous transactions are independent transactions that can be called from withinanother transaction. An autonomous transaction lets you leave the context of thecalling transaction, perform some SQL operations, commit or undo those operations,and then return to the calling transaction’s context and continue with that transaction.Once invoked, an autonomous transaction is totally independent of the maintransaction that called it. It does not see any of the uncommitted changes made by themain t

In a distributed database, Oracle must coordinate transaction control over anetwork and maintain data consistency, even if a network or system failureoccurs.A distributed transaction is a transaction that includes one or more statementsthat update data on two or more distinct nodes of a distributed database.A two-phase commit mechanism guarantees that all database servers participatingin a distributed transaction either all commit or all undo the statements in thetransaction. A two-phase commit

In previous releases, you could associate a comment with a transaction by usinga commit comment. However, a comment can be associated with a transaction onlywhen a transaction is being committed.Commit comments are still supported for backward compatibility. However, Oraclestrongly recommends that you use transaction names. Commit comments are ignoredin named transactions.Note: In a future release, commit comments will be deprecated.提交注释在之前版本 Oracle 的中 ，用户可以使用提交注释为事务添加

Name a transaction using the SET TRANSACTION ... NAME statement before you startthe transaction.When you name a transaction, you associate the transaction’s name with its ID.Transaction names do not have to be unique; different transactions can have thesametransaction name at the same time by the same owner. You can use any name thatenables you to distinguish the transaction.怎么命名事务在事务开始之前 , 通过SET TRANSACTION ... NAME命名 , 事务名可以重复.[@more@]

You can name a transaction, using a simple and memorable text string. This nameis a reminder of what the transaction is about. Transaction names replace commitcomments for distributed transactions, with the following advantages:■ It is easier to monitor long-running transactions and to resolve in-doubtdistributed transactions.■ You can view transaction names along with transaction IDs in applications.Forexample, a database administrator can view transaction names in EnterpriseManager when moni