课件oracle命令大集合

021.1打开和关闭数据库(手工)1.1.1sqlplus22oracle2oracle222.3冷备 3tar命令做物理备份021.1打开和关闭数据库(手工)1.1.1sqlplus22oracle2oracle222.3冷备 3tar命令做物理备份4联机全备份+日志备份5666 2.5.4注意要 3.数据库的扩充3.1增加一个表空 3.1.1创建表空间83.1.2增加某个表空间的大 3.2增加oracle9oracle9增加oracle的角2.5.4注意要 3.数据库的扩充3.1增加一个表空 3.1.1创建表空间83.1.2增加某个表空间的大 3.2增加oracle9oracle9增加oracle的角 表和视 管理 管理视 3.3.2索 3.3.3序列（Sequences）创建序列删除序列3.4网络管理3.4.1 server启动3.4.2Net3.3.3序列（Sequences）创建序列删除序列3.4网络管理3.4.1 server启动3.4.2Net8client端配 .43.4.5常见NET8错误 4.1本地化管理表空 4.2FBI索引4.3在线索引创建和重 sqlplus#su- sqlplus#su- 1.1.2打开数据库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关闭数据库sql>connectsys/oracleassqlshutdowndatabasedatabaseoracleinstancesshutdown，oracleSQLshutdownabortSQLshutdownimmediate做回滚*SQLshutdownnormalshutdown缺省参数是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数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创建表空间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,只有数据库管理员才有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删除数据列法ALTERTABLE[schema.]table_name;.4.3删除数据列法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管理视图视图是一个或多个表中的数据的简化描述，用户可以将视图看成一个存储查询.1建立视图FORUPDATEORACLE8iORDERBYCREATEVIEWORDERBYSQL>CREATEVIEWTOP_EMPFROMWHEREsalaryORREPLACESQL>CREATEVIEWSELECTempno,enameFROMWHERESQL>CREATEVIEWSELECTempno,enameFROMWHEREsalarySELECT*FROMwarning:Viewcreatewithcompilation以..3那么在一个时间里只能更新一 表。所有的能被更新的列可以在数据字CREATEVIEWWITH那么在一个时间里只能更新一 表。所有的能被更新的列可以在数据字CREATEVIEWWITHWITHREADONLY如在上面的例子中用户创建了一个视图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序列创建序列SETSEQUENCECREATESEQUENCE[schema]sequenceKEYWORD定义序列生成的第一个数字，缺省为INCREMENT创建序列SETSEQUENCECREATESEQUENCE[schema]sequenceKEYWORD定义序列生成的第一个数字，缺省为INCREMENTINCREMENTBY值升序为1，对于降序为-复缺省值为删除序列删除序列使用DROPSEQUENCEDROPSEQUENCE缺省值为删除序列删除序列使用DROPSEQUENCEDROPSEQUENCE3.4网络管理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、选本地->概要文件加入￠ 等参数 nttest=)) 等参数 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)参数或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指由于这种表空间的以上特性，所以它支持在一个表空间里边进行更多的并发*作，并减由于这种表空间的以上特性，所以它支持在一个表空间里边进行更多的并发*作，并减 DBA在SQL语句调优的过程中遇到的一个很常见的问题就是，如何优化那些在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