oracle数据库学习1

前面已经写完如何安装oracle数据库了，下面就是介绍一下如何学习oracle数据库进行学习了。

一    判断数据库是否进行启动

[oracle@zjk ~]$ ps -ef | grep ora_
oracle 1869 1 0 23:14 ? 00:00:00 ora_pmon_ora138
oracle 1871 1 0 23:14 ? 00:00:00 ora_psp0_ora138
oracle 1873 1 1 23:14 ? 00:00:05 ora_vktm_ora138
oracle 1877 1 0 23:14 ? 00:00:00 ora_gen0_ora138
oracle 1879 1 0 23:14 ? 00:00:00 ora_diag_ora138
oracle 1881 1 0 23:14 ? 00:00:00 ora_dbrm_ora138
oracle 1883 1 0 23:14 ? 00:00:00 ora_dia0_ora138
oracle 1885 1 0 23:14 ? 00:00:03 ora_mman_ora138
oracle 1887 1 0 23:14 ? 00:00:00 ora_dbw0_ora138
oracle 1892 1 0 23:14 ? 00:00:00 ora_lgwr_ora138
oracle 1894 1 0 23:14 ? 00:00:00 ora_ckpt_ora138
oracle 1896 1 0 23:14 ? 00:00:00 ora_smon_ora138
oracle 1898 1 0 23:14 ? 00:00:00 ora_reco_ora138
oracle 1900 1 0 23:14 ? 00:00:00 ora_mmon_ora138
oracle 1902 1 0 23:14 ? 00:00:00 ora_mmnl_ora138
oracle 1904 1 0 23:14 ? 00:00:00 ora_d000_ora138
oracle 1906 1 0 23:14 ? 00:00:00 ora_s000_ora138
oracle 1992 1 0 23:14 ? 00:00:00 ora_qmnc_ora138
oracle 2049 1 0 23:14 ? 00:00:00 ora_cjq0_ora138
oracle 2052 1 0 23:14 ? 00:00:00 ora_q000_ora138
oracle 2054 1 0 23:14 ? 00:00:00 ora_q001_ora138
oracle 2113 1 0 23:14 ? 00:00:00 ora_vkrm_ora138
oracle 2310 1 0 23:15 ? 00:00:00 ora_smco_ora138
oracle 2312 1 0 23:15 ? 00:00:00 ora_w000_ora138
oracle 2486 1 0 23:20 ? 00:00:00 ora_w001_ora138
oracle 2491 1 0 23:20 ? 00:00:00 ora_w002_ora138
oracle 2515 2402 0 23:21 pts/0 00:00:00 grep ora_

或者

[oracle@zjk ~]$ sqlplus / as sysdba

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

又或者

[oracle@zjk ~]$ netstat -an | grep :1521
tcp        0      0 192.168.80.138:28604        192.168.80.138:1521         ESTABLISHED 
tcp        0      0 :::1521                     :::*                        LISTEN      
tcp        0      0 ::ffff:192.168.80.138:1521  ::ffff:192.168.80.138:28604 ESTABLISHED

二    数据库的连接方式

[oracle@zjk ~]$ sqlplus / as sysdba

对scott用户进行解锁

SQL> alter user scott identified by tiger account unlock;
User altered.

三    了解数据库的变量

1    显示当前数据库的用户名

SQL> show user
USER is "SCOTT"

2    显示编译错误

SQL> show error;

3    显示对象的结构

SQL> desc emp;

4    如何某一列的列有点长，可以这么做

col 列名 for a长度    比如 col name for a30;

5    清屏命令

SQL> cle scr

6    显示所有变量

SQL> show all

7    其它命令

如果当你查询数据的时候，显示的不是那么规范，使用下面的命令，

SQL> set linesize 100    显示一行能够显示数据的长度

set autoc on|off    打开或者关闭sql语句自动提交的功能

如果想要将查询的数据输出到文件中，可以这样进行操作

SQL> spool 1.c rep

SQL> select * from cat;

SQL> spool off

然后打开1.c可以发现和select * from cat 显示的数据是一样的。

数据库中文显示的问题

显示中文

[oracle@zjk ~]$ export NLS_LANG='simplified chinese_china.al32utf8'

[oracle@zjk ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期六 4月 23 00:06:02 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

不显示中文

 [oracle@zjk ~]$ export NLS_LANG='simplified chinese_china.zhs16gbk'

oracle@zjk ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 3 00:08:03 2016

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, OLAP, Data Mining and Real Application Testing options
SQL> 

8    修改系统显示时间的方式和查询时间的方式

SQL> alter session set nls_date_format="yyyy-mm-dd";

SQL> select sysdate from dual;

SYSDATE
----------
2016-04-23

9    显示数据库中的实例名

[oracle@zjk ~]$ lsnrctl status

还可以这样

[oracle@zjk ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 23 08:01:31 2016
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, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora138

最后还可以这样

[oracle@zjk ~]$ tail -1  /etc/oratab     
ora138:/u01/oracle/11g:N                # line added by Agent

这一节就写到这里了，欢迎看下一节oracle数据库的操纵命令。