Oracle日常維護命令
測試環境：Oracle 10g
一、啟動、關閉數據庫
1、啟動實例的各種模式
1）啟動實例，裝載并打開數據庫
這是最普通的數據庫操作，意味著某個實例已經啟動并且數據庫也已經裝載并打開。這種模式允許任何一個有效的用戶連接到數據庫，并執行典型的數據訪問操作。啟動實例接著從默認的服務器參數文件位置讀取初始化參數，然后通過STARTUP命令來裝載和打開數據庫：
SQL> STARTUP
2）啟動實例，不裝載數據庫
啟動實例而不用裝載數據庫，通常只在整個數據庫創建過程中使用該模式：
SQL> STARTUP NOMOUNT
3）啟動實例，并裝載數據庫
可以啟動實例并裝載數據庫，但不打開數據庫。該模式允許執行特定的維護操作，例如在下面的任務中必須裝載數據庫而不打開數據庫。
* 重命名數據文件
* 添加取消或重命名重做日志文件
* 允許和禁止重做日志存檔選項
* 執行完整的數據為恢復操作
SQL> STARTUP MOUNT
4）在啟動過程中限制訪問數據庫
可以在受限制的模式下啟動實例并裝載和打開數據庫，以便只有管理人員而不是一般的數據庫用戶可以利用數據庫。當需要完成以下一種任務時，使用這種數據庫啟動模式：
* 執行數據庫數據的導入或導出操作
* 執行數據庫裝載操作用SQL*Loader
* 暫時阻止一般的用戶使用數據
* 在某個移植過程和升級操作過程中
SQL> STARTUP RESTRICT
5）迫使實例啟動
在一些特殊環境下，可能會在啟動數據庫實例的時候遇到一些問題。一般不要迫使數據庫啟動，除非存在以下情形：
* 用SHUTDOWN NORMAL、SHUTDOWN IMMEDIATE、SHUTDOWN TRANSACTIONAL命令不能關閉當前的實例時。
* 在啟動實例的時候遇到一些問題時。
SQL> STARTUP FORCE
6）啟動實例，裝載數據庫，并啟動完整的介質恢復過程
如果知道需要介質恢復過程，就可以啟動實例并為其裝載數據庫，以及通過使用帶RECOVER選項的STARTUP命令的方法來自動啟動恢復過程。
STARTUP OPEN RECOVER
2、改變數據庫的可用性
1）為實例裝載數據庫
SQL> ALTER DATABASE MOUNT;
2）打開一個關閉的數據庫
SQL> ALTER DATABASE OPEN;
3）以只讀模式打開數據庫
SQL> ALTER DATABASE OPEN READ ONLY;
以讀寫模式打開數據庫
SQL> ALTER DATABASE OPEN READ WRITE;
3、關閉數據庫
1）以正常模式關閉數據庫
不允許新的連接、等待會話結束、等待事務結束、做一個檢查點并關閉數據文件。啟動時不需要實例恢復。
SQL> SHUTDOWN NORMAL
2）以立即模式關閉數據庫
不允許新的連接、不等待會話結束、不等待事務結束、做一個檢查點并關閉數據文件。沒有結束的事務是自動rollback的。啟動時不需要實例恢復。
SQL> SHUTDOWN IMMEDIATE
3）以事務模式關閉數據庫
不允許新的連接、不等待會話結束、等待事務結束、做一個檢查點并關閉數據文件。啟動時不需要實例恢復。
SQL> SHUTDOWN TRANSACTIONAL
4）以終止模式關閉數據庫
不允許新的連接、不等待會話結束、不等待事務結束、不做檢查點且沒有關閉數據文件。啟動時自動進行實例恢復。
SQL> SHUTDOWN ABORT
二、用戶登錄
1、以沒有連接數據庫的方式啟動SQL*Plus
# sqlplus /nolog
2、在命令提示符環境中以SYSDBA角色登錄，并啟動SQL*Plus
1）
# sqlplus /nolog
SQL> connect / as sysdba
2）
# sqlplus / as sysdba
3、以某用戶身份登錄，并啟動SQL*Plus
1）
# sqlplus jsam/jsam123
2）
# sqlplus jsam/jsam123@orcl
3）
# sqlplus /nolog
SQL> conn jsam/jsam123
4）
# sqlplus /nolog
SQL> conn jsam/jsam123@orcl
等等...
三、安全管理
1、用戶管理
1）創建用戶賬號名為jsam，密碼為jsam123，采用數據庫認證方式。默認表空間為users，臨時表空間為temp，其中對表空間users的使用限額為500K，表示最多可以使用500K的表空間：
SQL> create user jsam identified by jsam123
default tablespace users
temporary tablespace temp
quota 500k on users;
應該為每一個用戶分配一個默認表空間。如果在創建用戶時不指定表空間，系統表空間將被作為默認表空間。系統表空間包含數據字典，并且經常被Oracle使用。在同一表空間放置多個用戶對象會由于磁盤競爭而導致數據庫系統性能退化。
如果不限制用戶使用表空間的限額，可以不加quota 500k on users。
一般創建用戶之后會同時進行授權，通常可授權用戶連接數據庫、創建表/序列/過程/包/函數/視圖等權限：
SQL> grant connect,resource,create view to jsam;
2）修改密碼
SQL> alter user jsam identified by "123456";
3）刪除用戶
* 刪除用戶jsam
SQL> drop user jsam;
* 如果在刪除用戶的同時，還要刪除用戶所擁有的數據庫對象（如表、索引、簇、視圖等），則可使用帶cascade的drop語句
SQL> drop user jsam cascade;
4）鎖定和解鎖用戶賬號
* 鎖定用戶賬號
SQL> alter user jsam account lock;
* 解鎖用戶賬號
SQL> alter user jsam account unlock;
5）查看當前用戶身份
SQL> show user
6）查詢用戶信息
* 查看當前用戶可以訪問的所有賬號信息、用戶ID及創建時間：
SQL> select * from all_users;
* 查看當前數據庫所有的用戶賬號信息、配置文件及狀態：
SQL> select username, profile, account_status from dba_users;
* 查詢某用戶的表空間使用限額情況（如果沒指定限額，將返回no rows selected）：
SQL> select * from dba_ts_quotas where username = 'JSAM';
7）查看用戶狀態
SQL> select username,account_status from dba_users;
8）用戶配置文件
* 查看所有配置情況
SQL> select * from dba_profiles;
* 查看默認配置文件參數情況
SQL> select * from dba_profiles where profile='DEFAULT';
* 查看密碼有效期配置
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
* 查看引起一個用戶被鎖定的連續登錄失敗的次數
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='FAILED_LOGIN_ATTEMPTS';
* 設置密碼有效期為"無限期"
SQL> alter profile default limit password_life_time unlimited;
* 修改連續登錄失敗上鎖次數限制
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 20;
2、授予和撤銷系統權限
1）授予用戶多個系統權限
SQL> grant create session,create table,create view,create any index,create sequence,create type to appdeveloper1;
2）如果想要某個用戶代為管理某個特定的權限，可在授予系統權限時指定with grant option選項。
該用戶具有以下操作能力：
* 可以向數據庫里的任何其他用戶授予或撤銷該系統權限；
* 可以在向其他用戶授予系統權限的時候使用with admin option選項。
SQL> grant create table to tableAdmin with admin option;
3）授予用戶所有系統權限（除了select any dictionary）
SQL> grant all privileges to admin;
4）授予用戶DBA權限
SQL> grant dba to admin;
5）撤銷已授予用戶appdeveloper1的create type和create sequence系統權限
SQL> revoke create type, create sequence from appdeveloper1;
3、授予和撤銷對象權限
1）授予表的全部對象權限
SQL> grant all privileges on bookinfo to booksystemdeveloper;
2）授予指定的對象權限
SQL> grant insert,update,delete,select on authorinfo to booksystemdeveloper;
3）如果授予對象權限的時候使用了with grant option，那么被授權者就能夠把獲得的對象權限再去授予其他用戶。
SQL> grant all privileges on bookinfo to booksystemdeveloper with grant option;
4）撤銷已授予用戶的update、delete對象權限：
SQL> revoke update,delete on authorinfo from booksystemdeveloper;
4、查看權限信息
1）查詢用戶所擁有的系統權限
SQL> select privilege, admin_option from dba_sys_privs where grantee = 'JSAM';
查看當前用戶的系統權限
SQL> select * from user_sys_privs;
2）查詢用戶擁有的對象權限
SQL> select owner || '.' || table_name object_name, privilege, grantable
from dba_tab_privs
where grantee = 'JSAM'
order by owner, table_name, privilege;
查看當前用戶的對象權限
SQL> select * from user_tab_privs;
3）查詢當前會話可以使用的權限
SQL> select * from session_privs;
5、角色管理
角色是具有名稱的一組系統權限和對象權限的集合。
1）將connect, resource兩個角色授予用戶
SQL> grant connect, resource to jsam;
2）查看當前會話啟用的角色列表
SQL> select * from session_roles;
3）查看當前用戶的角色列表
SQL> select * from user_role_privs;
4）查看數據庫所有角色
SQL> select * from dba_roles;
5）查看某個用戶所擁有的角色
SQL> select granted_role, admin_option
from dba_role_privs
where grantee = 'JSAM';
6）查看角色CONNECT擁有的系統權限
SQL> select role, privilege, admin_option
from role_sys_privs
where role = 'CONNECT';
7）查看角色擁有的對象權限
SQL> select owner || '.' || table_name object_name, privilege, grantable
from role_tab_privs
where role = 'CONNECT'
order by owner, table_name, privilege;
四、事務處理
1、事務提交
1）打開自動提交
SQL> set autocommit on;
2）關閉自動提交
SQL> set autocommit off;
3）顯示提交命令
SQL> commit;
2、事務回滾
1）保存存儲點
SQL> savepoint 存儲點名稱
2）回滾到某個存儲點
SQL> rollback to 存儲點名稱
3）回滾整個事務
SQL> rollback
五、對象管理
1、顯示一個表的結構
SQL> desc table_name;
SQL> describe table_name;
2、查看對象及狀態
查看當前用戶的函數與存儲過程及狀態：
SQL> select object_name,status from user_objects where object_type='FUNCTION';
SQL> select object_name,status from user_objects where object_type='PROCEDURE';
object_type可以是：
SEQUENCE
PROCEDURE
LOB
PACKAGE
PACKAGE BODY
TRIGGER
INDEX
TABLE
VIEW
FUNCTION
JAVA CLASS
JAVA SOURCE
TYPE
...
3、查看對象的源代碼
可用于查看存儲過程、函數、包等源代碼
SQL> select text from all_source where owner=user and name=upper('&plsql_name');
4、查看當前用戶所有的表
SQL> select * from user_tables;
SQL> select table_name from user_tables;
tabs是user_tables的同義詞，所以可以直接使用tabs更加簡潔。
5、編譯對象
SQL> alter function FUNCTION_NAME compile;
SQL> alter procedure PROCEDURE_NAME compile;
SQL> alter trigger TRIGGER_NAME compile;
...
6、鎖對象
1）當前所有被鎖的對象信息
SQL> select * from v$locked_object;
2）查詢出被鎖對象、鎖的模式及所屬用戶
SQL> select b.owner, b.object_name, a.session_id, a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id;
3）被鎖對象的會話信息
SQL> select b.username, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;
有些鎖可能等待很久都沒釋放，此時可能需要強行關閉：
語法：alter system kill session 'sid,serial#';
SQL> alter system kill session '104,1894';
4）查詢發生鎖對應的語句
a）
SQL> select sql_text
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (select session_id from v$locked_object));
b）
SQL> select distinct sql_text
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (select session_id from v$locked_object));
c）
SQL> select sql_text,count(*)
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (select session_id from v$locked_object)) group by sql_text;
六、表空間
1、查看一個數據庫內所有表空間的名稱和默認存儲參數
SQL> select tablespace_name "tablespace",
initial_extent "initial_ext",
next_extent "next_ext",
min_extents "min_ext",
max_extents "max_ext",
pct_increase
from dba_tablespaces;
2、創建表空間
1）創建表空間myspace，大小為100m
SQL> create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' size 100m;
2）創建表空間myspace，大小為100m，空間不足時按128K自動擴展，最大為200m
SQL> create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' size 100m autoextend on next 128K maxsize 200m;
3、增加表空間
1）通過增加表空間數據文件大小增加表空間
SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' resize 120m;
2）通過增加數據文件增加表空間
SQL> alter tablespace myspace add datafile '/home/oracle/oracle/oradata/ora10/myspace02.dbf' size 50M;
4、修改表空間
1）開啟數據文件自動擴展
SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' autoextend on;
2）關閉數據文件為自動擴展
SQL> alter database datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf' autoextend off;
5、刪除表空間
SQL> drop tablespace myspace;
6、查看用戶的缺省表空間
1）查看當前用戶的缺省表空間
SQL> select username,default_tablespace,temporary_tablespace from user_users;
2）查看某用戶的缺省表空間
SQL> select username,default_tablespace from dba_users where lower(username) = 'jsam';
查看所有用戶及默認表空間關系
SQL> select username,default_tablespace from dba_users;
3）查看一個表所在表空間
SQL> select tablespace_name from all_tables where table_name='COMPANY';
SQL> select tablespace_name from user_tables where table_name='COMPANY';
7、查看表空間物理文件的名稱及大小
1）查看一個數據庫內所有數據文件和相關的表空間
SQL> select file_name, blocks, tablespace_name from dba_data_files;
SQL> select file_name, blocks, tablespace_name, autoextensible from dba_data_files;
2）查看表空間的名稱及大小
SQL> 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;
3）查看表空間物理文件的名稱及大小
SQL> select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
4）查看表空間物理文件的名稱及大小（常用）
格式：FILE_NAME TABLESPACE TOTAL(M) USED(M) %USED
語句：
SQL> select b.file_name,
b.tablespace_name,
b.bytes / 1024 / 1024 "TOTAL(M)",
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 "USED(M)",
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) "%USED"
from dba_free_space a, dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;
5）查詢空閑的表空間
SQL> select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
6）查詢空閑的表空間
SQL> select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as "Free(MB)",
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
七、查看參數
1、查看初始化參數
1）查看所有初始化參數
SQL> show parameters;
2）如果只想查詢一個具體的參數值，則使用命令：show parameters [參數名稱]。
例如查詢控制文件信息：
SQL> show parameters control_files;
2、查詢字符集
1）查詢當前數據庫字符集
SQL> select userenv('LANGUAGE') from dual;
2）查看服務器端字符集配置
SQL> select * from v$nls_parameters;
SQL> select * from nls_database_parameters;
第二個語句的查詢結果比第一個語句多了一個數據庫版本的參數NLS_RDBMS_VERSION。
3）客戶端字符集
SQL> select * from nls_instance_parameters;
八、導入導出
EXP和IMP既可以在客戶端使用，也可以在服務端使用。
EXPDP和IMPDP只能在ORACLE服務端使用，不能在客戶端使用。
1、導出
1）完全導出數據庫
使用DBA用戶完全導出數據庫
SQL> exp system/pass123@ora10 file=./jsam.dmp full=y;
2）導出用戶jsam的表
SQL> exp jsam/jsam123@ora10 file=./jsam.dmp compress=n log=jsam.log;
compress=n：表明導出來的文件不需要壓縮放在一個數據塊中
log=jsam.log：指定日志文件
2、導入
1）導入數據庫
SQL> imp jsam/jsam123@ora10 file=./jsam.dmp;
2）當導出與導入使用的是不同的用戶名時，需要指定fromuser、touser
SQL> imp jsam/jsam123@ora10 file=./jsam.dmp fromuser=jsam_test touser=jsam ignore=y;
ignore=y：表明忽略表創建的過程，只是將表中的數據導入表中。例如表已經存在，使用該參數可忽略表的創建，而將數據導入到數據庫中。
3、數據泵導入導出
1）創建文件導出目錄
# mkdir /home/oracle/dbbackup/tempdump/
2）數據庫創建邏輯導出目錄并授權給jsam
SQL> create or replace directory tempdump as '/home/oracle/dbbackup/tempdump/';
SQL> grant read,write on directory tempdump to jsam;
3）導出數據庫(導出文件保存在/home/oracle/dbbackup/tempdump)
# expdp jsam/jsam123@ora10 dumpfile=jsam_dp.dmp directory=tempdump
4）導入數據庫
# impdp jsam/jsam123@ora10 dumpfile=jsam_dp.dmp directory=tempdump logfile=jsam_dp.log remap_schema="jsam":"新的用戶名" remap_tablespace="users":"新的表空間名稱"
說明：
remap_schema：該選項用于將源方案的所有對象裝載到目標方案中。當源、目標用戶名稱不同時需要使用
remap_tablespace：將源表空間的所有對象導入到目標表空間。當源、目標表空間名稱不同時需要使用
如果需要導入到其他服務器的數據庫中，需要根據情況創建用戶、表空間，同樣執行1）、2），將導出的文件放到相應的目錄（此處目錄可以與導出的時候不同）下，再執行導入操作。
九、用戶連接管理
1、用系統管理員登錄，查看當前數據庫有幾個用戶連接
SQL> select username,sid,serial# from v$session;
SQL> select username,sid,serial# from v$session where username=upper('jsam');
2、關閉某個連接
語法：alter system kill session 'sid,serial#';
SQL> alter system kill session '104,1894';
3、查詢Oracle支持的最大連接數
SQL> show parameter processes;
4、修改最大連接數
1）修改最大連接數
SQL> alter system set processes=300 scope = spfile;
2）創建pfile
SQL> create pfile from spfile;
3）重啟數據庫
5、查看連接數
1）查詢當前連接數
SQL> select count(*) from v$session;
2）查詢當前并發連接數
SQL> select count(*) from v$session where status='ACTIVE';
3）查詢不同用戶的連接數
SQL> select count(*) from v$session where username is not null;
SQL> select username, count(username)
from v$session
where username is not null
group by username;
4）查看當前數據庫建立的會話情況
SQL> select sid, serial#, username, program, machine, status from v$session;
十、其他命令
1、tnsping命令
1）驗證名字解析（name resolution，當然是oracle自己的網絡服務名）
2）檢查遠程的listener是否啟動（用法：tnsping <address> [<count>]）
# tnsping ora10
# tnsping 192.168.0.103
# tnsping 192.168.0.103 2
2、執行一個SQL腳本文件
SQL> start file_name
SQL> @ file_name
3、重新運行上一次運行的SQL語句
SQL> /
4、不退出sql*plus，在sql*plus中執行一個操作系統命令
* 切換到操作系統命令提示符命令：host或!
* 切換回SQL*PLUS命令：exit
示例：
SQL> host
[/home/oracle]pwd
/home/oracle
[/home/oracle]exit
exit
SQL>
5、環境變量
1）顯示當前環境變量
命令：show 參數名
顯示所有環境變量：
SQL> show all
顯示某個變量的值，如顯示分頁大小（默認是14）：
SQL> show pagesize;
2）設置環境變量
命令：set 參數名 參數值
將pagesize設置好100，可以一次顯示足夠夠多行記錄而受標題欄干擾
SQL> set pagesize 100;
掌握幾個簡單的常用變量設置，可以使查詢結果可讀性更強，以下列出幾個個人比較常用的變量設置：
* 設置行的寬度
默認顯示寬度比較小，查詢結果很多時候會折行顯示，將linesize適當調大一點可以使結果顯示在同一行：
SQL> set linesize 125;
測試語句：
SQL> select username, profile, account_status from dba_users;
* 設置列的寬度
調整列的寬度同樣可以增強可讀性。
語法：col 列名 format a寬度值
設置列PARAMETER、VALUE的寬度為30：
SQL> col PARAMETER format a30;
SQL> col VALUE format a30;
測試語句：
SQL> select * from v$nls_parameters;
* 打開/關閉標題欄
SQL> set heading on
SQL> set heading off
* 超長折行顯示或截斷不顯示
SQL> set wrap on
SQL> set wrap off
6、緩沖區
1）顯示SQL緩沖區命令
SQL> L
2）使用INPUT命令可以在SQL緩沖區中增加一行或多行
SQL> i
1 輸入內容
7、查看數據庫版本
SQL> select * from v$version;
8、spool假脫機命令
將各種操作及執行結果存儲到磁盤文件上，該文件默認文件擴展名為.lst。
1）執行假脫機命令
SQL> spool 文件名
2）停止假脫機
SQL> spool off
3）顯示假脫機狀態
SQL> spool
示例：
SQL> spool all_users.txt
SQL> select * from all_users;
此處省略很多行...
SQL> spool off
SQL> exit
結果：select * from all_users的執行結果將會保存為文件all_users.txt。
9、查詢構成一個數據庫的所有數據文件列表
SQL> select status, bytes, name from v$datafile;
10、查詢控制文件列表
SQL> select name from v$controlfile;
11、查詢當前連接到數據庫的類型
SQL> select server from v$session where audsid = userenv('SESSIONID');
DEDICATED：專用服務器
SHARED：多線程服務器
參考資料：
《Oracle 10g入門與提高》
http://database.51cto.com/art/200910/158936.htm