oracle常用命令大全和环境变量路径

Oracle 命令大全

底部为环境变量配置路径。

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 V ALUE

------------------------------------ ----------- -------------

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 V ALUE

------------------------------------ ----------- -------------

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