Oracle

2019-03-14

SQL*Plus基础命令

1.连接命令

1.conn [ect]

用法：conn 用户名/密码@网络服务名 [as sysdba]

eg:

SQL> show user
USER 为 "C##MALU"
SQL> conn system/xxxxx@orcl
已连接。
SQL> show user
USER 为 "SYSTEM"

eg2:

切换到sysdba

SQL> conn / as sysdba

2.disc/disconn/disconnect

断开连接

eg:

SQL> disc
从 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 断开
SQL> show user
USER 为 ""

3.passw[ord] [username]

说明：默认更改当前用户密码，如果指定用户名，需要用sys/system登录。

eg:

SQL> show user
USER 为 "SYSTEM"
SQL> conn c##malu
输入口令:
已连接。
SQL> show user
USER 为 "C##MALU"
SQL> passw
更改 C##MALU 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改

4.clear screen

说明：清屏

2.文件操作命令

1.start和@

说明：运行sql脚本

eg:

SQL> @ f:\test.sql

eg2:

SQL> start f:\test.sql

查看数据库版本

SQL> select * from v$version;

2.edit

说明：编辑sql脚本

eg:

SQL> edit f:\test.sql

3.spool

说明：将终端内容输出到指定文件中。

eq:

SQL> spool f:\1.sql
SQL> show user
USER 为 "C##MALU"
SQL> spool off

3.交互式命令

1.&

说明：相当于变量，会在执行时，要求用户输入。

eg:

SQL> select * from &a;
输入 a 的值:  table1
原值    1: select * from &a
新值    1: select * from table1

COLUMN1                                  COLUMN2
---------------------------------------- ----------------------------------------
1                                        2

4.显示和设置环境变量

1.show

说明：用于显示

eg:

SQL> show linesize
linesize 300
SQL> show user
USER 为 "C##MALU"
SQL> show pagesize
pagesize 14

2.set

说明：设置环境参数

eg:

设置终端显示宽度300个字符

SQL> set linesize 300

eg2:

设置单页显示30行

SQL> set pagesize 30

Oracle 用户管理

1.用户增删改查

1.创建用户 create user 用户名 identified by 密码

要创建一个新用户，一般需要dba权限才能使用。

eg:

SQL> create user c##boy identified by malu;
create user c##boy identified by malu
*
第 1 行出现错误:
ORA-01031: 权限不足

2.删除用户 drop user 用户名 [cascade]

如果要删除的用户，已经创建了表，那么就需要在删除的时候加上 cascade 参数，即连同该表一起删除。

3.修改用户密码

一种方法是，password username

另一种是，alter user 用户名 identified by 新密码

4.查看所有用户

select * from dba_users;
select * from all_users;
select * from user_users;

eg:

SQL> select username  from all_users;

5.获取当前用户下所有的表

SQL> select table_name from user_tables;

6.查看当前用户的缺省表空间

SQL> select username,default_tablespace from user_users;

7.查看当前用户的角色

SQL> select * from user_role_privs;

8.查看当前用户的系统权限和表级权限

SQL> select * from user_sys_privs; 
SQL> select * from user_tab_privs; 

9.用系统管理员，查看当前数据库有几个用户连接：

SQL> select username,sid,serial# from v$session;

配置

Oracle sqlnet.ora配置

sqlnet配置文件的存放位置一般在：$ORACLE_HOME/network/admin目录下

该配置文件的作用：

指定要附加到非限定名称的客户端域优先考虑命名方法启用日志记录和跟踪功能通过特定处理器路由连接配置外部命名的参数配置Oracle Advanced Security 使用特定于协议的参数来限制对数据库的访问

连接工具

web方式管理工具

管理界面：https://localhost:5500/em/

账号密码一般是安装时设置的sys和对应的密码

SQL Developer

这是Oracle安装自带的图形化管理工具，用来编程等

Navicat Permium

Navicat Permium集成了Oracle客户端

DBeaver

开源且支持很多数据库 https://github.com/dbeaver/dbeaver

故障处理

ORA-28040: No matching authentication protocol

1.打开 sqlnet.ora 文件

比如我的在：F:\app\malu\product\12.2.0\dbhome_1\network\admin\ 目录下

新增如下2行：

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

如何重启oracle：

set ORACLE_SID=SID_Name，回车。
sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup
SQL> exit

Navicat Permium 登录 oracle 用户名口令无效

问题现象：

使用sqlplus 和 sql developer可以登录，但是使用navicat或程序连接时总是提示ORA-01017: 用户名/口令无效; 登录被拒绝

处理方式：

修改密码: alter user C##malu identified by 123456;

或使用sql developer，直接重设口令;

sqlplus无法登录

我遇到的问题是本地装了多个版本的Oracle，在系统环境变量里默认指定的11g的sqlplus,而我要连的是12c

所以只要切换到12c目录下的sqlplus即可；

或者把环境变量中的sqlplus指向12c的目录；

启动sqlplus时报错：SP2-0667: Message file sp1.msb not found

该问题还连带报错：

You may need to set ORACLE_HOME to your Oracle software directory

处理办法是，新增环境变量：

export ORACLE_HOME=/app/oracle/product/12.2.0.1/dbhome_1

无法给表新增内容：ORA-01950: 表空间’USERS’中无权限

处理办法：先切换到sysdba，再增加该用户表空间’USERS’中的配额

SQL> conn / as sysdba
已连接。
SQL> alter user c##malu quota unlimited on "USERS";
用户已更改。

oracle下载地址

https://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html