轉(zhuǎn)自http://www.ee82.com/htm/oracle/17.asp
一、ORACLE的啟動(dòng)和關(guān)閉
1
、在單機(jī)環(huán)境下
要想啟動(dòng)或關(guān)閉ORACLE系統(tǒng)必須首先切換到ORACLE用戶，如下
su?
-
?oracle
a、啟動(dòng)ORACLE系統(tǒng)
oracle
>
svrmgrl
SVRMGR
>
connect?internal
SVRMGR
>
startup
SVRMGR
>
quit
b、關(guān)閉ORACLE系統(tǒng)
oracle
>
svrmgrl
SVRMGR
>
connect?internal
SVRMGR
>
shutdown
SVRMGR
>
quit
啟動(dòng)oracle9i數(shù)據(jù)庫(kù)命令：
$?sqlplus?
/
nolog
SQL
*
Plus:?Release?
9.2
.
0.1
.
0
?
-
?Production?
on
?Fri?Oct?
31
?
13
:
53
:
53
?
2003
Copyright?(c)?
1982
,?
2002
,?Oracle?Corporation.?
All
?rights?reserved.
SQL
>
?connect?
/
?
as
?sysdba
Connected?
to
?an?idle?instance.
SQL
>
?startup
^
C
SQL
>
?startup
ORACLE?instance?started.
2
、在雙機(jī)環(huán)境下
要想啟動(dòng)或關(guān)閉ORACLE系統(tǒng)必須首先切換到root用戶，如下
su?－?root
a、啟動(dòng)ORACLE系統(tǒng)
hareg?－y?oracle
b、關(guān)閉ORACLE系統(tǒng)
hareg?－n?oracle
Oracle數(shù)據(jù)庫(kù)有哪幾種啟動(dòng)方式
說明：
有以下幾種啟動(dòng)方式：
1
、startup?nomount
非安裝啟動(dòng)，這種方式啟動(dòng)下可執(zhí)行：重建控制文件、重建數(shù)據(jù)庫(kù)
讀取init.ora文件，啟動(dòng)instance，即啟動(dòng)SGA和后臺(tái)進(jìn)程，這種啟動(dòng)只需要init.ora文件。
2
、startup?mount?dbname
安裝啟動(dòng)，這種方式啟動(dòng)下可執(zhí)行：
數(shù)據(jù)庫(kù)日志歸檔、
數(shù)據(jù)庫(kù)介質(zhì)恢復(fù)、
使數(shù)據(jù)文件聯(lián)機(jī)或脫機(jī)，
重新定位數(shù)據(jù)文件、重做日志文件。
執(zhí)行“nomount”，然后打開控制文件，確認(rèn)數(shù)據(jù)文件和聯(lián)機(jī)日志文件的位置，
但此時(shí)不對(duì)數(shù)據(jù)文件和日志文件進(jìn)行校驗(yàn)檢查。
3
、startup?
open
?dbname
先執(zhí)行“nomount”，然后執(zhí)行“mount”，再打開包括Redo?log文件在內(nèi)的所有數(shù)據(jù)庫(kù)文件，
這種方式下可訪問數(shù)據(jù)庫(kù)中的數(shù)據(jù)。
4
、startup，等于以下三個(gè)命令
startup?nomount
alter
?
database
?mount
alter
?
database
?
open
5
、startup?
restrict
約束方式啟動(dòng)
這種方式能夠啟動(dòng)數(shù)據(jù)庫(kù)，但只允許具有一定特權(quán)的用戶訪問
非特權(quán)用戶訪問時(shí)，會(huì)出現(xiàn)以下提示：
ERROR：
ORA
-
01035
:?ORACLE?只允許具有?RESTRICTED?SESSION?權(quán)限的用戶使用
6
、startup?force
強(qiáng)制啟動(dòng)方式
當(dāng)不能關(guān)閉數(shù)據(jù)庫(kù)時(shí)，可以用startup?force來完成數(shù)據(jù)庫(kù)的關(guān)閉
先關(guān)閉數(shù)據(jù)庫(kù)，再執(zhí)行正常啟動(dòng)數(shù)據(jù)庫(kù)命令
7
、startup?pfile
=
參數(shù)文件名
帶初始化參數(shù)文件的啟動(dòng)方式
先讀取參數(shù)文件，再按參數(shù)文件中的設(shè)置啟動(dòng)數(shù)據(jù)庫(kù)
例：startup?pfile
=
E:Oracleadminoradbpfileinit.ora
8
、startup?EXCLUSIVE
二、用戶如何有效地利用數(shù)據(jù)字典
ORACLE的數(shù)據(jù)字典是數(shù)據(jù)庫(kù)的重要組成部分之一，它隨著數(shù)據(jù)庫(kù)的產(chǎn)生而產(chǎn)生,?隨著數(shù)據(jù)庫(kù)的變化而變化,
體現(xiàn)為sys用戶下的一些表和視圖。數(shù)據(jù)字典名稱是大寫的英文字符。
數(shù)據(jù)字典里存有用戶信息、用戶的權(quán)限信息、所有數(shù)據(jù)對(duì)象信息、表的約束條件、統(tǒng)計(jì)分析數(shù)據(jù)庫(kù)的視圖等。
我們不能手工修改數(shù)據(jù)字典里的信息。
很多時(shí)候，一般的ORACLE用戶不知道如何有效地利用它。
dictionary 全部數(shù)據(jù)字典表的名稱和解釋，它有一個(gè)同義詞dict
dict_column ?全部數(shù)據(jù)字典表里字段名稱和解釋
如果我們想查詢跟索引有關(guān)的數(shù)據(jù)字典時(shí)，可以用下面這條SQL語(yǔ)句:
SQL
>
select
?
*
?
from
?dictionary?
where
?instr(comments,
'
index
'
)
>
0
;
如果我們想知道user_indexes表各字段名稱的詳細(xì)含義，可以用下面這條SQL語(yǔ)句:
SQL
>
select
?column_name,comments?
from
?dict_columns?
where
?table_name
=
'
USER_INDEXES
'
;
依此類推，就可以輕松知道數(shù)據(jù)字典的詳細(xì)名稱和解釋，不用查看ORACLE的其它文檔資料了。
下面按類別列出一些ORACLE用戶常用數(shù)據(jù)字典的查詢使用方法。
1
、用戶
查看當(dāng)前用戶的缺省表空間
SQL
>
select
?username,default_tablespace?
from
?user_users;
查看當(dāng)前用戶的角色
SQL
>
select
?
*
?
from
?user_role_privs;
查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級(jí)權(quán)限
SQL
>
select
?
*
?
from
?user_sys_privs;
SQL
>
select
?
*
?
from
?user_tab_privs;
2
、表
查看用戶下所有的表
SQL
>
select
?
*
?
from
?user_tables;
查看名稱包含log字符的表
SQL
>
select
?
object_name
,
object_id
?
from
?user_objects
where
?instr(
object_name
,
'
LOG
'
)
>
0
;
查看某表的創(chuàng)建時(shí)間
SQL
>
select
?
object_name
,created?
from
?user_objects?
where
?
object_name
=
upper
(
'
&table_name
'
);
查看某表的大小
SQL
>
select
?
sum
(bytes)
/
(
1024
*
1024
)?
as
?"size(M)"?
from
?user_segments
where
?segment_name
=
upper
(
'
&table_name
'
);
查看放在ORACLE的內(nèi)存區(qū)里的表
SQL
>
select
?table_name,cache?
from
?user_tables?
where
?instr(cache,
'
Y
'
)
>
0
;
3
、索引
查看索引個(gè)數(shù)和類別
SQL
>
select
?index_name,index_type,table_name?
from
?user_indexes?
order
?
by
?table_name;
查看索引被索引的字段
SQL
>
select
?
*
?
from
?user_ind_columns?
where
?index_name
=
upper
(
'
&index_name
'
);
查看索引的大小
SQL
>
select
?
sum
(bytes)
/
(
1024
*
1024
)?
as
?"size(M)"?
from
?user_segments
where
?segment_name
=
upper
(
'
&index_name
'
);
4
、序列號(hào)
查看序列號(hào)，last_number是當(dāng)前值
SQL
>
select
?
*
?
from
?user_sequences;
5
、視圖
查看視圖的名稱
SQL
>
select
?view_name?
from
?user_views;
查看創(chuàng)建視圖的select語(yǔ)句
SQL
>
set
?view_name,text_length?
from
?user_views;
SQL
>
set
?
long
?
2000
;?說明：可以根據(jù)視圖的text_length值設(shè)定set?
long
?的大小
SQL
>
select
?
text
?
from
?user_views?
where
?view_name
=
upper
(
'
&view_name
'
);
6
、同義詞
查看同義詞的名稱
SQL
>
select
?
*
?
from
?user_synonyms;
7
、約束條件
查看某表的約束條件
SQL
>
select
?constraint_name,?constraint_type,search_condition,?r_constraint_name
from
?user_constraints?
where
?table_name?
=
?
upper
(
'
&table_name
'
);
SQL
>
select
?c.constraint_name,c.constraint_type,cc.column_name
from
?user_constraints?c,user_cons_columns?cc
where
?c.owner?
=
?
upper
(
'
&table_owner
'
)?
and
?c.table_name?
=
?
upper
(
'
&table_name
'
)
and
?c.owner?
=
?cc.owner?
and
?c.constraint_name?
=
?cc.constraint_name
order
?
by
?cc.position;
8
、存儲(chǔ)函數(shù)和過程
查看函數(shù)和過程的狀態(tài)
SQL
>
select
?
object_name
,status?
from
?user_objects?
where
?object_type
=
'
FUNCTION
'
;
SQL
>
select
?
object_name
,status?
from
?user_objects?
where
?object_type
=
'
PROCEDURE
'
;
查看函數(shù)和過程的源代碼
SQL
>
select
?
text
?
from
?all_source?
where
?owner
=
user
?
and
?name
=
upper
(
'
&plsql_name
'
);
三、查看數(shù)據(jù)庫(kù)的SQL
1
、查看表空間的名稱及大小
select
?t.tablespace_name,?
round
(
sum
(bytes
/
(
1024
*
1024
)),
0
)?ts_size
from
?dba_tablespaces?t,?dba_data_files?d
where
?t.tablespace_name?
=
?d.tablespace_name
group
?
by
?t.tablespace_name;
2
、查看表空間物理文件的名稱及大小
select
?tablespace_name,?
file_id
,?
file_name
,
round
(bytes
/
(
1024
*
1024
),
0
)?total_space
from
?dba_data_files
order
?
by
?tablespace_name;
3
、查看回滾段名稱及大小
select
?segment_name,?tablespace_name,?r.status,
(initial_extent
/
1024
)?InitialExtent,(next_extent
/
1024
)?NextExtent,
max_extents,?v.curext?CurExtent
From
?dba_rollback_segs?r,?v$rollstat?v
Where
?r.segment_id?
=
?v.usn(
+
)
order
?
by
?segment_name?;
4
、查看控制文件
select
?name?
from
?v$controlfile;
5
、查看日志文件
select
?member?
from
?v$logfile;
6
、查看表空間的使用情況
select
?
sum
(bytes)
/
(
1024
*
1024
)?
as
?free_space,tablespace_name
from
?dba_free_space
group
?
by
?tablespace_name;
SELECT
?A.TABLESPACE_NAME,A.BYTES?TOTAL,B.BYTES?USED,?C.BYTES?FREE,
(B.BYTES
*
100
)
/
A.BYTES?"
%
?USED",(C.BYTES
*
100
)
/
A.BYTES?"
%
?FREE"
FROM
?SYS.SM$TS_AVAIL?A,SYS.SM$TS_USED?B,SYS.SM$TS_FREE?C
WHERE
?A.TABLESPACE_NAME
=
B.TABLESPACE_NAME?
AND
?A.TABLESPACE_NAME
=
C.TABLESPACE_NAME;
7
、查看數(shù)據(jù)庫(kù)庫(kù)對(duì)象
select
?owner,?object_type,?status,?
count
(
*
)?
count
#?
from
?all_objects?
group
?
by
?owner,?object_type,?status;
8
、查看數(shù)據(jù)庫(kù)的版本
Select
?version?
FROM
?Product_component_version
Where
?SUBSTR(PRODUCT,
1
,
6
)
=
'
Oracle
'
;
9
、查看數(shù)據(jù)庫(kù)的創(chuàng)建日期和歸檔方式
Select
?Created,?Log_Mode,?Log_Mode?
From
?V$
Database
;
--
------------------------------------------------------------------------------
jxdco
四、ORACLE用戶連接的管理
用系統(tǒng)管理員，查看當(dāng)前數(shù)據(jù)庫(kù)有幾個(gè)用戶連接：
SQL
>
?
select
?username,sid,serial#?
from
?v$session;
如果要停某個(gè)連接用
SQL
>
?
alter
?system?
kill
?session?
'
sid,serial#
'
;
如果這命令不行,找它UNIX的進(jìn)程數(shù)
SQL
>
?
select
?pro.spid?
from
?v$session?ses,v$process?pro?
where
?ses.sid
=
21
?
and
?ses.paddr
=
pro.addr;
說明：21是某個(gè)連接的sid數(shù)
然后用?
kill
?命令殺此進(jìn)程號(hào)。
五、SQL
*
PLUS使用
a、近入SQL
*
Plus
$sqlplus?用戶名
/
密碼
退出SQL
*
Plus
SQL
>
exit
b、在sqlplus下得到幫助信息
列出全部SQL命令和SQL
*
Plus命令
SQL
>
help
列出某個(gè)特定的命令的信息
SQL
>
help?命令名
c、顯示表結(jié)構(gòu)命令DESCRIBE
SQL
>
DESC
?表名
d、SQL
*
Plus中的編輯命令
顯示SQL緩沖區(qū)命令
SQL
>
L
修改SQL命令
首先要將待改正行變?yōu)楫?dāng)前行
SQL
>
n
用CHANGE命令修改內(nèi)容
SQL
>
c
/
舊
/
新
重新確認(rèn)是否已正確
SQL
>
L
使用INPUT命令可以在SQL緩沖區(qū)中增加一行或多行
SQL
>
i
SQL
>
輸入內(nèi)容
e、調(diào)用外部系統(tǒng)編輯器
SQL
>
edit?文件名
可以使用DEFINE命令設(shè)置系統(tǒng)變量EDITOR來改變文本編輯器的類型，在login.sql文件中定義如下一行
DEFINE_EDITOR
=
vi
f、運(yùn)行命令文件
SQL
>
START?test
SQL
>
@test
常用SQL
*
Plus語(yǔ)句
a、表的創(chuàng)建、修改、刪除
創(chuàng)建表的命令格式如下：
create
?
table
?表名?（列說明列表）；
為基表增加新列命令如下：
ALTER
?
TABLE
?表名?
ADD
?（列說明列表）
例：為test表增加一列Age，用來存放年齡
sql
>
alter
?
table
?test
add
?（Age?
number
(
3
)）；
修改基表列定義命令如下：
ALTER
?
TABLE
?表名
MODIFY?（列名?數(shù)據(jù)類型）
例：將test表中的Count列寬度加長(zhǎng)為10個(gè)字符
sql
>
alter
?atble?test
modify?（County?
char
(
10
)）；
b、將一張表刪除語(yǔ)句的格式如下：
DORP?
TABLE
?表名；
例：表刪除將同時(shí)刪除表的數(shù)據(jù)和表的定義
sql
>
drop
?
table
?test
c、表空間的創(chuàng)建、刪除
六、ORACLE邏輯備份的SH文件
完全備份的SH文件：exp_comp.sh
rq
=
`?date?
+
"
%
m
%
d"?`
su?
-
?oracle?
-
c?"
exp
?system
/
manager?
full
=
y?inctype
=
complete?
file
=/
oracle
/
export
/
db_comp$rq.dmp"
累計(jì)備份的SH文件：exp_cumu.sh
rq
=
`?date?
+
"
%
m
%
d"?`
su?
-
?oracle?
-
c?"
exp
?system
/
manager?
full
=
y?inctype
=
cumulative?
file
=/
oracle
/
export
/
db_cumu$rq.dmp"
增量備份的SH文件:?exp_incr.sh
rq
=
`?date?
+
"
%
m
%
d"?`
su?
-
?oracle?
-
c?"
exp
?system
/
manager?
full
=
y?inctype
=
incremental?
file
=/
oracle
/
export
/
db_incr$rq.dmp"
root用戶crontab文件
/
var
/
spool
/
cron
/
crontabs
/
root增加以下內(nèi)容
0
?
2
?
1
?
*
?
*
?
/
oracle
/
exp_comp.sh
30
?
2
?
*
?
*
?
0
-
5
?
/
oracle
/
exp_incr.sh
45
?
2
?
*
?
*
?
6
?
/
oracle
/
exp_cumu.sh
當(dāng)然這個(gè)時(shí)間表可以根據(jù)不同的需求來改變的，這只是一個(gè)例子。
七、ORACLE?常用的SQL語(yǔ)法和數(shù)據(jù)對(duì)象
一.數(shù)據(jù)控制語(yǔ)句?(DML)?部分
1
.
INSERT
?(往數(shù)據(jù)表里插入記錄的語(yǔ)句)
INSERT
?
INTO
?表名(字段名1,?字段名2,?……)?
VALUES
?(?值1,?值2,?……);
INSERT
?
INTO
?表名(字段名1,?字段名2,?……)?
SELECT
?(字段名1,?字段名2,?……)?
FROM
?另外的表名;
字符串類型的字段值必須用單引號(hào)括起來,?例如:?’GOOD?
DAY
’
如果字段值里包含單引號(hào)’?需要進(jìn)行字符串轉(zhuǎn)換,?我們把它替換成兩個(gè)單引號(hào)
''
.
字符串類型的字段值超過定義的長(zhǎng)度會(huì)出錯(cuò),?最好在插入前進(jìn)行長(zhǎng)度校驗(yàn).
日期字段的字段值可以用當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)時(shí)間SYSDATE,?精確到秒
或者用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘
2001
-
08
-
01
’,’YYYY
-
MM
-
DD’)
TO_DATE()還有很多種日期格式,?可以參看ORACLE?DOC.
年
-
月
-
日?小時(shí):分鐘:秒?的格式Y(jié)YYY
-
MM
-
DD?HH24:MI:SS
INSERT時(shí)最大可操作的字符串長(zhǎng)度小于等于4000個(gè)單字節(jié),?如果要插入更長(zhǎng)的字符串,?請(qǐng)考慮字段用CLOB類型,
方法借用ORACLE里自帶的DBMS_LOB程序包.
INSERT時(shí)如果要用到從1開始自動(dòng)增長(zhǎng)的序列號(hào),?應(yīng)該先建立一個(gè)序列號(hào)
CREATE
?SEQUENCE?序列號(hào)的名稱?(最好是表名
+
序列號(hào)標(biāo)記)?INCREMENT?
BY
?
1
?START?
WITH
?
1
MAXVALUE?
99999
?CYCLE?NOCACHE;
其中最大的值按字段的長(zhǎng)度來定,?如果定義的自動(dòng)增長(zhǎng)的序列號(hào)?
NUMBER
(
6
)?,?最大值為999999
INSERT
?語(yǔ)句插入這個(gè)字段值為:?序列號(hào)的名稱.NEXTVAL
2
.
DELETE
?(刪除數(shù)據(jù)表里記錄的語(yǔ)句)
DELETE
?FROM表名?
WHERE
?條件;
注意：刪除記錄并不能釋放ORACLE里被占用的數(shù)據(jù)塊表空間.?它只把那些被刪除的數(shù)據(jù)塊標(biāo)成unused.
如果確實(shí)要?jiǎng)h除一個(gè)大表里的全部記錄,?可以用?
TRUNCATE
?命令,?它可以釋放占用的數(shù)據(jù)塊表空間
TRUNCATE
?
TABLE
?表名;
此操作不可回退.
3
.
UPDATE
?(修改數(shù)據(jù)表里記錄的語(yǔ)句)
UPDATE表名?
SET
?字段名1
=
值1,?字段名2
=
值2,?……?
WHERE
?條件;
如果修改的值N沒有賦值或定義時(shí),?將把原來的記錄內(nèi)容清為NULL,?最好在修改前進(jìn)行非空校驗(yàn);
值N超過定義的長(zhǎng)度會(huì)出錯(cuò),?最好在插入前進(jìn)行長(zhǎng)度校驗(yàn)..
注意事項(xiàng):
A.?以上SQL語(yǔ)句對(duì)表都加上了行級(jí)鎖,
確認(rèn)完成后,?必須加上事物處理結(jié)束的命令?
COMMIT
?才能正式生效,
否則改變不一定寫入數(shù)據(jù)庫(kù)里.
如果想撤回這些操作,?可以用命令?
ROLLBACK
?復(fù)原.
B.?在運(yùn)行INSERT,?
DELETE
?和?
UPDATE
?語(yǔ)句前最好估算一下可能操作的記錄范圍,
應(yīng)該把它限定在較小?(一萬條記錄)?范圍內(nèi),.?否則ORACLE處理這個(gè)事物用到很大的回退段.
程序響應(yīng)慢甚至失去響應(yīng).?如果記錄數(shù)上十萬以上這些操作,?可以把這些SQL語(yǔ)句分段分次完成,
其間加上COMMIT?確認(rèn)事物處理.
--
------------------------------------------------------------------------------
jxdco
二.數(shù)據(jù)定義?(DDL)?部分
1
.
CREATE
?(創(chuàng)建表,?索引,?視圖,?同義詞,?過程,?函數(shù),?數(shù)據(jù)庫(kù)鏈接等)
ORACLE常用的字段類型有
CHAR
?固定長(zhǎng)度的字符串
VARCHAR2
?可變長(zhǎng)度的字符串
NUMBER
(M,N)?數(shù)字型M是位數(shù)總長(zhǎng)度,?N是小數(shù)的長(zhǎng)度
DATE?日期類型
創(chuàng)建表時(shí)要把較小的不為空的字段放在前面,?可能為空的字段放在后面
創(chuàng)建表時(shí)可以用中文的字段名,?但最好還是用英文的字段名
創(chuàng)建表時(shí)可以給字段加上默認(rèn)值,?例如?
DEFAULT
?SYSDATE
這樣每次插入和修改時(shí),?不用程序操作這個(gè)字段都能得到動(dòng)作的時(shí)間
創(chuàng)建表時(shí)可以給字段加上約束條件
例如?不允許重復(fù)?
UNIQUE
,?關(guān)鍵字?
PRIMARY
?
KEY
2
.
ALTER
?(改變表,?索引,?視圖等)
改變表的名稱
ALTER
?
TABLE
?表名1?
TO
?表名2;
在表的后面增加一個(gè)字段
ALTER
?TABLE表名?
ADD
?字段名?字段名描述;
修改表里字段的定義描述
ALTER
?TABLE表名?MODIFY字段名?字段名描述;
給表里的字段加上約束條件
ALTER
?
TABLE
?表名?
ADD
?
CONSTRAINT
?約束名?
PRIMARY
?
KEY
?(字段名);
ALTER
?
TABLE
?表名?
ADD
?
CONSTRAINT
?約束名?
UNIQUE
?(字段名);
把表放在或取出數(shù)據(jù)庫(kù)的內(nèi)存區(qū)
ALTER
?
TABLE
?表名?CACHE;
ALTER
?
TABLE
?表名?NOCACHE;
3
.
DROP
?(刪除表,?索引,?視圖,?同義詞,?過程,?函數(shù),?數(shù)據(jù)庫(kù)鏈接等)
刪除表和它所有的約束條件
DROP
?
TABLE
?表名?
CASCADE
?CONSTRAINTS;
4
.
TRUNCATE
?(清空表里的所有記錄,?保留表的結(jié)構(gòu))
TRUNCATE
?表名;
三.查詢語(yǔ)句?(
SELECT
)?部分
SELECT字段名1,?字段名2,?……?
FROM
?表名1,?
[
表名2,?……
]
?
WHERE
?條件;
字段名可以帶入函數(shù)
例如:?
COUNT
(
*
),?
MIN
(字段名),?
MAX
(字段名),?
AVG
(字段名),?
DISTINCT
(字段名),
TO_CHAR(DATE字段名,
'
YYYY-MM-DD?HH24:MI:SS
'
)
NVL(EXPR1,?EXPR2)函數(shù)
解釋:
IF
?EXPR1
=
NULL
RETURN
?EXPR2
ELSE
RETURN
?EXPR1
DECODE(AA﹐V1﹐R1﹐V2﹐R2
.)函數(shù)
解釋:
IF
?AA
=
V1?
THEN
?
RETURN
?R1
IF
?AA
=
V2?
THEN
?
RETURN
?R2
..…
--
------------------------------------------------------------------------------
jxdco
ELSE
RETURN
?
NULL
LPAD(char1,n,char2)函數(shù)
解釋:
字符char1按制定的位數(shù)n顯示，不足的位數(shù)用char2字符串替換左邊的空位
字段名之間可以進(jìn)行算術(shù)運(yùn)算
例如:?(字段名1
*
字段名1)
/
3
查詢語(yǔ)句可以嵌套
例如:?
SELECT
?……?
FROM
(
SELECT
?……?FROM表名1,?
[
表名2,?……
]
?
WHERE
?條件)?
WHERE
?條件2;
兩個(gè)查詢語(yǔ)句的結(jié)果可以做集合操作
例如:?并集UNION(去掉重復(fù)記錄),?并集UNION?
ALL
(不去掉重復(fù)記錄),?差集MINUS,?交集INTERSECT
分組查詢
SELECT字段名1,?字段名2,?……?
FROM
?表名1,?
[
表名2,?……
]
?
GROUP
?BY字段名1
[
HAVING?條件
]
?;
兩個(gè)以上表之間的連接查詢
SELECT字段名1,?字段名2,?……?
FROM
?表名1,?
[
表名2,?……
]
?
WHERE
表名1.字段名?
=
?表名2.?字段名?
[
?AND?……
]
?;
SELECT字段名1,?字段名2,?……?
FROM
?表名1,?
[
表名2,?……
]
?
WHERE
表名1.字段名?
=
?表名2.?字段名(
+
)?
[
?AND?……
]
?;
有(
+
)號(hào)的字段位置自動(dòng)補(bǔ)空值
查詢結(jié)果集的排序操作,?默認(rèn)的排序是升序ASC,?降序是DESC
SELECT字段名1,?字段名2,?……?
FROM
?表名1,?
[
表名2,?……
]
ORDER
?BY字段名1,?字段名2?
DESC
;
字符串模糊比較的方法
INSTR(字段名,?‘字符串’)
>
0
字段名?
LIKE
?‘字符串
%
’?
[
‘%字符串%’
]
每個(gè)表都有一個(gè)隱含的字段ROWID,?它標(biāo)記著記錄的唯一性.
四.ORACLE里常用的數(shù)據(jù)對(duì)象?(
SCHEMA
)
1
.索引?(
INDEX
)
CREATE
?
INDEX
?索引名ON?表名?(?字段1,?
[
字段2,?……
]
?);
ALTER
?
INDEX
?索引名?REBUILD;
一個(gè)表的索引最好不要超過三個(gè)?(特殊的大表除外),?最好用單字段索引,?結(jié)合SQL語(yǔ)句的分析執(zhí)行情況,
也可以建立多字段的組合索引和基于函數(shù)的索引
ORACLE8.1.7字符串可以索引的最大長(zhǎng)度為1578?單字節(jié)
ORACLE8.0.6字符串可以索引的最大長(zhǎng)度為758?單字節(jié)
2
.視圖?(
VIEW
)
CREATE
?
VIEW
?視圖名AS?
SELECT
?….?
FROM
?…..;
ALTER
?VIEW視圖名?COMPILE;
視圖僅是一個(gè)SQL查詢語(yǔ)句,?它可以把表之間復(fù)雜的關(guān)系簡(jiǎn)潔化.
3
.同義詞?(SYNONMY)
CREATE
?SYNONYM同義詞名FOR?表名;
CREATE
?SYNONYM同義詞名FOR?表名
@數(shù)據(jù)庫(kù)鏈接名
;
4
.數(shù)據(jù)庫(kù)鏈接?(
DATABASE
?LINK)
CREATE
?
DATABASE
?LINK數(shù)據(jù)庫(kù)鏈接名CONNECT?
TO
?用戶名?IDENTIFIED?
BY
?密碼?USING?‘?dāng)?shù)據(jù)庫(kù)連接字符串’;
數(shù)據(jù)庫(kù)連接字符串可以用NET8?EASY?CONFIG或者直接修改TNSNAMES.ORA里定義.
數(shù)據(jù)庫(kù)參數(shù)global_name
=
true時(shí)要求數(shù)據(jù)庫(kù)鏈接名稱跟遠(yuǎn)端數(shù)據(jù)庫(kù)名稱一樣
數(shù)據(jù)庫(kù)全局名稱可以用以下命令查出
SELECT
?
*
?
FROM
?GLOBAL_NAME;
查詢遠(yuǎn)端數(shù)據(jù)庫(kù)里的表
SELECT
?……?
FROM
?表名
@數(shù)據(jù)庫(kù)鏈接名
;
五.權(quán)限管理?(DCL)?語(yǔ)句
1
.
GRANT
?賦于權(quán)限
常用的系統(tǒng)權(quán)限集合有以下三個(gè):
CONNECT(基本的連接),?RESOURCE(程序開發(fā)),?DBA(數(shù)據(jù)庫(kù)管理)
常用的數(shù)據(jù)對(duì)象權(quán)限有以下五個(gè):
ALL
?
ON
?數(shù)據(jù)對(duì)象名,?
SELECT
?
ON
?數(shù)據(jù)對(duì)象名,?
UPDATE
?
ON
?數(shù)據(jù)對(duì)象名,
DELETE
?
ON
?數(shù)據(jù)對(duì)象名,?
INSERT
?
ON
?數(shù)據(jù)對(duì)象名,?
ALTER
?
ON
?數(shù)據(jù)對(duì)象名
GRANT
?CONNECT,?RESOURCE?
TO
?用戶名;
GRANT
?
SELECT
?
ON
?表名?
TO
?用戶名;
GRANT
?
SELECT
,?
INSERT
,?
DELETE
?ON表名?
TO
?用戶名1,?用戶名2;
2
.
REVOKE
?回收權(quán)限
REVOKE
?CONNECT,?RESOURCE?
FROM
?用戶名;
REVOKE
?
SELECT
?
ON
?表名?
FROM
?用戶名;
REVOKE
?
SELECT
,?
INSERT
,?
DELETE
?ON表名?
FROM
?用戶名1,?用戶名2;
查詢數(shù)據(jù)庫(kù)中第63號(hào)錯(cuò)誤：
select
?orgaddr,destaddr?
from
?sm_histable0116?
where
?error_code
=
'
63
'
;
查詢數(shù)據(jù)庫(kù)中開戶用戶最大提交和最大下發(fā)數(shù)：?
select
?MSISDN,TCOS,OCOS?
from
?ms_usertable；
查詢數(shù)據(jù)庫(kù)中各種錯(cuò)誤代碼的總和：
select
?error_code,
count
(
*
)?
from
?sm_histable0513?
group
?
by
?error_code?
order
by
?error_code;
查詢報(bào)表數(shù)據(jù)庫(kù)中話單統(tǒng)計(jì)種類查詢。
select
?
sum
(Successcount)?
from
?tbl_MiddleMt0411?
where
?ServiceType2
=
111
select
?
sum
(successcount),servicetype?
from
?tbl_middlemt0411?
group
?
by
?servicetype
以下轉(zhuǎn)自:WWW.LOVEUNIX.COM MADE BY SDAWNYJ
1、查看表空間的名稱及大小
select?t.tablespace_name,?round(sum(bytes/(1024*1024)),0)?ts_size
from?dba_tablespaces?t,?dba_data_files?d
where?t.tablespace_name?=?d.tablespace_name
group?by?t.tablespace_name;
2、查看表空間物理文件的名稱及大小
select?tablespace_name,?file_id,?file_name,
round(bytes/(1024*1024),0)?total_space
from?dba_data_files
order?by?tablespace_name;
3、查看回滾段名稱及大小
select?segment_name,?tablespace_name,?r.status,
(initial_extent/1024)?InitialExtent,(next_extent/1024)?NextExtent,
max_extents,?v.curext?CurExtent
From?dba_rollback_segs?r,?v$rollstat?v
Where?r.segment_id?=?v.usn(+)
order?by?segment_name?;
4、查看控制文件
select?name?from?v$controlfile;
5、查看日志文件
select?member?from?v$logfile;
6、查看表空間的使用情況
select?sum(bytes)/(1024*1024)?as?free_space,tablespace_name
from?dba_free_space
group?by?tablespace_name;
SELECT?A.TABLESPACE_NAME,A.BYTES?TOTAL,B.BYTES?USED,?C.BYTES?FREE,
(B.BYTES*100)/A.BYTES?"%?USED",(C.BYTES*100)/A.BYTES?"%?FREE"
FROM?SYS.SM$TS_AVAIL?A,SYS.SM$TS_USED?B,SYS.SM$TS_FREE?C
WHERE?A.TABLESPACE_NAME=B.TABLESPACE_NAME?AND?A.TABLESPACE_NAME=C.TABLESPACE_NAME;
7、查看數(shù)據(jù)庫(kù)庫(kù)對(duì)象
select?owner,?object_type,?status,?count(*)?count#?from?all_objects?group?by?owner,?object_type,?status;
8、查看數(shù)據(jù)庫(kù)的版本
Select?version?FROM?Product_component_version
Where?SUBSTR(PRODUCT,1,6)='Oracle';
9、查看數(shù)據(jù)庫(kù)的創(chuàng)建日期和歸檔方式
Select?Created,?Log_Mode,?Log_Mode?From?V$Database;
10、捕捉運(yùn)行很久的SQL
column?username?format?a12
column?opname?format?a16
column?progress?format?a8
select?username,sid,opname,
round(sofar*100?/?totalwork,0)?||?'%'?as?progress,
time_remaining,sql_text
from?v$session_longops?,?v$sql
where?time_remaining?<>?0
and?sql_address?=?address
and?sql_hash_value?=?hash_value
/
11。查看數(shù)據(jù)表的參數(shù)信息
SELECT?partition_name,?high_value,?high_value_length,?tablespace_name,
pct_free,?pct_used,?ini_trans,?max_trans,?initial_extent,
next_extent,?min_extent,?max_extent,?pct_increase,?FREELISTS,
freelist_groups,?LOGGING,?BUFFER_POOL,?num_rows,?blocks,
empty_blocks,?avg_space,?chain_cnt,?avg_row_len,?sample_size,
last_analyzed
FROM?dba_tab_partitions
--WHERE?table_name?=?:tname?AND?table_owner?=?:towner
ORDER?BY?partition_position
12.查看還沒提交的事務(wù)
select?*?from?v$locked_object;
select?*?from?v$transaction;
13。查找object為哪些進(jìn)程所用
select
p.spid,
s.sid,
s.serial#?serial_num,
s.username?user_name,
a.type?object_type,
s.osuser?os_user_name,
a.owner,
a.object?object_name,
decode(sign(48?-?command),
1,
to_char(command),?'Action?Code?#'?||?to_char(command)?)?action,
p.program?oracle_process,
s.terminal?terminal,
s.program?program,
s.status?session_status
from?v$session?s,?v$access?a,?v$process?p
where?s.paddr?=?p.addr?and
s.type?=?'USER'?and
a.sid?=?s.sid?and
a.object='SUBSCRIBER_ATTR'
order?by?s.username,?s.osuser
14。回滾段查看
select?rownum,?sys.dba_rollback_segs.segment_name?Name,?v$rollstat.extents
Extents,?v$rollstat.rssize?Size_in_Bytes,?v$rollstat.xacts?XActs,
v$rollstat.gets?Gets,?v$rollstat.waits?Waits,?v$rollstat.writes?Writes,
sys.dba_rollback_segs.status?status?from?v$rollstat,?sys.dba_rollback_segs,
v$rollname?where?v$rollname.name(+)?=?sys.dba_rollback_segs.segment_name?and
v$rollstat.usn?(+)?=?v$rollname.usn?order?by?rownum
15。耗資源的進(jìn)程（top?session）
select?s.schemaname?schema_name,?decode(sign(48?-?command),?1,
to_char(command),?'Action?Code?#'?||?to_char(command)?)?action,?status
session_status,?s.osuser?os_user_name,?s.sid,?p.spid?,?s.serial#?serial_num,
nvl(s.username,?'[Oracle?process]')?user_name,?s.terminal?terminal,
s.program?program,?st.value?criteria_value?from?v$sesstat?st,?v$session?s?,?v$process?p
where?st.sid?=?s.sid?and?st.statistic#?=?to_number('38')?and?('ALL'?=?'ALL'
or?s.status?=?'ALL')?and?p.addr?=?s.paddr?order?by?st.value?desc,?p.spid?asc,?s.username?asc,?s.osuser?asc
16。查看鎖（lock）情況
select?/**//*+?RULE?*/?ls.osuser?os_user_name,?ls.username?user_name,
decode(ls.type,?'RW',?'Row?wait?enqueue?lock',?'TM',?'DML?enqueue?lock',?'TX',
'Transaction?enqueue?lock',?'UL',?'User?supplied?lock')?lock_type,
o.object_name?object,?decode(ls.lmode,?1,?null,?2,?'Row?Share',?3,
'Row?Exclusive',?4,?'Share',?5,?'Share?Row?Exclusive',?6,?'Exclusive',?null)
lock_mode,?o.owner,?ls.sid,?ls.serial#?serial_num,?ls.id1,?ls.id2
from?sys.dba_objects?o,?(?select?s.osuser,?s.username,?l.type,
l.lmode,?s.sid,?s.serial#,?l.id1,?l.id2?from?v$session?s,
v$lock?l?where?s.sid?=?l.sid?)?ls?where?o.object_id?=?ls.id1?and?o.owner
<>?'SYS'?order?by?o.owner,?o.object_name
17。查看等待（wait）情況
SELECT?v$waitstat.class,?v$waitstat.count?count,?SUM(v$sysstat.value)?sum_value
FROM?v$waitstat,?v$sysstat?WHERE?v$sysstat.name?IN?('db?block?gets',
'consistent?gets')?group?by?v$waitstat.class,?v$waitstat.count
18。查看sga情況
SELECT?NAME,?BYTES?FROM?SYS.V_$SGASTAT?ORDER?BY?NAME?ASC
19。查看catched?object
SELECT?owner,?name,?db_link,?namespace,
type,?sharable_mem,?loads,?executions,
locks,?pins,?kept?FROM?v$db_object_cache
20。查看V$SQLAREA
SELECT?SQL_TEXT,?SHARABLE_MEM,?PERSISTENT_MEM,?RUNTIME_MEM,?SORTS,
VERSION_COUNT,?LOADED_VERSIONS,?OPEN_VERSIONS,?USERS_OPENING,?EXECUTIONS,
USERS_EXECUTING,?LOADS,?FIRST_LOAD_TIME,?INVALIDATIONS,?PARSE_CALLS,?DISK_READS,
BUFFER_GETS,?ROWS_PROCESSED?FROM?V$SQLAREA
21。查看object分類數(shù)量
select?decode?(o.type#,1,'INDEX'?,?2,'TABLE'?,?3?,?'CLUSTER'?,?4,?'VIEW'?,?5?,
'SYNONYM'?,?6?,?'SEQUENCE'?,?'OTHER'?)?object_type?,?count(*)?quantity?from
sys.obj$?o?where?o.type#?>?1?group?by?decode?(o.type#,1,'INDEX'?,?2,'TABLE'?,?3
,?'CLUSTER'?,?4,?'VIEW'?,?5?,?'SYNONYM'?,?6?,?'SEQUENCE'?,?'OTHER'?)?union?select
'COLUMN'?,?count(*)?from?sys.col$?union?select?'DB?LINK'?,?count(*)?from
22。按用戶查看object種類
select?u.name?schema,?sum(decode(o.type#,?1,?1,?NULL))?indexes,
sum(decode(o.type#,?2,?1,?NULL))?tables,?sum(decode(o.type#,?3,?1,?NULL))
clusters,?sum(decode(o.type#,?4,?1,?NULL))?views,?sum(decode(o.type#,?5,?1,
NULL))?synonyms,?sum(decode(o.type#,?6,?1,?NULL))?sequences,
sum(decode(o.type#,?1,?NULL,?2,?NULL,?3,?NULL,?4,?NULL,?5,?NULL,?6,?NULL,?1))
others?from?sys.obj$?o,?sys.user$?u?where?o.type#?>=?1?and?u.user#?=
o.owner#?and?u.name?<>?'PUBLIC'?group?by?u.name?order?by
sys.link$?union?select?'CONSTRAINT'?,?count(*)?from?sys.con$
23。有關(guān)connection的相關(guān)信息
1）查看有哪些用戶連接
select?s.osuser?os_user_name,?decode(sign(48?-?command),?1,?to_char(command),
'Action?Code?#'?||?to_char(command)?)?action,?p.program?oracle_process,
status?session_status,?s.terminal?terminal,?s.program?program,
s.username?user_name,?s.fixed_table_sequence?activity_meter,?''?query,
0?memory,?0?max_memory,?0?cpu_usage,?s.sid,?s.serial#?serial_num
from?v$session?s,?v$process?p?where?s.paddr=p.addr?and?s.type?=?'USER'
order?by?s.username,?s.osuser
2）根據(jù)v.sid查看對(duì)應(yīng)連接的資源占用等情況
select?n.name,
v.value,
n.class,
n.statistic#
from?v$statname?n,
v$sesstat?v
where?v.sid?=?71?and
v.statistic#?=?n.statistic#
order?by?n.class,?n.statistic#
3）根據(jù)sid查看對(duì)應(yīng)連接正在運(yùn)行的sql
select?/**//*+?PUSH_SUBQ?*/
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate?start_time,
sysdate?finish_time,
'>'?||?address?sql_address,
'N'?status
from?v$sqlarea
where?address?=?(select?sql_address?from?v$session?where?sid?=?71)
24．查詢表空間使用情況select?a.tablespace_name?"表空間名稱",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)?"占用率(%)",
round(a.bytes_alloc/1024/1024,2)?"容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2)?"空閑(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)?"使用(M)",
Largest?"最大擴(kuò)展段(M)",
to_char(sysdate,'yyyy-mm-dd?hh24:mi:ss')?"采樣時(shí)間"
from?(select?f.tablespace_name,
sum(f.bytes)?bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))?maxbytes
from?dba_data_files?f
group?by?tablespace_name)?a,
(select?f.tablespace_name,
sum(f.bytes)?bytes_free
from?dba_free_space?f
group?by?tablespace_name)?b,
(select?round(max(ff.length)*16/1024,2)?Largest,
ts.name?tablespace_name
from?sys.fet$?ff,?sys.file$?tf,sys.ts$?ts
where?ts.ts#=ff.ts#?and?ff.file#=tf.relfile#?and?ts.ts#=tf.ts#
group?by?ts.name,?tf.blocks)?c
where?a.tablespace_name?=?b.tablespace_name?and?a.tablespace_name?=?c.tablespace_name
25.?查詢表空間的碎片程度
select?tablespace_name,count(tablespace_name)?from?dba_free_space?group?by?tablespace_name
having?count(tablespace_name)>10;
alter?tablespace?name?coalesce;
alter?table?name?deallocate?unused;
create?or?replace?view?ts_blocks_v?as
select?tablespace_name,block_id,bytes,blocks,'free?space'?segment_name?from?dba_free_space
union?all
select?tablespace_name,block_id,bytes,blocks,segment_name?from?dba_extents;
select?*?from?ts_blocks_v;
select?tablespace_name,sum(bytes),max(bytes),count(block_id)?from?dba_free_space
group?by?tablespace_name;
26.查看有哪些實(shí)例在運(yùn)行：
select?*?from?v$active_instances;