Oracle常用命令

最新推荐文章于 2022-05-15 17:50:07 发布

蓬莱道人

最新推荐文章于 2022-05-15 17:50:07 发布

阅读量437

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/MOU_IT/article/details/78535062

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Oracle数据库的一些名词：

1、数据库和实例：

完整的Oracle数据库通常由两部分组成：Oracle数据库和数据库实例。数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）。Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。

在启动Oracle数据库服务器时，实际上是在服务器的内存中创建一个Oracle实例（即在服务器内存中分配共享内存并创建相关的后台内存），然后由这个Oracle数据库实例来访问和控制磁盘中的数据文件。Oracle有一个很大的内存快，成为全局区（SGA）。我们访问Oracle都是访问一个实例，但这个实例如果关联了数据库文件，就是可以访问的，如果没有，就会得到实例不可用的错误。数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。比如我们作开发，要连接数据库，就得连接数据库实例名：

jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）

2、启动数据库(全局数据库)：

启动数据库也叫全局数据库，是数据库系统的入口。我们在安装Oracle数据库时，会让我们选择安装启动数据库（即默认的全局数据库），就是一个数据库的标识，在安装时就要想好，以后一般不修改，修改起来也麻烦，因为数据库一旦安装，数据库名就写进了控制文件，数据库表，很多地方都会用到这个数据库名。它会内置一些高级权限的用户如SYS，SYSTEM等。我们用这些高级权限账号登陆就可以在数据库实例中创建表空间，用户，表了。

3、表空间：

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。有了数据库，就可以创建表空间(创建表空间时需指定物理文件路径：一个或者多个文件路径)。表空间对应一个或者多个数据文件，表空间的大小是它所对应的数据文件的大小总和。表空间是数据库的逻辑划分，每个数据库至少有一个表空间（称作SYSTEM表空间）。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。

4、用户：

Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并为用户指定表空间。有了用户，要想使用用户账号管理自己的表空间，还得给它分权限，有了数据库，表空间和用户，就可以用自定义的用户在自己的表空间创建表了。有了表，我们可以开发了。

常用命令：

1、需要开启的Oracle服务：

1)只用Oracle自带的sqlplus：启动OracleServiceORCL ;
2)使用PL/SQL Developer等第三方工具：启动OracleOraDb11g_home1TNSListener ;
3)运行Enterprise Manager（企业管理器OEM）：启动OracleDBConsoleorcl ;

2、登录数据库：sqlplus user_name

DBA用户名为'system'或者'sys'，system密码为安装时设置的密码；

sys密码为：change_on_install as sysdba；

或者用：sqlplus /nolog
conn /as sysdba //此处登录为管理员，若为普通用户则输入：conn

解锁用户：alter user user_name account unlock ;

修改密码：alter user user_name identified by password;

3、查询当前数据库名：select name from v$database;

4、查询当前数据库实例名：select instance_name from v$instance;

5、查看已经创建好的表空间：select default_tablespace,temporary_tablespace,d.username from dba_users d；

6、创建表空间：create tablespace tablespace_name //表空间名称
datafile ‘tablespace_pathname’

//表空间的物理文件路径，可以有多个，文件格式.DBF
size tablespace_size[k|m] //表空间的大小，单位为M或K
autoextend on //开启表空间的自动扩展

7、查看所有角色：select * from dba_roles;

8、创建角色：create role role_name //角色名，初创的角色权限为空，需要授权

[not identified] //该角色由数据库授权，不需要口令使该角色生效

[identified by password | externally | globally] // 在用set role语句使该角色生效之前必须由指定的方法来授权一个用户。by password ：创建一个局部用户，在使角色生效前，用户必须指定password定义的口令；externally：创建一个外部用户，在使角色生效前，必须由外部服务器来授权用户；globally：创建一个全局用户，利用set role语句使该角色生效或者登陆时，用户必须由企业目录服务器授权使用该角色。

9、修改角色：alter role role_name

[not identified]

[identified by password | externally | globally]

10、查询所有用户名：select username from dba_users; select username from all_users;

11、查询当前用户信息：select * from dba_ustats; select * from user_users;

12、创建用户：create user user_name //用户名
[identified by {password | externally | globally as'external_name}] //如何验证用户
[default tablespace tablespace_name] //默认表空间
[temporary tablespace tablespace_name] //临时段的表空间
[quota integer K | integer M | unlimited on tablespace_name]

//用户规定的表空间存储对象，最多可达到这个定额规定的尺寸
[profile profile_name] //指定的概要文件分配给用户
[password expire] //使用户的密码过期，这样用户登录时必须修改密码
[account {lock | unlock}] //账户是否锁定

13、修改用户：create user user_name
[identified by {password | externally | globally as'external_name}]
[default tablespace tablespace_name]
[temporary tablespace tablespace_name]
[quota integer K | integer M | unlimited on tablespace_name]
[profile profile_name]
[password expire]
[account {lock | unlock}]

14、删除用户：drop user user_name cascade; //需要指定级联cascade.

15、查看默认概要文件：select distinct profile from dba_profiles where profile='DEFAULT';

16、创建用户概要文件：create profile profile_name limit

connect_time int //指定会话保持连接时间，单位为秒

cpu_per_call int //限制事务内每个调用使用cpu的时间

cpu_per_session int // 限制会话中使用的总cpu时间

sessions_per_user int //指定用户可以打开的并发会话的最大数目

idle_time int //限制一个会话空闲的时间量

logical_reads_per_session int //限制数据块读取的总数目

logical_reads_per_call int //限制每个会话调用的总的逻辑读取数

failed_login_attempts int //用户可尝试的登录次数

password_life_time int //设置密码的生命周期

password_grace_time int

//设置一个时间段，宽限时间内发出密码过期警告，超过宽限时间密码失效

password_lock_time int //用户登录失败后被锁多少天

password_reuse_time int //指定可重新使用相同密码前要经过的天数

password_reuse_max int

//确定在可以重新使用某个特定密码前可更改该密码多少次

password_verify_function //指定自己创建的密码验证函数

17、指派用户概要文件：alter user user_name profile profile_name;

18、更改概要文件：alter profile profile_name limit ......;

19、删除概要文件：drop profile profile_name cascade; //删除后用户被指派为默认概要文件

20、给用户、角色授予权限：grant privilege[,...n] to {user_name[...n] | role_name | public } [with admin option]；

public：Oracle中的公共用户组；

with admin option：表示权限可以传递。

21、取消用户、角色权限：revoke privilege[,...n] from {user_name[...n] | role_name | public } [with admin option]；

注：Oracle对系统权限不具有级联收回的功能。

22、将角色授予用户：grant role_name[,...n] to {user_name | role_name | public } [with admin option]；

23、收回用户的角色：revoke role_name[,...n] from {user_name | role_name | public } ;

24、启用和禁用用户的角色：set role { role_name[,...n] | all [ except role_name[,...n] ] | none] };

25、对象权限的授予：grant {object_privilege | all [ privilege ][(column[,...n])]} on [schema.]object_name to {user_name[...n] | role_name | public } [with admin option];

其中对象权限有select、update、delete、insert、execute、read、index、preferences、alter。

all表示授予该对象全部的对象权限，column来指定表的某列的权限(只有update可以指定列)。

示例：grant update(studentID,classID,grade) on selectiveinfo to teacher;

26、对象权限的收回：revoke {object_privilege | all [ privilege ][(column[,...n])]} on [schema.]object_name to {user_name[...n] | role_name | public } [cascade constraints];

注：与系统权限相反，Oracle会级联回收对象权限。

27、查看当前用户的缺省表空间 : select username,default_tablespace from user_users;

28、查看当前用户的角色 : select * from user_role_privs;

29、查看当前用户的系统权限和表级权限 : select * from user_sys_privs;select * from user_tab_privs;

30、查看用户下所有的表名 : select table_name from user_tables;

31、显示当前会话所具有的权限 : select * from session_privs;

32、显示指定用户所具有的系统权限 : select * from dba_sys_privs;

33、显示特权用户 : select * from v$pwfile_users;

34、查看oracle版本命令： select * from v$version;

35、查看视图信息:select * from user_views;

36、查看同义词的名称:select * from user_synonyms;

37、查看函数和过程状态：

select object_name,status from user_objects where object_type='FUNCTION';

select object_name,status from user_objects where object_type='PROCEDURE';

38、删除函数和过程：

drop procedure procedure_name;

drop function function_name;

39、查看函数和过程的源代码:
　　select text from all_source where owner=user and name='SF_SPLIT_STRING';

40、查看表结构：desc table_name;

41、创建密码验证函数：

Oracle的启用默认密码验证函数：@?/rdbms/admin/utlpwdmg.sql，执行该命令后，创建默认密码验证函数，并且制定为默认概要文件的密码验证函数。根据密码验证函数的格式，再结合具体的需求可以自定义密码验证函数。
需求：1)要求密码至少包含大小写字母、数字和特殊字符（#%&*）中的一个;
2)并且不少于10位;

CREATE OR REPLACE FUNCTION my_verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
  n boolean;
  m integer;
  isdigit boolean;
  ischar  boolean;
  isspecial boolean;
  digitarray varchar2(20);
  chararray varchar2(52);
  special varchar2(10);
 
BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   special:='#%&*';
   
   -- Check for the minimum length of the password
   IF length(password) < 10 THEN
      raise_application_error(-20001, 'Password length less than 10');
   END IF;

   -- Check if the password contains at least one letter,one digit,one special 
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20002, 'Password must contain at least one digit,one special ,one character');
   END IF;

   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findspecial;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20002, 'Password must contain at least one digit,one special , one character');
   END IF;

   -- 3. Check for the special
   <<findspecial>>
   isspecial:=FALSE;
   FOR i IN 1..length(special) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(special,i,1) THEN
            isspecial:=TRUE;             
         END IF;
      END LOOP;
   END LOOP;
   IF isspecial = FALSE THEN
      raise_application_error(-20002, 'Password must contain at least one digit,one special ,one character');
   END IF;

   -- Everything is fine; return TRUE; 
   RETURN(TRUE);
END;
/
alter profile student_profile limit
password_verify_function my_verify_function;

将创建函数的语句放在一个.sql文件中，然后在命令行执行：@file_path/xxx.sql即可。

42、数据库审计：

查看审计是否开启：show parameter audit; //如果audit_sys_operations值为FALSE就是没开审计)

开启审计功能：alter system set audit_trail=db_extended scope=spfile ;

alter system set audit_sys_operations=TRUEscope=spfile；

重启数据库实例：shutdown immediate ; //只有sys用户才有权限

startup force ;

语句级审计：audit table by user_name ;

权限级审计：audit all privilege ;

对象级审计：audit update any table；audit select on table_name ;

43、关闭审计：noaudit session ; no audit table by user_name ;