oracle常用命令数据库

1、oracle 常用命令数据库一、oracle的启动和关闭1、在单机环境下要想启动或关闭oracle系统必须首先切换到oracle用户，如下su - oraclea、启动oracle系统oraclesvrmgrlsvrmgrconnect internalsvrmgrstartupsvrmgrquitb、关闭oracle系统oraclesvrmgrlsvrmgrconnect internalsvrmgrshutdownsvrmgrquit启动oracle9i数据库命令：$ sqlplus /nologsql*plus: release 9.2.0.1.0 - production on fri

2、 oct 31 13:53:53 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.sql connect / as sysdba connected to an idle instance.sql startupcsql startuporacle instance started.2、在双机环境下要想启动或关闭oracle系统必须首先切换到root用户，如下su roota、启动oracle系统hareg y oracleb、关闭oracle系统hareg n oracleoracle数据库有哪几种启

3、动方式说明：有以下几种启动方式：1、startup nomount非安装启动，这种方式启动下可执行：重建控制文件、重建数据库读取init.ora文件，启动instance，即启动sga和后台进程，这种启动只需要init.ora文件。2、startup mount dbname安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机，重新定位数据文件、重做日志文件。执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。3、startup open dbname先执行“nomount”，然后执行“mount

4、”，再打开包括redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。4、startup，等于以下三个命令startup nomountalter database mountalter database open5、startup restrict约束方式启动这种方式能够启动数据库，但只允许具有一定特权的用户访问非特权用户访问时，会出现以下提示：error：ora-01035: oracle 只允许具有 restricted session 权限的用户使用6、startup force强制启动方式当不能关闭数据库时，可以用startup force来完成数据库的关闭先关闭数

5、据库，再执行正常启动数据库命令7、startup pfile=参数文件名带初始化参数文件的启动方式先读取参数文件，再按参数文件中的设置启动数据库例：startup pfile=e:oracleadminoradbpfileinit.ora8、startup exclusive二、用户如何有效地利用数据字典oracle的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化,体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。我们不能手工修改数据字典里的信息

6、。很多时候，一般的oracle用户不知道如何有效地利用它。dictionary全部数据字典表的名称和解释，它有一个同义词dictdict_column 全部数据字典表里字段名称和解释如果我们想查询跟索引有关的数据字典时，可以用下面这条sql语句:sqlselect * from dictionary where instr(comments,index)0;如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条sql语句:sqlselect column_name,comments from dict_columns where table_name=user_index

7、es;依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看oracle的其它文档资料了。下面按类别列出一些oracle用户常用数据字典的查询使用方法。1、用户查看当前用户的缺省表空间sqlselect username,default_tablespace from user_users;查看当前用户的角色sqlselect * from user_role_privs;查看当前用户的系统权限和表级权限sqlselect * from user_sys_privs;sqlselect * from user_tab_privs;2、表查看用户下所有的表sqlselect * from u

8、ser_tables;查看名称包含log字符的表sqlselect object_name,object_id from user_objectswhere instr(object_name,log)0;查看某表的创建时间sqlselect object_name,created from user_objects where object_name=upper(&table_name);查看某表的大小sqlselect sum(bytes)/(1024*1024) as size(m) from user_segmentswhere segment_name=upper(&table_na

9、me);查看放在oracle的内存区里的表sqlselect table_name,cache from user_tables where instr(cache,y)0;3、索引查看索引个数和类别sqlselect index_name,index_type,table_name from user_indexes order by table_name;查看索引被索引的字段sqlselect * from user_ind_columns where index_name=upper(&index_name);查看索引的大小sqlselect sum(bytes)/(1024*1024)

10、 as size(m) from user_segmentswhere segment_name=upper(&index_name);4、序列号查看序列号，last_number是当前值sqlselect * from user_sequences;5、视图查看视图的名称sqlselect view_name from user_views;查看创建视图的select语句sqlset view_name,text_length from user_views;sqlset long 2000; 说明：可以根据视图的text_length值设定set long 的大小sqlselect tex

11、t from user_views where view_name=upper(&view_name);6、同义词查看同义词的名称sqlselect * from user_synonyms;7、约束条件查看某表的约束条件sqlselect constraint_name, constraint_type,search_condition, r_constraint_namefrom user_constraints where table_name = upper(&table_name);sqlselect c.constraint_name,c.constraint_type,cc.co

12、lumn_namefrom user_constraints c,user_cons_columns ccwhere c.owner = upper(&table_owner) and c.table_name = upper(&table_name)and c.owner = cc.owner and c.constraint_name = cc.constraint_nameorder by cc.position;8、存储函数和过程查看函数和过程的状态sqlselect object_name,status from user_objects where object_type=func

13、tion;sqlselect object_name,status from user_objects where object_type=procedure;查看函数和过程的源代码sqlselect text from all_source where owner=user and name=upper(&plsql_name);三、查看数据库的sql1、查看表空间的名称及大小select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere

14、 t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status,(initial_extent/1024) initialexten

15、t,(next_extent/1024) nextextent,max_extents, v.curext curextentfrom dba_rollback_segs r, v$rollstat vwhere r.segment_id = v.usn(+)order by segment_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select sum(bytes)/(1024*1024) as free_space,tablespace_name

16、from dba_free_spacegroup by tablespace_name;select a.tablespace_name,a.bytes total,b.bytes used, c.bytes free,(b.bytes*100)/a.bytes % used,(c.bytes*100)/a.bytes % freefrom sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free cwhere a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_

17、name;7、查看数据库库对象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;8、查看数据库的版本select version from product_component_versionwhere substr(product,1,6)=oracle;9、查看数据库的创建日期和归档方式select created, log_mode, log_mode from v$database;四、oracle用户连接的管理用系统管理员，查看当

18、前数据库有几个用户连接：sql select username,sid,serial# from v$session;如果要停某个连接用sql alter system kill session sid,serial#;如果这命令不行,找它unix的进程数sql select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;说明：21是某个连接的sid数然后用 kill 命令杀此进程号。五、sql*plus使用a、近入sql*plus$sqlplus 用户名/密码退出sql*pl

19、ussqlexitb、在sqlplus下得到帮助信息列出全部sql命令和sql*plus命令sqlhelp列出某个特定的命令的信息sqlhelp 命令名c、显示表结构命令describesqldesc 表名d、sql*plus中的编辑命令显示sql缓冲区命令sqll修改sql命令首先要将待改正行变为当前行sqln用change命令修改内容sqlc/旧/新重新确认是否已正确sqll使用input命令可以在sql缓冲区中增加一行或多行sqlisql输入内容e、调用外部系统编辑器sqledit 文件名可以使用define命令设置系统变量editor来改变文本编辑器的类型，在login.sql文件中定

20、义如下一行define_editor=vif、运行命令文件sqlstart testsqltest常用sql*plus语句a、表的创建、修改、删除创建表的命令格式如下：create table 表名 （列说明列表）；为基表增加新列命令如下：alter table 表名 add （列说明列表）例：为test表增加一列age，用来存放年龄sqlalter table testadd （age number(3)）；修改基表列定义命令如下：alter table 表名modify （列名 数据类型）例：将test表中的count列宽度加长为10个字符sqlalter atble testmodify

21、 （county char(10)）；b、将一张表删除语句的格式如下：dorp table 表名；例：表删除将同时删除表的数据和表的定义sqldrop table testc、表空间的创建、删除 1、set verify on/off 控制输出行不显示old和new2、set define &定义变量字符3、set echo off/on 禁止或者启用sql脚本中的sql语句和命令4、accept varname vartype format a./$. prompt .；undefine varname5、变量中被定义为&1,&2意味者第一个变量和第二个变量6、ttitle和btitle 代

22、表的页眉和页脚7、break on 和 compute 用于为列添加小计8、内联视图 select colnum from （select colum from tablename where condition）；9、union:两个表不重复的所有行，intersect：两个表共有的行，minus：第一个表有而第二个表没有的行。10、translate（x，from_string，to_string）用作密码文件的形成。11、decode（value，search_value，result，default_value）条件查询，可以实现if的功能或者case的功能。12、case when

23、condition then result1 when condition then result2 when condition then resultn else default_result end 注意case一般存在from前面13、使用connect by和start with可以实现层次化查询，可以通过lpad命令实现目录树的功能，lpad（x，num）它指用x字符在左边填充num个。14、1、rollup可以为每个分组返回小计记录，cube，可以返回每一个列组合的小计记录，同时在末尾加上总计记录，他们都是group by的一种扩展。2、还有一个就是grouping函数，它可以接

24、受一列，但是只能爱使用rollup和cube的查询中使用，当需要返回空值的地方显示某个值时，就很有用，当列为空返回1，非空返回0。3、因此我们可以通过decode和grouping来指定非空行显示什么，空行显示什么，还有一种类似的功能就是通过nvl和nvl2也可以实现decode和grouping相结合的功能。4、还可以通过grouping sets只把小计记录显示出来，可以用来作为统计的信息显示。5、group_id()用来消除group by字句返回的重复记录，group_id()不接受任何参数。15、可以从一个表向另外一个表复制，通过insert into table1（列的种类）sel

25、ect from table2 where condition。16、子表引用父表，子表中不能含有父表中没有的列值，但是父表中可以含有子表中没有的列值。17、在创建表列时，可以指定default 来定义默认值。18、注意merge的活用 merge into table1 using table2 on （条件下） when matched then result1 when not matched then result2； matched就是条件吻合的情况，not matched就是条件不吻合的情况19、使用dbms包的闪回查询，一种是时间戳，一种是scnexecute dbms_flas

26、hback.enable_at_time（sysdate ？/ 1440）；execute dbms_flashback.enable_at_system_change_number（scn号）；20、系统授权可以传递使用with admin option，对象授权可以传递是使用with grant option可以用sys权限创建synonym（同义词）来让public都访问。21、修改列的注意事项：1、修改列的长度，条件是，改列的类型的长度可以修改，而且只有表中还没有任何行或者所有列都为空值时才可以减小列的长度。2、修改数字列的精度，同样也而且只有表中还没有任何行或者所有列都为空值时才可以

27、减小数字列的精度。3、修改列的数据类型，如果表中还没有任何行或列为控制，就可以将列修改为任何一种数据类型（包括更短的数据类型），否则，就只能将列的数据类型修改为一种兼容的数据类型，但条件时没有缩短列的长度。4、使用列的默认值，默认值只适用于新插入表中的行。22、禁用和启动约束，alter table tablename disable/enable constraint constraint_name23、如果需要删除表的所有行就需要使用truncate，而不是delete，应为truncate回重置表的存储空间，以准备接受新行，执行truncate语句不需要在数据库中使用任何undo空间，也

28、不需要执行commit命令使删除操作永久化。24、使用序列 create sequence namestart with start_num increment by increment_nummaxvalue max_numminvalue min_numcycle|nocyclecache cache_num|nocacheorder|noorder cycle用于指定是否循环的，cache则指定要保留在内存中的整数的个数，order则是确保按照请求次序生成整数。其中noorder为默认值,这里注意，一个序列包含了两个伪列，为currval和nextval，在检索序列的当前值之前，必须通过

29、检索序列的下一个值对序列进行初始化，也就是说，必须先nextval，才能currval。可以用序列填充表的主键，修改序列的注意：不能修改序列的初值，序列的最小值不能大于当前值，序列的最大值不能小于当前值25、一般都将表和索引存储到不同的表空间 create index index_name on table_name（column_name，） tablespace name； 如果某列的值几乎都是唯一的，而且用where使用改列查询所返回的行都小于该表总行的10，则改列就非常适合与创建索引。 如果要基于函数的索引，就必须将参数query_rewrite_enabled设置为true；通过al

30、ter system来修改。每个表创建，就会给主键自动增加一个索引。26、注意视图中不存储数据，它只是会访问基表中的行。 create or replace view force/noforce view_name as 查询语句 with (check option| read only) constraint constraint_name； 在视图上插入行就相当于在基表中插入，但是只能对简单视图执行dml操作，复杂视图不支持dml操作。而且在插入行时，还要满足创建视图时大的条件。用create or replace就可以用来彻底替换一个视图，alter view可以用来修改视图的约束。比

31、如alter view name drop constraint 27、块结构 declaredeclaration_statements beginexecutable_statements exceptionexception_handing_statements end；28、dbms_output.put_line()输出字符用的。它必须设定set serveroutput on 才可以看到。29、%type可以用来定义变量的类型比如说 column1_name table_name.column1_name%type,它的意思就是column1_name的类型和表table_name

32、中的column1_name列的类型一致30、pl/sql中使用的条件逻辑 简单循环 if condition1 then loop statement1 statements elsif condition2 then end loop；可以在循环中设定exit when condition 跳出statement2 while循环 else while condition loopstatement3 statements endif； 可以嵌套if语句 end loop； for循环for loop_variable in reverse lower_number.upper_numbe

33、r loopstatementsend loop; 默认的是增加值，使用reverse就可以减少131、游标，1、首先声明一些变量结果保存列值。一般都采用%type形式，用来和表中的列一样的类型 declare v_column table.column%type 2、声明游标，一般都和select组合在一起，如cursor cursor_name is select_statement；3、打开游标，直接open cursor_name 4、就可以从游标中取得记录了，fetch cursor_name into variable.,其中variable是declare中声明的变量，而且一般是

34、放在循环中一行一行的读取，并设定exit when cursor_name%notfound时跳出。5、最后一步一定要注意，用完游标要关闭open cursor_name。注意：一般将游标和for循环合用，因为这样可以增强在游标中访问记录的能力，而且使用for循环不用显式地打开和关闭游标，连声明都一起省略了。32、创建过程 create or replace procedure proceduce_name (parameter_name in | out |in out type,.) is | as begin procedure_body end; 这里in是默认地，它指在程序运行地时候

35、已经具有值，而且在程序体中这个值不会改变，而out定义参数只是在过程体内部赋值。in out是指参数在程序运行时可能已经具有值，但是在过程体中也可以修改的。如果发生错误，可以通过show error来显示出来。33、创建对象类型 create or replace type type_name as object（.），一个对象类型中可以引用已经建立好地对象类型比如说 column type_name 就可以指定column地属性为type_name在创建过程中，可以使用member function 函数 return 值；它和过程类似，唯一地区别式程序通常不返回值。用类型建立表 creat

36、e table table_name （column type_name）或者create table table_name of type_name；对象表还有一个特点就是可以为对象表之间地关系建立模型，而不是使用外键。使用ref例如 column ref type_name scope is table_name；这里地scope is是将对象引用限制在特定表中地对象上。 可以用set describe depth num 来显示信息地深度，通过desc就可以看到类型包含类型地信息了。set describe depth 1 | n | all linenum on | off inden

37、t on | off34、修改.sql脚本的执行路径，dos下可以通过修改sqlpath的方法来实现，就是set sqlpaht路径1；路径2，可以在sqlplus中编辑缺省的脚本目录，通过sqlset editfile 目录就可以了。35、从sqlplus环境切换到操作系统命令提示符下，可以采用host命令或者“！”，host通用，！使用于linux和unix系统中但在windows下不被支持。 。36、安装帮助文件可以通过启动$oracle_homesqlplusadminhelphelpbld.sql和hlpbld.sql来呼用helpus.sql建立，也可以呼用helpdrop.sql

38、来删除。37、调用show all可以查看所有的环境变量，show error用于显示当前在创建函数、存储过程、触发器、包等对象的错误信息。用show parameter parametername来显示初始化参数的值。show rel来显示数据库的版本、show sga显示sga的大小，show user显示当前用户。38. &与&的区别。&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一

39、次遇到时提示一次。39、与的区别是什么。等于start命令，用来运行一个sql脚本文件。命令调用当前目录下的，或指定全路径，或可以通过sqlpath环境变量搜寻到的脚本文件。用在脚本文件中，用来指定用执行的文件与所在的文件在同一目录，而不用指定全路径，也不从sqlpath环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。40、在查询语句中的活用，如：就是如果突然忘了表的结果，可以通过#desc 表名先来显示表的名字sql select deptno, empno, ename2 from emp3 where4 #desc empname null? type- - -empno n

40、ot null number(4)ename varchar2(10)job varchar2(9)mgr number(4)hiredate datesal number(7,2)comm number(7,2)deptno number(2)4 sal 4000;41、制作脚本的另类方法：set echo offset feedback offset newpage noneset linesize 500set verify offset pagesize 0set term offset trims onset heading offset timing offset numwidth

41、 38spool c:具体的文件名 你要运行的sql语句例如（select drop table | table_name |; from user_tables;）可以做一个删除当前用户的所有表spool off。这里注意（1）当前session是否对修改的数据进行自动提交 sqlset autocommit on|off|immediate（2）在用start命令执行一个sql脚本时，是否显示脚本中正在执行的sql语句 sql set echo on|off（3）是否显示当前sql语句查询或修改的行数 sql set feedback 6|n|on|off 默认只有结果大于6行时才显示结果

42、的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数。（4）是否显示列标题 sql set heading on|off 当set heading off 时，在每页的上面不显示列标题，而是以空白行代替（5）设置一行可以容纳的字符数 sql set linesize 80|n 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。（6）设置页与页之间的分隔 sql set newpage 1|n|none 当set newpage 0 时，会在每页的开头有一个小的黑方框。当set newpage n 时，会在页和页之间隔着n个空行。当

43、set newpage none 时，会在页和页之间没有任何间隔。（7）显示时，用text值代替null值 sql set null text（8）设置一页有多少行数 sql set pagesize 24|n 如果设为0，则所有的输出内容为一页并且不显示列标题（9）是否显示用dbms_output.put_line包进行输出的信息。sql set serveroutput on|off 在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。（10）当sql语句的长度大

44、于linesize时，是否在显示时截取sql语句。 sql set wrap on|off 当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。（11）是否在屏幕上显示输出的内容，主要用与spool结合使用。sql set termout on|off 在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度

45、。（12）将spool输出中每行后面多余的空格去掉。sql set trimsout on|off（13）显示每个sql语句花费的执行时间 set timing on|off42、动态生成spool命令所需的文件名在我们上面的例子中，spool命令所需要的文件名都是固定的。有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？column dat1 new_value filename;select to_char(sysdate,yyyymmddhh24mi) dat1 from dual; spool c:&filename.txt se

46、lect * from dept;spool off;43、常用的oracle元数据查询sequence: sql=select sequence_name from user_sequences order by sequence_nametable: sql=select table_name from user_tables order by table_nameview: sql=select view_name from user_views order by view_nameprocedure: sql=select object_name from user_objects w

47、here object_type=valuefunction: sql=select object_name from user_objects where object_type= valuepackage: sql=select object_name from user_objects where object_type= valuetype: sql=select object_name from user_objects where object_type= value44、oracle 表空间相关的系统表dba_tablespaces, dba_data_files, dba_te

48、mp_files, dba_free_space, dba_segments45、show feedback 检查feedback的行数 show numwidth 修改数值的宽度 show autocommit 自动提交工作的能力，即使没有下达commit命令，有些操作（如quit、exit）以及数据定义语言（ddl）的命令也会使提交发生。46、注意delete与truncate，delete可以提交或回滚删除操作，而truncate自动删除表中的所有记录。truncate命令的操作结果使部能被回滚或提交的，截除的记录不能被恢复。也不能靠执行闪回请求来找回被截除的数据。47、一下往一个表中插

49、入多行，要借助select和union两个语句insert into test(id,name)select * from(select 101，swust from dualunionselect 102，studentfrom dual）；48、修改系统时间显示模式：alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;sql alter session set nls_date_format=dd-mon-yyyy;session altered.sql alter session set nls_date_language=si

50、mplified chinese;sql select sysdate from dual;sysdate-17-8月 -2007sql alter session set nls_date_language=american;session altered.sql select sysdate from dual;sysdate-17-aug-200749、把一个表从一个表空间转移到另一个表空间首先,使用下面的命令移动:alter table table_name move tablespace tablespace_name;然后,如果有索引的话必须重建索引:alter index ind

51、ex_name rebuild tablespace tablespace_name;50、在oracle中，把一个表空间的所有索引换到另一个表空间呢？比如：例子1、在linux下操作的。1、建立一个select_index.sql，里面写入以下内容。select alter index |index_name| rebuild tablespace index_istqzdev; from user_indexes; 2、执行sqlplus username/password select_index.sql index.log3、gawk /alter index/ print $0 alter_index.sql4、在执行一下 sqlplus username/password alter_index.sql .5、表空间的所有索引都到另一个专门储存index的表空间里面去了 例子2、将swust用户在system表空间的全部索引移动到student表空间.1