oracle里的常用命令

oracle里的常用命令第一章:日志管理1.forcinglogswitchessql>altersystemswitchlogfile;2.forcingcheckpointssql>altersystemcheckpoint;3.addingonlineredologgroupssql>alterdatabaseaddlogfile[group4]sql>('/disk3/log4a.rdo','/disk4/log4b.rdo'size1m;4.addingonlineredologmemberssql>alterdatabaseaddlogfilemembersql>'/disk3/log1b.rdo'togroup1,sql>'/disk4/log2b.rdo'togroup2;5.changesthenameoftheonlineredologfilesql>alterdatabaserenamefile'c:/oracle/oradata/oradb/redo01.log'sql>to'c:/oracle/oradata/redo01.log';6.droponlineredologgroupssql>alterdatabasedroplogfilegroup3;7.droponlineredologmemberssql>alterdatabasedroplogfilemember'c:/oracle/oradata/redo01.log';8.clearingonlineredologfilessql>alterdatabaseclear[unarchived]logfile'c:/oracle/log2a.rdo';9.heinit.oraspecifyutl_file_dir=''b.sql>executedbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log';c.sql>executedbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',sql>dbms_logmnr.new;d.sql>executedbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',sql>dbms_logmnr.addfile;e.sql>executedbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora';f.sql>select*fromv$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql>v$logmnr_logs;g.sql>executedbms_logmnr.end_logmnr;第二章:表空间管理1.createtablespacessql>createtablespacetablespace_namedatafile'c:\oracle\oradata\file1.dbf'size100m,sql>'c:\oracle\oradata\file2.dbf'size100mminimumextent550k[logging/nologging]sql>defaultstorage(initial500knext500kmaxextents500pctinccease0sql>[online/offline][permanent/temporary][extent_management_clause]2.locallymanagedtablespacesql>createtablespaceuser_datadatafile'c:\oracle\oradata\user_data01.dbf'sql>size500mextentmanagementlocaluniformsize10m;3.temporarytablespacesql>createtemporarytablespacetemptempfile'c:\oracle\oradata\temp01.dbf'sql>size500mextentmanagementlocaluniformsize10m;4.changethestoragesettingsql>altertablespaceapp_dataminimumextent2m;sql>altertablespaceapp_datadefaultstorage(initial2mnext2mmaxextents999;5.takingtablespaceofflineoronlinesql>altertablespaceapp_dataoffline;sql>altertablespaceapp_dataonline;6.read_onlytablespacesql>altertablespaceapp_datareadonly|write;7.dropingtablespacesql>droptablespaceapp_dataincludingcontents;8.enableingautomaticextensionofdatafilessql>altertablespaceapp_dataadddatafile'c:\oracle\oradata\app_data01.dbf'size200msql>autoextendonnext10mmaxsize500m;9.changethesizefodatafilesmanuallysql>alterdatabasedatafile'c:\oracle\oradata\app_data.dbf'resize200m;10.Movingdatafiles:altertablespacesql>altertablespaceapp_datarenamedatafile'c:\oracle\oradata\app_data.dbf'sql>to'c:\oracle\app_data.dbf';11.movingdatafiles:alterdatabasesql>alterdatabaserenamefile'c:\oracle\oradata\app_data.dbf'sql>to'c:\oracle\app_data.dbf';第三章:表1.createatablesql>createtabletable_name(columndatatype,columndatatype]....sql>tablespacetablespace_name[pctfreeinteger][pctusedinteger]sql>[initransinteger][maxtransinteger]sql>storage(initial200knext200kpctincrease0maxextents50sql>[logging|nologging][cache|nocache]2.copyanexistingtablesql>createtabletable_name[logging|nologging]assubquery3.createtemporarytablesql>createglobaltemporarytablexay_tempasselect*fromxay;oncommitpreserverows/oncommitdeleterows4.pctfree=(averagerowsize-initialrowsize*100/averagerowsizepctused=100-pctfree-(averagerowsize*100/availabledataspace5.changestorageandblockutilizationparametersql>altertabletable_namepctfree=30pctused=50storage(next500ksql>minextents2maxextents100;6.manuallyallocatingextentssql>altertabletable_nameallocateextent(size500kdatafile'c:/oracle/data.dbf';7.movetablespacesql>altertableemployeemovetablespaceusers;8.deallocateofunusedspacesql>altertabletable_namedeallocateunused[keepinteger]9.truncateatablesql>truncatetabletable_name;10.dropatablesql>droptabletable_name[cascadeconstraints];11.dropacolumnsql>altertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;altertabletable_namedropcolumnscontinue;12.markacolumnasunusedsql>altertabletable_namesetunusedcolumncommentscascadeconstraints;altertabletable_namedropunusedcolumnscheckpoint1000;altertableordersdropcolumnscontinuecheckpoint1000data_dictionary:dba_unused_col_tabs第四章:索引1.creatingfunction-basedindexessql>createindexsummit.item_quantityonsummit.item(quantity-quantity_shipped;2.createaB-treeindexsql>create[unique]indexindex_nameontable_name(column,..asc/desctablespacesql>tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]sql>[logging|nologging][nosort]storage(initial200knext200kpctincrease0sql>maxextents50;3.pctfree(index=(maximumnumberofrows-initialnumberofrows*100/maximumnumberofrows4.creatingreversekeyindexessql>createuniqueindexxay_idonxay(areversepctfree30storage(initial200ksql>next200kpctincrease0maxextents50tablespaceindx;5.createbitmapindexsql>createbitmapindexxay_idonxay(apctfree30storage(initial200knext200ksql>pctincrease0maxextents50tablespaceindx;6.changestorageparameterofindexsql>alterindexxay_idstorage(next400kmaxextents100;7.allocatingindexspacesql>alterindexxay_idallocateextent(size200kdatafile'c:/oracle/index.dbf';8.alterindexxay_iddeallocateunused;第五章:约束1.defineconstraintsasimmediateordeferredsql>altersessionsetconstraint[s]=immediate/deferred/default;setconstraint[s]constraint_name/allimmediate/deferred;2.sql>droptabletable_namecascadeconstraintssql>droptablespacetablespace_nameincludingcontentscascadeconstraints3.defineconstraintswhilecreateatablesql>createtablexay(idnumber(7constraintxay_idprimarykeydeferrablesql>usingindexstorage(initial100knext100ktablespaceindx;primarykey/unique/referencestable(column/check4.enableconstraintssql>altertablexayenablenovalidateconstraintxay_id;5.enableconstraintssql>altertablexayenablevalidateconstraintxay_id;第六章:LOAD数据1.loadingdatausingdirect_loadinsertsql>insert/*+append*/intoempnologgingsql>select*fromemp_old;2.paralleldirect-loadinsertsql>altersessionenableparalleldml;sql>insert/*+parallel(emp,2*/intoempnologgingsql>select*fromemp_old;3.usingsql*loadersql>sqlldrscott/tiger\sql>control=ulcase6.ctl\sql>log=ulcase6.logdirect=true第七章:reorganizingdata1.usingexpoty$expscott/tigertables(dept,empfile=c:\emp.dmplog=exp.logcompress=ndirect=y2.usingimport$impscott/tigertables(dept,empfile=emp.dmplog=imp.logignore=y3.transportingatablespacesql>altertablespacesales_tsreadonly;$expsys/..file=xay.dmptransport_tablespace=ytablespace=sales_tstriggers=nconstraints=n$copydatafile$impsys/..file=xay.dmptransport_tablespace=ydatafiles=(/disk1/sles01.dbf,/disk2/sles02.dbfsql>altertablespacesales_tsreadwrite;4.checkingtransportsetsql>DBMS_tts.transport_set_check(ts_list=>'sales_ts'..,incl_constraints=>true;在表transport_set_violations中查看sql>dbms_tts.isselfcontained为true是,表示自包含第八章:managingpasswordsecurityandresources1.controllingaccountlockandpasswordsql>alteruserjunckyidentifiedbyoracleaccountunlock;2.user_providedpasswordfunctionsql>function_name(useridinvarchar2(30,passwordinvarchar2(30,old_passwordinvarchar2(30returnboolean3.createaprofile:passwordsettingsql>createprofilegrace_5limitfailed_login_attempts3sql>password_lock_timeunlimitedpassword_life_time30sql>password_reuse_time30password_verify_functionverify_functionsql>password_grace_time5;4.alteringaprofilesql>alterprofiledefaultfailed_login_attempts3sql>password_life_time60password_grace_time10;5.dropaprofilesql>dropprofilegrace_5[cascade];6.createaprofile:resourcelimitsql>createprofiledeveloper_proflimitsessions_per_user2sql>cpu_per_session10000idle_time60connect_time480;view=>resource_cost:alterresourcecostdba_Users,dba_profilesenableresourcelimitssql>altersystemsetresource_limit=true;第九章:Managingusers1.createauser:databaseauthenticationsql>createuserjunckyidentifiedbyoracledefaulttablespaceuserssql>temporarytablespacetempquota10mondatapasswordexpiresql>[accountlock|unlock][profileprofilename|default];2.changeuserquotaontablespacesql>alteruserjunckyquota0onusers;3.dropausersql>dropuserjuncky[cascade];4.monitoruserview:dba_users,dba_ts_quotas第十章:managingprivileges1.systemprivileges:view=>system_privilege_map,dba_sys_privs,session_privs2.grantsystemprivilegesql>grantcreatesession,createtabletomanagers;sql>grantcreatesessiontoscottwithadminoption;withadminoptioncangrantorrevokeprivilegefromanyuserorrole;3.sysdbaandsysoperprivileges:sysoper:startup,shutdown,alterdatabaseopen|mount,alterdatabasebackupcontrolfile,altertablespacebegin/endbackup,recoverdatabasealterdatabasearchivelog,restrictedsessionsysdba:sysoperprivilegeswithadminoption,createdatabase,recoverdatabaseuntil4.passwordfilemembers:view:=>v$pwfile_users5.O7_dictionary_accessibility=truerestrictionaccesstoviewortablesinotherschema6.revokesystemprivilegesql>revokecreatetablefromkaren;sql>revokecreatesessionfromscott;7.grantobjectprivilegesql>grantexecuteondbms_pipetopublic;sql>grantupdate(first_name,salaryonemployeetokarenwithgrantoption;8.displayobjectprivilege:view=>dba_tab_privs,dba_col_privs9.revokeobjectprivilegesql>revokeexecuteondbms_pipefromscott[cascadeconstraints];10.auditrecordview:=>sys.aud$11.protectingtheaudittrailsql>auditdeleteonsys.aud$byaccess;12.statementauditingsql>audituser;13.privilegeauditingsql>auditselectanytablebysummitbyaccess;14.schemaobjectauditingsql>auditlockonsummit.employeebyaccesswheneversuccessful;15.viewauditoption:view=>all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts16.viewauditresult:view=>dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement第十一章:managerrole1.createrolessql>createrolesales_clerk;sql>createrolehr_clerkidentifiedbybonus;sql>createrolehr_manageridentifiedexternally;2.modifyrolesql>alterrolesales_clerkidentifiedbycommission;sql>alterrolehr_clerkidentifiedexternally;sql>alterrolehr_managernotidentified;3.assigningrolessql>grantsales_clerktoscott;sql>granthr_clerktohr_manager;sql>granthr_managertoscottwithadminoption;4.establishdefaultrolesql>alteruserscottdefaultrolehr_clerk,sales_clerk;sql>alteruserscottdefaultroleall;sql>alteruserscottdefaultroleallexcepthr_clerk;sql>alteruserscottdefaultrolenone;5.enableanddisablerolessql>setrolehr_clerk;sql>setrolesales_clerkidentifiedbycommission;sql>setroleallexceptsales_clerk;sql>setrolenone;6.removerolefromusersql>revokesales_clerkfromscott;sql>revokehr_managerfrompublic;7.removerolesql>droprolehr_manager;8.displayroleinformationview:=>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles第十二章:BACKUPandRECOVERYv$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastatRmanneedsetdbwr_IO_slavesorbackup_tape_IO_slavesandlarge_pool_sizeMonitoringParallelRollbackv$fast_start_servers,v$fast_start_transactions4.performacloseddatabasebackup(noarchivelogshutdownimmediatecpfiles/backup/startup5.restoretoadifferentlocationconnectsystem/managerassysdbastartupmountalterdatabaserenamefile'/disk1/../user.dbf'to'/disk2/../user.dbf';alterdatabaseopen;6.recoversyntax--recoveramounteddatabase>recoverdatabase;>recoverdatafile'/disk1/data/df2.dbf';>alterdatabaserecoverdatabase;--recoveranopeneddatabase>recovertablespaceuser_data;>recoverdatafile2;>alterdatabaserecoverdatafile2;7.howtoapplyredologfilesautomatically>setautorecoveryon>recoverautomaticdatafile4;8.completerecovery:--method1(mounteddatabae>copyc:\backup\user.dbfc:\oradata\user.dbf>startupmount>recoverdatafile'c:\oradata\user.dbf;>alterdatabaseopen;--method2(openeddatabase,initiallyopened,notsystemorrollbackdatafile>copyc:\backup\user.dbfc:\oradata\user.dbf(altertablespaceoffline>recoverdatafile'c:\oradata\user.dbf'or>recovertablespaceuser_data;>alterdatabasedatafile'c:\oradata\user.dbf'onlineor>altertablespaceuser_dataonline;--method3(openeddatabase,initiallyclosedn