1. 启动和关闭数据库
sqlplus /nolog;
SQL >conn / as sysdba;
SQL >startup
(若启动文件名不是ORACLE缺省的文件名,则启动时应带启动目录与文件名)
exit
SQL>startup pfile=<file-pathr/init-file>
shutdown immediate
2. 控制监听
1.启动监听
lsnrctl start
2.停止监听
lsnrctl stop
3.查看监听状态
lsnrctl status
3. 数据库用户管理
1.创建用户
如:
create user imuse203
identified by imuse203
default tablespace IMUSE01
temporary tablespace IMUSE01_TMP
2.修改用户
将imuse203的口令改为hello:
alter user imuse203 identified by hello;
将imuse203的缺省表空间改为IMUSE02:
alter user imuse203 default tablespace IMUSE02;
将imuse203的临时表空间改为IMUSE02_TMP:
alter user imuse203 tempory tablespace IMUSE02_TMP;
3.删除用户
删除用户的命令为:
DROP USER 用户名 [CASCADE]
若不使用CASCADE选项,则必须在该用户的所有实体都删除之后,才能删除该用户。使用CASCADE后,则不论用户实体有多大,都一并删除。
4. Oracle的权限管理
1.系统权限
ORACLE7提供了80多种系统权限,每种系统权限允许用户执行特定的数据库操作。
系统权限的授予命令为GRANT,例如把创建任何表视图的权限授予imuse01用户:
GRANT create any view TO imuse01;
系统权限的回收命令为REVOKE,例如将create any view 权限从imuse01用户手中收回:
REVOKE create any view FROM imuse01;
2. 实体权限
每种类型的实体有与之相关的实体权限。
授予实体权限的命令举例(将basetab表上的Select和Insert权限授给imuse01):
GRANT select,insert ON basetab TO imuse01;
回收实体权限的命令举例(将basetab表上的Select权限从imuse01手中回收):
REVOKE select ON basetab FROM imuse01;
3. 管理角色
角色是许多权限和角色的组合。它极大地方便了ORACLE的权限管理。
• 创建角色,如创建一个名为dept1的角色,口令为hello:
CREATE ROLE ROLEiMUSE01 IDENTIFIED BY hello;
• 使用角色,可以通过修改用户的缺省角色来使用角色,或通过授权的方法来将角色授予其它角色或用户。如将imuse01用户的缺省角色修改为RoleTmp:
ALTER USER imuse01 DEFAULT ROLE RoleTmp;
将角色RoleTmp角色授予imuse01:
GRANT RoleTmp TO imuse01;
• 使角色生效或失效,DBA可以通过控制角色的生效或失效,来暂时回收用户的一部分权限。如使RoleTmp角色失效:
SET ROLE RoleTmp DISABLE;
• 删除角色,这将会影响到拥有该角色的用户和其它角色的权限。用DROP ROLE命令删除角色,如:
DROP ROLE RoleTmp;
5. 更改字符集为中文
sqlplus /nolog;
SQL>conn / as sysdba;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
(这一步一般会出错,所以需要重复执行上面从SHUTDOWN IMMEDIATE开始的所有语句)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
6. 查询语句
当前存在哪些表空间
Select * from v$tablespace;
表空间有多大
Select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
表空间还剩多少空闲空间
Select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查询imuse01用户所使用的缺省表空间
select default_tablespace from dba_users where username='imuse01';
查询imuse01用户所使用的临时表空间
select temporary_tablespace from dba_users where username='imuse01';
查询当前用户所拥有的角色
select * from session_roles;
查看违反唯一索引的表及列:
如果插入数据时系统提示:unique constraint (IMUSE01.SYS_C004960) violated.则说明在为IMUSE01用户插入数据时违反了唯一索引SYS_C004960。
查看违反唯一索引的表:
select table_name from user_indexes where index_name='SYS_C004960';
查看违反唯一索引的列:
select column_name from user_ind_columns where index_name='SYS_C004960';
查看编译无效的存储过程:
select object_name from user_objects where status='INVALID' and object_type='PROCEDURE';
查看当前运行的实例名:
select instance_name from v$instance;
7. 表空间管理
1.创建表空间
create tablespace IMUSE01
datafile '/export/home/oracle/oradata/mdspdata/imuse01_dat1'
size 100M;
2.增加表空间的大小
如将表空间IMUSE01增加100M:
alter tablespace IMUSE01
add datafile ‘/export/home/oracle/oradata/mdspdata/imuse01_dat2’
size 100M;
3.修改表空间的大小
如将表空间IMUSE01改为1000M:
alter database
datafile ‘/export/home/oracle/oradata/mdspdata/imuse01_dat1’
resize 1000M;
4 、删除表空间drop tablespace imuse01 cascade;
8. 数据文件被误删后的处理
如果不小心物理上删除了一Oracle的数据文件,比如说,某应用表空间所对应数据文件”adc.dbf”,Oracle读控制文件时,和打开数据库时所面对的参数不一致,Oracle数据库将启动不了,解决这种问题的方法是把该文件对应的表空间先卸下,再删除,以保证控制文件描述和物理上存在文件一致。
以sys用户登录并进入Sql*Plus:
SQL >startup mount
SQL >alter database datafile ‘/directory/abc.dbf’ offline;
SQL >alter database open;
SQL >drop tablespace abc;
9. 查询当前系统的配置参数
有三种查询方法:
1. 静态查询:
即直接查询initXXXX.ora文件(XXXX为ORACLE的SID)。 因为有很多系统参数使用的是
缺省值,并未在该文件中给出,所以该方法不能看到所有参数及其含义。
2. 在SQL*PLUS中用命令查询
1) 显示所有数据库参数值
SQL>show parameters;
2) 显示含有“sort”的参数的值
SQL>show parameter sort;
3. 在SQL*PLUS中用SQL语句查询
SQL> select name,type,value from v$parameter where name='db_block_buffers';
10. 显示当前用户
sql>show user;
11. Oracle排错处理
1.错误说明
ORACLE中出现的错误的格式为:错误类型-错误代码:错误信息,例如:
“ORA-1652: unable to extend temp segment by 128 in tablespace TEMP”
一般来说,这种错误信息比较简单,但是可以根据这个信息用oerr命令得到更详细的信息。
2.查看错误详细说明
oerr 是ORACLE提供的一个在服务器端使用的错误信息帮助命令。使用该命令前,必须先用ORACLE用户登录到服务器上,命令格式为:
oerr 错误类型 错误代码
返回信息格式为:
错误代码, “通用错误信息”
//*错误原因
//*应采取的动作
如对上面的错误可用如下命令:
oerr ora 1652
3.alert_XXXX.ora(XXXX为ORALE的SID)文件的说明
alert_XXXX.ora是ORACLE中一个十分有用的的文件,该文件在服务器的具体位置由initXXXX.ora中的参数“background_dump_dest"的值决定。该文件中的信息有:数据库每次STARTUP、SHUTDOWN的具体信息;在数据库中进行的各种DML操作;数据库中出现的各种错误的信息等等,内容十分详细,并且有各种信息发生的具体时间。如果遇到问题,可以仔细浏览该文件,根据问题发生的时间来寻找相应的信息。
12. 查看表结构
SQL>desc 表名
13. 查看数据库文件
共有三种数据库文件:控制文件、数据文件、日志文件
1.查看控制文件
select * from v$controlfile;
2.查看数据文件
select status,bytes,name from v$datafile;
3.查看日志文件
select name from v$logfile;
14. 将select查询出的结果保存至一个文件
SQL>spool /result.txt
SQL>select * from basetab;
SQL>spool off
则从basetab查询出的结果都被保存到当前路径下的result.txt文件中
15. 存储过程
1. 存储过程的写法:
create or replace procedure proc_name
(
ifield1 in number,
sfield2 out varchar
)
as
v_err_code int;
v_err_msg varchar2(2048);
begin
select field2 into sfield2 from tabSp where field1 = ifield1;
DBMS_OUTPUT.PUT_LINE(sfield2);
exception
when others then
begin
v_err_code :=sqlcode;
v_err_msg :=sqlerrm;
DBMS_OUTPUT.PUT_LINE(v_err_code||' '||v_err_msg);
rollback;
end;
end proc_name;
注意:
1) 存储过程的输入输出参数以逗号间隔,局部变量部分以分号间隔;
2) 存储过程的输入输出参数部分:最后一个参数后没有逗号;
3) 存储过程的局部变量部分:最后一个变量后有分号;
4) 可把多个存储过程保存到一个文件中,文件名必须用.sql后缀;
5) 每个存储过程结束后,要用“/”作为提交;
2. 存储过程的创建:
sqlplus 用户名/密码@数据库标识 @存储过程文件名
(这里的存储过程文件名可以省略.sql后缀,因为文件后缀缺省是.sql)
3. 存储过程的执行
sql>execute 存储过程名字(参数)
注意:
1.如果执行存储过程时提示:必须说明标识符’存储过程名’,则表明该存储过程不存在或编译未成功。可用如下命令重新编译该存储过程:
SQL>alter procedure存储过程名 compile;
2.如果执行存储过程时提示:未找到数据在’imuse01.test_adduser’,有可能是在该存储过程中存在类似”select col _name into tmp from table_name where …..”这样的语句,而查询出的结果为空的缘故。
3.如果执行存储过程时提示:SQL缓冲区中无可执行的程序,说明此时缓冲区是空的。如在执行上面找不到相应记录的脚本后会提示该错误。
4.如果执行存储过程时提示:输入被截为1个字符,表明某个”/”之后少一个回车符。
5.如果执行存储过程时提示:创建的过程带有编译错误,可能是某个存储过程结束处少一个”/”。
6.如果执行存储过程时提示:缺少表达式,有可能是某个变量没被赋值。
16. 数据库的备份与恢复
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. 表模式
EXP imuse01/ imuse01 BUFFER=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. 用户模式
EXP imuse01/ imuse01 OWNER= imuse01 BUFFER=8192(或64000)
FILE= imuse01.dmp 或(磁带设备/dev/rmt0)
ROWS=Y
COMPRESS=N
LOG= EXP_IMUSE01 .LOG
参数说明:
OWNER 将要卸出的用户名列表
BUFFER、FILE、ROWS、COMPRESS、LOG 同上
3. 全数据库模式
EXP imuse01/ imuse01 BUFFER=8192(或64000)
FILE=EXP_IMUSE01.dmp (或磁带设备/dev/rmt0)
FULL=Y ROWS=Y COMPRESS=N
LOG= EXP_IMUSE01_DB.LOG
对于数据库备份,建议采用增量备份,即只备份上一次备份以来更改的数据。
增量备份命令:
EXP ICDMAIN/ICD BUFFER=8192(或64000)
FILE=EXP_ICDMAIN_DB.DMP (或磁带设备/dev/rmt0)
FULL=Y INCTYPE= incremental ROWS=Y COMPRESS=N
LOG=EXP_ICDMAIN_DB.LOG
参数说明:
BUFFER、FILE、ROWS、COMPRESS、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,必须具有CREATE SESSION特权,以便能注册到ORACLE RDBMS中去。这一特权属于在数据库创建时所建立的CONNECT角色。
如果卸出文件是由某用户利用EXP_FULL_DATABASE角色创建的全数据库卸出,那么只有具有IMP_FULL_DATABASE角色的用户才能装入这样的文件。
数据库的逻辑恢复分为表、用户、数据库三种模式。
1. 表模式
恢复方法为:
IMP imuse01/imuse01 FILE=文件名 LOG=LOG文件名
ROWS=Y COMMIT=Y BUFFER=Y IGNORE=Y
TABLES=(表名1,表名2,表名3,表名4,.......)
参数说明:
BUFFER 缓冲区大小
FILE 用于装入的卸出文件名字
TABLES 将要装入的表名列表
ROWS 指明是否装入表数据的行数,缺省为“Y”。
IGNORE 指明如何处理实体创建错误。指定IGNORE=Y,当试图创建数据库实体时,忽略实体存在错误。对除了表之外的其他实体,指定IGNORE=Y,Import不报告错误,继续执行。而指定IGNORE=N时,Import在继续执行前报告实体创建错误。
COMMIT 指明在每个矩阵插入之后是否提交。缺省时,Import在装入每个实体之后提交。指定COMMIT=N时,如有错误产生,Import在记录装入下一个实体之前,完成一个回退。指定COMMIT=Y时,可以抑制回滚字段无限制增大,并改善大量装入时的性能,表具有唯一约束时,这种选择比较好。如果再次开始装入,将拒绝装入已经装入的任何行,原因是非致命性错误。表具有非唯一约束时,指定COMMIT=N可能是比较好的选择。因为重新装入可能会产生重复行。
LOG 指定一个接收有用信息和错误信息的文件
2. 用户模式
如果备份方式为用户模式,采用下列恢复方法:
IMP system/manager FROMUSER=imuse01 TOUSER= imuse01
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
参数说明同上。
3. 数据库模式
如果备份方式为数据库模式,采用下列恢复方法:
IMP system/manager FULL=Y
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
字符集转换
对于单字节字符集(例如US7ASCII),恢复时,数据库自动转换为该会话的字符集(NLA_LANG参数);对于多字节字符集(例如ZHS168CGB),恢复时,应尽量使字符集相同(避免转换),如果要转换,目标数据库的字符集应是输出数据库字符集的超集。
增量卸出/装入
下面介绍利用Export/Import实用程序对ORACLE数据库进行备份、恢复的方法:增量卸出/装入。增量卸出是一种常用的数据备份方法,包括3个子类:
(1) “完全”增量卸出
就是对整个ORACLE数据库进行完全卸出。如:
$ exp system/口令 inctype=complete full=y file=today.dmp
(1) “增量型”增量卸出
即从ORACLE数据库中卸出上次卸出操作之后所有数据库的变化信息。如:
$exp system/口令 inctype=incremental file=today.dmp
增量型卸出文件的大小,可能只是完全卸出文件大小的1%,具体要看“新信息或更新过的信息”的总量而定。
(2) “累积型”增量卸出
累积型卸出方式只是卸出自上次“完全” 卸出之后数据库中变化了的信息。用法如下:
$exp system/口令 inctype=cumulative file=today.dmp
DBA可以排定一个备份日程表,用数据卸出的三个不同方式合理高效地完成数据库的备份任务。比如DBA作如下安排:
星期一:完全卸出(F1)
星期二:增量卸出(I1)
星期三:增量卸出(I2)
星期四:累积卸出(C1)
星期五:增量卸出(I3)
星期六:增量卸出(I4)
如果在星期日,数据库遭到意外破坏,DBA可按以下步骤来恢复数据库:
用命令CREATE DATABASE重新生成你的数据库结构;
最近增量装入I4:$imp system/口令 inctype=system full=y file=I4
完全增量装入F1:$imp system/口令 inctype=restore full=y file=F1
累积增量装入C1:$imp system/口令 inctype=restore full=y file=C1
对于由累积装入或完全装入尚未能装入的信息,作增量装入:
$imp system/口令 inctype=restore full=y file=I3
$imp system/口令 inctype=restore full=y file=I4
注意:
在I1和I2中的信息已包括在C1中了。
Oracle日志目录:
app/oracle/diag/rdbms/orcl/ORCL/trace
sqlplus /nolog;
SQL >conn / as sysdba;
SQL >startup
(若启动文件名不是ORACLE缺省的文件名,则启动时应带启动目录与文件名)
exit
SQL>startup pfile=<file-pathr/init-file>
shutdown immediate
2. 控制监听
1.启动监听
lsnrctl start
2.停止监听
lsnrctl stop
3.查看监听状态
lsnrctl status
3. 数据库用户管理
1.创建用户
如:
create user imuse203
identified by imuse203
default tablespace IMUSE01
temporary tablespace IMUSE01_TMP
2.修改用户
将imuse203的口令改为hello:
alter user imuse203 identified by hello;
将imuse203的缺省表空间改为IMUSE02:
alter user imuse203 default tablespace IMUSE02;
将imuse203的临时表空间改为IMUSE02_TMP:
alter user imuse203 tempory tablespace IMUSE02_TMP;
3.删除用户
删除用户的命令为:
DROP USER 用户名 [CASCADE]
若不使用CASCADE选项,则必须在该用户的所有实体都删除之后,才能删除该用户。使用CASCADE后,则不论用户实体有多大,都一并删除。
4. Oracle的权限管理
1.系统权限
ORACLE7提供了80多种系统权限,每种系统权限允许用户执行特定的数据库操作。
系统权限的授予命令为GRANT,例如把创建任何表视图的权限授予imuse01用户:
GRANT create any view TO imuse01;
系统权限的回收命令为REVOKE,例如将create any view 权限从imuse01用户手中收回:
REVOKE create any view FROM imuse01;
2. 实体权限
每种类型的实体有与之相关的实体权限。
授予实体权限的命令举例(将basetab表上的Select和Insert权限授给imuse01):
GRANT select,insert ON basetab TO imuse01;
回收实体权限的命令举例(将basetab表上的Select权限从imuse01手中回收):
REVOKE select ON basetab FROM imuse01;
3. 管理角色
角色是许多权限和角色的组合。它极大地方便了ORACLE的权限管理。
• 创建角色,如创建一个名为dept1的角色,口令为hello:
CREATE ROLE ROLEiMUSE01 IDENTIFIED BY hello;
• 使用角色,可以通过修改用户的缺省角色来使用角色,或通过授权的方法来将角色授予其它角色或用户。如将imuse01用户的缺省角色修改为RoleTmp:
ALTER USER imuse01 DEFAULT ROLE RoleTmp;
将角色RoleTmp角色授予imuse01:
GRANT RoleTmp TO imuse01;
• 使角色生效或失效,DBA可以通过控制角色的生效或失效,来暂时回收用户的一部分权限。如使RoleTmp角色失效:
SET ROLE RoleTmp DISABLE;
• 删除角色,这将会影响到拥有该角色的用户和其它角色的权限。用DROP ROLE命令删除角色,如:
DROP ROLE RoleTmp;
5. 更改字符集为中文
sqlplus /nolog;
SQL>conn / as sysdba;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
(这一步一般会出错,所以需要重复执行上面从SHUTDOWN IMMEDIATE开始的所有语句)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
6. 查询语句
当前存在哪些表空间
Select * from v$tablespace;
表空间有多大
Select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
表空间还剩多少空闲空间
Select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查询imuse01用户所使用的缺省表空间
select default_tablespace from dba_users where username='imuse01';
查询imuse01用户所使用的临时表空间
select temporary_tablespace from dba_users where username='imuse01';
查询当前用户所拥有的角色
select * from session_roles;
查看违反唯一索引的表及列:
如果插入数据时系统提示:unique constraint (IMUSE01.SYS_C004960) violated.则说明在为IMUSE01用户插入数据时违反了唯一索引SYS_C004960。
查看违反唯一索引的表:
select table_name from user_indexes where index_name='SYS_C004960';
查看违反唯一索引的列:
select column_name from user_ind_columns where index_name='SYS_C004960';
查看编译无效的存储过程:
select object_name from user_objects where status='INVALID' and object_type='PROCEDURE';
查看当前运行的实例名:
select instance_name from v$instance;
7. 表空间管理
1.创建表空间
create tablespace IMUSE01
datafile '/export/home/oracle/oradata/mdspdata/imuse01_dat1'
size 100M;
2.增加表空间的大小
如将表空间IMUSE01增加100M:
alter tablespace IMUSE01
add datafile ‘/export/home/oracle/oradata/mdspdata/imuse01_dat2’
size 100M;
3.修改表空间的大小
如将表空间IMUSE01改为1000M:
alter database
datafile ‘/export/home/oracle/oradata/mdspdata/imuse01_dat1’
resize 1000M;
4 、删除表空间drop tablespace imuse01 cascade;
8. 数据文件被误删后的处理
如果不小心物理上删除了一Oracle的数据文件,比如说,某应用表空间所对应数据文件”adc.dbf”,Oracle读控制文件时,和打开数据库时所面对的参数不一致,Oracle数据库将启动不了,解决这种问题的方法是把该文件对应的表空间先卸下,再删除,以保证控制文件描述和物理上存在文件一致。
以sys用户登录并进入Sql*Plus:
SQL >startup mount
SQL >alter database datafile ‘/directory/abc.dbf’ offline;
SQL >alter database open;
SQL >drop tablespace abc;
9. 查询当前系统的配置参数
有三种查询方法:
1. 静态查询:
即直接查询initXXXX.ora文件(XXXX为ORACLE的SID)。 因为有很多系统参数使用的是
缺省值,并未在该文件中给出,所以该方法不能看到所有参数及其含义。
2. 在SQL*PLUS中用命令查询
1) 显示所有数据库参数值
SQL>show parameters;
2) 显示含有“sort”的参数的值
SQL>show parameter sort;
3. 在SQL*PLUS中用SQL语句查询
SQL> select name,type,value from v$parameter where name='db_block_buffers';
10. 显示当前用户
sql>show user;
11. Oracle排错处理
1.错误说明
ORACLE中出现的错误的格式为:错误类型-错误代码:错误信息,例如:
“ORA-1652: unable to extend temp segment by 128 in tablespace TEMP”
一般来说,这种错误信息比较简单,但是可以根据这个信息用oerr命令得到更详细的信息。
2.查看错误详细说明
oerr 是ORACLE提供的一个在服务器端使用的错误信息帮助命令。使用该命令前,必须先用ORACLE用户登录到服务器上,命令格式为:
oerr 错误类型 错误代码
返回信息格式为:
错误代码, “通用错误信息”
//*错误原因
//*应采取的动作
如对上面的错误可用如下命令:
oerr ora 1652
3.alert_XXXX.ora(XXXX为ORALE的SID)文件的说明
alert_XXXX.ora是ORACLE中一个十分有用的的文件,该文件在服务器的具体位置由initXXXX.ora中的参数“background_dump_dest"的值决定。该文件中的信息有:数据库每次STARTUP、SHUTDOWN的具体信息;在数据库中进行的各种DML操作;数据库中出现的各种错误的信息等等,内容十分详细,并且有各种信息发生的具体时间。如果遇到问题,可以仔细浏览该文件,根据问题发生的时间来寻找相应的信息。
12. 查看表结构
SQL>desc 表名
13. 查看数据库文件
共有三种数据库文件:控制文件、数据文件、日志文件
1.查看控制文件
select * from v$controlfile;
2.查看数据文件
select status,bytes,name from v$datafile;
3.查看日志文件
select name from v$logfile;
14. 将select查询出的结果保存至一个文件
SQL>spool /result.txt
SQL>select * from basetab;
SQL>spool off
则从basetab查询出的结果都被保存到当前路径下的result.txt文件中
15. 存储过程
1. 存储过程的写法:
create or replace procedure proc_name
(
ifield1 in number,
sfield2 out varchar
)
as
v_err_code int;
v_err_msg varchar2(2048);
begin
select field2 into sfield2 from tabSp where field1 = ifield1;
DBMS_OUTPUT.PUT_LINE(sfield2);
exception
when others then
begin
v_err_code :=sqlcode;
v_err_msg :=sqlerrm;
DBMS_OUTPUT.PUT_LINE(v_err_code||' '||v_err_msg);
rollback;
end;
end proc_name;
注意:
1) 存储过程的输入输出参数以逗号间隔,局部变量部分以分号间隔;
2) 存储过程的输入输出参数部分:最后一个参数后没有逗号;
3) 存储过程的局部变量部分:最后一个变量后有分号;
4) 可把多个存储过程保存到一个文件中,文件名必须用.sql后缀;
5) 每个存储过程结束后,要用“/”作为提交;
2. 存储过程的创建:
sqlplus 用户名/密码@数据库标识 @存储过程文件名
(这里的存储过程文件名可以省略.sql后缀,因为文件后缀缺省是.sql)
3. 存储过程的执行
sql>execute 存储过程名字(参数)
注意:
1.如果执行存储过程时提示:必须说明标识符’存储过程名’,则表明该存储过程不存在或编译未成功。可用如下命令重新编译该存储过程:
SQL>alter procedure存储过程名 compile;
2.如果执行存储过程时提示:未找到数据在’imuse01.test_adduser’,有可能是在该存储过程中存在类似”select col _name into tmp from table_name where …..”这样的语句,而查询出的结果为空的缘故。
3.如果执行存储过程时提示:SQL缓冲区中无可执行的程序,说明此时缓冲区是空的。如在执行上面找不到相应记录的脚本后会提示该错误。
4.如果执行存储过程时提示:输入被截为1个字符,表明某个”/”之后少一个回车符。
5.如果执行存储过程时提示:创建的过程带有编译错误,可能是某个存储过程结束处少一个”/”。
6.如果执行存储过程时提示:缺少表达式,有可能是某个变量没被赋值。
16. 数据库的备份与恢复
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. 表模式
EXP imuse01/ imuse01 BUFFER=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. 用户模式
EXP imuse01/ imuse01 OWNER= imuse01 BUFFER=8192(或64000)
FILE= imuse01.dmp 或(磁带设备/dev/rmt0)
ROWS=Y
COMPRESS=N
LOG= EXP_IMUSE01 .LOG
参数说明:
OWNER 将要卸出的用户名列表
BUFFER、FILE、ROWS、COMPRESS、LOG 同上
3. 全数据库模式
EXP imuse01/ imuse01 BUFFER=8192(或64000)
FILE=EXP_IMUSE01.dmp (或磁带设备/dev/rmt0)
FULL=Y ROWS=Y COMPRESS=N
LOG= EXP_IMUSE01_DB.LOG
对于数据库备份,建议采用增量备份,即只备份上一次备份以来更改的数据。
增量备份命令:
EXP ICDMAIN/ICD BUFFER=8192(或64000)
FILE=EXP_ICDMAIN_DB.DMP (或磁带设备/dev/rmt0)
FULL=Y INCTYPE= incremental ROWS=Y COMPRESS=N
LOG=EXP_ICDMAIN_DB.LOG
参数说明:
BUFFER、FILE、ROWS、COMPRESS、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,必须具有CREATE SESSION特权,以便能注册到ORACLE RDBMS中去。这一特权属于在数据库创建时所建立的CONNECT角色。
如果卸出文件是由某用户利用EXP_FULL_DATABASE角色创建的全数据库卸出,那么只有具有IMP_FULL_DATABASE角色的用户才能装入这样的文件。
数据库的逻辑恢复分为表、用户、数据库三种模式。
1. 表模式
恢复方法为:
IMP imuse01/imuse01 FILE=文件名 LOG=LOG文件名
ROWS=Y COMMIT=Y BUFFER=Y IGNORE=Y
TABLES=(表名1,表名2,表名3,表名4,.......)
参数说明:
BUFFER 缓冲区大小
FILE 用于装入的卸出文件名字
TABLES 将要装入的表名列表
ROWS 指明是否装入表数据的行数,缺省为“Y”。
IGNORE 指明如何处理实体创建错误。指定IGNORE=Y,当试图创建数据库实体时,忽略实体存在错误。对除了表之外的其他实体,指定IGNORE=Y,Import不报告错误,继续执行。而指定IGNORE=N时,Import在继续执行前报告实体创建错误。
COMMIT 指明在每个矩阵插入之后是否提交。缺省时,Import在装入每个实体之后提交。指定COMMIT=N时,如有错误产生,Import在记录装入下一个实体之前,完成一个回退。指定COMMIT=Y时,可以抑制回滚字段无限制增大,并改善大量装入时的性能,表具有唯一约束时,这种选择比较好。如果再次开始装入,将拒绝装入已经装入的任何行,原因是非致命性错误。表具有非唯一约束时,指定COMMIT=N可能是比较好的选择。因为重新装入可能会产生重复行。
LOG 指定一个接收有用信息和错误信息的文件
2. 用户模式
如果备份方式为用户模式,采用下列恢复方法:
IMP system/manager FROMUSER=imuse01 TOUSER= imuse01
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
参数说明同上。
3. 数据库模式
如果备份方式为数据库模式,采用下列恢复方法:
IMP system/manager FULL=Y
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
字符集转换
对于单字节字符集(例如US7ASCII),恢复时,数据库自动转换为该会话的字符集(NLA_LANG参数);对于多字节字符集(例如ZHS168CGB),恢复时,应尽量使字符集相同(避免转换),如果要转换,目标数据库的字符集应是输出数据库字符集的超集。
增量卸出/装入
下面介绍利用Export/Import实用程序对ORACLE数据库进行备份、恢复的方法:增量卸出/装入。增量卸出是一种常用的数据备份方法,包括3个子类:
(1) “完全”增量卸出
就是对整个ORACLE数据库进行完全卸出。如:
$ exp system/口令 inctype=complete full=y file=today.dmp
(1) “增量型”增量卸出
即从ORACLE数据库中卸出上次卸出操作之后所有数据库的变化信息。如:
$exp system/口令 inctype=incremental file=today.dmp
增量型卸出文件的大小,可能只是完全卸出文件大小的1%,具体要看“新信息或更新过的信息”的总量而定。
(2) “累积型”增量卸出
累积型卸出方式只是卸出自上次“完全” 卸出之后数据库中变化了的信息。用法如下:
$exp system/口令 inctype=cumulative file=today.dmp
DBA可以排定一个备份日程表,用数据卸出的三个不同方式合理高效地完成数据库的备份任务。比如DBA作如下安排:
星期一:完全卸出(F1)
星期二:增量卸出(I1)
星期三:增量卸出(I2)
星期四:累积卸出(C1)
星期五:增量卸出(I3)
星期六:增量卸出(I4)
如果在星期日,数据库遭到意外破坏,DBA可按以下步骤来恢复数据库:
用命令CREATE DATABASE重新生成你的数据库结构;
最近增量装入I4:$imp system/口令 inctype=system full=y file=I4
完全增量装入F1:$imp system/口令 inctype=restore full=y file=F1
累积增量装入C1:$imp system/口令 inctype=restore full=y file=C1
对于由累积装入或完全装入尚未能装入的信息,作增量装入:
$imp system/口令 inctype=restore full=y file=I3
$imp system/口令 inctype=restore full=y file=I4
注意:
在I1和I2中的信息已包括在C1中了。
Oracle日志目录:
app/oracle/diag/rdbms/orcl/ORCL/trace