select t.segment_name,sum(t.bytes)/1024/1024
from user_segments t
where segment_name like 'TB%'
group by t.segment_name
order by sum(t.bytes)/1024/1024 desc
2、删除表分区
alter table TB_CALL_CUST_DAY droppartition PART_20110528;(普通分区名称)
alter table TB_ORDER_USER_DAY drop partitionPART_20110609_SUBPART_210001;(子分区名称)
3、查询表的子分区(当前数据用户)
select *
from user_segments t
where segment_name like 'TB_ORDER_USER_DAY%'
手工增加子分区(分区必须大于当前分区日期)
alter table tb_order_info_user_day add partition part_20111002 values less than ('20111103');
转移分区
alter table tb_fi_cust_view_day move Partition PART_20100505 tablespace TBS_user nologging;
转移表空间 ,将该表20100505的分区转移至表空间TBS_user ,这样他就占用的是user的空间
alter table TB_MK_CUST_INFO_DAY drop partition PART_20100301; 删除表分区
alter table tbname drop partition partname
alter table tbname move partition partname tablespace tbspacename
4、创建表的时候指定表分区
create table test
tablespace tbs_test
as
select * from user
5、alter table 增加多个字段
alter table tb_cust_view_day
add (total_pith_fee NUMBER(12,2),total_call_dur NUMBER(12),total_send_sms_cnt NUMBER(12),total_gprs_m NUMBER(17,2)) ;
删除字段 alter table tb_cust_view_day drop column total_pith_fee
6、单表赋权,如果想把用户A下的table1的update权限赋予用户B
在用户A连接状态下执行:
grant update on table1 to B;
grant select on table1 to B ; --查询权限
grant select on tb_user_info_day to user
7、统计一个月的上班时间点(包括周末)
select
substr(to_char(t.oper_time,'yyyymmddhh24miss'),-6)
from user_operation t
where substr(to_char(t.oper_time,'yyyymmddhh24miss'),-6) between '083000' And '180000'
and to_char(t.oper_time,'yyyymmdd') like '201110%'
--and to_char(t.oper_time,'yyyymmdd') ='20111030'
Order By t.f_time
8、截取用户邮箱前缀
select substr(t.email,0,instr(t.email,'@')-1)as befor_email from user_test t
2. select * from v$sqltext a
where a.ADDRESS= &1_sqladdress
order by a.PIECE
select elapsed_seconds,opname,target,message,start_time,last_update_time ,v$sqltext.HASH_VALUE,v$sqltext.piece,sql_text
from v$session_longops,v$sqltext
where v$session_longops.SQL_HASH_VALUE=v$sqltext.HASH_VALUE
order by elapsed_seconds desc,v$session_longops.SQL_HASH_VALUE,v$sqltext.piece
to_char(100*(NVL(T.SUCESS_ORDER_USER, 0) / nvl(T.ACT_SEND_USER,0)),'fm99999990.00') || '%'