##锁定用户
alter user USERNAME account lock;
##查看用户资源占用
select saddr,sid,serial#,paddr,username,status from v$session where username = 'USERNAME';
##停止资源占用
alter system kill session 'sid,serial#';
##批量生成停止资源占用的SQL
select 'alter system kill session'||' '||''''||sid||','||serial#||''''||';' from v$session where username = 'USERNAME';
##查询用户权限
select * from dba_sys_privs where grantee = 'USERNAME';
select * from dba_role_privs where grantee = 'USERNAME';
##查询用户所属表及表空间
select distinct owner,tablespace_name from dba_tables where owner = 'USERNAME';
##删除用户
drop user USERNAME cascade;
##创建表空间
create tablespace DS datafile '/oracle/app/oracle/oradata/hstadb/DS1.dbf' size 10g;
##创建用户并指定默认表空间
create user USERNAME identified by oracle default tablespace DS;
##用户授权
grant CONNECT,RESOURCE to USERNAME;
grant CREATE VIEW,UNLIMITED TABLESPACE,CREATE SYNONYM,CREATE TABLE,CREATE DATABASE LINK to USERNAME;
grant DBA,IMP_FULL_DATABASE,EXP_FULL_DATABASE to USERNAME;
##查询虚拟目录位置
set linesize 200;
col DIRECTORY_PATH for a80;
select * from dba_directories;
##创建虚拟目录 MYDMP
create directory MYDMP as '/home/oracle';
##授权虚拟目录读写权限给用户 USERNAME
grant read,write on directory MYDMP to USERNAME;
##授权导出导入权限给用户 USERNAME
grant EXP_FULL_DATABASE,IMP_FULL_DATABASE to USERNAME;
##导入数据 参数 table_exists_action=replace 不设置
impdp USERNAME/[email protected]:1521/ORCL directory=mydmp dumpfile=orcl_dmp_20221017.dmp logfile=orcl_log_20221017.log remap_schema=USERNAME:USERNAME content=all;
用户未锁定
SQL> drop user USERNAME cascade;
drop user USERNAME cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected