Oracle Advanced Notes-use of common tools-oracle SQL developer

Oracle SQL developer similar to Plsqldeveloper

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

Install the tool to unzip it OK

Open it

You must first establish a connection to the database

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=" "/>

1 Connection name (any) oracle11g_connect

Enter account name Sys

Password Oracle

Connection mode Basic Role SYSDBA

Host Name: Database server hostname or IP address 192.168.3.1

Ports on listen: 1521

Choose one of the following

A SID instance name ORCL

B Service Name 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 "/>

Test--"success

Connection

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, 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))) Here is the port

Services Summary ...

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

Instance "+asm", status ready, have 1 handler (s) for the This service ...

Service "orcl.example.com" here is the name of the server has 1 instance (s).

Instance "ORCL" Here is the instance name, status ready, have 1 handler (s) for the This service ...

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

Instance "ORCL", status ready, have 1 handler (s) for the This service ...

The command completed successfully

Exercise: Build a Scott Connection

Connection name (any) scott_connect

Enter account name Scott

Password Oracle

Connection mode basic Role normal

Host Name: Database server hostname or IP address 192.168.3.1

Ports on listen: 1521

Choose one of the following

A SID instance name ORCL

B Service Name 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 "/>

Features for collection graphics and SQL

1 See what columns of Scott's EMP table have the DDL statements (statements that create the table) on which the data is isolated from the EMP table

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="/>

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 using the command to isolate the structure information of the Scott.emp table

Select Connect right-click to open Worksheet

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 See the structure information of the table

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 Viewing scripts on the system

View---"File---" Browse files on your computer

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="/>

4 Build a table on the Scott user to build a table for KC (kcid number Kcname char (10))

Kcid as the primary key

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 "/>

2 data is inserted in the KC table first

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= "/>"

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 How to enter SQL statements

Request to find out the contents of KC table

Tools---SQL worksheet

Or on the toolbar---SQL worksheet

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="/>

SELECT * from SCOTT.KC; Click Execute

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 Save script Save

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 How to execute a script

7.1 Method 1: File found script---Double-click---"Select your connection---" F5 execute script

7.2 Method 2: @ Determines the path

@d:\scripts\2.sql

8 How formatting guarantees consistent code formatting

Select Code Right-click Format

9 How to use clips

Some daily functions provide a sample template

The highest wage for employees

Select Max (SAL) from Scott.emp;

The cursor hovers over the function with a hint to drag past a case

10 Viewing reports

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 "/>

System already defined

Users can define their own

Define yourself a script show tablespace

Paste in statement

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 "/>

Double click to execute

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="/>