1.设置不换行
set lines 150
set wrap off
set pagesize 1000
set trimspool on
2.查询用户
select * from dba_users;
select * from all_users;
3.查询用户的权限
3.1. oracle用户查看自己的权限和角色
select * from user_tab_privs;
select * from user_role_privs;
3.2. sys用户查看任一用户的权限和角色
select * from dba_tab_privs;
select * from dba_role_privs;
dba_roles
dba_role_privs
dba_sys_privs
dba_tab_privs
4.
select table_name from user_tables;
select table_name from dba_tables where owner='USERNAME';
select * from user_objects where upper(object_type)='TABLE';
select * from all_objects where object_type='TABLE' and owner='用户';
select privilege from dba_sys_privs where grantee='JCR'
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='JCR');
select * from dba_sys_privs where grantee='JCR'
union
select * from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='JCR');
----查看用户全部权限
select * from dba_sys_privs where grantee='RELEASE'
union
select * from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='RELEASE');
5.表空间
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
6.用户定义
create user release identified by password default tablespace users temporary tablespace temp;
grant connect, resource to release;
7.导入导出单个用户的数据
exp system/passw0rd@instance file=E:/RELEASE.dmp owner=RELEASE;
imp system/passw0rd@instance file=E:/RELEASE.dmp FROMUSER=RELEASE TOUSER=RELEASE;