oracle数据库入门

ORACLE1997.1Oracle(1)datafilesredologfilescontrolfilesparameterfileparameterilesInitoraid.oracontrolfileredologfilesdatafiles*.ctl*.log*.dbfPMONLCKnRECORECOSGADataDataDataDataDataDataDataDataDataDataDatafilesRedoLogfilesParameterFileControlfilesServerUserDBWRLGWRCKPTARCHOfflinestoragesharedPoolDatabaseBufferCacheRedoLogBuffer(2)(SGA)OS60-70%sharedpool()databasebuffercache(),redologbuffer()()SGA=share_pool_size+db_block_size*db_block_buffers+log_buffersshared_pooldatabase_buffer_cacheredo_logbufferSGAbackgroundprocess(3)instance=SGA+backgroundprocess=+Oracleinstance,oracleinstance.(4)session()Oracleoraclesessionuseruseruser(5)transaction()Eg:1insertDDL()deletecreate,alter,drop,conmitcommitDDLtransaction2updateDML()rollbackInsert,Delete,Update(6)PMONLCLNRECOSMONDBWRLGWRCKPTARCHPMONSMONLCKNOracle.RECODBWRLGWRCKPTARCH(7)SQLOrcleASQLBSQLCDESQLoracle“”PMONLCKnRECORECOsharedPoolDatabaseBufferCacheRedoLogBufferSGA(1)%svrmgrl.SVRMGRLSVRMGRLconnectinternal;SVRMGRLstartupinstance(2)(Oracle%sqldbalmode=Y)%svrmgrl.(V7.3.2.0)SVRMGRLSVRMGRLconnectinternal;SVRMGRLshutdown.(3)Oracleshutdownopeninitoraid.orainstancesnomountmount(4)Oracle“adc.dbf?OracleOracle%svrmgrlSVRMGRconnectinternalSVRMGRstartupmountSVRMGRalterdatabasedatafile‘/directory/abc.dbf’offline;SVRMGRalterdatabaseopen;SVRMGRdroptablespaceabc;Oracle1Oracleablock2kI-Odatabasebextentcsegment(extent)dtablespace()efilefdatabase(SQLDBAdba-extents,dba-segments,dba-tablespace,dba-data-filesextent,segment,tablespacedatafile)commonandvariableheader85-100bytesTabledirectoryRowdirectoryFreespaceinsertupdataRowdatablock31block2blockdatablock41block2blockdatablockdatablockdatablock5blockpctfree20%pctused40%InittransMaxtransPctfree12312312Pctused12initial(10k,5)nextmaxextentsextentminextentsextnetextentpctincreaseoptimal0()freelist100()n1+6extentpctincrease123Oracle4extentsdataobject(table,view,indexsequence...)indextemporary(join,groupby,orderbysorting)rollbackminextent2increace0bootstrap40block7segmentdatasegment(1)table(2)viewselect(3)index(3)(4)cluster()(5)sequence(6)synonme(7)snapsot(8)storedprocedure(function)(9)package(10)dbtriggerupdata,delete,insertsun......SQL10SQLrollbackconnecttime(1)8table123546............1rowoverhead2rowpiece3clusterkey()4rowid(blockrowid5length6valuenulllength06value,tablenullrowid:xxxxxxxx.xxxx.xxxxrowsystemsystem9tablespaceRBSsystem()userstempapplicationdataapplicationindextoolsnosystem(rollback)12(1)Export/Import(2)1databasedatabase(cptar)2SVRMGRarchireloglist3exp.imptableuserdatabasetelnetincretypecompleteincrementalcumulative(cun;complete)imp(1)createtable(2)insertdata(3)createindex(4)createtriggers,constraints4failureP6-98(1)impsystem/managerfile=incr19.dmpinctype=system(2)impsystem/managerfile=x1.dmpinctype=restore(3)impsystem/managerfile=c8.dmpinctype=restore(4)impsystem/managerfile=c15.dmpinctype=restore(5)impsystem/managerfile=c16.dmpinctype=restore(6)impsystem/managerfile=c17.dmpinctype=restore(7)impsystem/managerfile=c18.dmpinctype=restore(8)impsystem/managerfile=c19.dmpinctype=restore1oracle(1)SVRMGLCREATEUSERusernameIDENTIFIEDBYuserpassword[DEFAULTTABLESPACEtablespace1][TEMPORARYTABLESPACEtablespace2][QUOTAnKONtablespace1];MUNLIMITED(2)Oracleusername[withgrantoption];rolenameSVRMGLgrantconnecttoprivelege......role()connect(8)Oracle,resource(5)dba(77)exp-full-databaseimp-full-datsabase(3)alteruser;(4)dropuser[cascade];(5)revokerolefromuser;2GrantingPrivilegeswithoutrolesusersGrantingPrivilegesUsingRolesusersroleprivilegesROLESsystemprivilegeobjectprivilegeobjectdba-sys-privs,dba-fab-privs;dba-col-privs;SQLLOADER/oracle/home/rdbs/demo/ulcase*.ctlusend=scott/tiger