Oracle数据库常用命令自总结

1、Oracle数据库常用命令自总结1.数据库恢复imp ecr3/ecr3loan fromuser=ecr3 touser=ecr3 ignore=y file=ECR3.dmpimp test/testtest file=D:常州常州征信程序Today.dmp fromuser=bank touser=test feedback=100 commit=y解析结构:imp 用户名/ 密码服务名file=指定的数据库名称fromuser=导出数据库时的用户名touser=将要导入的用户名feedback表示每个点表示100条记录。2.数据库备份exp ecr3/ecr3loan file=D:西

2、安升级西安数据库备份ecr3.dmp log=D:西安升级西安数据库备份ecr3.log owner=ecr3解析结构:exp 用户名/密码服务名file=要备份的文件的路径及文件名log=日志的路径及名称owner=导出数据库的用户。3.创建用户sqlplus system/managercreate user ecr3 identified by ecr3出现2 时输入/ 就可以了这是在linux下4.为用户授权grant dba to ecr3出现2 时输入/ 就可以了这是在linux下5.提交Commit6.删除用户drop user ecr3 cascade7.列出所有用户selec

3、t username from dba_users;8.修改口令alter user spgroup identified by spgtest;9.表空间select * from dba_data_files;select * from dba_tablespaces;10.空闲表空间select tablespace_name,sum(bytes, sum(blocksfrom dba_free_space group by tablespace_name;11.表空间对应的数据文件select * from dba_data_files where tablespace_name=RB

4、S;select * from dba_segments where tablespace_name=INDEXS;12.数据库对象select * from dba_objects;CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。13.表select * from dba_tables;analyze my_table compute statistics;-dba_tables后6列se

5、lect extent_id,bytes from dba_extentswhere segment_name=CUSTOMERS and segment_type=TABLEorder by extent_id;/表使用的extent的信息。segment_type=ROLLBACK查看回滚段的空间分配信息列信息:select distinct table_namefrom user_tab_columnswhere column_name=SO_TYPE_ID;14.索引select * from dba_indexes;/索引,包括主键索引select * from dba_ind_co

6、lumns;/索引列select i.index_name,i.uniqueness,c.column_namefrom user_indexes i,user_ind_columns cwhere i.index_name=c.index_name and i.table_name =ACC_NBR;/联接使用15.序列select * from dba_sequences;16.视图select * from dba_views;select * from all_views;text 可用于查询视图生成的脚本17.聚簇:select * from dba_clusters;18.快照:s

7、elect * from dba_snapshots;快照、分区应存在相应的表空间。19.同义词select * from dba_synonymswhere table_owner=SPGROUP;/if owner is PUBLIC,then the synonyms is a public synonym.if owner is one of users,then the synonyms is a private synonym.20.数据库链:select * from dba_db_links;在spbase下建数据库链create database link dbl_spnew

8、connect to spnew identified by spnew using jhhx;insert into acc_nbrdbl_spnewselect * from acc_nbr where nxx_nbr=237 and line_nbr=8888;21.触发器:select * from dba_trigers;存储过程,函数从dba_objects查找。其文本:select text from user_source where name=BOOK_SP_EXAMPLE;建立出错:select * from user_errors;oracle总是将存储过程,函数等软件放

9、在SYSTEM表空间。22.约束:(1约束是和表关联的,可在create table或alter table table_name add/drop/modify来建立、修改、删除约束。可以临时禁止约束,如:alter table book_exampledisable constraint book_example_1;alter table book_exampleenable constraint book_example_1;(2主键和外键被称为表约束,而not null和unique之类的约束被称为列约束。通常将主键和外键作为单独的命名约束放在字段列表下面,而列约束可放在列定义的同一

10、行,这样更具有可读性。(3列约束可从表定义看出,即describe;表约束即主键和外键,可从dba_constraints和dba_cons_columns 查。select * from user_constraintswhere table_name=BOOK_EXAMPLE;select owner,CONSTRAINT_NAME,TABLE_NAMEfrom user_constraints where constraint_type=R order by table_name;(4定义约束可以无名(系统自动生成约束名和自己定义约束名(特别是主键、外键如:create table bo

11、ok_example(identifier number not null;create table book_example(identifier number constranit book_example_1 not null;23.回滚段:在所有的修改结果存入磁盘前,回滚段中保持恢复该事务所需的全部信息,必须以数据库发生的事务来相应确定其大小(DML语句才可回滚,create,drop,truncate等DDL不能回滚。回滚段数量=并发事务/4,但不能超过50;使每个回滚段大小足够处理一个完整的事务;create rollback segment r05tablespace rbs;c

12、reate rollback segment rbs_cvttablespace rbsstorage(initial 1M next 500k;使回滚段在线alter rollback segment r04 online;用dba_extents,v$rollback_segs监测回滚段的大小和动态增长。回滚段的区间信息select * from dba_extentswhere segment_type=ROLLBACK and segment_name=RB1;回滚段的段信息,其中bytes显示目前回滚段的字节数select * from dba_segmentswhere segme

13、nt_type=ROLLBACK and segment_name=RB1;为事物指定回归段set transaction use rollback segment rbs_cvt针对bytes可以使用回滚段回缩。alter rollback segment rbs_cvt shrink;select bytes,extents,max_extents from dba_segmentswhere segment_type=ROLLBACK and segment_name=RBS_CVT;回滚段的当前状态信息:select * from dba_rollback_segswhere segm

14、ent_name=RB1;比多回滚段状态status,回滚段所属实例instance_num查优化值optimalselect ,s.optsizefrom v$rollname n,v$rollstat swhere n.usn=s.usn;回滚段中的数据set transaction use rollback segment rb1;/*回滚段名*/select ,s.writesfrom v$rollname n,v$rollstat swhere n.usn=s.usn;当事务处理完毕,再次查询$rollstat,比较writes(回滚段条目字节数差值,可确定事

15、务的大小。查询回滚段中的事务column rr heading RB Segment format a18column us heading Username format a15column os heading Os User format a10column te heading Terminal format a10select rr,nvl(s.username,no transaction us,s.osuser os,s.terminal tefrom v$lock l,v$session s,v$rollname rwhere l.sid=s.sid(+and t

16、runc(l.id1/65536=R.USNand l.type=TXand l.lmode=6order by ;24.作业查询作业信息select job,broken,next_date,interval,what from user_jobs;select job,broken,next_date,interval,what from dba_jobs;查询正在运行的作业select * from dba_jobs_running;使用包exec dbms_job.submit(:v_num,a;,sysdate,sysdate + (10/(24*60*60加入作业。间隔

17、10秒钟exec dbms_job.submit(:v_num,a;,sysdate,sysdate + (11/(24*60加入作业。间隔11分钟使用包exec dbms_job.remove(21删除21号作业。统计表空间大小select initial_extent from dba_tables where table_name=TABLE_NAME25.ORACLE的基本语法集锦- 表create table test (names varchar2(12,dates date,num int,dou double;- 视图create or replace view vi_test

18、 asselect * from test;- 同义词create or replace synonym aafor dbusrcard001.aa;- 存储过程create or replace produce dd(v_id in employee.empoy_id%typeasbeginenddd;- 函数create or replace function ee(v_id in employee%rowtype return varchar(15 isvar_test varchar2(15;beginreturn var_test;exception when others then

19、end- 三种触发器的定义create or replace trigger ffalter deleteon testfor each rowdeclarebegindelete from test;if sql%rowcount 0 or sql%rowcount is null thenrais_replaction_err(-20004,错误end ifendcreate or replace trigger ggalter inserton testfor each rowdeclarebeginif :s = :s then raise_replac

20、tion_err(-2003,编码重复; end if end create or replace trigger hh for update on test for each row declare begin if updating then if :s :s then reaise_replaction_err(-2002,关键字不能修改 end if end if end - 定义游标 declare cursor aa is select names,num from test; begin for bb in aa loop if s

21、= ORACLE then end if end loop; end - 速度优化，前一语句不后一语句的速度快几十倍 select names,dates from test,b where s = s(+ and s is null and b.dates date(2003-01-01,yyyy-mm-dd select names,dates from test where names not in ( select names from b where dates to_date(2003-01-01,yyyy-mm-dd - 查找重复记录 s

22、elect names,num from test where rowid != (select max(rowid from test b where s = s and b.num = test.num - 查找表 TEST 中时间最新的前 10 条记录 select * from (select * from test order by dates desc where rownum 11 - 序列号的产生 create sequence row_id minvalue 1 maxvalue 9999999999999999999999 start with

23、 1 increment by 1 insert into test values(row_id.nextval,. 26Windows 下如何停止服务和重启服务 进入 dos 下 输入 NET STOP OracleServiceName 进入 dos 下输入 NET START OracleServiceName 27Oracle 锁表如何处理 select /*+ RULE */ ls.osuser os_user_name, ls.username user_name, decode(ls.type, RW, Row wait enqueue lock, TM, DML enqueue

24、 lock, TX, Transaction enqueue lock, UL, User supplied lock lock_type, o.object_name object, decode(ls.lmode, 1, null, 2, Row Share, 3, Row Exclusive, 4, Share, 5, Share Row Exclusive, 6, Exclusive, null lock_mode, o.owner, ls.sid, ls.serial# serial_num, s.username, l.id2 ls.id1, l.type, ls.id2 from sys.dba_objects o, ( select s.osuser, l.lmode, v$lock l SYS s.sid, s.serial#, l.id1, from v$session s, o.owner where s.sid = l.sid ls where o.object_i