查看数据库连接情况

查询重复数据

select parameter_name,eqpno,COUNT(*) from eap_fdc.edc_pam_data_cur group by parameter_name,eqpno having count(*) > 1;

查看数据库连接情况

select  b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from  v$session b where b.username is not null group by  b.MACHINE, b.PROGRAM, b.USERNAME order by count(*) desc

查询数据库游标使用情况

select 'session_cached_cursors' parameter,
       lpad(value, 5) value,
       decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%') usage
  from (select max(s.value) used
          from v$statname n, v$sesstat s
         where n.name = 'session cursor cache count'
           and s.statistic# = n.statistic#),
       (select value from v$parameter where name = 'session_cached_cursors')
union all
select 'open_cursors',
       lpad(value, 5),
       to_char(100 * used / value, '990') || '%'
  from (select max(sum(s.value)) used
          from v$statname n, v$sesstat s
         where n.name in
               ('opened cursors current', 'session cursor cache count')
           and s.statistic# = n.statistic#
         group by s.sid),
       (select value from v$parameter where name = 'open_cursors')

0、查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;

1、表占用空间：

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;

2、索引占用空间：

select segment_name ,sum(bytes)/1024/1024 from `user_segments where segment_type ='INDEX' group by segment_name;

3、分区表TABLE PARTITION占用空间：

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;

–将查询出来的表数据重新插入

INSERT INTO EAP_BAS.bas_sec_auth_menu
  (ID,description,  MENU_NAME, PAGE_NAME, ACTION, SUB_PAGENAME, MENU_ORDER,URL,PROJECT,ICON,COMPONENT_NAME,MODULE)
  (SELECT t.id+4000000,
        t.description,
          t.menu_name,
          t.page_name,t.action,t.sub_pagename,t.menu_order+4000000,t.url,'EMS',t.icon,t.component_name,t.module
         
     FROM EAP_BAS.bas_sec_auth_menu T where t.project='EMSv1');
     
     delete from EAP_BAS.bas_sec_auth_menu T where t.project='EMSv1';
     

update EAP_BAS.bas_sec_auth_menu set id = id-110000 ,menu_order = id-110000 WHERE id >= 8150000 and id< 8160000;

修改子分区模板

alter table edc_pam_tracedata
set SUBPARTITION TEMPLATE ( 
    SUBPARTITION "AHD" VALUES ( 'AHD' ), 
    SUBPARTITION "AHD01" VALUES ( 'AHD01' ), 
    SUBPARTITION "AHD02" VALUES ( 'AHD02' ), 
    SUBPARTITION "AHD03" VALUES ( 'AHD03' ), 
    SUBPARTITION "AHD04" VALUES ( 'AHD04' ), 
    SUBPARTITION "AHD05" VALUES ( 'AHD05' ), 
    SUBPARTITION "AHD06" VALUES ( 'AHD06' ), 
    SUBPARTITION "AHD07" VALUES ( 'AHD07' ), 
    SUBPARTITION "AHD08" VALUES ( 'AHD08' ), 
    SUBPARTITION "AHD09"