Oracle 基礎知識(特別基礎)
1 如何確定數據庫已經啟動
（1）查看進程
Ps –ef |grep oracle
oracle 39452856 1 0 May 23 - 11:59 ora_smon_GJ2
oracle 40304850 1 0 May 23 - 3:15 ora_dbw3_GJ2
oracle 40894500 1 0 May 23 - 17:37 ora_lgwr_GJ2
oracle 41156664 1 0 May 23 - 3:13 ora_dbw2_GJ2
oracle 42336376 1 0 May 23 - 0:18 ora_reco_GJ2
oracle 42401822 1 0 May 23 - 70:24 ora_ckpt_GJ2
oracle 42532870 1 0 May 23 - 3:33 ora_dbw0_GJ2
oracle 43122876 1 0 May 23 - 1:08 ora_mman_GJ2
oracle 43384926 1 0 May 23 - 2:21 ora_lmhb_GJ2
oracle 43450406 1 0 May 23 - 0:53 ora_acms_GJ2
oracle 43647158 1 0 May 23 - 3:19 ora_dbw1_GJ2
oracle 43909190 1 0 May 23 - 281:54 ora_lms2_GJ2
oracle 44105784 1 0 May 23 - 286:50 ora_lms1_GJ2
oracle 44957698 1 0 May 23 - 281:44 ora_lms0_GJ2
oracle 45875210 1 0 May 23 - 259:28 ora_lmon_GJ2
oracle 46399690 1 1 May 23 - 501:22 ora_dia0_GJ2
oracle 46530668 1 0 May 23 - 1:34 ora_dbrm_GJ2
oracle 46596202 1 0 May 23 - 13:46 ora_psp0_GJ2
oracle 47055062 1 0 Jun 19 - 1:28 ora_pz97_GJ2
oracle 47120434 1 0 May 23 - 1:09 ora_gen0_GJ2
oracle 47775828 1 0 May 23 - 17:54 ora_pmon_GJ2
oracle 47841304 1 0 May 23 - 40:13 ora_vktm_GJ2
oracle 48300276 1 0 May 23 - 63:36 ora_lmd0_GJ2
如果可以看到相關的進程說明數據庫已經啟動 注：進程命名規則（ora_進程名_實例名）
（2）直接登錄數據庫
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 11 08:47:08 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
OPEN
如果顯示為open則說明數據庫已經正常運行
注：若出現如下情況說明數據庫未啟動或者實例名不正確
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 11 08:49:39 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
此時對數據庫進行startup操作
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 494931328 bytes
Database Buffers 331350016 bytes
Redo Buffers 6590464 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;
STATUS
------------
OPEN
如果仍然無法啟動說明是實例名錯誤（在數據庫沒有故障時）。如何更改實例名在下面會說明
2 如何確定/更改當前實例名
查看實例名：
Wuyj:/home/oracle$echo $ORACLE_SID
Jian
此時輸出"jian"即為當前環境變量中的數據庫實例名
更改實例名：
Wuyj:/home/oracle$export ORACLE_SID=jian1
Wuyj:/home/oracle$echo $ORACLE_SID
jian1
3 如何登陸數據庫
先確定當前實例名是否正確 export $ORACLE_SID
確定無誤後登陸數據庫：
本底登陸數據庫：sqlplus / as sysdba
*遠程登陸數據庫：sqlplus system/[email protected]/jian as sysdba
4 建立用戶，解鎖，切換用戶
SQL> create user jian identified by jian; 建立用戶
User created.
SQL> alter user jian account lock; 鎖定用戶
User altered.
SQL> alter user jian account unlock; 解鎖用戶
User altered.
SQL>conn jian/jian 切換用戶
Connected.
SQL> show user; 查看當前用戶
USER is "JIAN"
5 sqlplus基本命令
SQL> select count(*) from v$session;
COUNT(*)
----------
31
SQL> l
1* select count(*) from v$session l 查看上一條sql
SQL> / / 執行上一條sql
COUNT(*)
----------
29
SQL> run 執行上一條sql
1* select count(*) from v$session
COUNT(*)
----------
29
SQL> save '/home/oracle/sql1.txt' 將sql保存到文件
Created file /home/oracle/sql1.txt
SQL> !cat /home/oracle/sql1.txt 在sqlplus環境下使用操作系統命令
select count(*) from v$session
/
SQL> @/home/oracle/sql1.txt 執行外部sql命令
COUNT(*)
----------
29
