表格模板-SMSE10管理表对象 精品.ppt

《表格模板-SMSE10管理表对象 精品.ppt》由会员分享，可在线阅读，更多相关《表格模板-SMSE10管理表对象 精品.ppt（78页珍藏版）》请在第一文库网上搜索。

1、Managing Tables管理表管理表ObjectivesAfter completing this lesson, you should be able todo the following:Identify the various methods of storing dataOutline Oracle data typesDistinguish between an extended versus a restricted ROWIDOutline the structure of a rowCreate regular and temporary tablesManage sto

2、rage structures within a tableManage index-organized table,clusters and partitioned tableReorganize, truncate, drop a tableAdd, Drop or modify a column within a tableObtain table information Distribution of Rows Within a TableOracle Data TypesCHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)RAW(N

3、)DATETIMESTAMPBLOB, CLOB,NCLOB, BFILELONG, LONG RAWROWID, UROWIDVARRAYTABLEREFData typeBuilt-inUser-definedScalarRelationshipCollectionRepresenting Numeric DataUse the NUMBERNUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to

4、 be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. Oracle 9i DateTime SupportData TypeDescriptionDateFixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) TIMESTAMPVaries from 7 to 11 bytes.A value representing a date a

5、nd time, including fractional seconds. INTERVAL YEAR TO MONTHFixed at 5 bytes.Stored as an interval of years and monthsINTERVAL DAY TO SECONDFixed at 11 bytes.Stored as an interval of days to hours minutes and secondsTIMESTAMP WITH TIME ZONEFixed at 13 bytes. A value representing a date and time, pl

6、us an associated time zone setting. TIMESTAMP WITH LOCAL TIME ZONE Varies from 7 to 11 bytes.TO_DATE (November 13, 1992, MONTH DD, YYYY) TO_DATE(13-NOV-92 10:56 A.M.,DD-MON-YY HH:MI A.M. )CURRENT_DATE CURRENT_TIMESTAMPINTERVAL YEAR TO MONTH Data TypeINTERVAL YEAR TO MONTH stores a period of time usi

7、ng the YEAR and MONTH datetime fields.INTERVAL YEAR (year_precision) TO MONTHINTERVAL 123-2 YEAR(3) TO MONTHIndicates an interval of 123 years, 2 months.INTERVAL 123 YEAR(3)Indicates an interval of 123 years 0 months.INTERVAL 300 MONTH(3)Indicates an interval of 300 months.INTERVAL 123 YEARReturns a

8、n error, because the default precision is 2, and 123 has 3 digits. CREATE TABLE time_example2 (loan_duration INTERVAL YEAR (3) TO MONTH); INSERT INTO time_example2 (loan_duration) VALUES (INTERVAL 120 MONTH(3); SELECT TO_CHAR( sysdate+loan_duration, dd-mon-yyyy) FROM time_example2; -todays date is 0

9、3-May-2003-查询结果：查询结果：03-May-2013Data Types for Storing Large ObjectsOracle provides six data types for storing LOBsCLOB and LONG for large fixed-width character dataNCLOB for large fixed-width national character set dataBLOB and LONG RAW for storing unstructured dataBFILE for storing unstructured da

10、ta in operating system filesThere are two distinct parts of LOB :LOB value and locator. The LOB column stores a locator to the LOBs valueLOB locatorLOB columnOf a tableLOB ValueData Types for Storing Large ObjectsLONG, LONG RAWSingle column per tableUp to 2 gigabytesSELECT returns dataData stored in

11、-lineNo object type supportSequential access to chunksLOBMultiple columns per tableUp to 4 gigabytesSELECT returns locator Data stored in-line or out-of-lineSupports object typesRandom access to chunksOracle9i Application Developers Guide - Large Objects (LOBs)PDF:1011页页 Data Types for Storing Large

12、 ObjectsExternal LOBs(BFILE):Store a locator to the physical file.Definition of BFILE objectsAssociation of BFILE object to corresponding external filesSecurity for BFILEsExternal LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying ope

13、rating system. You cannot locate a single BFILEBFILE on more than one device.Data Types for Storing Large ObjectsInternal LOBs: the LOB value is stored in the databaseManaging internal LOBsPL/SQL package DBMS_LOBOracle Call Interface(OCI)Oracle Objects for object linking and embeddingODBC/JDBCSQLROW

14、ID Data TypeUnique identifier for each row in the database.Does not stored explicitly as a column value.Although the ROWID does not directly give the physical 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 indexe

15、s to specify rows with a given set of key valuesOOOOOOBBBBBBFFFRRRData object numberRelative file numberRow numberBlock numberROWID Data TypeData object (segment) identifier Datafile identifier Block identifier Row identifier SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = Sys_DepotOperat

16、eDetail; select extent_id, file_id,block_id,blocks from dba_extents where segment_name=Sys_DepotOperateDetail;ROWID Data TypeBBBBBBBBFFFFRRRRBlock numberRow numberFile number.SELECT department_id, rowid FROM hr.departments;DEPARTMENT_ID ROWID10 AAABQMAAFAAAAA6AAA20 AAABQMAAFAAAAA6AAB30 AAABQMAAFAAAAA6AAC ExampleRestricted ROWID(INDEX)Can identify rows within a segmentNeeds less spaceCollection Data TypesA collection is an object that contains other objects, where each contained object is of the