标签 会话 下的文章

expdp/impdp/rman会话进度查看

oracle11g使用DBA_DATAPUBMP_JOBS和DBA_DATAPUMP_SESSIONS视图来显示数据泵取作业的信息,
查看expdp进度:

select * from DBA_DATAPUBMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
select sid,serial# from v$session s,dba_datapump_sessions d where s.saddr=d.saddr;

查看impdp进度:

SELECT   a.tablespace_name,
         ROUND (a.total_size) "total_size(MB)",
         ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
         ROUND (b.free_size, 3) "free_size(MB)",
         ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
  FROM   (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 total_size
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 free_size
              FROM   dba_free_space
          GROUP BY   tablespace_name) b
 WHERE   a.tablespace_name = b.tablespace_name(+);

查看RMAN备份进度:

SELECT SID,OPNAME, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
order by "%_COMPLETE"
desc

删除用户ORA-01940: cannot drop a user that is currently connected解决方法

出现场景:Oracle删除用户时报错

SQL〉DROP USER USERXXXX CASCADE

ERROR:ORA-01940: cannot drop a user that is currently connected

由于当前用户正连接到数据库,所以无法删除

解决办法:

1、查询此用户的会话进程,

SQL〉SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USERXXXX';

SID SERIAL#
---------- ----------
98 27341
23 18117

2、结束此用户的所有会话

SQL>ALTER SYSTEM KILL SESSION '98,27341';

System altered.

SQL>ALTER SYSTEM KILL SESSION '23,18117';

System altered.

如果没有发现会话.要就要看是不是自己当前有没有使用这个用户.

3、删除用户

SQL〉DROP USER USERXXXX CASCADE;

最新

分类

归档

评论

其它