大话Oracle——9 常用操作（1）

qmf

大话Oracle——9 常用操作
00001班的同学将自己使用Oracle的经验总结了一些tips，留给了学弟学妹们。特摘录了一部份如下。
人在世界上安身立命的知识来源于自己的经验或者别人的经验，尤其是失败的经验。我想反对这个观点的人并不多。当你翻箱倒柜在自己往昔的邮件里面去寻找某些能解决你当前问题的信息时，你可能更加赞成这句话。要是那封邮件不小心被删除了，那你永远要记住：好记性不如难笔头。
所以要随时整理自己头脑中的知识：管理的、技术的、感情的……这是提升自己能力的方法，你是不是已经感觉自己进步缓慢？那是因为你只知道别人的，而没有形成自己的知识体系。
1.1.1  个性化你的sqlplus登录环境

sqlplus默认登录后的信息显示并不是很人性化。Oracle提供了一系列的设置参数，同时也提供了启动时设置的机制：
创建一个login.sql文件，将设置的参数置于其中。并在unix系统中增加环境变量SQLPATH设置login.sql的全路径。这样sqlplus登录时会去读取指定的login.sql中设置，下面是一个login.sql样列：
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr(global_name, 1,
    decode(dot, 0, length(global_name), dot - 1)) global_name
from (select global_name, instr(global_name, '.') dot from global_name);
set sqlprompt '&gname>'
set termout on
1.1.2  导入导出数据

Oracle提供了sqlldr和外部表工具进行文本记录数据导入。同时也提供了专有格式的数据导入导出的工具exp/imp。但遗憾是，到目前为此，Oracle本身没有提供以文本模式导出数据的命令行工具（提供了图像界面的），其实严格来说，这句话也是错的，sqlplus也是支持以文本方式导出数据的，只是使用麻烦，而且性能不是让人很放心。
因此，诞生了很多第三方这样命令行工具。如ENIP平台就提供了myload, myunload, unload1。使用语法如下：
myunload username/password filename delimate sqlstatement
举例：
myunload / student.unl ‘|’ ‘select * from student’
myload username/password@connect_identifier filename delimate sqlcontent
举例：
myload / student.unl ‘|’ ‘insert into student’
1.1.3  导出建表语句

导出建表语句的方式有很多，下面只介绍其中两种比较简洁的方式：
(1)exp和imp组合
先用exp只导出该张表的结构定义
exp username/password rows=n file=export.dmp tables=XXXXXXXXX
然后用imp生成表定义文件
imp username/password rows=n file=export.dmp indexfile=schema.sql
最后使用sed命令将schemal.sql文件中的REM去除就可以了：
如八戒就曾经使用上面的方法导出过student的建表语句，试图自己再创建一个系统：
exp / rows=n file=student.dmp tables=student
imp / rows=n file=student.dmp indexfile=student.tmp
sed 's/REM  //g' student.tmp > student.sql
(1) 使用DMBS_METADATA.GET_DLL
oracle里面定义DMBS_METADATA.GET_DLL这个存储过程来获取表结构的sql语句的，使用方法如下：
spool portal_schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
spool off;
1.1.4  sqlplus里面怎么执行shell命令

可以在sqlplus提示符下执行本地操作系统的命令，其实这也不是什么秘密。ftp工具早就支持同样的方式了：只要在命令前面加上!就可以了，真神奇啊！
ops$dsbscp@DSBSCP>!ls -alF
×ÜÓÃÁ¿ 148
drwxr-xr-x  2 dsbscp sms   4096 2008-08-04 06:13 ./
drwxr-x---  5 dsbscp sms   4096 2008-08-01 07:28 ../
-rw-------  1 dsbscp sms 229376 2008-08-04 06:13 core
-rw-r--r--  1 dsbscp sms    886 2008-08-03 12:20 create.sql
-rw-r--r--  1 dsbscp sms    446 2008-08-03 12:20 getage.sql
-rw-r--r--  1 dsbscp sms   1451 2008-08-03 12:20 getgrade.sql
-rw-r--r--  1 dsbscp sms   1295 2008-08-03 12:20 girlgrade.sql
-rw-r--r--  1 dsbscp sms   1658 2008-08-03 12:20 insert.sql
-rw-r--r--  1 dsbscp sms    494 2008-08-03 12:20 login.sql
-rw-r-----  1 dsbscp sms   2313 2008-07-24 11:49 swqTools.sql
-rw-r--r--  1 dsbscp sms   2239 2008-08-03 12:20 update.sql
-rw-r-----  1 dsbscp sms    436 2008-07-24 14:20 usingPackage.sql

ops$dsbscp@DSBSCP>
1.1.5  sqlplus里面怎么获取最近一次操作的错误信息

使用show errors就可以排捕获最近一次sql操作的错误信息。这个在定位procedure, function, package编译错误时，有不错的效果。
ops$dsbscp@DSBSCP>@swqTools.sql

Warning: Package created with compilation errors.

ops$dsbscp@DSBSCP>show errors
Errors for PACKAGE SWQTOOLS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/1     PLS-00103: Encountered the symbol "CREATE"
ops$dsbscp@DSBSCP>
1.1.6  怎么查询指定的错误编号的详细信息

使用oerr命令就可以查询Oracle具体编号的错误详细信息，使用格式：
oerr <error type> <error id>
演示效果如下：
ops$dsbscp@DSBSCP>call notheproc();
call notheproc()
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name


ops$dsbscp@DSBSCP>!oerr ora 06576;
06576, 00000, "not a valid function or procedure name"
// *Cause:  Could not find a function (if an INTO clause was present) or
//          a procedure (if the statement did not have an INTO clause) to
//          call.
// *Action: Change the statement to invoke a function or procedure

ops$dsbscp@DSBSCP>
这个对定位错误还是很有帮助的，00001班同学就是凭借这个工具解决了很多棘手的问题。当然有时候还要结合google的。
1.1.7  查询某个用户是从哪台机器登陆Oracle的

使用select * from v$session语句即可。
使用select username from v$session可查询哪些用户正在使用Oracle。
1.1.8  如何确定RAC组网情况下Oracle的主机和备机情况

Oracle的数据字典视图v$database字段DATABASE_ROLE可以显示当前数据库是Primary还是Standby：
DATABASE_ROLE  VARCHAR2(16)     Current role of the database:
•     SNAPSHOT STANDBY
•     LOGICAL STANDBY
•     PHYSICAL STANDBY
•     PRIMARY
select database_role from sys.v_$database
1.1.9  数据表中的字段最大数是多少

表或视图中的最大列数为1000。
1.1.10  如何在字符串里加回车

select 'Welcome to visit'||chr(10)||'www.csdn.net' from dual
1.1.11  怎样解除PROCEDURE被意外锁定

alter system kill session ,把那个session给杀掉，不过你要先查出它的session id
如果你没有权限操作的话，那就只有把该过程重新改个名字了。
1.1.12  如何修改一张表的主键

修改方法如下，当然你首先必须有修改的权限。
alter table aaa
drop constraint aaa_key ;
alter table aaa
add constraint aaa_key primary key(a1,b1) ;
1.1.13  如何测试SQL语句执行所用的时间

fengerapple

Thank you very much for sharing!The good man!The good life of peace!