[原創]Oracle常見問題總結

SQL 語句1.       擷取有哪些使用者在使用資料庫 select username 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/TIGER10.       把表移到另一個資料表空間
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:/WINNT/Temp→c:/temp/systmp,重新啟動機器就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_nameoem:（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_MACHINE/SOFTWARE/ORACLE，按del鍵刪除這個入口。(4)、運行regedit，選擇HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services，滾動這個列表，刪除所有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=yexp 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:/Exp/RMTAFIS.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