oracle dba 应该熟悉的命令

1 运行SQLPLUS工具
sqlplus
2 以OS的默认身份连接
/ as sysdba
3 显示当前用户名
show user
4 直接进入SQLPLUS命令提示符
sqlplus /nolog
5 在命令提示符以OS身份连接
connect / as sysdba
6 以SYSTEM的身份连接
connect system/xxxxxxx@服务名
7 显示当然用户有哪些表
select * from tab;
8 显示有用户名和帐户的状态
select username,account_status from dba_users;
9 将SCOTT帐号解锁(加锁)
alter user scott account unlock(lock);
10 以SCOTT的身份连接并且查看所属表
connect scott/tiger
select * from tab;

11 查看EMP的表结构及记录内容
desc emp
select empno,ename from emp;
12 以OS的身份登看SGA,共享池,CACHE的信息
connect / as sysdba
show sga
select name,value/1024/1024 from v$sga;
show parameter shared_pool_size
select value/1024/1024 from v$parameter where name ='shared_pool_size';
show parameter db_cache_size
select value/1024/1024 from v$parameter where name ='db_cache_size';
13 查看所有含有SIZE的信息
show parameter size
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 4096
db_cache_size big integer 33554432
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
NAME TYPE VALUE
------------------------------------ ----------- -------------
global_context_pool_size string
hash_area_size integer 1048576
java_max_sessionspace_size integer 0
java_pool_size big integer 33554432
large_pool_size big integer 8388608
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size integer 33554432
oracle_trace_collection_size integer 5242880
parallel_execution_message_size integer 2148
NAME TYPE VALUE
------------------------------------ ----------- -------------
sga_max_size big integer 143727516
shared_pool_reserved_size big integer 2516582
shared_pool_size big integer 50331648
sort_area_retained_size integer 0
sort_area_size integer 524288
workarea_size_policy string AUTO

14 显示SGA的信息
select * from v$sgastat;
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 453532
buffer_cache 33554432
log_buffer 656384
shared pool subheap 46884
shared pool KGK heap 3756
shared pool KQR M PO 586792
shared pool KQR S PO 180232
shared pool KQR S SO 5128
shared pool sessions 410720
shared pool sql area 2144664
shared pool 1M buffer 2098176
POOL NAME BYTES
----------- -------------------------- ----------
shared pool KGLS heap 901756
shared pool parameters 8352
shared pool free memory 38687204
shared pool PL/SQL DIANA 420816
shared pool FileOpenBlock 695504
shared pool PL/SQL MPCODE 135692
shared pool library cache 2985576
shared pool miscellaneous 4889396
shared pool MTTR advisory 21164
shared pool PLS non-lib hp 2068
shared pool XDB Schema Cac 4966300
POOL NAME BYTES
----------- -------------------------- ----------
shared pool joxs heap init 4220
shared pool kgl simulator 563260
shared pool sim memory hea 44184
shared pool table definiti 1728
shared pool trigger defini 1896
shared pool trigger inform 1140
shared pool trigger source 448
shared pool type object de 69120
shared pool Checkpoint queue 282304
shared pool VIRTUAL CIRCUITS 265160
shared pool dictionary cache 1610880
POOL NAME BYTES
----------- -------------------------- ----------
shared pool KSXR receive buffers 1033000
shared pool character set object 323724
shared pool FileIdentificatonBlock 323292
shared pool message pool freequeue 834752
shared pool KSXR pending messages que 841036
shared pool event statistics per sess 1718360
shared pool fixed allocation callback 180
large pool free memory 8388608
java pool free memory 33554432
已选择42行。
15 显示PGA的信息
select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ---------
aggregate PGA target parameter 16777216 bytes
aggregate PGA auto target 7640064 bytes
global memory bound 838656 bytes
total PGA inuse 8293376 bytes
total PGA allocated 13106176 bytes
maximum PGA allocated 22090752 bytes
total freeable PGA memory 0 bytes
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 4096 bytes
total PGA used for manual workareas 0 bytes
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ---------
maximum PGA used for manual workareas 4096 bytes
over allocation count 0
bytes processed 8783872 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
已选择16行。
17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效
define _editor=vi
set line 2000

18 将当前命令随加到文件中
save c:a.sql append
19 将指定文件的命读出缓冲区
get c:a.sql
20 执行脚本语句
@ c:a.sql