Oracle資料庫日常維護命令

一、啟動、關閉資料庫

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 viewto 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_statusfrom 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 whereprofile='DEFAULT';

 

* 查看密碼有效期間配置

SQL> select * from dba_profiles whereprofile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

 

* 查看引起一個使用者被鎖定的連續登入失敗的次數

SQL> select * from dba_profiles whereprofile='DEFAULT' and resource_name='FAILED_LOGIN_ATTEMPTS';

 

* 設定密碼有效期間為"無限期"

SQL> alter profile default limitpassword_life_time unlimited;

 

* 修改連續登入失敗上鎖次數限制

SQL> alter profile default limitFAILED_LOGIN_ATTEMPTS unlimited;

SQL> alter profile default limitFAILED_LOGIN_ATTEMPTS 20;

 

2、授予和撤銷系統許可權

1）授予使用者多個系統許可權

SQL> grant create session,create table,createview,create any index,create sequence,create type to appdeveloper1;

 

2）如果想要某個使用者代為管理某個特定的許可權，可在授予系統許可權時指定with grant option選項。

該使用者具有以下操作能力：

* 可以向資料庫裡的任何其他使用者授予或撤銷該系統許可權；

* 可以在向其他使用者授予系統許可權的時候使用with admin option選項。

SQL> grant create table to tableAdmin with adminoption;

 

3）授予使用者所有系統許可權（除了selectany 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 fromappdeveloper1;

 

3、授予和撤銷對象許可權

1）授予表的全部對象許可權

SQL> grant all privileges on bookinfo tobooksystemdeveloper;

 

2）授予指定的對象許可權

SQL> grant insert,update,delete,select onauthorinfo to booksystemdeveloper;

 

3）如果授予對象許可權的時候使用了withgrant option，那麼被授權者就能夠把獲得的對象許可權再去授予其他使用者。

SQL> grant all privileges on bookinfo tobooksystemdeveloper with grant option;

 

4）撤銷已授予使用者的update、delete對象許可權：

SQL> revoke update,delete on authorinfo frombooksystemdeveloper;

 

4、查看許可權資訊

1）查詢使用者所擁有的系統許可權

SQL> select privilege, admin_option fromdba_sys_privs where grantee = 'JSAM';

 

查看目前使用者的系統許可權

SQL> select * from user_sys_privs;

 

2）查詢使用者擁有的對象許可權

SQL> select owner || '.' || table_nameobject_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_nameobject_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_objectswhere object_type='FUNCTION';

SQL> select object_name,status from user_objectswhere 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=userand 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 (selectsession_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 (selectsession_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 (selectsession_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 next128K 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> selectusername,default_tablespace,temporary_tablespace from user_users;

 

2）查看某使用者的預設資料表空間

SQL> select username,default_tablespace from dba_userswhere lower(username) = 'jsam';

 

查看所有使用者及預設資料表空間關係

SQL> select username,default_tablespace fromdba_users;

 

3）查看一個表所在資料表空間

SQL> select tablespace_name from all_tables wheretable_name='COMPANY';

SQL> select tablespace_name from user_tableswhere table_name='COMPANY';

 

7、查看錶空間物理檔案的名稱及大小

1）查看一個資料庫內所有資料檔案和相關的資料表空間

SQL> select file_name, blocks, tablespace_namefrom 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) asfree_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=nlog=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.dmpfromuser=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.dmpdirectory=tempdump

 

4）匯入資料庫

# impdp jsam/jsam123@ora10 dumpfile=jsam_dp.dmpdirectory=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 whereusername=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 wherestatus='ACTIVE';

 

3）查詢不同使用者的串連數

SQL> select count(*) from v$session whereusername 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）驗證名字解析（nameresolution，當然是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 參數名