Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
-- 查看表空间物理文件的名称及大小
SELECT tablespace_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
-- 查询所有用户所对应的表空间,有dba权限的帐号,如sys,system
select username, default_tablespace from dba_users;
-- 查看你能管理的所有用户
select * from all_users;
-- 查看当前用户信息
select * from user_users;
-- 创建表空间
-- 格式:create tablespace 表空间名 datafile '数据库物理路径及文件名' size 表空间大小
create tablespace DDATA datafile '/u01/app/oracle/oradata/XE/DDATA.dbf' size 2000M;
create tablespace DDATA datafile 'c:\oracle\oradata\test\DDATA.dbf' size 2000M;
-- 删除表空间
drop tablespace DDATA including contents and datafiles cascade constraint;
-- 创建用户并指定表空间
-- 格式: create user 用户名 identified by 密码 default tablespace 表空间名
create user DNAME identified by DPWD default tablespace DDATA;
-- 修改指定用户的密码
alter user DNAME identified by DPWD
-- 为用户指定表空间
-- 格式:alter user 用户名 default tablespace 表空间名
alter user DNAME default tablespace DDATA;
-- 删除用户
-- 格式:drop user 用户名 cascade
drop user DNAME cascade;
-- 授予权限
-- connect:授予最终用户的典型权利,最基本的
-- resource:授予开发人员
-- dba:授予数据库所有的权限
grant connect, resource to DNAME;
grant dba to DNAME;
-- 撤销权限
revoke dba from DNAME;
-- 查询用户授予的角色权限
select * from dba_role_privs where grantee='DNAME'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.