oracle有用资料

第一章:日志管理1.forcinglogswitchessqlaltersystemswitchlogfile;2.forcingcheckpointssqlaltersystemcheckpoint;3.addingonlineredologgroupssqlalterdatabaseaddlogfile[group4]sql('/disk3/log4a.rdo','/disk4/log4b.rdo')size1m;4.addingonlineredologmemberssqlalterdatabaseaddlogfilemembersql'/disk3/log1b.rdo'togroup1,sql'/disk4/log2b.rdo'togroup2;5.changesthenameoftheonlineredologfilesqlalterdatabaserenamefile'c:/oracle/oradata/oradb/redo01.log'sqlto'c:/oracle/oradata/redo01.log';6.droponlineredologgroupssqlalterdatabasedroplogfilegroup3;7.droponlineredologmemberssqlalterdatabasedroplogfilemember'c:/oracle/oradata/redo01.log';8.clearingonlineredologfilessqlalterdatabaseclear[unarchived]logfile'c:/oracle/log2a.rdo';9.usinglogmineranalyzingredologfilesa.intheinit.oraspecifyutl_file_dir=''b.sqlexecutedbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');c.sqlexecutedbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',sqldbms_logmnr.new);d.sqlexecutedbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',sqldbms_logmnr.addfile);e.sqlexecutedbms_logmnr.start_logmnr(dictfilename='c:\oracle\oradb\log\oradb.ora');f.sqlselect*fromv$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssqlv$logmnr_logs);g.sqlexecutedbms_logmnr.end_logmnr;第二章:表空间管理1.createtablespacessqlcreatetablespacetablespace_namedatafile'c:\oracle\oradata\file1.dbf'size100m,sql'c:\oracle\oradata\file2.dbf'size100mminimumextent550k[logging/nologging]sqldefaultstorage(initial500knext500kmaxextents500pctinccease0)sql[online/offline][permanent/temporary][extent_management_clause]2.locallymanagedtablespacesqlcreatetablespaceuser_datadatafile'c:\oracle\oradata\user_data01.dbf'sqlsize500mextentmanagementlocaluniformsize10m;3.temporarytablespacesqlcreatetemporarytablespacetemptempfile'c:\oracle\oradata\temp01.dbf'sqlsize500mextentmanagementlocaluniformsize10m;4.changethestoragesettingsqlaltertablespaceapp_dataminimumextent2m;sqlaltertablespaceapp_datadefaultstorage(initial2mnext2mmaxextents999);5.takingtablespaceofflineoronlinesqlaltertablespaceapp_dataoffline;sqlaltertablespaceapp_dataonline;6.read_onlytablespacesqlaltertablespaceapp_datareadonly|write;7.dropingtablespacesqldroptablespaceapp_dataincludingcontents;8.enableingautomaticextensionofdatafilessqlaltertablespaceapp_dataadddatafile'c:\oracle\oradata\app_data01.dbf'size200msqlautoextendonnext10mmaxsize500m;9.changethesizefodatafilesmanuallysqlalterdatabasedatafile'c:\oracle\oradata\app_data.dbf'resize200m;10.Movingdatafiles:altertablespacesqlaltertablespaceapp_datarenamedatafile'c:\oracle\oradata\app_data.dbf'sqlto'c:\oracle\app_data.dbf';11.movingdatafiles:alterdatabasesqlalterdatabaserenamefile'c:\oracle\oradata\app_data.dbf'sqlto'c:\oracle\app_data.dbf';第三章:表1.createatablesqlcreatetabletable_name(columndatatype,columndatatype]....)sqltablespacetablespace_name[pctfreeinteger][pctusedinteger]sql[initransinteger][maxtransinteger]sqlstorage(initial200knext200kpctincrease0maxextents50)sql[logging|nologging][cache|nocache]2.copyanexistingtablesqlcreatetabletable_name[logging|nologging]assubquery3.createtemporarytablesqlcreateglobaltemporarytablexay_tempasselect*fromxay;oncommitpreserverows/oncommitdeleterows4.pctfree=(averagerowsize-initialrowsize)*100/averagerowsizepctused=100-pctfree-(averagerowsize*100/availabledataspace)5.changestorageandblockutilizationparametersqlaltertabletable_namepctfree=30pctused=50storage(next500ksqlminextents2maxextents100);6.manuallyallocatingextentssqlaltertabletable_nameallocateextent(size500kdatafile'c:/oracle/data.dbf');7.movetablespacesqlaltertableemployeemovetablespaceusers;8.deallocateofunusedspacesqlaltertabletable_namedeallocateunused[keepinteger]9.truncateatablesqltruncatetabletable_name;10.dropatablesqldroptabletable_name[cascadeconstraints];11.dropacolumnsqlaltertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;altertabletable_namedropcolumnscontinue;12.markacolumnasunusedsqlaltertabletable_namesetunusedcolumncommentscascadeconstraints;altertabletable_namedropunusedcolumnscheckpoint1000;altertableordersdropcolumnscontinuecheckpoint1000data_dictionary:dba_unused_col_tabs第四章:索引1.creatingfunction-basedindexessqlcreateindexsummit.item_quantityonsummit.item(quantity-quantity_shipped);2.createaB-treeindexsqlcreate[unique]indexindex_nameontable_name(column,..asc/desc)tablespacesqltablespace_name[pctfreeinteger][initransinteger][maxtransinteger]sql[logging|nologging][nosort]storage(initial200knext200kpctincrease0sqlmaxextents50);3.pctfree(index)=(maximumnumberofrows-initialnumberofrows)*100/maximumnumberofrows4.creatingreversekeyindexessqlcreateuniqueindexxay_idonxay(a)reversepctfree30storage(initial200ksqlnext200kpctincrease0maxextents50)tablespaceindx;5.createbitmapindexsqlcreatebitmapindexxay_idonxay(a)pctfree30storage(initial200knext200ksqlpctincrease0maxextents50)tablespaceindx;6.changestorageparameterofindexsqlalterindexxay_idstorage(next400kmaxextents100);7.allocatingindexspa