ORACLE常用命令

首先登录数据库

管理员登录：sqlplus / as sysdba;

普通用户登录：sqlplus uname/upass@orcl;

1、查看数据库服务名

select global_name form global_name;

2、显示当前登录用户

show user;

3、查看所有用户

select * from dba_users;

select * from all_users;

select * from user_users;

4、查看用户对象权限

select * from dba_table_privs;

select * from all_table_privs;

select * from user_table_privs;

5、查看登录用户所拥有的角色

select * from role_sys_privs; 

6、用户相关命令

创建用户：create user uname identified by upass default tablespace user_data_ts temporary tablespace user_temp_ts; //创建用户并为其指定数据表空间各临时表空间，注表空间必须先创建好

为用户授权：(其中etl为用户名)

grant connect to etl;

grant select_catalog_role to etl;

grant select any table to etl;

grant create session to etl;

grant alter session to etl;

grant create table to etl;

grant create view to etl;

grant create type to etl;

grant create synonym to etl;

grant create sequence to etl;

grant create procedure to etl;

  删除用户：drop user uname cascade;//级联删除用户

修改口令：alter user uname identified by new_pass;

修改默认表空间：alter user uname default tablespace new_data_ts;// new_data_ts为新表空间名

修改表空间配额：alter user uname quota unlimited on data_4;

      alter user uname quota 100M on data_1;

锁定用户：alter user uname account lock;

解锁用户：alter user uname account unlock;