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
