ST 's Ch01 Oracle常識及常用SQL

SpringTang發表於2007-08-31
OracleSQL
Oracle不得不知道的問題
  1. 一個表空間只能屬於一個資料庫
  2. 每個資料庫至少有一個控制檔案（建議3個並分別存在不同的磁碟上）
  3. 每個資料庫至少有一個表空間（System表空間）
  4. 每個資料庫至少兩個聯機日誌檔案（redoLog檔案）
  5. 一個資料檔案只能屬於一個表空間
  6. 一個資料檔案一旦加入到一個表空間中就不能被移除，也不能加入到其他表空間中
  7. 建立新的表空間需要建立新的資料檔案
  8. 資料檔案被Oracle格式化為Oracle塊，且塊的大小在建立資料庫是指定，以後不能更改
  9. 單一一個事務不能跨越多個回滾段
  10. 索引表不含ROWID
  11. 一個Oracle塊的最大大小為16KB（2K，4K，8K，16K）
  12. 一個Oracle資料庫可以有多個Oracle例項啟動
  13. 一套作業系統中只能安裝一個版本的Oracle
[@more@]
SQL語句
1. 獲取有哪些使用者在使用資料庫
select username from v$session;
select count(*) from v$session;
2. 獲取資料庫的SID
select name from v$database;

3. 檢視最大會話數
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';

4. 檢視資料庫中所有使用者及所有表
Select owner, table_name from dba_tables;

5. 分辨某個使用者是從哪臺機器登陸ORACLE的
SELECT machine , terminal FROM V$SESSION;

6. 查詢每個使用者的許可權
SELECT * FROM DBA_SYS_PRIVS;
7. 查詢當前使用者物件
SELECT * FROM USER_OBJECTS;

表空間管理和使用者管理

8. 查詢表空間資訊
SELECT * FROM DBA_DATA_FILES;

9. 執行SQLPLUS時不用輸入使用者名稱和密碼，進入之後使用CONNECT
SQLPLUS /NOLOG
SQL>CONNECT SCOTT/TIGER
10. 把表移到另一個表空間
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;
11. 建立表
CREAE TABLE TABLENAME
(COLUMN1 COLUTYPE DEFAULT(VALUE) NOT NULL)
(COLUMN2 COLUTYPE DEFAULT(VALUE) NOT NULL);
12. 建立主鍵
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)

13. 刪除約束
ALTER TABLE TABLENAME DROP CONSTRAINT constraintname;
DROP TABLE TABLENAEM CASCADE CONSTRAINTS;(刪除表後將所用的外來鍵刪除)

14. 給表增加列
ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(VALUE) NOT NULL;

15. 給列增加預設值
ALTER TABLE TABLENAME
MODIFY COLUMNNAME DEFAULT(VALUE) NOT NULL;

16. 建立主鍵
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)
17. 給表增加外來鍵
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME
FOREIGN KEY(COLUMN) REFERENCES TABLE1NAME(COLUMN1);

18. 重新建立索引
ALTER INDEX INDEXNAME REBUILD TABLESPACE TABLESPACE;

19. 建立使用者、賦予許可權
CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA
DEFAULT
TABLESPACE USER_DATA TEMPORARY
TABLESPACE USER_DATA ACCOUNT UNLOCK;

GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;
20. 修改資料檔案大小
ALTER DATABASE
DATAFILE 'c:USERS01113.DBF' RESIZE 40M;

21. 刪除表空間
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
22. 增加資料檔案
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01113.DBF' SIZE 50M;
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01114.DBF' SIZE 50M
AUTOEXTEND ON
23. 檢視錶空間和資料檔案
select file_name,tablespace_name,autoextensible from dba_data_files;

24. 在SQLPLUS中呼叫儲存過程
SET SERVEROUTPUT ON
declare
out_param varchar2(100);
begin
your_proc(1,out_param);
dbms_output.put_line(out_param);
end;
/
SET SERVEROUTPUT OFF

25. 檢視當前會話

(1) userenv() 函式
select userenv('language') from dual 字符集
select userenv('isdba') from dual 是否DBA
select userenv('sessionid') from dual sessionid
select userenv('TERMINAL') from dual 客戶端名字
select userenv('INSTANCE') from dual 例項數

(2) SYS_CONTEXT() 函式
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual; 當前模式
select SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual; 當前模式ID
select SYS_CONTEXT('USERENV','CURRENT_USER') from dual; 當前使用者
select SYS_CONTEXT('USERENV','DB_NAME') from dual; 資料庫
select SYS_CONTEXT('USERENV','HOST') from dual; 主機
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual; 檢視IP

26. 使用OEM備份或者EXP的步驟

WIN2000下：
(1). 控制皮膚――>管理工具―― >計算機管理――>本地使用者和組――>使用者――>新建使用者sys和sysman(sys和sysman 的帳號要和登陸資料庫的帳號相同)；
(2)．控制皮膚――>管理工具―― >本地安全策略――>本地策略――>使用者權利指派――>
作為批處理作業登陸――>新增sys和sysman兩個帳號。
(3)．使用Enterprise Manager配置輔助工具
開始→程式→Oracle - OraHome81→Enterprise Manager→Configuration Assistant
a、使用Configuration Assistant工具來建立一個新的資料檔案庫。
(4)．控制皮膚――>管理工具―― > 服務，檢視OracleOraHome81ManagementServer是否啟動，如果沒有啟動，則手動啟動該服務。
(5)．以sysman/oem_temp（default）登陸DBA Studio
（第二個選項：登陸到Oracle Management Server）,立即修改密碼為你剛才在NT下建的使用者sysman的密碼。
(6)． 以sysman/ *** (bluesky) 從開始→程式→Oracle - OraHome81→Console 登陸到 控制檯。
在 系統→首選項→首選身份證明（我的首選身份設定如下：）
DEFAULT節點：name:sysman
DEFAULT資料庫：name:sys
(7)． 在搜尋/新增結點後，以sysman/ *** 登陸到該結點，以sys/ *** as sysdba登陸資料庫（也就是在首選身份設定的結果）。
(8)． 在工具→備份管理→嚮導→預定義備份策略（自定義備份策略）→提交備份計劃
(9)．從開始→程式→Oracle - OraHome81→Console 登陸到 控制檯，檢視活動（歷史記錄）可以看到你的備份是否成功，如果不成功，可以點選備份看明細。（我第一次也沒成功，後來我修改系統的臨時目錄C:WINNTTemp→c: empsystmp,重新啟動機器就ok了）

27. 修改表的列名
Oracle9i:
alter table xxx rename column xx to yy;
Oracle8i & lower version
connect sys/passed;
update col$ set name=xx where obj#=物件id and name = 欄位
(一般不要這樣用，會造成意想不到的結果)
注：最好是刪除再建立新的列

28. ORACLE的登入問題，使用者名稱和密碼
可以直接輸入：
internal/oracle@serivce_name
　　sys/change_on_install@serivce_name
　　system/manager@serivce_name
　　scott/tiger@serivce_name
注意：
9i中沒有internal/oracle
如果選擇典型安裝則有　scott使用者
如果自定義可以不安裝 　scott使用者
如果是本機則可以省略@serivce_name
oem:（ORACLE ENTERPRISE MANAGER）
sysman/oem_temp

29. 如何在Windows 2000下將Oracle完全解除安裝？
一、系統環境：
(1)、作業系統：Windows 2000 Server，機器記憶體128M
(2)、資料庫：　Oracle 8i R2 (8.1.6) for NT 企業版
(3)、安裝路徑：D:ORACLE
二、解除安裝步驟：
(1)、開始－＞設定－＞控制皮膚－＞管理工具－＞服務
停止所有Oracle服務。
(2)、開始－＞程式－＞Oracle - OraHome81－＞Oracle Installation Products－＞Universal Installer
卸裝所有Oracle產品
(3)、執行regedit，選擇HKEY_LOCAL_MACHINESOFTWAREORACLE，按del鍵刪除這個入口。
(4)、執行regedit，選擇HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices，滾動這個列表，刪除所有Oracle入口
(5)、從桌面上、STARTUP（啟動）組、程式選單中，刪除所有有關Oracle的組和圖示
(6)、重新啟動計算機，重起後才能完全刪除Oracle所在目錄
(7)、刪除與Oracle有關的檔案，選擇Oracle所在的預設目錄C:Oracle，刪除這個入口目錄及所有子目錄，
　 並從Windows 2000目錄（一般為C:WINNT）下刪除以下檔案
　 ORACLE.INI、oradim80.INI
(8)、WIN.INI檔案中若有[ORACLE]的標記段，刪除該段
--------------------------------------------------------------------

30. exp與imp的具體用法

exp username/password@mzbs_61 full=y file=yourdata.dmp grants=y rows=y
imp username/password full=y ignore=y file=yourdata.dmp grants=y
exp mzbs/mzbs@mzbs_61 file = c:zzzzzzz.dmp grants = y rows = y
imp mzbs/mzbs@mzbs_61 file = c:zzzzzzz.dmp grants = y ignore=y FULL=Y
(1)
exp引數：
關鍵字 說明（預設）
----------------------------------------------
USERID 使用者名稱/口令
FULL 匯出整個檔案 (N)
BUFFER 資料緩衝區的大小
OWNER 所有者使用者名稱列表
FILE 輸出檔案 (EXPDAT.DMP)
TABLES 表名列表
COMPRESS 匯入一個範圍 (Y)
RECORDLENGTH IO 記錄的長度
GRANTS 匯出許可權 (Y)
INCTYPE 增量匯出型別
INDEXES 匯出索引 (Y)
RECORD 跟蹤增量匯出 (Y)
ROWS 匯出資料行 (Y)
PARFILE 引數檔名
CONSTRAINTS 匯出限制 (Y)
CONSISTENT 交叉表一致性
LOG 螢幕輸出的日誌檔案
STATISTICS 分析物件 (ESTIMATE)
DIRECT 直接路徑 (N)
TRIGGERS 匯出觸發器 (Y)
FEEDBACK 顯示每 x 行 (0) 的進度
FILESIZE 各轉儲檔案的最大尺寸
QUERY 選定匯出表子集的子句
imp引數：
關鍵字 說明（預設）
----------------------------------------------
USERID 使用者名稱/口令
FULL 匯入整個檔案 (N)
BUFFER 資料緩衝區大小
FROMUSER 所有人使用者名稱列表
FILE 輸入檔案 (EXPDAT.DMP)
TOUSER 使用者名稱列表
SHOW 只列出檔案內容 (N)
TABLES 表名列表
IGNORE 忽略建立錯誤 (N)
RECORDLENGTH IO 記錄的長度
GRANTS 匯入許可權 (Y)
INCTYPE 增量匯入型別
INDEXES 匯入索引 (Y)
COMMIT 提交陣列插入 (N)
ROWS 匯入資料行 (Y)
PARFILE 引數檔名
LOG 螢幕輸出的日誌檔案
CONSTRAINTS 匯入限制 (Y)
DESTROY 覆蓋表空間資料檔案 (N)
INDEXFILE 將表/索引資訊寫入指定的檔案
SKIP_UNUSABLE_INDEXES 跳過不可用索引的維護 (N)
ANALYZE 執行轉儲檔案中的 ANALYZE 語句 (Y)
FEEDBACK 顯示每 x 行 (0) 的進度
TOID_NOVALIDATE 跳過指定型別 id 的校驗
FILESIZE 各轉儲檔案的最大尺寸
RECALCULATE_STATISTICS 重新計算統計值 (N)
(2)
一、建立一個expdata.sql檔案
USERID=RMTAFIS/3 這裡寫你的使用者名稱和密碼
BUFFER=32768
OWNER=RMTAFIS 這裡寫匯出的使用者
FILE=E:ExpRMTAFIS.DMP 匯出的檔案,可以是相對路徑
ROWs=Y
GRANTS=Y
COMPRESS=Y
CONSISTENT=Y
二、建立一個expdata.bat
exp parfile=expdata.sql
如果是805
exp80 parfile=expdata.sql
雙擊expdata.bat就匯出資料了

30. 全資料庫的匯入與匯出
exp username/password full=y file=yourdata.dmp grants=y rows=y
imp username/password full=y ignore=y file=yourdata.dmp grants=y

31. 單引號的插入問題
SQL> insert into a values('i''m good');　　　　　　 --兩個''可以表示一個'
SQL> insert into a values('i'||chr(39)||'m good');　--chr(39)代表字元'
SQL> insert into a values('a'||'&'||'b');　　　　　

32. ORACLE動態庫及配置檔案
Tnsnames.ora CORE35O.DLL NASNSNT.DLL NAUNTSNT.DLL NCRNT.DLL Nlnt.dll NLSRTL32.DLL Nnfdnt.dll NNFNNT.DLL NSNT.DLL NTNT.DLL NTTNT.DLL CIW32.DLL Ora73.dll OTRACE73.DLL Sqlnet.ora Sqltnsnt.dll CORE35.DLL

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7189057/viewspace-967249/，如需轉載，請註明出處，否則將追究法律責任。

相關文章