課件oracle命令大集合

021.1打開和關閉數(shù)據(jù)庫(手工)1.1.1sqlplus22oracle2oracle222.3冷備 3tar命令做物理備份021.1打開和關閉數(shù)據(jù)庫(手工)1.1.1sqlplus22oracle2oracle222.3冷備 3tar命令做物理備份4聯(lián)機全備份+日志備份5666 2.5.4注意要 3.數(shù)據(jù)庫的擴充3.1增加一個表空 3.1.1創(chuàng)建表空間83.1.2增加某個表空間的大 3.2增加oracle9oracle9增加oracle的角2.5.4注意要 3.數(shù)據(jù)庫的擴充3.1增加一個表空 3.1.1創(chuàng)建表空間83.1.2增加某個表空間的大 3.2增加oracle9oracle9增加oracle的角 表和視 管理 管理視 3.3.2索 3.3.3序列（Sequences）創(chuàng)建序列刪除序列3.4網(wǎng)絡管理3.4.1 server啟動3.4.2Net3.3.3序列（Sequences）創(chuàng)建序列刪除序列3.4網(wǎng)絡管理3.4.1 server啟動3.4.2Net8client端配 .43.4.5常見NET8錯誤 4.1本地化管理表空 4.2FBI索引4.3在線索引創(chuàng)建和重 sqlplus#su- sqlplus#su- 1.1.2打開數(shù)據(jù)庫sql>connectsys/oracleassql>startuporacleinstancesdatabasestartupSQLstartupnomount僅啟動SQLstartupmount啟動instances,連上(mountSQLstartupnormal啟動instances,連上(mountstartup1.1.3啟動oracle#su-#lsnrctl1.1.4關閉oraclestartup1.1.3啟動oracle#su-#lsnrctl1.1.4關閉oracle#su-#lsnrctl1.1.5關閉數(shù)據(jù)庫sql>connectsys/oracleassqlshutdowndatabasedatabaseoracleinstancesshutdown，oracleSQLshutdownabortSQLshutdownimmediate做回滾*SQLshutdownnormalshutdown缺省參數(shù)是2.2.1冷備份2.2用exp、imp有三種主要的方式（完全、用戶、表expsystem/managerbuffer=640002.1冷備份2.2用exp、imp有三種主要的方式（完全、用戶、表expsystem/managerbuffer=64000file=/backup/full.dmpEXP_FULL_DATABASEexpsystem/managerbuffer=64000file=/backup/bill.dmp具有三種模式（完全、用戶、表impsystem/managerbuffer=64000file=/backup/full.dmp具有三種模式（完全、用戶、表impsystem/managerbuffer=64000file=/backup/full.dmp2billbillbakKfromuser、touser。 文件中用戶billareabillbak2.3用tar1、#su2tarcvf/dev/rmt03、%tarxvforacle4tartvf2.4（最好在不同的磁盤上oracleoracle4tartvf2.4（最好在不同的磁盤上oracleSQL>connectSQL>alterdatabasebackupcontrolfile SQL>alterdatabasebackupcontrolfileto設置（ARCHIVEOG數(shù)sql>connectsql>startupsql>connectsql>startupsql>alterdatabase5.2.5.2步驟sqlplus"/asSQL>altertablespacesystembegintablespace各個sqlplus"/asSQL>altertablespacesystembegintablespace各個SQL>altertablespacesystemendSQL>alterdatabasebackupcontrolfile…….sqlplus"/asSQL>altersystemarchivelogSQL>altersystemarchivelog oracleinitSid.oracontrolfilesqlplus"/asSQL>startupSQL>recoverdatabaseuntilcancelusingbackupsqlplus"/asSQL>startupSQL>recoverdatabaseuntilcancelusingbackupSQL>alterdatabaseopensqlplus"/asSQL>startupSQL>alterdatabaserecover SQL>alterdatabasesqlplus"/asSQL>connectSQL>startupSQL>alterdatabase SQL>alterdatabase2.5.4注意要點1.rollback21.rollback2sqlplus"/asSQL>startupSQL>alterdatabase.1創(chuàng)建表空間SQL>createtablespaceapplicationsizeSQL>createrollbacksegmentrbs8tablespacerbsstorage(initial1mnextSQL>createrollbacksegmentrbs8tablespacerbsstorage(initial1mnextDATAFILE"/usr/oracle/dbs/temp.dbf"SIZE32MAUTOEXTENDONNEXT MINIMUMEXTENTINITIALNEXTMINEXTENTSMAXEXTENTSPCTINCREASESQL>droptablespaceSQL>droptablespacesSQL>altertablespacerbsSQL>altertablespacebilltemp3.1.2SQL>altertablespaceadd size3.2oracle3.2.1增加oracleSQL>createusernewuseridentifiedbydefaulttablespace3.2oracle3.2.1增加oracleSQL>createusernewuseridentifiedbydefaulttablespacetemporarytablespaceSQL>grantconnecttoSQL>grantresourcetoSQL>grantupdateonempto3.2.2增加oracleoracleconnect、resource、dba的權限總和，connect有8個權限，resource有5個權限，dba77connectconnectresource,只有數(shù)據(jù)庫管理員才有dba限。SQL>createrolenewroleidentifiedbySQL>grantselectonalltabletoSQL>grantconnecttonewrolewithadmin3.2.3oracleracle的連接。①、SQL>selectsid,serial#,usernamefrom②、SQL>altersystemkillracle的連接。①、SQL>selectsid,serial#,usernamefrom②、SQL>altersystemkill 表和視圖引入了臨管理表.1(PROD_IDPROD_NAME定項值為null，oracle定項值為null，oracle...........3SQL>CREATETABLEempASSELECT*FROMSQL>CREATETABLEempASSELECT*FROMSQL>CREATETABLEYASSELECT*FROMXWHERE.4更改表定義.4.1增加列語法：ALTERTABLE[schema.]table_nameADDALTERTABLEordersADDorder_date.4.2更改列ALTERTABLE[schema.]table_nameMODIFYcolumn_nameALTERTABLEordersMODITY(quantitynumber(10,3),status;.4.3刪除數(shù)據(jù)列法ALTERTABLE[schema.]table_name;.4.3刪除數(shù)據(jù)列法ALTERTABLE[schema.]table_nameDROP{COLUMcolumn_names(column_names)}[CASCADEALTERTABLE[schema.]table_nameSETUNUSED{COLUMcolumn_names(column_names)}[CASCADE.4.4DROPTABLE[schema.]table_name[CASCADERENAMEORACLE語法：RENAMEold_nameTO例：SQL>RENAMEordersTO.4.5截短表觸TRUNCATE{TABLE|CLUSTER}[schema.]name{DROP|REUSETRUNCATE{TABLE|CLUSTER}[schema.]name{DROP|REUSE管理視圖視圖是一個或多個表中的數(shù)據(jù)的簡化描述，用戶可以將視圖看成一個存儲查詢.1建立視圖FORUPDATEORACLE8iORDERBYCREATEVIEWORDERBYSQL>CREATEVIEWTOP_EMPFROMWHEREsalaryORREPLACESQL>CREATEVIEWSELECTempno,enameFROMWHERESQL>CREATEVIEWSELECTempno,enameFROMWHEREsalarySELECT*FROMwarning:Viewcreatewithcompilation以..3那么在一個時間里只能更新一 表。所有的能被更新的列可以在數(shù)據(jù)字CREATEVIEWWITH那么在一個時間里只能更新一 表。所有的能被更新的列可以在數(shù)據(jù)字CREATEVIEWWITHWITHREADONLY如在上面的例子中用戶創(chuàng)建了一個視圖TOP_EMP,在這個視圖中用戶不能插入salary小于.4刪除視圖DROPVIEW同例：DROPVIEW3.3.2索引索引（基于一列）或連接的（多列。B-Tree索引（基于一列）或連接的（多列。B-Tree的，由枝干塊（branchblock)和樹葉塊（leafblock)組成,枝干塊包含了索引列（CREATEUNIQUEINDEXONscott.ord(ord_no)PCTFREESTORAGE(INITIAL200KNEXTPCTINCREASEMAXEXTENTS位圖索引單列低到中群集（cardinality)AND或OR*TRUE（1、或NULLB-TreeB-Tree非CREATEBITMAPINDEXONPCTFREE、或NULLB-TreeB-Tree非CREATEBITMAPINDEXONPCTFREESTORAGE(INITIAL200KNEXTPCTINCREASEMAXEXTENTS修改索引ALTERINDEXALLOCATEEXTENT(SIZEDATAFILEDISK6/indx01.dbfALTERINDEXscott.ord_region_id_idx3.3.3序列創(chuàng)建序列SETSEQUENCECREATESEQUENCE[schema]sequenceKEYWORD定義序列生成的第一個數(shù)字，缺省為INCREMENT創(chuàng)建序列SETSEQUENCECREATESEQUENCE[schema]sequenceKEYWORD定義序列生成的第一個數(shù)字，缺省為INCREMENTINCREMENTBY值升序為1，對于降序為-復缺省值為刪除序列刪除序列使用DROPSEQUENCEDROPSEQUENCE缺省值為刪除序列刪除序列使用DROPSEQUENCEDROPSEQUENCE3.4網(wǎng)絡管理3.4.1 serverCONNECT_TIMEOUT_LISTENER=LOGGING_LISTENER=LISTENER(DESCRIPTION_LIST(DESCRIPTION#Local(ADDRESS_LIST(ADDRESS=(PROTOCOL=IPC)(KEY=))(DESCRIPTION#GeneralTCP(ADDRESS(PROTOCOL=TCP)(HOST=dbsvr)(PORT=))(DESCRIPTION#GeneralTCP(ADDRESS(PROTOCOL=TCP)(HOST=dbsvr)(PORT=))#ForsecureconnectionsoverSSL,uncommentthefollowing##(DESCRIPTION#SecureTCP#(ADDRESS#(PROTOCOL=TCPS)(HOST=dbsvr)(PORT=#)#)(DESCRIPTION#IIOP(SESSION=)(ADDRESS=(PROTOCOL=TCP)(HOST=dbsvr)(PORT=)#ForsecureIIOPconnectionsoverSSL,uncommentthefollowing##(DESCRIPTION#Secure)#ForsecureIIOPconnectionsoverSSL,uncommentthefollowing##(DESCRIPTION#SecureIIOP###(SESSION=#)#(ADDRESS=(PROTOCOL=TCPS)(HOST=dbsvr)(PORT=#))(SID_LIST(SID_DESC(GLOBAL_DBNAME=s450(ORACLE_HOME=(SID_NAME s450)(SID_DESC(ORACLE_HOME=(SID_NAME=(PROGRAM=))啟動1.>lsnrctl2.>lsnrctl(PROGRAM=))啟動1.>lsnrctl2.>lsnrctl3.4.2Net8client￠Net8clienttnsNetworkAdministration->Net81、選本地->概要文件加入￠ 等參數(shù) nttest=)) 等參數(shù) nttest=))).4ping1.sqlplussystem/manager2．在客戶端>Tnsping 端的er.ora1．PingserverIP3.4.5常見NET8錯誤任務1SQLPLUSORA-1017:invalidORA-1034:Oraclenot任務2:UNIX在：WindowsNTa.b.TNS_ADMINUNIX在：WindowsNTa.b.TNS_ADMIN使用Net8Assistant1.啟動Net8在UNIX,從$ORACLE_HOME/bin.目錄來運行NTchooseStartProgramsOracleHOME_NAMEAdministration>Net8在導航器擴展Directory或Local>Servicenetservicename或database選命令>TestNet客戶端診斷 UNIX,可以運行$ORACLE_HOME/binInstalledNet8TranportProtocolsUNIX,可以運行$ORACLE_HOME/binInstalledNet8TranportProtocols2.檢查所有Net83.$ORACLE_HOME/network/admin目錄下的和a.b.TNS_ADMINNTTNS_ADMINc.用FTPTELNETPING4.用TNSPING、TRCROUTE實用程序或Net8Assistant進行測試tnspingnet_service_namenetservicename:在tnsnames.ora或名字服務上得使用。如：NIS或DCEcount例1：spotdbtnspingTNSPingUtilityforCopyrightcount例1：spotdbtnspingTNSPingUtilityforCopyright(c)OracleCorporation1998.AllrightsAttemptingtoOK測試tnspingstprdTNSPingUtilityforCopyright(c)OracleCorporation1998.AllrightsAttemptingtocontactOK(290OK(100OK(70OK(70OK(60OK(70OK(70OK(80OK(180OK(340OK(70OK(70OK(80OK(180OK(3405*與OracleWorldwideSupport常見錯誤ORA-12154:TNS:couldnotresolveservice原因:在tnsnames.oraNet8措施:1.確定tnsnames.ora2.確定不要有多個3.確認tnsnames.ora4.確認不要有多個5.sqlnet.ora包含NAMES.DEFAULT_DOMAIN不6@ORA-12198:TNS:couldnot4.確認不要有多個5.sqlnet.ora包含NAMES.DEFAULT_DOMAIN不6@ORA-12198:TNS:couldnotfindpathtoORA-12203:TNS:unabletoconnecttotnsnames.oraADDRESSlsnrctl>statuslistener_name缺省lsnrctl>startTNS:unabletoconnecttolsnrctl>startTNS:unabletoconnectto目錄的。OracleUNIX$ORACLE_HOME/bin目錄下的adaptersInstalledNet8TranportProtocolsORA-12203HOST或SERVICE另外原因ORA-12203HOST或SERVICE另外原因 Net8在tnsnames.ora文件中沒有找到服務描述符lsnrctl>statuslsnrctl>start 確認tnsnames.oraORA-12203lsnrctl>statuslsnrctl>startORA-12203ORA-12203lsnrctl>statuslsnrctl>startORA-12203ORA-12203另外原因 tnsnames.ora文件沒有放對地方（目錄Action:確保tnsnames.oraORA-12203另外原因 TCP/IP地址(HOST=server_name)參數(shù)或SPX地址(SERVICE=tns_application)對于TCP/IPlistener.ora文件的HOST和tnsnames.ora對于SPXORA-12224:TNS:no TNS:illegalADDRESS原因：在tnsnames.ora文件的 TNS:illegalADDRESS原因：在tnsnames.ora文件的ORA-12545:TNS:namelookup1.tnsnames.ora文件的ADDRESS lsnrctl>statuslsnrctl>startTNS:protocoladapter1.ORA-3113:TNS:EndofTNS:protocoladapter1.ORA-3113:TNS:EndoffileoncommunicationORA-3121:Nointerfacedriverconnection-functionnot4.1Oracle8IManagedTablespace.Oracle8IOracle8I，OracleLocally4.1Oracle8IManagedTablespace.Oracle8IOracle8I，OracleLocallyredoOracle8I，OracleDATAFILE[EXTENTMANAGEMENT{DICTIONARY|{AUTOALLOCATE|UNIFORM[SIZEINTETER[K|M]]}}EXTENTMANGEMENTLOCAL若為DICTIONARY當選擇了AUTOALLOCATE還是UNIFORM.。若為AUTOALLOCATEOracleEXTENTMANGEMENTLOCAL若為DICTIONARY當選擇了AUTOALLOCATE還是UNIFORM.。若為AUTOALLOCATEOracle指由于這種表空間的以上特性，所以它支持在一個表空間里邊進行更多的并發(fā)*作，并減由于這種表空間的以上特性，所以它支持在一個表空間里邊進行更多的并發(fā)*作，并減 DBA在SQL語句調(diào)優(yōu)的過程中遇到的一個很常見的問題就是，如何優(yōu)化那些在SQL>createindexnon_fbionsale_contactsSQL>analyzeindexnon_fbicomputeSQL>createindexnon_fbionsale_contactsSQL>analyzeindexnon_fbicomputeSQL>:analyzetablesale_contactscomputeSQL>SELECTcount(*)FROMWHEREUPPER(surname) ELLISONExecution0SELECTSTATEMENTOptimizer=CHOOSE(Cost=3Card=110SORT21TABLEACCESS(FULL) (Cost=3的SQL>createindexfbionsale_contactsSQL>analyzeindexfbicomputeSQL>analyzetablesale_contactscomputeSQL>SELECTcount(*)FROMsale_contactsWHE