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/123456@172.16.12.1/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