如何查看存过函数执行到第几步

如何查看存过函数执行到第几步：

根据sid 去v$session 里查，有个sql_id字段，根据这个sql_id去v$sql里查，就是现在在执行的sql

1、是确定正在执行的存储过程的sid：
　    select * from v$session t where t.username = 'ZC_AUDI' and t.status='ACTIVE';--用户名大写　　

　　select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object); 

　　select sql_text from v$sql where hash_value in (     select sql_hash_value from v$session where sid in (select session_id from v$locked_object) )

 

2、SQL_EXEC_START字段，是sql开始执行的时间，根据这个时间能看出来哪个是对应存过的sid。

 

查看当前库有哪些用户在执行脚本，执行完成的百分比是多少

select username,sid,opname,
      round(sofar*100 / totalwork,0) || '%' as progress,
      time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
order by round(sofar*100 / totalwork,0) desc;

 --查看表空间剩余量

select UPPER(F.TABLESPACE_NAME) "表 空 间 名 ",
       D.TOT_GROOTTE_MB "表 空 间 大 小 (M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已 使 用 空 间 (M)",TO_char(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使 用 比 ",
       F.TOTAL_BYTES "空 闲 空 间 (M)",
       F.MAX_BYTES "最 大 块 (M)"
  FROM (select TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (select DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 where D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;

 

--查看对象占用的表空间

 SELECT   owner,SEGMENT_NAME, (SUM(BYTES) / 1024 / 1024)     FROM DBA_SEGMENTS   WHERE TABLESPACE_NAME = 'TBS_CRM2_DAT_HASH07'   GROUP BY owner,SEGMENT_NAME

 

--建立dblink连接

create database link DB_GROUP_SH   connect to 用户 identified by 密码 using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ××.××.××.××
)(PORT = 3436)) (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = ctmx**pr) (INSTANCE_NAME = ctmx**pr1)))';

 

 

 

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s where p.name

='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid; 17:36:17 计费-熊杰 2017/9/29 17:36:17 --表空间  select 　　a.a1 表空间名称, 　　c.c2 类型, 　　c.c3 区管理, 　　b.b2/1024/1024 表空间大小M, 　　(b.b2-a.a2)/1024/1024 已使用M, 　　substr((b.b2-a.a2)/b.b2*100,1,5) 利用率 　　from 　　(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a, 　　(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b, 　　(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c 　　where a.a1=b.b1 and c.c1=b.b1 and a.a1 like 'ACCOUNT%';

--表空间带temp undo SELECT d.status "Status",        d.tablespace_name "Name",        d.contents "Type",        d.extent_management "Extent Management",        to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",        to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,                '99999999.999') "Used (M)",        to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",        to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),                '990.00') "Used %"   FROM sys.dba_tablespaces d,        (SELECT tablespace_name, SUM(bytes) bytes           FROM dba_data_files          GROUP BY tablespace_name) a,        (SELECT tablespace_name, SUM(bytes) bytes           FROM dba_free_space          GROUP BY tablespace_name) f  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = f.tablespace_name(+)    AND NOT         (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY') UNION ALL SELECT d.status "Status",        d.tablespace_name "Name",        d.contents "Type",        d.extent_management "Extent Management",        to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",        to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",        to_char((nvl(a.bytes / 1024 / 1024, 0)) -                (nvl(t.bytes, 0) / 1024 / 1024),                '99999999.999') "Free (M)",        to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"   FROM sys.dba_tablespaces d,        (SELECT tablespace_name, SUM(bytes) bytes           FROM dba_temp_files          GROUP BY tablespace_name) a,        (SELECT tablespace_name, SUM(bytes_cached) bytes           FROM v$temp_extent_pool          GROUP BY tablespace_name) t  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = t.tablespace_name(+)    AND d.extent_management LIKE 'LOCAL'    AND d.contents LIKE 'TEMPORARY'  ORDER BY "Used %" DESC;   SELECT   tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)" FROM   dba_undo_extents GROUP BY   tablespace_name, status;

--清理表空间 select owner, segment_name, bytes / 1024 / 1024   from dba_segments  where tablespace_name like 'BOSS_DEST_DATA'  --and bytes / 1024 / 1024 > 100  order by bytes desc;   --查看回收站 select * from user_recyclebin; --清理回收站 purge recyclebin;