Oracle常用知识的一些总结

1.创建索引 create [unique][cluster] index <索引名> on <表名>(<列名>)

2.删除索引 drop index <索引名>

3.重建索引 alter index 索引名 rebuild

4.查看索引 select index_name,index_type,table_name from user_indexes

5.查看某个表对应的索引及索引对应的列名

select index_name,column_name from user_ind_dolumns where table_name=’表名’

6.复制表且表不存在的时候 create table 表名 as select语句

7.复制表，表已经存在时 insert into 表名 select语句

8.向表中追加主键 alter table 表名 add constraint pk_表名_01 primary key(列名)

    alter table 表名 add(primary key(列名))

9.查看oracle的执行计划

create public synonym plan_table for plan_table

set autotrace on

只显示执行计划，不显示执行结果

set autotrace traceonly

10.查看sql的执行时间 set timing on

 

11.创建临时表

create global temporary table 表名 on commit preserve(事务)/delete(会话) rows

12.drop,truncate,delete的比较

drop删除整个表的定义以及表中所有数据，释放表空间，不需要commit

truncate删除表中所有数据，保留表定义，释放表空间，不需要commit

delete删除符合条件的数据，保留表定义，不释放表空间，需要commit

从速度上说，drop>truncate>delete,对于大量数据作删除，最好使用truncate,实在用不了的话，使用delete,commit,然后move释放表空间，对大表作删除，最好是先truncate table,然后drop table，原因我也不明白，大家都这么用。

13.设置页面记录数 set pagesize 20

14.设置行宽 set linesize 1000

15.dual表相关

查询用户 select user from dual

查询系统时间 select sysdate from dual

当计算器使用 select 1+2 from dual

16.复制表，不复制数据，建立一个一模一样的表

create table 新表 as select * from 旧表 where 1=2

17.查看用户下的对象 select * from tab

18.数据备份相关

a.数据完全导出 exp 用户名/密码＠服务名 file=盘名:/*.dmp full=y

b.根据用户导出数据 exp 用户名/密码@服务名 file=盘名:/*.dmp owner=(user名)

c.根据表名导出 exp 用户名/密码@服务名 file=盘名:/*.dmp tables=(table名)

d.将表中某些数据导出

exp 用户名/密码@服务名 file=盘名:/*.dmp tables=(table名) query=”””sql语句”””(三个双引号,windows操作系统下)

e.全部数据导入 imp 用户名/密码@服务名 file=盘名:/*.dmp ignore=y(不需要重建表，原表不需要删除，数据导出的同时，表定义也一同导出，如果ignore不等于y的话，会出问题。Q:表导出的时候索引是怎么处理的？好象是一起备份了)

f.根据表名导入数据 imp 用户名/密码@服务名 file=盘名:/*.dmp tables=（table名）

19.数据导入加速方面（未经试验）

a.     建立数据库时增大DB_BLOCK_SIZE

b.     建立并使用大的回滚（rollback）段，同时关闭其他回滚段，大小为表的50%

c.     在import完成之前将DB置于NOARCHIVELOG，减少创建和管理aichive日志时间

d.     与回滚段一样，做几个大的重做日志文件（redo log files），越大越好，关闭晓得重做日志文件

e.     可能的话将回滚段，表数据和重做日志文件分布在不同硬盘，减少I/O冲突可能

f.     将LOG_CHECKPOINT_INTERVAL的值设为重做日志文件的大小，使CHECKPOINT减少到最少

g.     增大SORT_AREA_SIZE，这个与机器有关系

h.     import时commit=n，在么个对象结束后提交，而非缓冲区提交，所以要设置大的回滚段

i.     import时采用大的buffer值，这个要看实际情况来定

j.     import时设置indexes=n，采用INDEXFILE选项，数据导完后建立索引，建立索引时SORT_AREA_SIZE要足够大

20.删除表中的一列 alter table 表名 drop column 列名

21.取得表中的字段名 select column_name from user_tab_columns where table_name=UPPER(表名)

22.表重命名 rename 旧表名 to 新表名

23.数据库关闭 shutdown immediate，如果关不掉，另开一个窗口，shutdown abort，然后再startup restrict 打开一次，让oracle作一次实例恢复，然后shutdown immediate，必要时查看alert.log

24．最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID>(SELECT MIN(X.ROWID) FROM EMPX WHERE X.EMP_NO=E.EMP_NO);

25. 用EXISTS替换DISTINCT

(低效):SELECT  DISTINCT  DEPT_NO,DEPT_NAME  FROM  DEPT D , EMP E WHERE  D.DEPT_NO = E.DEPT_NO

(高效): SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X' FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO);

26.ORACLE进行语法检查的时候，是从句尾开始执行检查的。

27．典型索引创建方法

CREATE INDEX IX_DAWTR080_01 ON DAWTR080

( SEIKYU_NO )

TABLESPACE "WEBTRADE_IDX"

STORAGE(INITIAL 100M

        NEXT 20M

        MAXEXTENTS UNLIMITED)

PCTFREE 5

NOLOGGING;

28.merge into table A

   using table B

   on A.?=B.?

   when matched then

   update

   set

   delete

   when not matched then

   insert

   values

29.设定列未使用，不可恢复

   alter table A set unused column a

30.用户数据字典

   user_tables

   user_objects

   user_catalog