Oracle 常用命令总结

一、数据库启动 & 关闭&查看

1、启动数据库

SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open;

2、关闭数据库

SQL> shutdown immediate；

3、连接数据库

• 登陆普通用户

SQL>sqlplus 用户名/密码@实例名//登陆普通用户，@实例名可省略

• 登陆sys帐户

SQL>sqlplus / as sysdba；//登陆 sys 帐户

说明：sys ：系统管理员，拥有最高权限
system ：本地管理员，次高权限
scott ：普通用户，密码默认为tiger，默认未解锁

4、查看数据库名

SELECT NAME FROM V$DATABASE;

5、查看实例

• Linux下查看oracle上已启动实例，如果多实例，会展示多个smon进程

ps -ef|grep smon

• 查看当前启动的实例

SQL>select * from global_name;

• 查看默认实例

 $ echo $ORACLE_SID

• 切换实例

$ export ORACLE_SID=实例名 

二、用户

1、创建用户

create user 用户名 identified by 密码;

2、重置密码

alter user 用户名 identified by 密码;

3、账户解锁

alter user 用户名 account unlock;

4、账号赋权

grant create session to zhangsan；//授予zhangsan用户创建session的权限，即登陆权限

grant unlimited session to zhangsan；//授予zhangsan用户使用表空间的权限

grant create table to zhangsan；//授予创建表的权限

grant drop table to zhangsan；//授予删除表的权限

grant insert table to zhangsan；//插入表的权限

grant update table to zhangsan；//修改表的权限

grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

grant all to public；//授予所有权限(all)给所有用户(public)

tip：oracle对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权

grant select on tablename to zhangsan；//授予zhangsan用户查看指定表的权限

grant drop on tablename to zhangsan；//授予删除表的权限

grant insert on tablename to zhangsan；//授予插入的权限

grant update on tablename to zhangsan；//授予修改指定表的权限

grant insert(id) on tablename to zhangsan；//授予对指定表特定字段的插入权限

grant update(id) on tablename to zhangsan;//授予对指定表特定字段的修改权限

grant role1 to test;//授予角色role1（role1为已存在的角色）

5、账户撤销权限

基本语法同 grant，关键字为 revoke

6、删除用户

drop user zhangsan; //仅删除用户

drop user zhangsan cascade; //删除用户的同时，删除此用户名下所有的对象

7、查询所有用户（DBA账号执行）

select * from all_users;

8、查看当前用户连接

select * from v$Session;

9、查看用户角色

SELECT * FROM USER_ROLE_PRIVS;

10、查看当前用户权限

select * from session_privs;

select * from user_sys_privs；//查看当前用户所有权限

11、查看所有用户所拥有的角色（DBA账号执行）

SELECT * FROM DBA_ROLE_PRIVS;

12、创建用户并指定表空间

create user 用户名 identified by 密码 
default tablespace 默认表空间名
temporary tablespace 默认临时表空间名;

13、查看当前用户的缺省表空间

select username,default_tablespace from user_users;

三、视图

1、创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]
AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]

参数说明：

• OR REPLACE ：若所创建的试图已经存在，则替换旧视图；

• FORCE：不管基表是否存在ORACLE都会自动创建该视图(即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用)；

• NOFORCE ：如果基表不存在，无法创建视图，该项是默认选项(只有基表都存在ORACLE才会创建该视图)。

• alias：为视图产生的列定义的别名；

• subquery ：一条完整的SELECT语句，可以在该语句中定义别名；

• WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

• WITH READ ONLY ：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH
  READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

2、删除视图

drop BIEW view_name;

四、角色

1、创建角色

create role myrole；

2、给角色赋权

grant select on table1 to role1;//给角色 role1 赋予查询表 table1 的权限

3、将角色赋予某用户：grant 角色名 to 用户名

grant role1 to zhangsan;//role1为已存在的角色

4、删除角色：drop role 角色名

drop role myrole；

五、表空间

1、创建表空间

create tablespace 表空间名 datafile '/oracle/.../数据文件名.dbf' size 100m autoextend on next 100m maxsize 400m extent management local;

2、删除表空间

drop tablespace 表空间名 ;

3、查看数据库的表空间使用状态

select a.tablespace_name tnm,b.FILE_PATH,--b.autoextensible,
       b.cnt,
       trunc(a.bytes/1024/1024/1024) total_G,
       trunc(a.bytes/1024/1024/1024/b.cnt) avg_G,
       trunc(c.bytes/1024/1024/1024) free_G,
       trunc((a.bytes-c.bytes)*100/a.bytes,2) used--,(c.bytes*100)/a.bytes "% FREE"
     from SYS.SM$TS_AVAIL A,SYS.SM$TS_FREE C,
         (select tablespace_name,substr(file_name,1,instr(file_name,'/',2)) FILE_PATH, --f.autoextensible,
         count(*) cnt 
          from dba_data_files  f group by tablespace_name,substr(file_name,1,instr(file_name,'/',2))--,autoextensible
          ) b
     WHERE  A.TABLESPACE_NAME=C.TABLESPACE_NAME(+)
          AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
       --   AND A.TABLESPACE_NAME IN (select distinct tablespace_name from dba_tablespaces)
          order by  avg_g desc;