吉林大学大型数据库设计与实现dba-11.PPT

ManagingTables

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:IdentifythevariousmethodsofstoringdataOutlineOracledatatypesDistinguishbetweenanextendedversusarestrictedROWIDOutlinethestructureofarowCreateregularandtemporarytablesManagestoragestructureswithinatableReorganize,truncate,dropatableDropacolumnwithinatable

StoringUserDataRegulartableClusterPartitioned

tableIndex-organized

table

OracleDataTypesCHAR(N),NCHAR(N)

VARCHAR2(N),

NVARCHAR2(N)NUMBER(P,S)DATE

TIMESTAMPRAW(N)

BLOB,CLOB,NCLOB,BFILELONG,LONGRAWROWID,UROWIDVARRAY

TABLEREFDatatypeBuilt-inUser-definedScalarRelationshipCollection

ROWIDFormatExtendedROWIDFormatOOOOOOBBBBBBFFFRRRDataobjectnumberRelativefilenumberRownumberBlocknumberBBBBBBBBFFFFRRRRBlocknumberRownumberFilenumber..RestrictedROWIDFormat

StructureofaRowDatabaseblockRowheaderColumnlengthColumnvalue

CreatingaTableCREATETABLEhr.employees(employee_idNUMBER(6),first_name VARCHAR2(20), last_nameVARCHAR2(25)emailVARCHAR2(25)phone_numberVARCHAR2(20)hire_dateDATEDEFAULTSYSDATEjob_id VARCHAR2(10)salaryNUMBER(8,2)commission_pctNUMBER(2,2)manager_idNUMBER(6)department_idNUMBER(4));

CreatingTemporaryTablesCreatedusingtheGLOBALTEMPORARYclauseTablesretaindataonlyforthedurationofatransactionorsessionDMLlocksarenotacquiredonthedataDMLsdonotgenerateredologsCancreateindexes,views,andtriggersontemporarytablesCREATEGLOBALTEMPORARYTABLEhr.employees_tempASSELECT*FROMhr.employees;

CreatingaTable:GuidelinesPlacetablesinseparatetablespaces.Uselocally-managedtablespacestoavoidfragmentation.Usefewstandardextentsizesfortablestoreducetablespacefragmentation.

ChangingStorageParametersALTERTABLEhr.employeesPCTFREE30PCTUSED50STORAGE(NEXT500KMINEXTENTS2MAXEXTENTS100);

ManuallyAllocatingExtentsALTERTABLEhr.employeesALLOCATEEXTENT(SIZE500KDATAFILE‘/DISK3/DATA01.DBF’);

N