oracle的日常使用命令 二文档格式.docx
《oracle的日常使用命令 二文档格式.docx》由会员分享，可在线阅读，更多相关《oracle的日常使用命令 二文档格式.docx（30页珍藏版）》请在冰豆网上搜索。
29.SQL*Plus系统环境变量有哪些？
如何修改？
20
30.如何在PL/SQL中读写文件？
31.某个数据文件损坏，如何打开数据库？
21
1.基本知识
一个表空间只能属于一个数据库
每个数据库最少有一个控制文件（建议3个，分别放在不同的磁盘上）
每个数据库最少有一个表空间（SYSTEM表空间）
建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率，只应存放数据字典
每个数据库最少有两个联机日志组，每组最少一个联机日志文件
一个数据文件只能属于一个表空间
一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中
建立新的表空间需要建立新的数据文件
数据文件被ORACLE格式化为ORACLE块，Oracle9i以前版本中，ORACLE块的大小是在第一次创建数据库时设定的，
并且以后不能改变，要想改变，只能重建数据库
一个段segment只能属于一个表空间，但可以属于多个数据文件
一个区extent只能属于一个数据文件，即区间（extent）不能跨越数据文件
PCTFREE和PCTUSED总和不能大于等于100
单独一个事务不能跨越多个回滚段
索引表不含ROWID值
一个事务即使不被提交，也会被写入到重做日志中。
一个块的最大长度为16KB（有2K、4K、8K、16K）
每个数据库最大文件数（按块大小）
2K块 20000个文件
4K块 40000个文件
8K块或以上 65536个文件
2.启动和关闭数据库
sqlplus/nolog;
SQL&
gt;
conn/assysdba;
startup
（若启动文件名不是ORACLE缺省的文件名
，则启动时应带启动目录与文件名）
SQL&
startuppfile=&
lt;
file-pathr/init-file&
shutdownimmediate
3.控制监听
1．启动监听
lsnrctlstart
2．停止监听
lsnrctlstop
3．查看监听状态
lsnrctlstatus
4.数据库用户管理
1．创建用户
如：
createuserimuse203
identifiedbyimuse203
defaulttablespaceIMUSE01
temporarytablespaceIMUSE01_TMP
2．修改用户
将imuse203的口令改为hello：
alteruserimuse203identifiedbyhello;
将imuse203的缺省表空间改为IMUSE02：
alteruserimuse203defaulttablespaceIMUSE02;
将imuse203的临时表空间改为IMUSE02_TMP：
alteruserimuse203temporytablespaceIMUSE02_TMP;
3．删除用户
删除用户的命令为：
DROPUSER用户名[CASCADE]
若不使用CASCADE选项，则必须在该用户的所有实体都删除之后，才能删除该用户。
使用CASCADE后，则不论用户实体有多大，都一并删除。
5.Oracle的权限管理
1．系统权限
ORACLE7提供了80多种系统权限，每种系统权限允许用户执行特定的数据库操作。
系统权限的授予命令为GRANT，例如把创建任何表视图的权限授予imuse01用户：
GRANTcreateanyviewTOimuse01；
系统权限的回收命令为REVOKE，例如将createanyview权限从imuse01用户手中收回：
REVOKEcreateanyviewFROMimuse01；
2．实体权限
每种类型的实体有与之相关的实体权限。
授予实体权限的命令举例（将basetab表上的Select和Insert权限授给imuse01）：
GRANTselect，insertONbasetabTOimuse01；
回收实体权限的命令举例（将basetab表上的Select权限从imuse01手中回收）：
REVOKEselectONbasetabFROMimuse01；
3．管理角色
角色是许多权限和角色的组合。
它极大地方便了ORACLE的权限管理。
?
创建角色，如创建一个名为dept1的角色，口令为hello：
CREATEROLEROLEiMUSE01IDENTIFIEDBYhello；
使用角色，可以通过修改用户的缺省角色来使用角色，或通过授权的方法来将角色授予其它角色或用户。
如将imuse01用户的缺省角色修改为RoleTmp：
ALTERUSERimuse01DEFAULTROLERoleTmp；
将角色RoleTmp角色授予imuse01：
GRANTRoleTmpTOimuse01；
使角色生效或失效，DBA可以通过控制角色的生效或失效，来暂时回收用户的一部分权限。
如使RoleTmp角色失效：
SETROLERoleTmpDISABLE；
删除角色，这将会影响到拥有该角色的用户和其它角色的权限。
用DROPROLE命令删除角色，如：
DROPROLERoleTmp；
6.更改字符集为中文
SHUTDOWNIMMEDIATE;
STARTUPMOUNT;
ALTERSYSTEMENABLERESTRICTEDSESSION;
ALTERSYSTEMSETJOB_QUEUE_PROCESSES=0;
ALTERDATABASEOPEN;
ALTERDATABASECHARACTERSETZHS16GBK;
（这一步一般会出错，所以需要
重复执行上面从SHUTDOWNIMMEDIATE开始的所有语句）
STARTUP;
7.查询语句
当前存在哪些表空间
Select*fromv$tablespace;
表空间有多大
Selecttablespace_name,sum（bytes）/1024/1024fromdba_data_filesgroupbytablespace_name;
表空间还剩多少空闲空间
Selecttablespace_name,sum（bytes）/1024/1024fromdba_free_spacegroupbytablespace_name;
查询imuse01用户所使用的缺省表空间
selectdefault_tablespacefromdba_userswhereusername=’imuse01’;
查询imuse01用户所使用的临时表空间
selecttemporary_tablespacefromdba_userswhereusername=’imuse01’;
查询当前用户所拥有的角色
select*fromsession_roles;
查看违反唯一索引的表及列：
如果插入数据时系统提示：
uniqueconstraint（IMUSE01.SYS_C004960）violated.则说明在为IMUSE01用户插入数据时违反了唯一索引SYS_C004960。
查看违反唯一索引的表：
selecttable_namefromuser_indexeswhereindex_name=’SYS_C004960’;
查看违反唯一索引的列：
selectcolumn_namefromuser_ind_columnswhereindex_name=’SYS_C004960’;
查看编译无效的存储过程：
selectobject_namefromuser_objectswherestatus=’INVALID’andobject_type=’PROCEDURE’;
查看当前运行的实例名：
selectinstance_namefromv$instance;
8.表空间管理
1．创建表空间
createtablespaceIMUSE01
datafile&
#39;
/export/home/oracle/oradata/mdspdata/imuse01_dat1&
size100M;
2．增加表空间的大小
如将表空间IMUSE01增加100M：
altertablespaceIMUSE01
adddatafile‘/export/home/oracle/oradata/mdspdata/imuse01_dat2’
3．修改表空间的大小
如将表空间IMUSE01改为1000M：
alterdatabase
datafile‘/export/home/oracle/oradata/mdspdata/imuse01_dat1’
resize1000M;
4.删除表空间
DROPtablespaceBPOS01_temp;
5.创建临时表空间
createTEMPORARYTABLESPACEBPOS01_temp
tempfile&
/opt/oracle/oradata/orcl/bpos01_dat3&
9.数据文件被误删后的处理
如果不小心物理上删除了一Oracle的数据文件，比如说，某应用表空间所对应数据文件”adc.dbf”，Oracle读控制文件时，和打开数据库时所面对的参数不一致，Oracle数据库将启动不了，解决这种问题的方法是把该文件对应的表空间先卸下，再删除，以保证控制文件描述和物理上存在文件一致。
以sys用户登录并进入Sql*Plus：
startupmount
alterdatabasedatafile‘/directory/abc.dbf’offlinedrop;
alterdatabaseopen;
droptablespaceabc;
10.查询当前系统的配置参数
有三种查询方法：
1.静态查询：
即直接查询initXXXX.ora文件（XXXX为ORACLE的SID）。
因为有很多系统参数使用的是
缺省值，并未在该文件中给出，所以该方法不能看到所有参数及其含义。
2.在SQL*PLUS中用命令查询
1）显示所
有数据库参数值
showparameters;
2）显示含有“sort”的参数的值
showparametersort;
3.在SQL*PLUS中用SQL语句查询
selectname,type,valuefromv$parameterwherename=&
db_block_buffers&
；
11.显示当前用户
sql&
showuser;
12.Oracle排错处理
1．错误说明
ORACLE中出现的错误的格式为：
错误类型-错误代码：
错误信息，例如：
“ORA-1652:
unabletoextendtempsegmentby128intablespaceTEMP”
一般来说，这种错误信息比较简单，但是可以根据这个信息用oerr命令得到更详细的信息。
2．查看错误详细说明
oerr是ORACLE提供的一个在服务器端使用的错误信息帮助命令。
使用该命令前，必须先用ORACLE用户登录到服务器上，命令格式为：
oerr错误类型错误代码
返回信息格式为：
错误代码，“通用错误信息”
//*错误原因
//*应采取的动作
如对上面的错误可用如下命令：
oerrora1652
3．alert_XXXX.ora（XXXX为ORALE的SID）文件的说明
alert_XXXX.ora是ORACLE中一个十分有用的的文件，该文件在服务器的具体位置由initXXXX.ora中的参数“background_dump_dest&
quot;
的值决定。
该文件中的信息有：
数据库每次STARTUP、SHUTDOWN的具体信息；
在数据库中进行的各种DML操作；
数据库中出现的各种错误的信息等等，内容十分详细，并且有各种信息发生的具体时间。
如果遇到问题，可以仔细浏览该文件，根据问题发生的时间来寻找相应的信息。
13.查看表结构
desc表名
14.查看数据库文件
共有三种数据库文件：
控制文件、数据文件、日志文件
1.查看控制文件
select*fromv$controlfile;
2．查看数据文件
selectstatus,bytes,namefromv$datafile;
3．查看日志文件
selectnamefromv$logfile;
15.将select查询出的结果保存至一个文件
spool/result.txt
select*frombasetab;
spooloff
则从basetab查询出的结果都被保存到当前路径下的result.txt文件中
16.存储过程
1.存储过程的写法：
createorreplaceprocedureproc_name
（
ifield1innumber,
sfield2outvarchar
）
as
v_err_codeint;
v_err_msgvarchar2（2048）;
begin
selectfield2intosfield2fromtabSpwherefield1=ifield1;
DBMS_OUTPUT.PUT_LINE（sfield2）;
exception
whenothersthen
v_err_code:
=sqlcode;
v_err_msg:
=sqlerrm;
DBMS_OUTPUT.PUT_LINE（v_err_code||&
&
||v_err_msg）;
rollback;
end;
endproc_name;
注意：
1）存储过程的输入输出参数以逗号间隔，局部变量部分以分号间隔；
2）存储过程的输入输出参数部分：
最后一个参数后没有逗号；
3）存储过程的局部变量部分：
最后一个变量后有分号；
4）可把多个存储过程保存到一个文件中，文件名必须用.sql后缀；
5）每个存储过程结束后，要用“/”作为提交；
2.存储过程的创建：
sqlplus用户名/密码@数据库标识@存储过程文件名
（这里的存储过程文件名可以省略.sql后缀，因为文件后缀缺省是.sql）
3.存储过程的执行
execute存储过程名字（参数）
1．如果执行存储过程时提示：
必须说明标识符’存储过程名’，则表明该存储过程不存在或编译未成功。
可用如下命令重新编译该存储过程：
alterprocedure存储过程名compile;
2．如果执行存储过程时提示：
未找到数据在’imuse01.test_adduser’，有可能是在该存储过程中存在类似”selectcol_nameintotmpfromtable_namewhere…..”这样的语句，而查询出的结果为空的缘故。
3．如果执行存储过程时提示：
SQL缓冲区中无可执行的程序，说明此时缓冲区是空的。
如在执行上面找不到相应记录的脚本后会提示该错误。
4．如果执行存储过程时提示：
输入被截为1个字符，表明某个”/”之后少一个回车符。
5．如果执行存储过程时提示：
创建的过程带有编译错误，可能是某个存储过程结束处少一个”/”。
6．如果执行存储过程时提示：
缺少表达式，有可能是某个变量没被赋值。
17.数据库的备份与恢复
ORACLE系统提供的Export/转入（备份）、Import/转出（恢复）应用程序实现备份与恢复功能。
Export是在数据库打开并能使用的情况下备份数据库数据的实用程序。
用Export将数据库中的数据写到以二进制形式表示的操作系统文件中（ORACLE），该文件叫卸出文件。
用Export可实现应用程序失败时的恢复，例如可把某个表或某些表恢复到执行该Export时的状态。
由于卸出文件的特殊格式，所以只能用Import实用程序将其读入数据库中。
Export转入程序
ORACLE数据库有两类备份方法，第一类为物理备份，该方法实现数据库的完整恢复，但数据库必须运行在归档模式下，且需要极大的外部存储设备，例如磁带机；
第二类备份方式为逻辑备份，客户服务中心业务数据库就是采用这种方式，这种方法不需要数据库运行在归档模式下，不但备份简单，而且可以不需要外部存储设备。
逻辑备份又分为三种模式。
表模式（T）：
这种模式可以卸出当前用户数据库模式下的表，甚至是所有的表。
具有特权的用户可根据所指定的数据库模式来（限制表）卸出他们所包含的表。
缺省情况是卸出属于当前正在进行卸出的用户的所有表。
用户模式（U）：
这种模式可以卸出当前用户数据库模式下的所有实体（表、数据和索引）。
全数据库模式（F）：
只有具有EXP_FULL_DATABASE角色的用户才可能以这种模式卸出。
以这种模式进行卸出的用户，除SYS模式下的内容之外，数据库中所有实体都可以卸出。
下面列出给用户赋
予EXP_FULL_DATABASE角色的方法。
要选择表、用户或全数据库方式，可相应指定TABLES＝tablelist、OWNER＝userlist或FULL＝y。
1.表模式
EXPimuse01/imuse01BUFFER=8192（或64000）
FILE=imuse01.dmp或（磁带设备/dev/rmt0）
TABLES=imuse01.basetab
（或imuse01.basetab，imuse01.serviceinfo.....）
ROWS=Y
COMPRESS=N
LOG=EXP_IMUSE01_SERVICEINFO.LOG
参数说明：
BUFFER缓冲区大小
FILE由Export创建的输出文件的名字
TABLES将要卸出的表名列表
ROWS指明是否卸出表中数据的行数，缺省为“Y”。
COMPRESS指明在装入期间是否将表中数据压缩到一个区域中。
如果在卸出数据时，指定参数COMPRESS=Y，那么装入时，就会将数据压缩到一个初始区域中。
这种选择可以保持初始化区域的原始大小。
缺省为“Y”。
LOG指定一个接收有用信息和错误信息的文件
2.用户模式
EXPimuse01/imuse01OWNER=imuse01BUFFER=8192（或64000）
FILE=imuse01.dmp或（磁带设备/dev/rmt0）
COMPRESS=N
LOG=EXP_IMUSE01.LOG
OWNER将要卸出的用户名列表
BUFFER、FILE、ROWS、COMPRESS、LOG同上
3.全数据库模式
FILE=EXP_IMUSE01.dmp（或磁带设备/dev/rmt0）
FULL=YROWS=YCOMPRESS=N
LOG=EXP_IMUSE01_DB.LOG
对于数据库备份，建议采用增量备份，即只备份上一次备份以来更改的数据。
增量备份命令：
EXPICDMAIN/ICDBUFFER=8192（或64000）
FILE=EXP_ICDMAIN_DB.DMP（或磁带设备/dev/rmt0）
FULL=YINCTYPE=incrementalROWS=YCOMPRESS=N
LOG=EXP_ICDMAIN_DB.LOG
FULL指明是否卸出完整的数据库。
如果FULL=Y，将以全数据库模式进行卸出。
INCTYPE增加卸出的类型，有效值有complete（完全）、comulative（固定）和incremental（增量）。
complete输出所有表
comulative将输入第一次完全输出后修改过的表
incremental将输出前一次输出后修改过的表
说明：
关于增量备份必须满足下列条件：
只对数据库备份有效，且第一次需要FULL=Y参数，以后需要INCTYPE=INCREMENTAL参数。
用户必须有EXP_FULL_DATABASE权限。
Import恢复程序
Import和Export是两个相配套的实用程序，Export把数据库中的数据卸出到操作系统文件中，而Import实用程序则把Export卸出的数据恢复到数据库中。
按备份方案确定恢复方案，例如：
采用表逻辑备份方案，则恢复方案也采用恢复到表的方式（不应恢复到用户）。
要使用Import，必须具有CREATESESSION特权，以便能注册到ORACLERDBMS中去。
这一特权属于在数据库创建时所建立的CONNECT角色。
如果卸出文件是由某用户利用EXP_F
升级会员 