SMSE10_管理表对象
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
• There are two distinct parts of LOB :LOB value and locator.
The LOB column stores a locator to the LOB’s value
LOB locator
LOB column Of a table
LOB Value
SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = 'Sys_DepotOperateDetail';
• Datafile identifier
select extent_id, file_id,block_id,blocks from dba_extents where segment_name='Sys_DepotOperateDetail';
address of a row, it can be used to locate the row.
• ROWID provides the fastest means of accessing a row in
a table.
• ROWIDs are stored in indexes to specify rows with a
partitioned table
• Reorganize, truncate, drop a table • Add, Drop or modify a column within a table • Obtain table information
10-2
Distribution of Rows Within a Table
Varies from 7 to 11 bytes.A value representing a date and time, including fractional seconds.
Fixed at 5 bytes.Stored as an interval of years and months
• Block identifier
• Row identifier
10-13
ROWID Data Type
• Example
SELECT department_id, rowid FROM hr.departments;
DEPARTMENT_ID
ROWID
10
AAABQMAAFAAAAA6AAA
CREATE TABLE time_example2 (loaINnT_EdRuVArLat'i12o3n' IYENATRE(R3)VAL YEAR (3) TO MONTH);
Indicates an interval of 123 years 0 months.
INSERT INTO time_example2 (loan_duration) VALUINETSER(VAILNT'E30R0V'ALMON'T1H2(30)' MONTH(3));
Table
Cluster
Ordering of Rows
Random
(Heap)
Grouped
(Clustered)
Indexorganized
table
Ordered
(Sorted)
Partitioned table
10-3
Oracle Data Types
Data type
User-defined
restricted ROWID
• Outline the structure of a row • Create regular and temporary tables • Manage storage structures within a table • Manage index-organized table,clusters and
ROWID Data Type
• Unique identifier for each row in the database. • Does not stored explicitly as a column value. • Although the ROWID does not directly give the physical
10-8
10-9
Data Types for Storing Large Objects
• External LOBs(BFILE):Store a locator to the
physical file.
– Definition of BFILE objects
– Association of BFILE object to corresponding external files
Relationship REF
10-4
Representing Numeric Data • Use the NUMBER datatype to store real numbers in
a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision.
10-7
Data Types for Storing Large Objects
• Oracle provides six data types for storing LOBs
– CLOB and LONG for large fixed-width character data – NCLOB for large fixed-width national character set data – BLOB and LONG RAW for storing unstructured data – BFILE for storing unstructured data in operating system files
• Managing internal LOBs
– PL/SQL package DBMS_LOB – Oracle Call Interface(OCI) – Oracle Objects for object linking and embedding – ODBC/JDBC – SQL
10-11
– VARRAYs are ordered sets of elements containing a count and a limit.
– Nested tables are tables with a column or variable of the TABLE data type.
Indicates an interval of 300 months.
SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy') FROMINTERtVAiLme'_12e3x'amYEpAlRe2; ------t查o询daRa结enydt’果us'r：n1s2d03a3a't-nMeheaarsiyrs-o32r0,d01i33bg-eiMctasau.ys-e2t0h0e3 default precision is 2,
10-5
Oracle 9i DateTime Support
Data Type
Description
Date
TIMESTAMP
INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY)
Built-in
Scalar
CHAR(N), NCHAR(N) VARCHAR2(N), NVARCHAR2(N) NUMBER(P,S) RAW(N) DATE TIMESTAMP BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID
Collection VARRAY TABLE
– Security for BFILEs
• External LOBs do not
participate in transactions.
• Any support for integrity
and durability must be provided by the underlying operating system.
Fixed at 11 bytes.Stored as an interval of days to hours minutes and seconds
TIMETSOTA_MDPATWEIT(H'NToIMveEmberF1ix3e, d19a9t21'3, 'MbyOteNsT.HADvaDl,uYeYYY') ZONETO_DATE('13-NOV-92r1e0p:r5e6seAn.Mtin.'g,'DaDd-aMtOe Nan-YdYtiHmHe:,MpIluAs.Ma.n' )
associated time zone setting. CURRENT_DATE TIMESTAMP WITH LOCAL Varies from 7 to 11 bytes. TIMECZUORNRE ENT_TIMESTAMP
10-6
INTERVAL YEAR TO MONTH Data Type
given set of key values
ROWID Format
OOOOOO
Data object number
FFF
Relative file number
BBBBBB Block number
RRR Row number
10-12
ROWID Data Type • Data object (segment) identifier
Row number
File number
10-14
Collection Data Types
• A collection is an object that contains other
objects, where each contained object is of the same type. Collection types are parameterized datatypes.
20
AAABQMAAFAAAAA6AAB
30
Байду номын сангаасAAABQMAAFAAAAA6AAC
……
• Restricted ROWID(INDEX)
– Can identify rows within a segment – Needs less space
BBBBBBBB .
RRRR
.
FFFF
Block number
• INTERVAL YEAR TO MONTH stores a period of time
using the YEAR and MONTH datetime fields.
INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL '123-2' YEAR(3) TO MONTH Indicates an interval of 123 years, 2 months.
• You cannot locate a single
BFILE on more than one device.
10-10
Data Types for Storing Large Objects • Internal LOBs: the LOB value is stored in the
database
Managing Tables 管理表
Objectives
After completing this lesson, you should be able to do the following:
• Identify the various methods of storing data • Outline Oracle data types • Distinguish between an extended versus a
The LOB column stores a locator to the LOB’s value
LOB locator
LOB column Of a table
LOB Value
SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = 'Sys_DepotOperateDetail';
• Datafile identifier
select extent_id, file_id,block_id,blocks from dba_extents where segment_name='Sys_DepotOperateDetail';
address of a row, it can be used to locate the row.
• ROWID provides the fastest means of accessing a row in
a table.
• ROWIDs are stored in indexes to specify rows with a
partitioned table
• Reorganize, truncate, drop a table • Add, Drop or modify a column within a table • Obtain table information
10-2
Distribution of Rows Within a Table
Varies from 7 to 11 bytes.A value representing a date and time, including fractional seconds.
Fixed at 5 bytes.Stored as an interval of years and months
• Block identifier
• Row identifier
10-13
ROWID Data Type
• Example
SELECT department_id, rowid FROM hr.departments;
DEPARTMENT_ID
ROWID
10
AAABQMAAFAAAAA6AAA
CREATE TABLE time_example2 (loaINnT_EdRuVArLat'i12o3n' IYENATRE(R3)VAL YEAR (3) TO MONTH);
Indicates an interval of 123 years 0 months.
INSERT INTO time_example2 (loan_duration) VALUINETSER(VAILNT'E30R0V'ALMON'T1H2(30)' MONTH(3));
Table
Cluster
Ordering of Rows
Random
(Heap)
Grouped
(Clustered)
Indexorganized
table
Ordered
(Sorted)
Partitioned table
10-3
Oracle Data Types
Data type
User-defined
restricted ROWID
• Outline the structure of a row • Create regular and temporary tables • Manage storage structures within a table • Manage index-organized table,clusters and
ROWID Data Type
• Unique identifier for each row in the database. • Does not stored explicitly as a column value. • Although the ROWID does not directly give the physical
10-8
10-9
Data Types for Storing Large Objects
• External LOBs(BFILE):Store a locator to the
physical file.
– Definition of BFILE objects
– Association of BFILE object to corresponding external files
Relationship REF
10-4
Representing Numeric Data • Use the NUMBER datatype to store real numbers in
a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision.
10-7
Data Types for Storing Large Objects
• Oracle provides six data types for storing LOBs
– CLOB and LONG for large fixed-width character data – NCLOB for large fixed-width national character set data – BLOB and LONG RAW for storing unstructured data – BFILE for storing unstructured data in operating system files
• Managing internal LOBs
– PL/SQL package DBMS_LOB – Oracle Call Interface(OCI) – Oracle Objects for object linking and embedding – ODBC/JDBC – SQL
10-11
– VARRAYs are ordered sets of elements containing a count and a limit.
– Nested tables are tables with a column or variable of the TABLE data type.
Indicates an interval of 300 months.
SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy') FROMINTERtVAiLme'_12e3x'amYEpAlRe2; ------t查o询daRa结enydt’果us'r：n1s2d03a3a't-nMeheaarsiyrs-o32r0,d01i33bg-eiMctasau.ys-e2t0h0e3 default precision is 2,
10-5
Oracle 9i DateTime Support
Data Type
Description
Date
TIMESTAMP
INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY)
Built-in
Scalar
CHAR(N), NCHAR(N) VARCHAR2(N), NVARCHAR2(N) NUMBER(P,S) RAW(N) DATE TIMESTAMP BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID, UROWID
Collection VARRAY TABLE
– Security for BFILEs
• External LOBs do not
participate in transactions.
• Any support for integrity
and durability must be provided by the underlying operating system.
Fixed at 11 bytes.Stored as an interval of days to hours minutes and seconds
TIMETSOTA_MDPATWEIT(H'NToIMveEmberF1ix3e, d19a9t21'3, 'MbyOteNsT.HADvaDl,uYeYYY') ZONETO_DATE('13-NOV-92r1e0p:r5e6seAn.Mtin.'g,'DaDd-aMtOe Nan-YdYtiHmHe:,MpIluAs.Ma.n' )
associated time zone setting. CURRENT_DATE TIMESTAMP WITH LOCAL Varies from 7 to 11 bytes. TIMECZUORNRE ENT_TIMESTAMP
10-6
INTERVAL YEAR TO MONTH Data Type
given set of key values
ROWID Format
OOOOOO
Data object number
FFF
Relative file number
BBBBBB Block number
RRR Row number
10-12
ROWID Data Type • Data object (segment) identifier
Row number
File number
10-14
Collection Data Types
• A collection is an object that contains other
objects, where each contained object is of the same type. Collection types are parameterized datatypes.
20
AAABQMAAFAAAAA6AAB
30
Байду номын сангаасAAABQMAAFAAAAA6AAC
……
• Restricted ROWID(INDEX)
– Can identify rows within a segment – Needs less space
BBBBBBBB .
RRRR
.
FFFF
Block number
• INTERVAL YEAR TO MONTH stores a period of time
using the YEAR and MONTH datetime fields.
INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL '123-2' YEAR(3) TO MONTH Indicates an interval of 123 years, 2 months.
• You cannot locate a single
BFILE on more than one device.
10-10
Data Types for Storing Large Objects • Internal LOBs: the LOB value is stored in the
database
Managing Tables 管理表
Objectives
After completing this lesson, you should be able to do the following:
• Identify the various methods of storing data • Outline Oracle data types • Distinguish between an extended versus a