oracle進階筆記-常用工具的使用-oracle sql developer

標籤：developer      資料庫連接   常用工具   blank   

oracle sql developer 類似於plsqldeveloper

;http://www.oracle.com/technology/products/database/sql_developer/index.html

安裝工具 解壓就OK

開啟

首先必須建立一個對資料庫連接

650) this.width=650;" title="clip_image002" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image002" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525636fdOX.jpg" "244" height="115" />

1 串連名（任意） oracle11g_connect

輸入賬戶名sys

密碼 oracle

串連方式 基本 角色 sysdba

主機名稱： 資料庫伺服器主機名稱或者ip地址 192.168.3.1

偵聽的連接埠：1521

下面任意選一個

a sid 執行個體名 orcl

b 服務名 orcl.example.com

650) this.width=650;" title="clip_image004" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image004" src="http://img1.51cto.com/attachment/201407/28/9202005_14065256378zzN.jpg" "244" height="129" />

測試 --》成功

串連

650) this.width=650;" title="clip_image006" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image006" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525637vMnI.jpg" "244" height="186" />

su - grid

[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-MAR-2014 05:57:21

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 16-MAR-2014 01:52:20

Uptime 0 days 4 hr. 5 min. 2 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/yutianedu/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yutianedu)(PORT=1521)))這裡是連接埠

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "orcl.example.com" 這裡是服務名 has 1 instance(s).

Instance "orcl"這裡是執行個體名 , status READY, has 1 handler(s) for this service...

Service "orclXDB.example.com" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

練習：建立一個scott串連

串連名（任意） scott_connect

輸入賬戶名scott

密碼 oracle

串連方式 基本 角色 normal

主機名稱： 資料庫伺服器主機名稱或者ip地址 192.168.3.1

偵聽的連接埠：1521

下面任意選一個

a sid 執行個體名 orcl

b 服務名 orcl.example.com

650) this.width=650;" title="clip_image008" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image008" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525638ZRrQ.jpg" "244" height="129" />

650) this.width=650;" title="clip_image010" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image010" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525638XW6G.jpg" "244" height="194" />

集合圖形和sql的功能

1 查看scott的emp表有哪些列 有多少上資料 查出emp表的ddl語句 (建立表的語句）

650) this.width=650;" title="clip_image012" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image012" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525639mSLJ.jpg" "244" height="105" />

650) this.width=650;" title="clip_image014" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image014" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525639ANeO.jpg" "244" height="149" />

2 用命令查出scott.emp表的結構資訊

選擇串連 右鍵 開啟工作表

650) this.width=650;" title="clip_image016" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image016" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525640qYPv.jpg" "244" height="192" />

desc scott.emp 看到表的結構資訊

650) this.width=650;" title="clip_image018" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image018" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525640XWuh.jpg" "244" height="177" />

3 查看系統上的指令碼

查看---》檔案 ---》瀏覽你的電腦上的檔案

650) this.width=650;" title="clip_image020" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image020" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525641t4tb.jpg" "244" height="230" />

4 建立一個表 在scott使用者建立一個表kc (kcid number kcname char(10))

kcid 作為主鍵

650) this.width=650;" title="clip_image021" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image021" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525641MXXU.png" "244" height="149" />

650) this.width=650;" title="clip_image023" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image023" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525642MMW3.jpg" "244" height="172" />

650) this.width=650;" title="clip_image025" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image025" src="http://img1.51cto.com/attachment/201407/28/9202005_14065256438bRX.jpg" "244" height="171" />

650) this.width=650;" title="clip_image027" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image027" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525644bmda.jpg" "244" height="172" />

650) this.width=650;" title="clip_image028" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image028" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525644m9Zo.png" "222" height="181" />

先kc表中插入2條資料

1 database

2 rac

650) this.width=650;" title="clip_image030" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image030" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525645G9PQ.jpg" "244" height="112" />

650) this.width=650;" title="clip_image032" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image032" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525646MiOY.jpg" "244" height="129" />

5 如何輸入SQL語句

要求查出kc表的內容

工具---》sql工作表

或者在工具列---》sql工作表

650) this.width=650;" title="clip_image034" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image034" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525647e2b1.jpg" "244" height="142" />

650) this.width=650;" title="clip_image035" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image035" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525647Pt9y.png" "244" height="114" />

select * from scott.kc ; 點擊執行

650) this.width=650;" title="clip_image037" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image037" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525647U8OA.jpg" "244" height="168" />

6 儲存指令碼 儲存

650) this.width=650;" title="clip_image039" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image039" src="http://img1.51cto.com/attachment/201407/28/9202005_14065256485dcN.jpg" "244" height="167" />

7 如何執行指令碼

7.1 方法1：檔案 找到指令碼 ---雙擊 ---》選擇你的串連---》f5 執行指令碼

7.2 方法2： @決定路徑

@d:\scripts\2.sql

8 如何格式化 保證代碼格式一致

選中代碼 右鍵 格式化

9 如何使用片段

日常一些函數 提供一個樣本模板

求員工的最高工資

select max(sal) from scott.emp;

游標懸停在函數 有提示 拖拽過去有案例

10 查看報告

650) this.width=650;" title="clip_image041" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image041" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525648ivaL.jpg" "227" height="244" />

系統已經定義

使用者可以自己定義

自己定義一個指令碼show tablespace

貼入語句

SELECT D.STATUS STATUS,

D.TABLESPACE_NAME NAME,

D.CONTENTS TYPE,

D.EXTENT_MANAGEMENT EXTENTMANAGEMENT,

TO_CHAR(NVL(A.BYTES / 1024 / 1024, 0), ‘99,999,990.900‘) TABLE_SIZE,

TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024,

‘99999999.999‘) || ‘/‘ ||

TO_CHAR(NVL(A.BYTES / 1024 / 1024, 0), ‘99999999.999‘) USED,

TO_CHAR(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0),

‘990.00‘) RATE

FROM SYS.DBA_TABLESPACES D,

(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) A,

(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)

AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

AND NOT

(D.EXTENT_MANAGEMENT LIKE ‘LOCAL‘ AND D.CONTENTS LIKE ‘TEMPORARY‘);

650) this.width=650;" title="clip_image043" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image043" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525648CHSE.jpg" "244" height="203" />

雙擊一下就可以執行

650) this.width=650;" title="clip_image045" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image045" src="http://img1.51cto.com/attachment/201407/28/9202005_1406525649zqqb.jpg" "244" height="91" />