Oracle 常用命令总结

文章目录

• 一、数据库启动 & 关闭&查看
• • 1、启动数据库
  • 2、关闭数据库
  • 3、连接数据库
  • 4、查看数据库名
  • 5、查看实例
  • 二、用户
  • • 1、创建用户
    • 2、重置密码
    • 3、账户解锁
    • 4、账号赋权
    • 5、账户撤销权限
    • 6、删除用户
    • 7、查询所有用户（DBA账号执行）
    • 8、查看当前用户连接
    • 9、查看用户角色
    • 10、查看当前用户权限
    • 11、查看所有用户所拥有的角色（DBA账号执行）
    • 12、创建用户并指定表空间
    • 13、查看当前用户的缺省表空间
    • 三、视图
    • • 1、创建视图
      • 2、删除视图
      • 四、角色
      • • 1、创建角色
        • 2、给角色赋权
        • 3、将角色赋予某用户：grant 角色名 to 用户名
        • 4、删除角色：drop role 角色名
        • 五、表空间
        • • 1、创建表空间
          • 2、删除表空间
          • 3、查看数据库的表空间使用状态

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

            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;