New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

使用 SQL *Plus 管理 Oracle 数据库 #31

Open
nodejh opened this Issue Feb 17, 2017 · 0 comments

Comments

Projects
None yet
1 participant
@nodejh
Owner

nodejh commented Feb 17, 2017

SQL *Plus 是基于命令行的 Oracle 管理工具,可以用来执行 SQLPL/SQL、 和 SQL*Plus 命令:

  • 支持查询、插入和更新数据
  • 执行 PL/SQL 程序
  • 查看表和对象的定义
  • 开发和执行批处理脚本
  • 进行数据库管理

登录 SQL *PLUS

直接登录,输入命令后会提示输入用户名密码:

$ sqlplus

使用用户名和密码:

$ sqlplus [username]/[user_password]

操作系统权限认证的 Oracle SYS 管理员登陆:

$ sqlplus / as sysdba

不在终端暴露密码登录:

$ sqlplus /nolog
SQL> conn [username]/[user_password]
# 或者
SQL > conn / as sysdba

退出登录:

SQL> exit

数据库信息

查看数据库名

通常情况了我们称的 数据库,并不仅指物理的数据集合,而是物理数据、内存、操作系统进程的组合体。

SQL> select name from v$database; 

查询当前数据库实例名

实例是访问Oracle数据库所需的一部分计算机内存和辅助处理后台进程,是由进程和这些进程所使用的内存(SGA)所构成一个集合。

SQL> select instance_name from v$instance;    

数据库实例名用于对外部连接。在操作系统中要取得与数据库的联系,必须使用数据库实例名。比如我们作开发,要连接数据库,就得连接数据库实例名,orcl 就为数据库实例名:

jdbc:oracle:thin:@localhost:1521:orcl

一个数据库可以有多个实例,在作数据库服务集群的时候可以用到。

用户管理

Oracle 使用 PROFILE 文件对用户访问资源的权限进行控制。

若不做特殊指定,创建用户时用户默认使用的 PROFILE 就是 DEFAULT

查看当前用户:

SQL> show user

查看数据库用户:

SQL> select * from dba_users;  

解锁用户

默认当密码输错 10 次之后,用户就会被锁定:

ORA-28000: the account is locked

这个时候就需要管理员来解锁:

$ sqlplus / as sysdba
SQL> alter user [username] account unlock;

有解锁肯定就有锁定:

SQL> alter user [username] account lock;

密码错误次数

当然,也可以自己修改最大密码错误次数,最大错误次数存储在 dba_profiles 表中。

首先根据 username 查看用户使用的 PROFILE

SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME='[username]'

然后根据 username 以及查询到的 PROFILE 查看该用户的最大密码错误次数 FAILED_LOGIN_ATTEMPTS

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

将错误次数修改为无限次:

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTSUNLIMITED;

密码有效期

Oracle 11g 默认用户每三个月(180 天)就要修改一次密码,快到密码过期时间就会提醒:

ORA-28002: the password will expire within 7 days

这里同样要先查找到 PROFILE 再查看用户密码剩余过期时间:

SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

修改密码有效期(不受限):

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIMEUNLIMITED;

设置密码过期:

SQL> alter user [username] password expire;

修改密码

修改当前登录用户密码:

SQL> password

修改某个用户的密码:

SQL> alter user [username] identified by [password];

表管理

Oracle 的表都是存储在表空间里面的。创建表之前需要先创建一个表空间。

查看用户所拥有的表

查看用户所拥有的表:

SQL> SELECT TABLE_NAME FROM USER_TABLES; 

查看用户可存取的表:

SQL> SELECT TABLE_NAME FROM ALL_TABLES; 

数据库中所有表:

SQL> SELECT TABLE_NAME FROM DBA_TABLES;

查看表空间

查看表空间详细数据文件:

SQL> SELECT FILE_NAME,TABLESPACE_NAME from DBA_DATA_FILES;

创建表空间

create tablespace [表空间名称]
datafile [表空间数据文件路径 ]
size [表空间大小]
autoextend on;

例如:

SQL> create tablespace SoftwareManagement
  2  datafile '/data/oracle/oradata/orcl/SoftwareManagement.dbf'
  3  size 50m
  4  autoextend on;

创建新用户

CREATE USER [用户名]  
IDENTIFIED BY [密码]  
DEFAULT TABLESPACE [表空间] (默认USERS)  
TEMPORARY TABLESPACE [临时表空间] (默认TEMP)  

例如:

SQL> create USER software
  2  identified by 123456
  3  default tablespace Softwaremanagement;

分配权限

SQL> GRANT CONNECT TO [username];  
SQL> GRANT RESOURCE TO [username];  
SQL> GRANT DBA TO [username];  -- DBA为最高级权限,可以创建数据库、表等。 

到这里,数据库中的表空间、用户以及用户权限都创建并分配好了,接下来用户就可以在自己的表空间中创建表,然后进行开发。

权限管理

在给用户分配权限的时候,分配了 CONNECTRESOURCE 权限给用户。这两个权限到底是什么呢?

oracle中的权限

Oracle 中的权限分为两类:

  • 系统权限:系统规定用户使用数据库的权限,系统权限是对用户而言。
  • 实体权限:某种权限的用户对其他用户的表或视图的存取权限,是针对表或者视图而言。如 selectupdateinsertdeletealterindexall,其中 all 包含所有的实体权限。

系统权限分类

  • DBA:拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。
  • RESOURCE:拥有resource权限的用户只可以创建实体,不可以创建数据库结构。
  • CONNECT:拥有connect权限的用户只可以登录oracle,不可以创建实体,不可以创建数据库结构。

建议:
对于普通用户,授予 CONNECTRESOURCE 权限;
对于 DBA 管理用户,授予 CONNECTRESOURCEDBA 权限。

导入导出

数据库的导入导出也是一个很常见的需求。

导出

$ exp [username]/[password]@[orcl] file=./database.dmp  full=y
  • username 是数据库用户名
  • password 是数据库用户密码
  • orcl 是数据库实例名称
  • file 后面的参数是导出的数据库文件存放位置及文件名
  • full 其值为 y 表示全部导出,默认为 no

如果只需导出某几张表,可以指定 tables 参数:tables='(tableName, tableName1)'

导入

$ imp [username]/[password]@[orcl] file=./database.dmp

和导出数据库语法一样,只是关键字不一样。

执行 SQL 文件

执行 SQL 文件的方法有很多种。如下:

使用 SQL PLUS 命令

$ sqlplus [username]/password@[orcl] @path/file.name

或者远程执行:

$ sqlplus [username]/password@server_IP/service_name @path/file.name

如果sql脚本文件比较复杂,包含了begin end语句,就会不断显示行号,解决办法就是在 sql 脚本的最后用 / 符号结尾。

在 SQL PLUS 中执行

SQL>start file_path
SQL>@ file_path

其中 file_path 是文件路径。


参考

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment