《oracle常用命令大全和环境变量路径》由会员分享,可在线阅读,更多相关《oracle常用命令大全和环境变量路径(27页珍藏版)》请在金锄头文库上搜索。
1、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_us
2、ers; 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
3、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
4、 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
5、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
6、 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
7、 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 p
8、ool 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
9、 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 4418
10、4 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
11、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 poo
12、l 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 1310617
13、6 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 wo
14、rkareas 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.
15、sql 20 执行脚本语句 c:a.sql 21 将输入保存到指定文件中 spool c:O.LOG select * from v$sga; spool off 22 设定行大小 set linesize 2000 23 设定页大小 set pagesize 10 24 设定字符列格式 col ename format a30 25 设定数字列格式 col sal format 999,999.999 26 10G 查看文件$ORACLE_HOME/install/protlist 显示端口 http:/127.0.0.1:5560/isqlplus 9i 查看文件$ORACLE_HOME/Apache/Apache/port