oracle数据库语法大全

--锁定账户
Alter user scott account lock;

--解锁账户
Alter user scott account unlock;

--创建用户及密码
create user mm identified by 123;

--修改用户密码
alter user mm identified by bbs;

--查询用户mm的默认表空间
select username,default_tablespace from dba_users;

--创建表空间
Create tablespace worktbs
Datafile 'D:\ljk\worktbs.dbf'
size 10M autoextend on;

--分配表空间
alter user mm default tablespace worktbs;

--给用户分配系统权限
grant create session,create table,create view ,create sequence,unlimited tablespace to mm;

--查询所有权限
select * from session_privs;

--删除建表权限
revoke create table from mm;

--给予建表权限
grant create table to mm;

create table workmm(
id number not null
);

--授权角色权限（conncet resource dba ）
grant dba to scott;

--查看所有用户
select * from dba_users;
select * from all_users;
select * from user_users;

--查看用户系统权限
select * from dba_sys_privs;
select * from session_privs;
select * from user_sys_privs;

--查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

--查看所有角色
select * from dab_roles;

--查看用户所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;

--收回权限
Revoke dba from scott;

--删除用户
drop user mm cascade;

--删除表空间
drop Tablespace worktbs;