oracledba應(yīng)該熟悉令1： 2：請(qǐng)輸令：dc記住輸入用戶名的時(shí)候帶上表空間名（數(shù)據(jù)庫(kù)名2009-06-29SQLPLUS以O(shè)S的默認(rèn)連/asshowSQLPLUSsqlplus在命令提示符以O(shè)S連conn/as以SYSTEM的連connsystem/xxxxxxx@select*fromselectusername,account_statusfrom將SCOTT帳號(hào)(加鎖alteruserscottaccount以SCOTT的連接并且查看所屬connscott@服務(wù)名/tigerselect*fromtab;EMPdesc selectempno,enamefrom以O(shè)S的登看SGA,共享池,CACHE的信connect/assysdbashowsgaselectname,value/1024/1024fromv$sga;showparametershared_pool_sizeselectvalue/1024/1024fromv$parameterwhereshowparameterselectvalue/1024/1024fromv$parameterwherenameSIZEshowparametersizebitmap_merge_area_sizeintegercreate_bitmap_area_sizeintegerdb_16k_cache_sizebiginteger0db_2k_cache_sizebiginteger0db_32k_cache_sizebiginteger0db_4k_cache_sizebiginteger0db_8k_cache_sizebiginteger0db_block_sizeinteger4096db_cache_sizebigintegerdb_keep_cache_sizebiginteger0db_recycle_cache_sizebiginteger0NAMETYPEVALUEglobal_context_pool_sizestringhash_area_sizeintegerjava_max_sessionspace_sizeinteger0java_pool_sizebigintegerlarge_pool_sizebigintegermax_dump_file_sizestringUNLIMITEDobject_cache_max_size_percentintegerobject_cache_optimal_sizeinteger102400olap_page_pool_sizeintegeroracle_trace_collection_sizeintegerparallel_execution_message_sizeinteger2148NAMETYPEVALUEsga_max_sizebigintegershared_pool__sizebigintegershared_pool_sizebigintegersort_area_retained_sizeinteger0sort_area_sizeinteger524288workarea_size_stringAUTOSGAselect*fromv$sgastat;POOLNAMEBYTESfixed_sga453532log_buffer656384sharedpoolsubheapsharedpoolKGKheap3756sharedpoolKQRMPO586792sharedpoolKQRSPO180232sharedpoolKQRSSO5128sharedpoolsessions410720sharedpoolsqlareasharedpool1MbufferPOOLNAMEBYTESsharedpoolKGLSheap901756sharedpoolparameters8352sharedpoolmemorysharedpoolPL/SQLDIANA420816sharedpoolFileOpenBlock695504sharedpoolPL/SQLMPCODE135692sharedpoollibrarycachesharedpoolmiscellaneoussharedpoolMTTRadvisory21164sharedpoolPLSnon-libhp2068sharedpoolXDBSchemaCacPOOLNAMEsharedpooljoxsheapinit4220sharedpoolkglsimulator563260sharedpoolsimmemoryhea44184sharedpooltabledefiniti1728sharedpooltriggerdefini1896sharedpooltriggerinform1140sharedpooltriggersource448sharedpooltypeobjectde69120sharedpoolCheckpointqueue282304sharedpoolVIRTUALCIRCUITS265160sharedpooldictionarycachePOOLNAMEsharedpoolKSXRreceivesharedpoolcharactersetobject323724sharedpoolFileIdentificatonBlock323292sharedpoolmessagepoolqueue834752sharedpoolKSXRpendingmessagesque841036sharedpooleventstatisticspersesssharedpoolfixedallocationcallback180largepoolmemoryjavapool42PGAselect*fromv$pgastat;NAMEVALUEUNITaggregatePGAtargetparameter aggregatePGAautotarget globalmemorybound838656bytestotalPGAinuse totalPGAallocated umPGAallocated totalablePGAmemory0bytesPGAmemorydbacktoOS0bytestotalPGAusedforautoworkareas0umPGAusedforautoworkareas4096bytestotalPGAusedformanualworkareas0bytesNAMEVALUEUNITumPGAusedformanualworkareas4096bytesoverallocationcount0bytesprocessed extrabytesread/written0bytescachehitpercentage100percent16在$ORACLE_HOME/sqlplus/admin/glogin.sqldefine_editor=visetline2000savec:a.sql將指定文件讀出緩沖get執(zhí)行語(yǔ)@spoolselect*fromv$sga;spooloffsetlinesizesetpagesizecolenameformatcolsalformat10G$ORACLE_HOME/install/protlist9i$ORACLE_HOME/Apache/Apache/ports.ini啟動(dòng)Oracle9i程O(píng)racle的程序主要是為客戶端的連接提供接$lsnrctl關(guān)閉Oracle9i程$lsnrctlOracleWeb$cd$/database/oracle/product/9i/Apache/Apache/bin/apachectlstart:httpdOracleWeb$cd$/database/oracle/product/9i/Apache/Apache/bin/apachectlstop:httpd啟動(dòng)OracleWebServer后默認(rèn)的端是7777cdhtpasswd$ORACLE_HOME/sqlplus/admin/iplusdba.pwadmincdhtpasswdD:oracleora92/sqlplus/admin/iplusdba.pwadminNewpassword:*****Re-typenewpassword:*****Addingpasswordforuseradminpfile:$ORACLE_BASE/admin/實(shí)例名/Pfilespfile:$ORACLE_HOME/dbs器配置文件:listener.ora:$ORACLE_HOME/network/admin/tnsnames.ora:$ORACLE_HOME/network/admin/SELECTDEFAULT_TABLESPACEFROMDBA_USERSWHEREUSERNAME=selectuserfromdual;showSELECTDEFAULT_TABLESPACEFROMDBA_USERSWHEREUSERNAME=(selectfromshowparameterspfileNAMETYPEVALUEspfilestring%ORACLE_HOME%DATABASESPFILE%SPfilecreatepfile='c:init.ora'fromspfile;createpfilefromspfile;UNIX將生成在 WINDOWS將生成在 cacheshowparameterselect*fromv$parameter;select*fromv$spparameter;descselect*fromv$parameterwherenamelikealtersystemsetsga_max_size=256mscope=spfile;&&SPFILE,重啟altersystemsetsga_max_size=256mscope=memory;&&寫(xiě)入內(nèi)存區(qū),立即生altersystemsetsga_max_size=256mscope=both;&&即寫(xiě)入內(nèi)存區(qū),又寫(xiě)入SPFILE,BOTH.large_pool_sizealtersystemsetSGAselectsum(bytes)/1024/1024fromdb_cache_sizealtersystemsetdb_cache_size=128mshared_pool_sizeSPFILEaltersystemsetshared_pool_size=80mPGAselect*fromv$pgastat;showparameterpgaNAMETYPEpga_aggregate_targetbigPFILEORACLEstartup顯示當(dāng)前數(shù)據(jù)庫(kù)的狀態(tài),mountselectopen_modefrom顯示當(dāng)前實(shí)例nomountselect*frombackground_dump_destshowparameterbackground_dump_destNAMETYPEVALUEbackground_dump_deststringselect*from顯示當(dāng)有用戶可以select*fromSCOTTselect*fromdba_tableswhereselect*from建立用戶并指定createuseredpthwidentifiedby給用戶grantconnect,resourcetocreatetablea(aUSERselecttable_namefromdictwheretable_namelikeUNIXoerroraunixgrepORAalert_PROD.log|tail-SELECT*FROMSELECT*FROMORACLRSELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROMSELECT*FROM顯示當(dāng)前進(jìn)SELECT*FROMSGASELECT*FROMSGASELECT*FROMPGASELECT*FROMSHOWPARAMETERNOMOUNTaltersystemsetcontrol_files='D:ORADATAMYDBCONTROL01.CTL','E:CONTROL04.CTL'SCOPE=SPFILE;altersystemsetcontrol_files='D:oracleoradatathwerpCONTROL01.CTL','D:oracleoradatathwerpCONTROL04.CTL'scope=spfile;STARTUPSELECT*FROMV$CONTROLFILE;SHOWPARAMETERCONTROLaltersystemsetcontrol_files='D:ORADATAMYDBCONTROL01.CTL','D:ORADATAMYDBCONTROL03.CTL'SELECT*FROMARCHIVELOGSELECT*FROMALTERSYSTEMSWITCHALTERSYSTEMselect*fromaltersessionsetnls_date_format='yyyy-mm-ddALTERDATABASEADDLOGFILEGROUP4('D:ORADATAMYDBredo4.log')SIZE92ALTERDATABASEDROPLOGFILEGROUP4ALTERDATABASEADDLOGFILEMEMBER'D:ORADATAMYDBredo11.log'TOGROUPALTERDATABASEDROPLOGFILEMEMBER'D:ORADATAMYDBredo14.log'select*fromselect*fromselect*fromselecttablespace_name,sum(bytes)/1024/1024mfromdba_data_filesgroupbyselecttablespace_name,sum(bytes)/1024/1024mfromdba__spacegroupby10GCREATEBIGFILETABLESPACE"MYTBS1"DATAFILE'D:ORADATAMYDBmytbs_01.dbf'SIZE100MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;9ICREATETABLESPACE"MYTBS1"'D:oracleORADATAthwerpmytbs_01.dbf'SIZE100MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;CREATESMALLFILETABLESPACE"MYTBS1"DATAFILE'D:ORADATAMYDBmytbs_01.dbf'SIZE100M,'D:ORADATAMYDBmytbs02.dbf'SIZE100MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTcreatetablet1(aint)tablespacecreateindext1_indont1(a)tablespaceALTERTABLESPACE"MYTBS1"ADDDATAFILE'D:ORADATAMYDBmytbs03.dbf'SIZE100MALTERDATABASEDATAFILE'D:ORADATAMYDBMYTBS_01.DBF'RESIZEALTERDATABASEDATAFILE'D:ORADATAMYDBMYTBS03.DBF'AUTOEXTENDONNEXT5MMAXSIZEUNLIMITEDdroptablespacemytbs1includingcontentsandCREATESMALLFILETEMPORARYTABLESPACE"MYTEMP"'D:oracleORADATAthwerpmytemp01.dbf'SIZE200MAUTOEXTENDONNEXT10MMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCALUNIFORMSIZE1MCREATETEMPORARYTABLESPACE"MYTEMP"'D:oracleORADATAthwerpmytemp01.dbf'SIZE200MAUTOEXTENDONNEXT10MMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCALUNIFORMSIZE1MALTERUSER"GARY"TEMPORARYTABLESPACEALTERDATABASEDEFAULTTEMPORARYTABLESPACEALTERTABLESPACEMYTEMPTABLESPACEGROUPALTERTABLESPACETEMPTABLESPACEGROUPCREATEUNDOTABLESPACE"MYUNDO"DATAFILE'D:ORADATAMYDBmyundo01.dbf'SIZE200Mshowparameterselect*fromaltersystemsetcreatetableatablespaceusersasselect*frominsertintoaselect*fromselect*fromdba_tableswheretable_name='A'andselect*fromdba_segmentswheresegment_name='A'andselectdistinctsegment_typefrom查詢表空間的對(duì)應(yīng)關(guān)select*fromdba_extentswheresegment_name='A'and16KCREATETABLESPACE"MYTBS3"'D:oracleORADATAthwerpmytbs3.dbf'SIZE100MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOBLOCKSIZE16384;createtableb(aint,bvarchar2(10))tablespaceusersINITRANS2PCT5;showparameteraltertableadeallocatetruncatetablealtertableaallocateextent(datafile'D:ORADATAMYDBusers01.dbf'size1m);createtableC(aint)tablespacesystempct10pctused60storage(lists2);CREATETABLE"SYS"."D"("A"VARCHAR2(10))TABLESPACE"SYSTEM"PCT5PCTUSED60INITRANS2MAXTRANS100STORAGE(LISTS2)createtableatablespaceusersasselect*fromselectrowidform用包令查看表行的物理地OWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)FROMACREATEINDEXA_INDONA(OBJECT_NAME)TABLESPACESELECt*FROMDBA_INDEXESWHERESELECT*FROMDBA_SEGMENTSWHEREALTERTABLEAMOVETABLESPACEALTERINDEXA_INDALTERTABLEAENABLEROWALTERTABLEASHRINK查看表所的信SELECTBYTESFORMDBA_SEGMENTSWHERESEGMENT_NAME='A';SELECTOWNER,SEGMENT_NAME,BYTESFROMDBA_SEGMENTSWHERETRUNCATETABLEDROPTABLEACASCADE顯示桶(10G才有)showrecyclebinSELECT*FROMDBA_RECYCLEBIN;SELECT*FROMflashbacktableatobeforeflashbacktable"BIN$hWrBq1irS0COp10n22NZJg==$0"tobeforedroprenametoa1;select*fromdba_indexswhere清除PURGERECYCLEBIN;PURGEDBA_RECYCLEBIN;刪除表并清除桶(10G)DROPTABLEAPURGE;ALTERTABLEADROPCOLUMNCOMMENTSCASCAGECONSTRAINTESCHECKPOINTALTERTABLEARENAMECOLUMNHIRE_DATETOALTERTABLEASETUNUSEDCOLUMNCOMMENTSCASCADEALTERTABLEADROPUNUSEDCOLUMNSCHECKPOINTALTERTABLEADROPCOLUMNSCONTINUECHECKPOINTALTERTABLEAADDPRIMARYKEYALTERTABLEADROPPRIMARYALTERTABLEAADDPRIMARYKEY(OBJECT_ID)USINGINDEXTABLESPACECREATEINDEXA_NAME_INDONSELECT/*+INDEX(AA_NAME2_IND)*/*FROMAWHERECREATEBITMAPINDEXA_NAME_BTONSELECT/*+INDEX(AA_NAME_BT)*/*FROMAWHEREOWNER='SYS'ORSELECT/*+FULL(A)*/*FROMAWHEREOWNER='SYS'OR生成一個(gè)來(lái)建立索引重SELECT'ALTERINDEX'||INDEX_NAME||'COALESCE;'FROMDBA_INDEXESWHERETABLE_NAME='A'ANDOWNER='SYS'SELECT'ALTERINDEX'||INDEX_NAME||'REBUILDONLINE;'FROMDBA_INDEXESWHERETABLE_NAME='A'ANDOWNER='SYS'SELECT*FROMUSERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEFROMcreateuseredpthwidentifiedby"123456"defaulttablespaceuserstemporarytablespacetempquotaunlimitedonusersquota10monmytbs2;select*fromdba_ts_quotas;select*from用戶改alteruseredpthwidentifiedbyalteruseredpthwaccount使過(guò)期,迫使改下一次進(jìn)入改alteruseredpthwpasswordalteruseredpthwquotaunlimitedondropuserhmtong167用grantcreate 分別GRANTCREATEANYTABLETO"hmtong"GRANTCREATETABLETO"hmtong"GRANTDROPANYTABLETO"hmtong"GRANTSELECTANYTABLETO"hmtong"折消REVOKEUNLIMITEDTABLESPACEFROM對(duì)象GRANTSELECTON"hmtong"."A"TO折消對(duì)象REVOKESELECTON"hmtong"."A"FROM取消進(jìn)入系統(tǒng)直接用默認(rèn),而一定要自己輸入才可以進(jìn)unixwindowsD:oracleproduct10.1.0Db_1NETWORKADMINSQLNET.ORASQLNET.AUTHENTICATION_SERVICES=(NTS,NONE)修改$ORACLE_HOME/network/admin/sqlnet.ora將原有的：SQLNET.AUTHENTICATION_SERVICES=改為:SQLNET.AUTHENTICATION_SERVICES=或者直接注釋：#SQLNET.AUTHENTICATION_SERVICES=Pwfileselect*from174sysdba給指定用grantsysdbatosysdbarevokesysdbafrom修改系統(tǒng) 的屬altersystemsetremote_login_passwordfile=exclusive;sysdba能altersystemsetremote_login_passwordfile=sharedscope=spfile;sysdbaaltersystemsetremote_login_passwordfile=NONE;orapwdfile=PWDmydb.oracreateuserhmtongidentifiedby179連接給指定用GRANT"CONNECT"TOGRANTUNLIMITEDTABLESPACE,CONNECT,RESOURCETO180createtablet1(aint);createtablet2(a181查看系統(tǒng)select*from182查看對(duì)象select*from183select*from184select*from185select*from186select*from187器的狀lsnrctl188器的啟動(dòng)和關(guān)閉lsnrctlstart[名字]lsnrctlstop[名字]189器的狀態(tài)和服務(wù)信lsnrctlstatlsnrctlservice190配置器的工動(dòng)態(tài)altersystem配置器的兩個(gè)參showparameterlocal_listenershowparameteraltersystemset檢查指定的器是否存TNSSQLNET.ORAconnectsystem/testdb1@0:1521/testdb1SQLNET.AUTHENTICATION_SERVICES=(NTS,NONE)NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)shutdownmountstartuparchivelog自動(dòng)存檔禁用存檔終點(diǎn)最早的概要日志序列下一個(gè)存檔日志序列當(dāng)前日志序列mountalterdatabasearchivelog;alterdatabasenoarchivelog;alterdatabaseSQL>showparameter動(dòng)態(tài)的配置歸檔路徑在兩個(gè)不同 下同時(shí)歸altersystemsetlog_archive_dest_1='location=e:archive';altersystemsetaltersystemswitchlogfile;(10Galtersystemsetlog_archive_start=truescope=spfile;(9i)(10Gselect*fromselect*fromselect*from2altersystemsetlog_archive_dest_state_2=defer;altersystemsetshowparametershowparameteraltersystemsetlog_archive_format='mydb_%s_%r_%t.log'showparametershowparameteraltersystemsetaltersystemsetselect*fromaltersystemsetselectlog_modefromv$database;archiveloglistshowparameterlog_archive_destselect*fromv$archive_dest;select*fromselect*from啟動(dòng)備份表空altertablespaceusersbeginselectfile_namefromdba_data_fileswhereSQLShostocopyuser*.dbfe:backupaltertablespaceusersendalterdatabasebackupcontrolfileto生成創(chuàng)建控制文件的alterdatabasebackupcontrolfiletotraceselect*fromselect*fromv$datafilewhereselect*fromrecoverdatafilecreatetablea11tablespacemytbs3asselect*from233select*fromdba_extentswhererecoverAUTOMATICdatafileALTERTABLESPACEUSERSOFFLINERECOVERTABLESPACEALTERDATABASEDATAFILE6將指定的表文件設(shè)置成ALTERDATABASEDATAFILE6CREATETABLETEST(AINSERTINTOTESTTESTSELECT*FROM在表里的設(shè)置日期格NLS_DATE_FORMAT=YYYY-MM-DDRECOVERDATABASEUNTILTIME'2006-11-26alterdatabaseopenRECOVERDATABASEusingbackupcontrolfileUNTILaltertablespacetempaddtempfile'd:oradatamydbtemp01.dbf'size10mreuse;RmanconnecttargetreportRMANrunallocatechannelch1typediskformat'e:rman%U.bak'backup}在RMAN的顯者默認(rèn)設(shè)showbackupCONFIGURECHANNELDEVICETYPEDISKFORMAT'E:RMAN%U.BAK'MAXPIECESIZE2G;CONFIGURECHANNELDEVICEtypeDISKCONFIGUREDEVICETYPEDISKPARALLELISMCONFIGURECONTROLFILEAUTOBACKUPCONFIGURERETENTIONTOREDUNDANCY7CONFIGURERETENTIONTOrecoverywindowofbackuptablespacebackupdatafilebackuparchiveloguntiltime'sysdate-SPbackupbackupcurrentlistbackupsetlistbackupsetsummary;254列出具體的備份信息listbackupsetdeletebackupsetdeletenopromptUSERSdeletebackupoftablespacereportobsoletebackupascompressedbackupset0backupincrementallevel0tablespace2backupincrementallevel2cumulativetablespaceDELETEARCHIVELOGUNTILTIME'SYSDATE-RMANRUNSQL'ALTERT