oracle操作3

常用oracle操作(三)

杀掉oracle进程：alter system kill session 'sid,serial#';

--从数据库段中获得段名为TB_DW_SC_USER_CUR的所有的分区名以及他们的容量大小
select partition_name,bytes/1024/1024 from dba_segments where segment_name='TB_DW_SC_USER_CUR'
 order by 1;
 
select * from v$open_cursor where sid = :sid;

select   value from     v$sesstat
where    sid = :sid
order by statistic#;
select l.*, o.owner object_owner, o.object_Name
from  sys.all_objects o, v$lock l
where l.sid = :sid and l.type = 'TM' and o.object_id = l.id1

alter session set current_schema=rene;
##################################
##会话相关,从会话找到对应的unix进程号及正在执行的sql语句
##################################
Select  sid,serial#,program ,logon_time,username,osuser
 From v$session
 Where 1=1
 -- and program Like 'sql%'
 -- and username = 'HW_QUERY'
 -- And logon_time<sysdate-5/60
 and osuser ='hwuser'
 order by 3,4
 ;
Select paddr,sql_address From v$session
 Where sid =932;
 paddr='07000001371FC968'
SELECT spid
  FROM v$process
 WHERE (addr = '07000003F52BFF68');

SELECT   hash_value,sql_text
    FROM v$sqltext_with_newlines
   WHERE hash_value = TO_NUMBER (:HASH)
ORDER BY piece

select SQL_FULLTEXT from v$sqlarea_plan_hash t Where t.ADDRESS='07000003E1DD6818'
 v$sqlarea

Select p.spid,s.sid,s.serial#,s.username,s.program ,s.logon_time From v$session s,v$process p
 Where s.paddr =p.addr
 order by 4,5;

##################################
##会话相关,spid====>sqltext
##################################

--人v$process找到session的地址
SELECT addr,spid
  FROM v$process
 WHERE (spid= 296118);
 --找当前执行的sql及前一sql的地址
Select sql_address,prev_sql_addr,sid,serial#,paddr From v$session Where
 paddr='07000004EC287A80';
--从sql_address找语句
select sql_fulltext from v$sqlarea_plan_hash t Where t.ADDRESS='07000004DB478478';

select sid,serial#,paddr,sql_address,program,logon_time from v$session
where program like 'sqlpl%'
and sql_address <>'00';

##################################
#被锁定的对象
##################################
select o.owner,                                            
       o.object_name,                                      
       s.sid,                                               
       s.serial#,                                        
       -- lo.xidusn,                                          
       -- lo.xidslot,                                         
       -- lo.xidsqn,                                          
       -- lo.session_id,                                      
       --lo.oracle_username,                                 
       -- lo.os_user_name,                                    
       --lo.process,                                         
       --lo.locked_mode,                                     
       --s.machine,                                          
       --s.status,                                           
       --s.server,                                            
       s.program                                           
from all_objects o,                                      
   v$locked_object lo,                                 
   sys.gv_$session s                                   
where ( o.object_id = lo.object_id )
and        ( lo.process = s.process )
-- and s.sid=1013
order by object_name     
;
--EXPLAIN PLAN
EXPLAIN PLAN
    SET STATEMENT_ID = 'pxs'
    INTO plan_table
    FOR
 
 ;
column OPERATION format a48
column options format a32
 
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
    FROM plan_table
    START WITH id = 0 AND statement_id = 'pxs'
    CONNECT BY PRIOR id = parent_id AND
    statement_id = 'pxs';

222120
--查看数据分区情况
select partition_name,bytes/1024/1024 from dba_segments where segment_name = upper('tb_mk_user_last_call_day')

$ORACLE_HOME/sqlplus/admin/glogin.sql
##################################
--周处理
to_char(start_date,'ww')--年内周
to_char(start_date,'w') --月内周
##################################
#空间相关
##################################

--查看空间占用
select tablespace_name,round(sum(bytes)/1024/1024/1024) as "free(G)"
from dba_free_space
group by tablespace_name
order by 1;

--查看表空间的名称及大小
column db_name format a10
select sys_context ('USERENV', 'db_name') db_name,t.tablespace_name,round(sum(bytes/(1024*1024*1024)),0) "ts_size(G)"
from sys.dba_tablespaces t, sys.dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name
order by 2;

--dba_segments 数据块字典
select segment_name,tablespace_name,bytes/1024/1024
 from dba_segments t1 ,
('TB_MK_CL_DOWNLOAD_WEEK','TB_MK_NS_SERVICE_WEEK','TB_MK_SC_OPP_USER_WEEK','TB_MK_SC_USER_CELL_WEEK_TEN','TB_MK_SC_USER_PLUS_WEEK','TB_MK_SC_USER_WEEK')
;

##################################
#权限角色相关
##################################

#数据库中定义的角色
SELect * from dba_role_privs where GRANTED_ROLE='DBA' order by 1,2;
#角色具有的权限
SELect * from dba_sys_privs where grantee ='BIM' order by 1,2;
#表空间使用份额
select * from dba_ts_quotas;
#分区配置表的关键参数,bim用户下对dw/mk/kr的tb_sys_tab_partitions 做了个union all的视图
select table_name,partition_name,tablespace_name,partition_count,PARTITION_TYPE_ID,deal_cycle,TABLESPACE_TYPE_ID,partition_count
--select *
from tb_sys_tab_partitions
where table_name in('TB_DW_CL_SHDPAY_CUR','TB_DW_CL_GROUP_PERSON_CUR_H','TB_DW_CL_SHDPAY_CUR');

update dgdm_dw.tb_sys_tab_partitions set PARTITION_TYPE_ID='L' where table_name ='TB_DW_CL_GROUP_PERSON_CUR_H';

##################################
#分区相关
##################################
#新增没有配置的分区表
select owner,segment_name from dba_segments
 where segment_type='TABLE PARTITION' and owner like 'DGDM%' and owner not like '%ODS'
MINUS
select owner,table_name from tb_sys_tab_partitions;

#分区表数据字典,查找分区类型
select table_name,partitioning_type,partitioning_type
from dba_part_tables
where table_name in('TB_DW_CL_SHDPAY_CUR','TB_DW_CL_GROUP_PERSON_CUR_H','TB_DW_CL_SHDPAY_CUR');
#分区字段字典.
select * from dba_part_key_columns;
--表分区字典
--select table_name,partition_name,compression from dba_tab_partitions where table_name like 'TB_MK%' and rownum<5;
select table_owner,table_name,partition_name,compression from dba_tab_partitions
 where table_name like 'TB/_%' escape '/'
 and compression<>'ENABLED'
 and table_owner in ('DGDM_DW','DGDM_MK','DGDM_KR');

# 分区表的默认表空间,前段困扰我们的masa表空间不足的罪魁祸首
Select table_name, def_tablespace_name From all_part_tables  Where (substr(def_tablespace_name,-1) <>'0') And TABLE_NAME Like 'TB_DW%';
Select 'alter table  dgdm_dw.'||table_name|| ' modify default attributes tablespace tbs_data_0;' def_tablespace_name From all_part_tables 
Where 0=0
 --and (substr(def_tablespace_name,-1) <>'0')
 And TABLE_NAME Like 'TB_DW%'
 and def_tablespace_name ='TBS_DW_CUST_DATA_0';
#更改分区表的默认表空间
alter table test_part modify default attributes tablespace test03;

##################################
#用sql语句生成脚本然后拷贝到sqlplus中执行是一种很常用的技巧.
##################################
select 'alter table '||table_owner||'.'||table_name ||' move partition '||partition_name||' compress;'
 from dba_tab_partitions
 where table_name like 'TB_MK%'
  and compression <>'ENABLED'
  and table_name like 'TB_MK%';

select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||' compress;'
select sys_context ('USERENV', 'db_name') db_name,table_owner,table_name,partition_name,compression
from dba_tab_partitions
where  1=1
and table_name like 'TB____%'
and compression ='DISABLED'
and table_owner like 'DGDM%'
;

##################################
#日志
##################################
column table_name format a32
column task_name format a32
column task_log format a60
column task_pos format a60
column task_sign format a60
select  task_sign,start_time,end_time,task_name,table_name ,task_log
from --dgdm_dw.tb_dw_sys_log
  (
   select * from gddm_cb.tb_cb_sys_log
   union all
   select * from dgdm_ods.tb_in_sys_log
   union all
   select * from dgdm_dw.tb_dw_sys_log
   union all
   select * from dgdm_mk.tb_mk_sys_log
   union all
   select * from dgdm_kr.tb_kr_sys_log
  )    
where 1=1
 and start_time>sysdate-1
 and table_name like 'tb_kr_data_service_mon'
 -- and instr( task_log ,'ORA-')>0
 order by 1 ;

#表空间中的对象
select segment_name from dba_segments where partition_name like '%200706%' and tablespace_name='TBS_MK_FACT_DATA_5';

#分区大小
select owner,segment_name,partition_name,tablespace_name,bytes/1024/1024
 from dba_segments
 where 1=1
  --and segment_name='TB_DW_PD_USR_MARKPLAN'
  and tablespace_name='TBS_DATA_H6'
 order by 1,2,3;

#周表月份核对
--周表
 select tablespace_name,owner,segment_name,partition_name,segment_type,bytes/1024/1024
 ,(select to_char(end_date,'m') from gddm_cb.tb_dic_week t2 where t2.STATIS_WEEK= substr(partition_name,instr(partition_name,'_',-1)+5,2)),substr(tablespace_name,-1) wm,
 (select tablespace_name from tb_sys_tab_partitions tt where tt.table_name=t1.segment_name) ts
 ,
 from dba_segments t1
 where SEGMENT_TYPE='TABLE PARTITION'
 and segment_name like '%WEEK%'
 and length(substr(partition_name,instr(partition_name,'_',-1)+1))=6
 and owner like 'DGDM%' and owner not like '%ODS'
ORDER BY segment_name,partition_name;

--普通表
select tablespace_name,owner,segment_name,partition_name,segment_type,bytes/1024/1024
 ,substr(partition_name,instr(partition_name,'_',-1)+5,2),substr(tablespace_name,-1),
 (select tablespace_name from tb_sys_tab_partitions ta where ta.table_name= t1.segment_name)||'_'
 from dba_segments  t1
 where SEGMENT_TYPE='TABLE PARTITION'
  --and substr(partition_name,instr(partition_name,'_',-1)+5,2) - substr(tablespace_name,-1) not in (0,6)
  and segment_name not like '%WEEK'
  and owner ='DGDM_MK'
 ORDER BY segment_name,partition_name;

##################################
#表空间整理
##################################

select T1.owner,T1.segment_name,T1.partition_name,T1.bytes/1024/1024 MB,t1.tablespace_name
,t1.segment_type
,(SELECT TABLESPACE_NAME FROM TB_SYS_TAB_PARTITIONS T2 WHERE T1.SEGMENT_NAME= T2.TABLE_NAME)
,substr(partition_name,instr(partition_name,'_',-1)+5,2)
,SYS_CONTEXT ('USERENV', 'db_name') db_name
from dba_segments T1
where 1=1
and tablespace_name LIKE'TBS_KR_DATA_1%'
--and partition_name like '%200708%'
-- and segment_type='TABLE PARTITION'
 -- and segment_name in (
-- 'TB_KR_SC_USER_DAY'
-- )
-- and segment_name not like 'TB_MK_LS%'
--and segment_name in (select table_name from tb_sys_tab_partitions where table_name like '%MK%MID')
and segment_name not in ('TB_MK_CL_DOWNLOAD_WEEK','TB_MK_NS_SERVICE_WEEK','TB_MK_SC_OPP_USER_WEEK','TB_MK_SC_USER_CELL_WEEK_TEN','TB_MK_SC_USER_PLUS_WEEK','TB_MK_SC_USER_WEEK')
--and owner like 'DGDM%'
--and T1.bytes/1024/1024 between 1 and 32
order by 1,2,3;

update dgdm_mk.tb_sys_tab_partitions set tablespace_name='TBS_MK_FACT_DATA'
 where tablespace_name  in('TBS_DW_RES_DATA'
-- ,'TBS_DW_DATA_FUNC'
-- ,'TBS_DW_CUST_DATA_H'
-- ,'TBS_DW_CUST_DATA'
 );
 update dgdm_dw.tb_sys_tab_partitions set tablespace_name='TBS_DW_AC_DATA'
 where tablespace_name  in('TBS_DW_RES_DATA'
-- ,'TBS_DW_DATA_FUNC'
-- ,'TBS_DW_CUST_DATA_H'
 --,'TBS_DW_CUST_DATA'
 );
 
update dgdm_mk.tb_sys_tab_partitions set tablespace_name='TBS_MK_FACT_DATA' where table_name
IN(
'TB_MK_SC_USER_CELL_WEEK_TEN'
,'TB_MK_SC_USER_DAY'
,'TB_MK_SC_USER_DTAL'
);

select segment_name,partition_name,bytes/1024/1024 from dba_segments where segment_name in (select table_name from tb_sys_tab_partitions where tablespace_name ='TBS_DATA_H') order by 1,2;

select  T1.owner,T1.segment_name,T1.partition_name,T1.bytes/1024/1024 MB,t1.tablespace_name
,t1.segment_type
,(SELECT TABLESPACE_NAME FROM TB_SYS_TAB_PARTITIONS T2 WHERE T1.SEGMENT_NAME= T2.TABLE_NAME)
 from dba_segments t1 ,tb_sys_tab_partitions t2
  where t1.segment_name= t2.table_name
  and t1.partition_name like '%200707%'
  and t1.tablespace_name not like t2.tablespace_name||'%'
  and segment_name not in ('TB_MK_CL_DOWNLOAD_WEEK','TB_MK_NS_SERVICE_WEEK','TB_MK_SC_OPP_USER_WEEK','TB_MK_SC_USER_CELL_WEEK_TEN','TB_MK_SC_USER_PLUS_WEEK','TB_MK_SC_USER_WEEK');

select SYS_CONTEXT ('USERENV', 'db_name') db_name,'JUN',t2.tablespace_name,round(sum(t1.bytes)/1024/1024/1024 )
  from dba_segments t1,tb_sys_tab_partitions t2
  where 1=1
   and t1.segment_name= t2.table_name
   and t1.partition_name like '%200706%'
 and t2.owner<>'DGDM_ODS'
  group by t2.tablespace_name
  order by 1,2,3;

alter table dgdm_mk.TB_DIC_TEL_PHONE_SEGMENT move tablespace tbs_etl_data storage( initial 8k);

ALTER TABLE DGDM_MK.TB_MK_CL_USER_MON DROP PARTITION CL_USER_MON_200701;

#表空间与数据文件
select tablespace_name,file_name from dba_data_files where tablespace_name in(
'TBS_DW_CUST_DATA_0'
,'TBS_DW_CUST_DATA_1'
,'TBS_DW_CUST_DATA_2'
,'TBS_DW_CUST_DATA_3'
,'TBS_DW_CUST_DATA_4'
,'TBS_DW_CUST_DATA_5'
,'TBS_DW_CUST_DATA_6'
,'TBS_DW_CUST_DATA_H1'
,'TBS_DW_CUST_DATA_H10'
,'TBS_DW_CUST_DATA_H11'
,'TBS_DW_CUST_DATA_H12'
,'TBS_DW_CUST_DATA_H2'
,'TBS_DW_CUST_DATA_H3'
,'TBS_DW_CUST_DATA_H4'
,'TBS_DW_CUST_DATA_H5'
,'TBS_DW_CUST_DATA_H6'
,'TBS_DW_CUST_DATA_H7'
,'TBS_DW_CUST_DATA_H8'
,'TBS_DW_CUST_DATA_H9'
)
order by 1,2;

##################################
---LOB
##################################
ALTER TABLE foo MOVE
TABLESPACE new_tbsp STORAGE(new_storage)
LOB (lobcol) STORE AS lobsegment
(TABLESPACE new_tbsp STORAGE (new_storage));

##################################
---月空间占用情况
##################################

select SYS_CONTEXT ('USERENV', 'db_name') db_name,'JUN',t2.tablespace_name,round(sum(t1.bytes)/1024/1024/1024 ) as GB
  from dba_segments t1,tb_sys_tab_partitions t2
  where 1=1
   and t1.segment_name= t2.table_name
   and t1.partition_name like '%200706%'
   and t2.tablespace_name not like '%0'
   and t1.segment_type='TABLE PARTITION'
 and t2.owner<>'DGDM_ODS'
  group by t2.tablespace_name
  order by 1,2,3;

select sys_context ('USERENV', 'db_name') db_name,t.tablespace_name,round(sum(bytes/(1024*1024*1024)),0) "ts_size(G)"
from sys.dba_tablespaces t, sys.dba_data_files d
where t.tablespace_name = d.tablespace_name
and t.tablespace_name like '%3'
and t.tablespace_name <>'TBS_ODS_DATA_3'
group by t.tablespace_name
order by 1,2;

select segment_name,sum(bytes)/1024/1024 MB
from dba_segments
where segment_name in
 (select TAble_name from tb_sys_tab_partitions
  where tablespace_name   like 'TBS_MK_LIST_DATA'
  --and table_name like 'TB_MK%'
  )
and partition_name like '%200707%'
group by segment_name order by 1;

select segment_name,sum(bytes)/1024/1024 MB
from dba_segments
where segment_name in
 (select TAble_name from tb_sys_tab_partitions where 1=1
  -- and tablespace_name  like 'TBS_MK_LIST_DATA'
 and table_name like 'TB_MK_LS%'
 )
and partition_name like '%200707%'
group by segment_name order by 1;

update dgdm_mk.tb_sys_tab_partitions set tablespace_name='TBS_MK_FACT_DATA' where table_name
IN(
'TB_MK_CL_FX_BILL'
,'TB_MK_SC_CELL_DAY_MID'
,'TB_MK_SC_USER_CELL_WEEK_TEN'
,'TB_MK_SC_USER_DAY'
,'TB_MK_SC_USER_DTAL'
,'TB_MK_SC_USER_PLUS_DAY'
);

TB_MK_CL_FX_BILL                |        16
TB_MK_SC_CELL_DAY_MID           |      5432
TB_MK_SC_USER_CELL_WEEK_TEN     |         1
TB_MK_SC_USER_DAY               |      6428
TB_MK_SC_USER_DTAL              |      7224
TB_MK_SC_USER_PLUS_DAY          |      4814

--Using the GROUPING SETS Clause: Example
SELECT channel_desc, calendar_month_desc, co.country_id,
      TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$
   FROM sales, customers, times, channels, countries co
   WHERE sales.time_id=times.time_id
      AND sales.cust_id=customers.cust_id
      AND sales.channel_id= channels.channel_id
      AND customers.country_id = co.country_id
      AND channels.channel_desc IN ('Direct Sales', 'Internet')
      AND times.calendar_month_desc IN ('2000-09', '2000-10')
      AND co.country_id IN ('UK', 'US')
  GROUP BY GROUPING SETS(
      (channel_desc, calendar_month_desc, co.country_id),
      (channel_desc, co.country_id),
      ( calendar_month_desc, co.country_id) );

CHANNEL_DESC         CALENDAR CO SALES$
-------------------- -------- -- --------------
Direct Sales         2000-09  UK      1,378,126
Direct Sales         2000-10  UK      1,388,051
Direct Sales         2000-09  US      2,835,557
Direct Sales         2000-10  US      2,908,706
Internet             2000-09  UK        911,739
Internet             2000-10  UK        876,571
Internet             2000-09  US      1,732,240
Internet             2000-10  US      1,893,753
Direct Sales                  UK      2,766,177
Direct Sales                  US      5,744,263
Internet                      UK      1,788,310
Internet                      US      3,625,993
                     2000-09  UK      2,289,865
                     2000-09  US      4,567,797
                     2000-10  UK      2,264,622
                     2000-10  US      4,802,459