Feb 26, 2006
BLOCK、EXTENT、SEGMENT、TABLESPACE、Data Dictionary
Block
Block 可以分为
Header
Table Directory
Row Directory
Row Data
Free Space
其中，block header、table directory、row directory 合起来称为 Overhead。
详细解释：
Header (Common and Variable)
The header contains general block information, such as the block address and the type of segment (for example, data or index).
Table Directory
This portion of the data block contains information about the table having rows in this block.
Row Directory
This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).this space is not reclaimed when the row is deleted.
Overhead
The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
Row Data
This portion of the data block contains table or index data. Rows can span blocks.
Free Space
Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).
The header contains general block information, such as the block address and the type of segment (for example, data or index).
Table Directory
This portion of the data block contains information about the table having rows in this block.
Row Directory
This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).this space is not reclaimed when the row is deleted.
Overhead
The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
Row Data
This portion of the data block contains table or index data. Rows can span blocks.
Free Space
Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).
Row Chaining（行连接）和 Row Migrating（行迁移）
Row Chaining:
the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.
Row Migrating:
a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.
the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.
Row Migrating:
a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.
SEGMENT
High Water Mark
The high water mark is the boundary between used and unused space in a segment.
When Extents Are Deallocated?
当 Extents 分配给某个对象时，一般不会自己回收，除非以下几种情况发生：
1.drop the schema object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement)
2.TRUNCATE...DROP STORAGE statement（用 delete 不会回收）
3.ALTER TABLE table_name DEALLOCATE UNUSED（HWM 之后的空间）;
4.Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified
2.TRUNCATE...DROP STORAGE statement（用 delete 不会回收）
3.ALTER TABLE table_name DEALLOCATE UNUSED（HWM 之后的空间）;
4.Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified
TRUNCATE 语法：
TRUNCATE TABLE XXX { DROP STORAGE(DEFAULT) | REUSE STORAGE }
TABLESPACE
EXTENT MANAGEMENT LOCAL 的两种 Extents 分配方式：
AUTOALLOCATE (system-managed):you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.
UNIFORM SIZE:you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
UNIFORM SIZE:you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.
当使用 Local 管理 Extents 时参数 NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE 不能使用。
SEGMENT SPACE MANAGEMENT 的两种管理方式：
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments.Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. 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. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments.Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. 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. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
系统表空间：
When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
UNDO 表空间：
Through the use of a system parameter (UNDO_RETENTION), you can specify the amount of committed undo information to retain in the database.
The Data Dictionary
参数 O7_DICTIONARY_ACCESSIBILITY 的作用：
Oracle recommends that you implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary. If you enable dictionary protection (O7_DICTIONARY_ACCESSIBILITY is false), then access to objects in the SYS schema (dictionary objects) is restricted to users with the SYS schema. These users are SYS and those who connect as SYSDBA.
O7_DICTIONARY_ACCESSIBILITY:
说明: 主要用于从 Oracle7 移植到 Oracle8i。如果该值为 TRUE, SYSTEM 权限 (如 SELECT ANY TABLE) 将不限制对 SYS 方案中各对象的访问 (Oracle7 行为)。如果该值为 FALSE, 只有被授予了 SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE 或 DELETE_CATALOG_ROLE 权限的用户才能访问 SYS 方案中的各对象。
值范围: TRUE | FALSE
默认值: TRUE
说明: 主要用于从 Oracle7 移植到 Oracle8i。如果该值为 TRUE, SYSTEM 权限 (如 SELECT ANY TABLE) 将不限制对 SYS 方案中各对象的访问 (Oracle7 行为)。如果该值为 FALSE, 只有被授予了 SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE 或 DELETE_CATALOG_ROLE 权限的用户才能访问 SYS 方案中的各对象。
值范围: TRUE | FALSE
默认值: TRUE
该参数主要为了兼容 7.0 版本，用来限制其他用户是否可以访问 sys schema 下的对象。以后的版本可以通过 SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE 或 DELETE_CATALOG_ROLE 权限来控制。
另外，由于 pl/sql 的编译的一些特性，oracle 采取了角色在存储过程、函数、包等在 pl/sql 中不起作用的策略，必须直接授权才生效，所以即使有dba角色在存储过程中也是无效的。（参考：http://www.itpub.net/251232,1.html）
DBMS_METADATA
The DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL.
最简单的使用方法：
SQL> set long 2000
SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."TEST"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(18),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
http://blog.csdn.net/eygle/archive/2004/12/22/225673.aspx
更详细的使用方法：
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1024701