Oracle DBA常用脚本.docx

Oracle DBA常用脚本袁波 2010-9-15SQL show all -查看所有68个系统变量值 SQL show user -显示当前连接用户 SQL show error -显示错误 SQL set heading off -禁止输出列标题，默认值为ON SQL set feedback off -禁止显示最后一行的计数反馈信息，默认值为对6个或更多的记录，回送ON SQL set timing on -默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能 SQL set sqlprompt SQL -设置默认提示符，默认值就是SQL SQL set linesize 1000 -设置屏幕显示行宽，默认100 SQL set autocommit ON -设置是否自动提交，默认为OFF SQL set pause on -默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页 SQL set arraysize 1 -默认为15 SQL set long 1000 -默认为80 说明： long值默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型，如： SQL desc user_views 列名 可空值否 类型 - - - VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG SQL define a = 20000101 12:01:01 -定义局部变量，如果想用一个类似在各种显示中所包括的回车那样的常量， -可以用define命令来设置 SQL select &a from dual; 原值 1: select &a from dual 新值 1: select 20000101 12:01:01 from dual 2000010112:01:01 - 20000101 12:01:01 问题提出： 1、用户需要对数据库用户下的每一张表都执行一个相同的SQL操作，这时，一遍、一遍的键入SQL语句是很麻烦的 实现方法： SQL set heading off -禁止输出列标题 SQL set feedback off -禁止显示最后一行的计数反馈信息 列出当前用户下所有同义词的定义，可用来测试同义词的真实存在性 select desc |tname from tab where tabtype=SYNONYM; 查询当前用户下所有表的记录数 select select |tname|,count(*) from |tname|; from tab where tabtype=TABLE; 把所有符合条件的表的select权限授予为public select grant select on |table_name| to public; from user_tables where 条件; 删除用户下各种对象 select drop |tabtype| |tname from tab; 删除符合条件用户 select drop user |username| cascade; from all_users where user_id25; 快速编译所有视图 -当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍， -因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。 SQL SPOOL ON.SQL SQL SELECTALTER VIEW |TNAME| COMPILE; FROM TAB; SQL SPOOL OFF 然后执行ON.SQL即可。 SQL ON.SQL 当然，授权和创建同义词也可以快速进行，如： SQL SELECT GRANT SELECT ON |TNAME| TO 用户名; FROM TAB; SQL SELECT CREATE SYNONYM |TNAME| FOR 用户名.|TNAME|; FROM TAB; 命令列表： 假设当前执行命令为：select * from tab; (a)ppend 添加文本到缓冲区当前行尾a order by tname结果：select * from tab order by tname; （注：a后面跟2个空格） (c)hange/old/new 在当前行用新的文本替换旧的文本c/*/tname结果：select tname from tab; (c)hange/text从当前行删除文本c/tab结果：select tname from ; del删除当前行 del n删除第n行 (i)nput 文本 在当前行之后添加一行 (l)ist 显示缓冲区中所有行 (l)ist n 显示缓冲区中第 n 行 (l)ist m n 显示缓冲区中 m 到 n 行 run执行当前缓冲区的命令 /执行当前缓冲区的命令 r执行当前缓冲区的命令 文件名运行调入内存的sql文件，如： SQL edit s 如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件， 在其中输入select * from tab;，存盘退出。 SQL s 系统会自动查询当前用户下的所有表、视图、同义词。 文件名 在.sql文件中调用令一个.sql文件时使用 save 文件名将缓冲区的命令以文件方式存盘，缺省文件扩展名为.sql get 文件名 调入存盘的sql文件 start 文件名 运行调入内存的sql文件 spool 文件名 把这之后的各种操作及执行结果假脱机即存盘到磁盘文件上，默认文件扩展名为.lst spool显示当前的假脱机状态 spool off停止输出 例： SQL spool a SQL spool 正假脱机到 A.LST SQL spool off SQL spool 当前无假脱机 exit 退出SQL*PLUS desc 表名显示表的结构 show user显示当前连接用户 show error 显示错误 show all 显示所有68个系统变量值 edit 打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑 edit 文件名把当前目录中指定的.sql文件调入编辑器进行编辑 clear screen 清空当前屏幕显示 二Oracle sqlplus语句编辑命令 首先我们输入这样一条指令： SELECT emp_id, emp_name FROM Employees input 命令可以接着上一条指令的后面添加语句，比如在上述语句运行后输入： input WHERE emp_age 30 便可得到如下指令： SELECT emp_id, emp_name FROM Employees WHERE emp_age 30 ln 命令用于指定对以输入的第n行语句进行操作，比如在上述语句运行后输入： l1则当前选中的语句行即为 SELECT emp_id, emp_name （语句前有*表示） a 命令用于直接在当前行的末尾加上字符，比如在上述语句运行后输入： a , emp_dept 则执行的指令变为： SELECT emp_id, emp_name, emp_dept FROM Employees WHERE emp_age 30 c 命令用于修改当前语句中的字符，比如在上述语句运行后输入： c /emp_name/emp_age/则执行的指令变为： SELECT emp_id, emp_age, emp_dept FROM Employees WHERE emp_age 30 del n 命令用于删除第n行指令，比如在上述语句运行后输入： DEL 3 则执行的指令变为： SELECT emp_id, emp_age, emp_dept FROM EmployeesSELECT upper(f.tablespace_name) 表空间名, d.Tot_grootte_Mb 表空间大小(M), d.Tot_grootte_Mb - f.total_bytes 已使用空间(M), to_char(round(d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),990.99) 使用比, f.total_bytes 空闲空间(M), f.max_bytes 最大块(M) FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC;1. select * from table_name where rownumbegin and rownum end2.sql = select * from tablecon.prepareCall(SELECT * FROM(SELECT A.*, rownum r FROM(+sql+) A WHERE rownum +(intPage-1)*intPageSize);select table_name from all_tables;/所有的表明select table_name from user_all_tables;/用户的所有的表一下是转贴的sql语句的帖子.select * from user_objects; /查询所有的表select * from dba_tables; /查询所有的表select * from all_tables; /查询所有的表select * from user_users /查出一个用户select * from all_users /查询所有用户select * from dba_users /查询所有用户select name,dbid from v$database; /查询数据库名和它的IDselect * from sys.user_tab_columns; /查询表名 ，并显示列名describe 表名 /查询表结构select * from sys.user_tab_columns where table_name=表名 /查询指定表名的字段2: 查询数据库参数show parameter db;3：查询数据库的实例名select instance_name from v$instance;4: 数据库域名数据库安装结束后，如果要知道正在运行额数据库是否有域名以及数据库域名名称可以用select value from v$parameter where name=db_domainshow parameter domain5:数据库服务名如果数据库有域名，则数据库服务名就是全局数据库名，如果该数据库没有定义域名，则数据库服务名与数据库名相同show parameter service_name6:显示当前用户show user7:直接登陆sqlplus /as sysdba8:当前ORACLE系统时间select sysdate from dual;9:查询数据库字典v$nls_parameter产看字符集相关参数select * from v$nls_parameters;/*oracle基本操作语句（适合初学者）oracle操作语句：1.创建表create table 表名(列名1 类型，列名2 类型);2.修改类属性alter table 表名 modify(列名 类型);3.添加列alter table 表名 add(列名 类型);4.添加主键约束和非空约束alter table 表名 add constraint pk_表名 primary key(列名);alter table 表名 modify(列名 not null);5.删除主键约束alter table 表名 drop primary key;alter table 表名 drop constraint pk_表名;6.失效约束alter table 表名 disable primary key;alter table 表名 disable constraint pk_表名;7.有效约束alter table 表名 enable primary key;alter table 表名 enable constraint pk_表名;8.删除列alter table 表名 drop column 列名;9.设置某列不可用，然后删除alter table 表名 set unused(列名);alter table 表名 drop unused columns;10.修改表名rename 表名1 to 表名2alter 表名1 rename to 表名2;11.截断表truncate table 表名;12.截断表保留行空间truncate table 表名 resue storage;13.查看表结构desc table 表名;14.删除表drop table 表名;15.插入记录例：insert into 表名 values(内容1,内容2,内容3,内容4);16.带参数对话方式插入行例:insert into 表名 values(&列名1,&列名2);insert into 表名 values(内容1,内容2);17.插入某几列记录insert into 表名(列名1,列名2) values(内容1,内容2);18.为列插入空值（其列不能为not null）insert into 表名 values(内容1,null,null);19.创建表（包括主键及外键设置）方法一create table 表名(列名1 类型constraint pk_表名 primary key,列名2 类型 not null,列名3 类型constraint fk_表名 reference 表名(列名),列名3 类型constraint ck_表名 check(列名3 in(内容1,内容2,内容3);20.查询所有行select * from 表名;21.查询某几列select 列名1,列名2 from 表名;22.重复行消除select distict 列名 from 表名;23.where语句查询select * from 表名 where 条件 order by 列名;(注：如number类型查出自动按升序排列，如要按降序排列，则select * from 表名 where 条件 order by 列名 desc;)24.创建表,方法二create table 表名(列名1 类型 primary key,列名2 类型 not null,列名3 类型 check(列名3 in(,),列名4 类型 refernce 表名(列名);25.修改 列=？的数据update 表名 set (列=？) where 列=？;26.删除行delete from 表名 where 条件;27.事务处理-事务处理update 表名set 列名（日期） = 30-5月-98where 条件;savepoint mark1;delete from 表名 where 条件;savepoint mark2;rollback to savepoint mark1;rollback;28.建立用户user1，密码为password授予用户connect,resource的权限connect角色用于登录resource角色用于建表等.connect system/managercreate user user1 identified by password;grant connect,resource to password;29.数据控制语言connect scott/tiger30.把对表1查询和修改的权限授予user1grant select,update on 表1 to user1;31.把对表表1中列1和列2修改的权限授予user1grant update(列1,列2) on 表1 to user1;32.把对表表1查询的权限授予用户user1并且user1用户还可以把这个权限授予别的用户(with grant option)grant select on 表1 to user1 with grant option;33.从用户user1撤销对表1查询和修改的权限revoke select,update on 表1 from user1;alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;SELECT SEQUENCE#,APPLIED,COMPLETION_TIME,status FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc;1. oracle安装完成后的初始口令? internal/oracle sys/change_on_install system/manager scott/tiger sysman/oem_temp 2. oracle9ias web cache的初始默认用户和密码？ administrator/administrator 3. oracle 8.0.5怎么创建数据库? 用orainst。假如有motif界面，可以用orainst /m 4. oracle 8.1.7怎么创建数据库? dbassist 5. oracle 9i 怎么创建数据库? dbca 6. oracle中的裸设备指的是什么? 裸设备就是绕过文件系统直接访问的储存空间 7. oracle如何区分 64-bit/32bit 版本？ $ sqlplus / as sysdba sql*plus: release .0 - production on mon jul 14 17:01:09 2003 (c) copyright 2001 oracle corporation. all rights reserved. connected to: oracle9i enterprise edition release .0 - production with the partitioning option jserver release .0 - production sql select * from v$version; banner - oracle9i enterprise edition release .0 - production pl/sql release .0 - production core .0 production tns for solaris: version .0 - production nlsrtl version .0 - production sql 8. svrmgr什么意思？ svrmgrl，server manager. 9i下没有，已经改为用sqlplus了 sqlplus /nolog 变为归档日志型的 9. 请问如何分辨某个用户是从哪台机器登陆oracle的? select machine , terminal from v$session; 10. 用什么语句查询字段呢？ desc table_name 可以查询表的结构 select field_name,. from . 可以查询字段的值 select * from all_tables where table_name like % select * from all_tab_columns where table_name=? 11. 怎样得到触发器、过程、函数的创建脚本？ desc user_source user_triggers 12. 怎样计算一个表占用的空间的大小？ select owner,table_name, num_rows, blocks*aaa/1024/1024 size m, empty_blocks, last_analyzed from dba_tables where table_name=xxx; here: aaa is the value of db_block_size ; xxx is the table name you want to check 13. 如何查看最大会话数？ select * from v$parameter where name like proc%; sql sql show parameter processes name type value - - - aq_tm_processes integer 1 db_writer_processes integer 1 job_queue_processes integer 4 log_archive_max_processes integer 1 processes integer 200 这里为200个用户。 select * from v$license; 其中sessions_highwater纪录曾经到达的最大会话数 14. 如何查看系统被锁的事务时间？ select * from v$locked_object ; 15. 如何以archivelog的方式运行oracle。 init.ora log_archive_start = true restart database 16. 怎么获取有哪些用户在使用数据库 select username from v$session; 17. 数据表中的字段最大数是多少? 表或视图中的最大列数为 1000 18. 怎样查得数据库的sid ? select name from v$database; 也可以直接查看 init.ora文件 19. 如何在oracle服务器上通过sqlplus查看本机ip地址 ? select sys_context(userenv,ip_address) from dual; 假如是登陆本机数据库，只能返回，呵呵 20. unix 下怎么调整数据库的时间？ su -root date -u 08010000 21. 在oracle table中如何抓取memo类型字段为空的数据记录? select remark from oms_flowrec where trim( from remark) is not null ; 22. 如何用bbb表的数据去更新aaa表的数据(有关联的字段) up2003-10-17 aaa set bns_snm=(select bns_snm from bbb where aaa.dpt_no=bbb.dpt_no) where bbb.dpt_no is not null; 23. p4计算机安装方法 将symcjit.dll改为sysmcjit.old 24. 何查询server是不是ops? select * from v$option; 假如parallel server=true则有ops能 25. 何查询每个用户的权限? select * from dba_sys_privs; 26. 如何将表移动表空间? alter table table_name move tablespace_name; 27. 如何将索引移动表空间? alter index index_name rebuild tablespace tablespace_name; 28. 在linux,unix下如何启动dba studio? oemapp dbastudio 29. 查询锁的状况的对象有? v$lock, v$locked_object, v$session, v$sqlarea, v$process ; 查询锁的表的方法: select s.sid session_id, s.username, decode(lmode, 0, none, 1, null, 2, row-s (ss), 3, row-x (sx), 4, share, 5, s/row-x (ssx), 6, exclusive, to_char(lmode) mode_held, decode(request, 0, none, 1, null, 2, row-s (ss), 3, row-x (sx), 4, share, 5, s/row-x (ssx), 6, exclusive, to_char(request) mode_requested, o.owner|.|o.object_name| (|o.object_type|), s.type lock_type, l.id1 lock_id1, l.id2 lock_id2 from v$lock l, sys.dba_objects o, v$session s where l.sid = s.sid and l.id1 = o.object_id ; 30. 如何解锁? alter system kill session sid,serir#; 31. sqlplus下如何修改编辑器? define _editor= - 必须加上双引号 来定义新的编辑器，也可以把这个写在$oracle_home/sqlplus/admin/glogin.sql里面使它永久有效。 32. oracle产生随机函数是? dbms_random.random 33. linux下查询磁盘竞争状况命令? sar -d 33. linux下查询cpu竞争状况命令? sar -r 34. 查询当前用户对象? select * from user_objects; select * from dba_segments; 35. 如何获取错误信息? select * from user_errors; 36. 如何获取链接状况? select * from dba_db_links; 37. 查看数据库字符状况? select * from nls_database_parameters; select * from v$nls_parameters; 38. 查询表空间信息? select * from dba_data_files; 39. oracle的interal用户要口令? 修改 sqlnet.ora sqlnet.authentication_services=(nts) 40. 出现java.exe的解决办法? 一般是将oracleorahomexihttpserver改成手工启动可以的 x是8或9 41. 如何给表、列加注释？ sqlcomment on table 表 is 表注释; 注释已创建。 sqlcomment on column 表.列 is 列注释; 注释已创建。 sql select * from user_tab_comments where comments is not null; 42. 如何查看各个表空间占用磁盘情况？ sql col tablespace format a20 sql select b.file_id 文件id号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes-sum(nvl(a.bytes,0) 已使用, sum(nvl(a.bytes,0) 剩余空间, sum(nvl(a.bytes,0)/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id 43. 如把oracle设置为mts或专用模式？ #dispatchers=(protocol=tcp) (service=sidxdb) 加上就是mts，注释就是专用模式，sid是指你的实例名。 44. 如何才能得知系统当前的scn号 ? select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe; 45. 请问如何在oracle中取毫秒? 9i之前不支持,9i开始有timestamp. 9i可以用select systimestamp from dual; 46. 如何在字符串里加回车？ select welcome to visit|chr(10)| from dual ; 47. 中文是如何排序的？ oracle9i之前，中文是按照二进制编码进行排序的。 在oracle9i中新增了按照拼音、部首、笔画排序功能。设置nls_sort值 schinese_radical_m 按照部首（第一顺序）、笔划（第二顺序）排序 schinese_stroke_m 按照笔划（第一顺序）、部首（第二顺序）排序 schinese_pinyin_m 按照拼音排序 48. oracle8i中对象名可以用中文吗？ 可以 49. 如何改变win中sql*plus启动选项？ sql*plus自身的选项设置我们可以在$oracle_home/sqlplus/admin/glogin.sql中设置。 50. 怎样修改oracel数据库的默认日期? alter session set nls_date_format=yyyymmddhh24miss; or 可以在init.ora中加上一行 nls_date_format=yyyymmddhh24miss 51. 如何将小表放入keep池中? alter table xxx storage(buffer_pool keep); 52. 如何检查是否安装了某个patch? check that orainventory 53. 如何使select语句使查询结果自动生成序号? select rownum,col from table; 54. 如何知道数据裤中某个表所在的tablespace? select tablespace_name from user_tables where table_name=test; select * from user_tables中有个字段tablespace_name，（oracle）; select * from dba_segments where ; 55. 怎么可以快速做一个和原表一样的备份表? create table new_table as (select * from old_table); 55. 怎么在sqlplus下修改procedure? select line,trim(text) t from user_source where name =a order by line; 56. 怎样解除procedure被意外锁定? alter system kill session ,把那个session给杀掉，不过你要先查出她的session id or 把该过程重新改个名字就可以了。 57. sql reference是个什么东西？ 是一本sql的使用手册，包括语法、函数等等，oracle官方网站的文档中心有下载. 58. 如何查看数据库的状态? unix下 ps -ef | grep ora windows下 看服务是否起来 是否可以连上数据库 59. 请问如何修改一张表的主键? alter table aaa drop constraint aaa_key ; alter table aaa add constraint aaa_key primary key(a1,b1) ; 60. 改变数据文件的大小? 用 alter database . datafile . ; 手工改变数据文件的大小，对于原来的 数据文件有没有损害。 61. 怎样查看oracle中有哪些程序在运行之中？ 查看v$sessions表 62. 怎么可以看到数据库有多少个tablespace? select * from dba_tablespaces; 63. 如何修改oracle数据库的用户连接数？ 修改initsid.ora，将process加大，重启数据库. 64. 如何查出一条记录的最后更新时间? 可以用logminer 察看 65. 如何在pl/sql中读写文件？ utl_file包答应用户通过pl/sql读写操作系统文件。 66. 怎样把“&”放入一条记录中？ insert into a values (translate (at&t,at,at); 67. exp如何加参数？ exp user/pass file=a.dmp tables(bsempms) query=where emp_no=s09394 68. 关于oracle8i支持简体和繁体的字符集问题？ zhs16gbk可以支 69. data guard是什么软件？ 就是standby的换代产品 70. 如何创建spfile? sql connect / as sysdba sql select * from v$version; sql create pfile from spfile; sql create spfile from pfile=e:ora9iadmineyglepfileinit.ora; 文件已创建。 sql create spfile=e:ora9idatabasespfileeygle.ora from pfile=e:ora9iadmineyglepfileinit.ora; 文件已创建。 71. 内核参数的应用? shmmax 含义：这个设置并不决定究竟oracle数据库或者操作系统使用多少物理内存，只决定了最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。 设置方法：0.5*物理内存 例子：set shmsys:shminfo_shmmax=10485760 shmmin 含义：共享内存的最小大小。 设置方法：一般都设置成为1。 例子：set shmsys:shminfo_shmmin=1： shmmni 含义：系统中共享内存段的最大个数。 例子：set shmsys:shminfo_shmmni=100 shmseg 含义：每个用户进程可以使用的最多的共享内存段的数目。 例子：set shmsys:shminfo_shmseg=20： semmni 含义：系统中semaphore identifierer的最大个数。 设置方法：把这个变量的值设置为这个系统上的所有oracle的实例的init.ora中的最大的那个processes的那个值加10。 例子：set semsys:seminfo_semmni=100 semmns 含义：系统中emaphores的最大个数。 设置方法：这个值可以通过以下方式计算得到：各个oracle实例的initsid.ora里边的processes的值的总和（除去最大的processes参数）最大的那个processes210oracle实例的个数。 例子：set semsys:seminfo_semmns=200 semmsl: 含义：一个set中semaphore的最大个数。 设置方法：设置成为10所有oracle实例的initsid.ora中最大的processes的值。 例子：set semsys:seminfo_semmsl=-200 72. 怎样查看哪些用户拥有sysdba、sysoper权限？ sqlconn sys/change_on_install sqlselect * from v_$pwfile_users; 73. 如何单独备份一个或多个表？ exp 用户/密码 tables=(表1,表2) 74. 如何单独备份一个或多个用户？ exp