oracle常用操作

1 验证表统计信息

begin     DBMS_STATS.GATHER_TABLE_STATS (        ownname => 'owner_name',        tabname => 'table_name',        estimate_percent => 100    );end;

2 用table2更新table1

--根据id(有唯一约束的字段)将table2的字段更新到table1update (select a.field1 a1, a.field2 a2, b.field1 b1, b.field2 b2 from table1 a, table2 b where a.id = b.id) set a1 = b1, a2 = b2;

3 添加/删除主键

alter table table_name add constraint table_name_pk primary key(id);alter table table_name drop constraint table_name_pk;

4 启用/禁用触发器

alter trigger trigger_name enable;alter trigger trigger_name disable;

5查询rownum在10-20的行

select * from (select t.*,rownum r from test t where rownum <= 20) where r >10;select t.*,rownum r from test t where rownum <= 20 minus select t.*,rownum r from test t where rownum <= 10;

6 用户授权

--授予用户选择和删除的权限grant select,delete on table_name to user_name;