oracle 常用命令

OracleLOADreorganizingdatamanagingpasswordsecurityandresourcesManagingusersmanagingprivilegesmanagerrole:BACKUPandRECOVERYOracle1.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:oracleoradblog');c.sqlexecutedbms_logmnr_add_logfile('c:oracleoradataoradbredo01.log',sqldbms_logmnr.new);d.sqlexecutedbms_logmnr.add_logfile('c:oracleoradataoradbredo02.log',sqldbms_logmnr.addfile);e.sqlexecutedbms_logmnr.start_logmnr(dictfilename='c:oracleoradblogoradb.ora');f.sqlselect*fromv$logmnr_contents(v$logmnr_dictionary,v$logmnr_parametersCreatePDFwithGO2PDFforfree,ifyouwishtoremovethisline,clickheretobuyVirtualPDFPrintersqlv$logmnr_logs);g.sqlexecutedbms_logmnr.end_logmnr;1.createtablespacessqlcreatetablespacetablespace_namedatafile'c:oracleoradatafile1.dbf'size100m,sql'c:oracleoradatafile2.dbf'size100mminimumextent550k[logging/nologging]sqldefaultstorage(initial500knext500kmaxextents500pctinccease0)sql[online/offline][permanent/temporary][extent_management_clause]2.locallymanagedtablespacesqlcreatetablespaceuser_datadatafile'c:oracleoradatauser_data01.dbf'sqlsize500mextentmanagementlocaluniform.size10m;3.temporarytablespacesqlcreatetemporarytablespacetemptempfile'c:oracleoradatatemp01.dbf'sqlsize500mextentmanagementlocaluniform.size10m;4.changethestoragesettingsqlaltertablespaceapp_dataminimumextent2m;sqlaltertablespaceapp_datadefaultstorage(initial2mnext2mmaxextents999);5.takingtablespaceofflineoronlinesqlaltertablespaceapp_dataoffline;sqlaltertablespaceapp_dataonline;6.read_onlytablespacesqlaltertablespaceapp_datareadonly|write;7.dropingtablespacesqldroptablespaceapp_dataincludingcontents;8.enableingautomaticextensionofdatafilessqlaltertablespaceapp_dataadddatafile'c:oracleoradataapp_data01.dbf'size200msqlautoextendonnext10mmaxsize500m;9.changethesizefodatafilesmanuallysqlalterdatabasedatafile'c:oracleoradataapp_data.dbf'resize200m;10.Movingdatafiles:altertablespacesqlaltertablespaceapp_datarenamedatafile'c:oracleoradataapp_data.dbf'sqlto'c:oracleapp_data.dbf';11.movingdatafiles:alterdatabasesqlalterdatabaserenamefile'c:oracleoradataapp_data.dbf'sqlto'c:oracleapp_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]assubqueryCreatePDFwithGO2PDFforfree,ifyouwishtoremovethisline,clickheretobuyVirtualPDFPrinter3.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_tabs1.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.createbitma