首页 > 代码库 > oracle常用

oracle常用

创建、删除用户:

create user lgehr identified by lgehr default tablespace users quota 1000M on users;
grant create session, create table, create view to lgehr;
grant dba  to lgehr;

drop user lgehr cascade;

创建、删除表空间:

CREATE TABLESPACE "LGEHR"
LOGGING
DATAFILE ‘F:\TABLESPACE\LGEHR.dbf‘ SIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

drop tablespace "TB42" including contents and datafiles;

创建多记录测试表:
create table t3 as select rownum as id ,rownum+1 as id2,rpad(‘*‘,1000,‘*‘) as contents from dual connect by level<=100; 

trace报告设置:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告,这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on,但是不显示查询输出

显示执行耗时:
set timing on

索引:

create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on
select count(*) from t; t表含有索引列object_id,但object_id列没有设置not null,查询不走索引 【因为索引不能存储空值】
select count(*) from t where object_id is not null; 语句中用了is not null过滤,查询走索引
select count(*) from t; 设置为非空列,查询走索引,alter table t modify OBJECT_ID not null;

oracle常用