MySQL数据库工程师入门实战课程视频教程
4223 人在学
oracledba应该熟悉的命令
1运行SQLPLUS工具
sqlplus
2以OS的默认身份连接
/assysdba
3显示当前用户名
showuser
4直接进入SQLPLUS命令提示符
sqlplus/nolog
5在命令提示符以OS身份连接
connect/assysdba
6以SYSTEM的身份连接
connectsystem/xxxxxxx@服务名
7显示当然用户有哪些表
select*fromtab;
8显示有用户名和帐户的状态
selectusername,account_statusfromdba_users;
9将SCOTT帐号解锁(加锁)
alteruserscottaccountunlock(lock);
10以SCOTT的身份连接并且查看所属表
connectscott/tiger
select*fromtab;
11查看EMP的表结构及记录内容
descemp
selectempno,enamefromemp;
12以OS的身份登看SGA,共享池,CACHE的信息
connect/assysdba
showsga
selectname,value/1024/1024fromv$sga;
showparametershared_pool_size
selectvalue/1024/1024fromv$parameterwherename='shared_pool_size';
showparameterdb_cache_size
selectvalue/1024/1024fromv$parameterwherename='db_cache_size';
13查看所有含有SIZE的信息
showparametersize
bitmap_merge_area_sizeinteger1048576
create_bitmap_area_sizeinteger8388608
db_16k_cache_sizebiginteger0
db_2k_cache_sizebiginteger0
db_32k_cache_sizebiginteger0
db_4k_cache_sizebiginteger0
db_8k_cache_sizebiginteger0
db_block_sizeinteger4096
db_cache_sizebiginteger33554432
db_keep_cache_sizebiginteger0
db_recycle_cache_sizebiginteger0
NAMETYPEVALUE
------------------------------------------------------------
global_context_pool_sizestring
hash_area_sizeinteger1048576
java_max_sessionspace_sizeinteger0
java_pool_sizebiginteger33554432
large_pool_sizebiginteger8388608
max_dump_file_sizestringUNLIMITED
object_cache_max_size_percentinteger10
object_cache_optimal_sizeinteger102400
olap_page_pool_sizeinteger33554432
Oracle_trace_collection_sizeinteger5242880
parallel_execution_message_sizeinteger2148
NAMETYPEVALUE
------------------------------------------------------------
sga_max_sizebiginteger143727516
shared_pool_reserved_sizebiginteger2516582
shared_pool_sizebiginteger50331648
sort_area_retained_sizeinteger0
sort_area_sizeinteger524288
workarea_size_policystringAUTO
14显示SGA的信息
select*fromv$sgastat;
POOLNAMEBYTES
-----------------------------------------------
fixed_sga453532
buffer_cache33554432
log_buffer656384
sharedpoolsubheap46884
sharedpoolKGKheap3756
sharedpoolKQRMPO586792
sharedpoolKQRSPO180232
sharedpoolKQRSSO5128
sharedpoolsessions410720
sharedpoolsqlarea2144664
sharedpool1Mbuffer2098176
POOLNAMEBYTES
-----------------------------------------------
sharedpoolKGLSheap901756
sharedpoolparameters8352
sharedpoolfreememory38687204
sharedpoolPL/SQLDIANA420816
sharedpoolFileOpenBlock695504
sharedpoolPL/SQLMPCODE135692
sharedpoollibrarycache2985576
sharedpoolmiscellaneous4889396
sharedpoolMTTRadvisory21164
sharedpoolPLSnon-libhp2068
sharedpoolXDBSchemaCac4966300
POOLNAMEBYTES
-----------------------------------------------
sharedpooljoxsheapinit4220
sharedpoolkglsimulator563260
sharedpoolsimmemoryhea44184
sharedpooltabledefiniti1728
sharedpooltriggerdefini1896
sharedpooltriggerinform1140
sharedpooltriggersource448
sharedpooltypeobjectde69120
sharedpoolCheckpointqueue282304
sharedpoolVIRTUALCIRCUITS265160
sharedpooldictionarycache1610880
POOLNAMEBYTES
-----------------------------------------------
sharedpoolKSXRreceivebuffers1033000
sharedpoolcharactersetobject323724
sharedpoolFileIdentificatonBlock323292
sharedpoolmessagepoolfreequeue834752
sharedpoolKSXRpendingmessagesque841036
sharedpooleventstatisticspersess1718360
sharedpoolfixedallocationcallback180
largepoolfreememory8388608
javapoolfreememory33554432
已选择42行。
15显示PGA的信息
select*fromv$pgastat;
NAMEVALUEUNIT
-----------------------------------------------------------------------------------
aggregatePGAtargetparameter16777216bytes
aggregatePGAautotarget7640064bytes
globalmemorybound838656bytes
totalPGAinuse8293376bytes
totalPGAallocated13106176bytes
maximumPGAallocated22090752bytes
totalfreeablePGAmemory0bytes
PGAmemoryfreedbacktoOS0bytes
totalPGAusedforautoworkareas0bytes
maximumPGAusedforautoworkareas4096bytes
totalPGAusedformanualworkareas0bytes
NAMEVALUEUNIT
-----------------------------------------------------------------------------------
maximumPGAusedformanualworkareas4096bytes
overallocationcount0
bytesprocessed8783872bytes
extrabytesread/written0bytes
cachehitpercentage100percent
已选择16行。
17在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效
define_editor=vi
setline2000
18将当前命令随加到文件中
savec:a.sqlappend
19将指定文件的命读出缓冲区
getc:a.sql
20执行脚本语句
@c:a.sql
21将输入保存到指定文件中
spoolc:O.LOG
select*fromv$sga;
spooloff
22设定行大小
setlinesize2000
23设定页大小
setpagesize10
24设定字符列格式
colenameformata30
25设定数字列格式
colsalformat999,999.999
2610G查看文件$ORACLE_HOME/install/protlist显示端口
http://127.0.0.1:5560/isqlplus
9i查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口
http://127.0.0.1:7778/isqlplus
http://127.0.0.1:7778/isqlplusdba
27启动Oracle9i监听程序
Oracle的监听程序主要是为客户端的连接提供接口
$lsnrctlstart
28关闭Oracle9i监听程序
$lsnrctlstop
29启动OracleWebServer
$cd$ORACLE_HOME/Apache/Apache/bin
$./startJServ.sh
/database/oracle/product/9i/Apache/Apache/bin/apachectlstart:httpdstarted
30关闭OracleWebServer
$cd$ORACLE_HOME/Apache/Apache/bin
$./stopJServ.sh
/database/oracle/product/9i/Apache/Apache/bin/apachectlstop:httpdstopped
31启动OracleWebServer后默认的端口号是7777
cd$ORACLE_HOME/Apache/Apache/bin/
htpasswd$ORACLE_HOME/sqlplus/admin/iplusdba.pwadmin
cdD:oracleora92ApacheApachebin
htpasswdD:oracleora92/sqlplus/admin/iplusdba.pwadmin
Newpassword:*****
Re-typenewpassword:*****
Addingpasswordforuseradmin
32pfile:$ORACLE_BASE/admin/实例名/Pfile
spfile:$ORACLE_HOME/dbs
监听器配置文件:listener.ora:$ORACLE_HOME/network/admin/
tnsnames.ora:$ORACLE_HOME/network/admin/
33指定用户的表空间
SELECTDEFAULT_TABLESPACEFROMDBA_USERSWHEREUSERNAME='用户名'
34当前用户
selectuserfromdual;showuser
35当前用户的缺省表空间
SELECTDEFAULT_TABLESPACEFROMDBA_USERSWHEREUSERNAME=(selectuserfromdual);
37显示正在使用的初始化参数文件
showparameterspfile
NAMETYPEVALUE
-----------------------------------------------------------------------------
spfilestring%ORACLE_HOME%DATABASESPFILE%
ORACLE_SID%.ORA
38用SPfile转Pfile
createpfile='c:init.ora'fromspfile;
createpfilefromspfile;
UNIX将生成在$ORACLE_HOME/dbs目录下
WINDOWS将生成在$ORACLE_HOME/DATABASE目录下
找Oracle视频教程学oracle,上课课家教育IT培训专家。