【Oracle學(xué)習(xí)13】 實例管理
13.1 使用數(shù)據(jù)庫管理工具
Oracle Database Management Tools
13.1.1 SQL*PLUS
SQL and SQL*Plus Interaction
SQL*Plus Editing Commands
SQL*Plus File Commands
Using the SQL*Plus SPOOL Command
Using the AUTOTRACE Command
lrwrp:
可以改進(jìn)sqlplus使用體驗。
#配置epel源
vim epel.repo
[aliyun_epel] #這是Redhat的擴(kuò)展包
name=ailiyun_yum_epel
baseurl=https://mirrors.aliyun.com/epel/$releasever/x86_64
gpgkey=http://mirrors.aliyun.com/centos/RPM-GPG-KEY-CentOS-6
gpgcheck=0
yum install rlwrap -y
#切換到oracle用戶權(quán)限
su - oracle
vim ~/.bash_profile
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
alias ggsci="rlwrap ggsci"
#生效
source ~/.bash_profile
#測試 alias
SQLPLUS使用:
- 幫助
sqlplus [username[/password[@database]]]
SQL> help index
@ COPY PASSWORD SHOW
@@ DEFINE PAUSE SHUTDOWN
/ DEL PRINT SPOOL
#查看表結(jié)構(gòu)
DESC[RIBE] tablename
- 執(zhí)行腳本
$vim test.sql
select * from hr.departments where location_id = 1400;
quit
$sqlplus hr/hr @test.sql
- 執(zhí)行命令
#編輯SQL
SQL> select FIRST_NAME,last_name
2 from employees
3 where employee_id=113;
FIRST_NAME LAST_NAME
--------------------------------------------------
Luis Popp
SQL> 3
3* where employee_id=113
SQL> change /113/100
3* where employee_id=100
SQL> list
1 select FIRST_NAME,last_name
2 from employees
3* where employee_id=100
SQL>/ 執(zhí)行
FIRST_NAME LAST_NAME
--------------------------------------------------
Steven King
#保留
SQL> save /home/oracle/temp.sql
Created file /home/oracle/temp.sql
SQL> !ls -l /home/oracle/temp.sql
SQL> !cat /home/oracle/temp.sql
select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=100
/
#讀取文件
SQL> get /home/oracle/temp.sql
1* select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=100
#執(zhí)行文件
SQL> start temp.sql
EMPLOYEE_ID FIRST_NAME LAST_NAME
--------------------------------------------------
100 Steven King
SQL> @temp.sql --相同于start temp.sql
#編輯
SQL> edit
Wrote file afiedt.buf
select EMPLOYEE_ID,first_name,last_name from employees where EMPLOYEE_ID=200
/
qw
SQL>/ 執(zhí)行
- Spool
SQL> spool spool_op.txt
SQL> select EMPLOYEE_ID,first_name from employees where EMPLOYEE_ID=200;
EMPLOYEE_ID FIRST_NAME
----------- ----------------------------------------
200 Jennifer
SQL> c /200/100
1* select EMPLOYEE_ID,first_name from employees where EMPLOYEE_ID=100
SQL> /
EMPLOYEE_ID FIRST_NAME
----------- ----------------------------------------
100 Steven
SQL> spool off
SQL> !cat spool_op.txt
- AUTOTRACE
語法: SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> SET AUTOTRACE ON
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=200)
Statistics
----------------------------------------------------------
173 recursive calls
0 db block gets
248 consistent gets
0 physical reads
0 redo size
626 bytes sent via SQL*Net to client
#關(guān)閉
SQL> set AUTOTRACE off
- 格式化
SQL> set lines 200 --即set linesize 200
SQL> set pagesize 200
SQL> desc v$parameter;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(4000)
DISPLAY_VALUE VARCHAR2(4000)
DEFAULT_VALUE VARCHAR2(255)
#如下會特別亂
SQL> Select name, type,value,isdefault from v$parameter where name ='db_file_multiblock_read_count'; --會特別亂
# 設(shè)置格式
Col name format a30
Col type for 9
Col value for a20
Set line size 150;
SQL> Select name, type,value,isdefault from v$parameter where name ='db_file_multiblock_read_count';
NAME TYPE VALUE ISDEFAULT
------------------------------ ---- -------------------- ------------------
db_file_multiblock_read_count 3 128 TRUE
# 取消格式化
SQL> clear col
columns cleared
13.1.3 使用Database Express
exec DBMS_XDB.setHTTPPort(5500)
netstat -lntp |grep 5500
