oracle_DBA常用指令

1最基本的oracledba常用命令1运行SQLPLUS工具sqlplus2以OS的默认身份连接/assysdba3显示当前用户名showuser4直接进入SQLPLUS命令提示符sqlplus/nolog5在命令提示符以OS身份连接connect/assysdba6以SYSTEM的身份连接connectsystem/xxxxxxx@服务名7显示当然用户有哪些表select*fromtab;8显示有用户名和帐户的状态selectusername,account_statusfromdba_users;9将SCOTT帐号解锁(加锁)alteruserscottaccountunlock(lock);10以SCOTT的身份连接并且查看所属表connectscott/tigerselect*fromtab;11查看EMP的表结构及记录内容descempselectempno,enamefromemp;12以OS的身份登看SGA,共享池,CACHE的信息connect/assysdbashowsgaselectname,value/1024/1024fromv$sga;showparametershared_pool_sizeselectvalue/1024/1024fromv$parameterwherename='shared_pool_size';showparameterdb_cache_sizeselectvalue/1024/1024fromv$parameterwherename='db_cache_size';13查看所有含有SIZE的信息showparametersizebitmap_merge_area_sizeinteger1048576create_bitmap_area_sizeinteger8388608db_16k_cache_sizebiginteger0db_2k_cache_sizebiginteger0db_32k_cache_sizebiginteger0db_4k_cache_sizebiginteger0db_8k_cache_sizebiginteger0db_block_sizeinteger4096db_cache_sizebiginteger33554432db_keep_cache_sizebiginteger0db_recycle_cache_sizebiginteger0NAMETYPEVALUE------------------------------------------------------------global_context_pool_sizestringhash_area_sizeinteger1048576java_max_sessionspace_sizeinteger0java_pool_sizebiginteger33554432large_pool_sizebiginteger8388608max_dump_file_sizestringUNLIMITEDobject_cache_max_size_percentinteger10object_cache_optimal_sizeinteger102400olap_page_pool_sizeinteger33554432oracle_trace_collection_sizeinteger5242880parallel_execution_message_sizeinteger2148NAMETYPEVALUE------------------------------------------------------------sga_max_sizebiginteger143727516shared_pool_reserved_sizebiginteger2516582shared_pool_sizebiginteger50331648sort_area_retained_sizeinteger0sort_area_sizeinteger524288workarea_size_policystringAUTO14显示SGA的信息select*fromv$sgastat;POOLNAMEBYTES-----------------------------------------------fixed_sga453532buffer_cache33554432log_buffer656384sharedpoolsubheap46884sharedpoolKGKheap3756sharedpoolKQRMPO586792sharedpoolKQRSPO180232sharedpoolKQRSSO5128sharedpoolsessions410720sharedpoolsqlarea2144664sharedpool1Mbuffer2098176POOLNAMEBYTES-----------------------------------------------sharedpoolKGLSheap901756sharedpoolparameters8352sharedpoolfreememory38687204sharedpoolPL/SQLDIANA420816sharedpoolFileOpenBlock695504sharedpoolPL/SQLMPCODE135692sharedpoollibrarycache2985576sharedpoolmiscellaneous4889396sharedpoolMTTRadvisory21164sharedpoolPLSnon-libhp2068sharedpoolXDBSchemaCac4966300POOLNAMEBYTES-----------------------------------------------sharedpooljoxsheapinit4220sharedpoolkglsimulator563260sharedpoolsimmemoryhea44184sharedpooltabledefiniti1728sharedpooltriggerdefini1896sharedpooltriggerinform.1140sharedpooltriggersource448sharedpooltypeobjectde69120sharedpoolCheckpointqueue282304sharedpoolVIRTUALCIRCUITS265160sharedpooldictionarycache1610880POOLNAMEBYTES-----------------------------------------------sharedpoolKSXRreceivebuffers1033000sharedpoolcharactersetobject323724sharedpoolFileIdentificatonBlock323292sharedpoolmessagepoolfreequeue834752sharedpoolKSXRpendingmessagesque841036sharedpooleventstatisticspersess1718360sharedpoolfixedallocationcallback180largepoolfreememory8388608javapoolfreememory33554432已选择42行。15显示PGA的信息select*fromv$pgastat;NAMEVALUEUNIT-----------------------------------------------------------------------------------aggregatePGAtargetparameter16777216bytesaggregatePGAautotarget7640064bytesglobalmemorybound838656bytestotalPGAinuse8293376bytestotalPGAallocated13106176bytesmaximumPGAallocated22090752bytestotalfreeablePGAmemory0bytesPGAmemoryfreedbacktoOS0bytestotalPGAusedforautoworkareas0bytesmaximumPGAusedforautoworkareas4096bytestotalPGAusedformanualworkareas0bytesNAMEVALUEUNIT-----------------------------------------------------------------------------------maximumPGAusedformanualworkareas4096bytesoverallocationcount0bytesprocessed8783872bytesextrabytesread/written0bytescachehitpercentage100percent已选择16行。17在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效define_editor=visetline200018将当前命令随加到文件中savec:a.sqlappend19将指定文件的命读出缓冲区getc:a.sql20执行脚本语句@c:a.sql21将输入保存到指定文件中spoolc:O.LOGselect*fromv$sga;spooloff22设定行大小setlinesize200023设定页大小setpagesize1024设定字符列格式colenameformata3025设定数字列格式colsalformat999,999.9992610G查看文件$ORACLE_HOME/install/protlist显示端口查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口://127.0.0.1:7778/isqlplusdba27启动Oracle9i监听程序Oracle的监听程序主要是为客户端的连接提供接口$lsnrctlstart28关闭Oracle9i监听程序$lsnrctlstop29启动OracleWebServer$cd$ORACLE_HOME/Apache/Apache/bin$./startJServ.sh/database/oracle/product/9i/Apache/Apache/bin/apachectlstart:httpdstarted30关闭OracleWebServer$cd$ORACLE_HOME/Apache/Apache/bin$./stopJServ.sh/database/oracle/product/9i/Apache/Apache/bin/apachectlstop:httpdstopped31启动OracleWebServer后默认的端口号是7777unixcd$ORACLE_HOME/Apache/Apache/bin/htpasswd$ORACLE_HOME/sqlplus/admin/iplusdba.pwadminwindowscdD:oracleora92ApacheApachebinhtpasswdD:oracleora92/sqlplus/admin/iplusdba.pwadminNewpassword:*****Re-typenewpassword:***