|
1、查询当前用户表 占用的空间大小
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 drop partition PART_20110528;（普通分区名称）
alter table TB_ORDER_USER_DAY drop partition PART_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');
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 vgopqry.masa_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
| 
转播
