Oracle数据库的日常使用命令

Oracle 数据库的日常使用命令1. 启动和关闭数据库 22. 控制监听 23. 数据库用户管理 24. Oracle 的权限管理35. 更改字符集为中文 36. 查询语句 47. 表空间管理 58. 数据文件被误删后的处理 69. 查询当前系统的配置参数 610. 显示当前用户 611. Oracle 排错处理612. 查看表结构 713. 查看数据库文件 714. 将 select 查询出的结果保存至一个文件 815. 存储过程 816. 数据库的备份与恢复 9Export 转入程序 9Import 恢复程序 12增量卸出/装入 131. 启动和关闭数据库sqlplus /nolog;SQL conn / as sysdba;SQL startup（若启动文件名不是ORACLE缺省的文件名，则启动时应带启动目录与文件名）SQLstartup pfile=停止：SQLshutdown immediate2. 控制监听1启动监听lsnrctl start2停止监听lsnrctl stop3查看监听状态lsnrctl status3. 数据库用户管理1创建用户如：create user imuse203identified by imuse203default tablespace IMUSE01temporary tablespace IMUSE01_TMP2修改用户将 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，例如把创建任何表视图的权限授予imuseOl用户：GRANT create any view TO imuse01；系统权限的回收命令为REVOKE，例如将create any view权限从imuseOl用户手中收回： REVOKE create any view FROM imuse0l；2实体权限每种类型的实体有与之相关的实体权限。授予实体权限的命令举例（将basetab表上的Select和Insert权限授给imuse01）：GRANT select， insert ON basetab TO imuseOl；回收实体权限的命令举例（将basetab表上的Select权限从imuse01手中回收）： REVOKE select ON basetab FROM imuseO1；3管理角色角色是许多权限和角色的组合。它极大地方便了 ORACLE的权限管理。创建角色，如创建一个名为dept1的角色，口令为hello：CREATE ROLE ROLEiMUSEO1 IDENTIFIED BY hello； 使用角色，可以通过修改用户的缺省角色来使用角色，或通过授权的方法来将角色授 予其它角色或用户。如将imuse01用户的缺省角色修改为RoleTmp：ALTER USER imuseO1 DEFAULT ROLE RoleTmp；将角色 RoleTmp 角色授予 imuseO1：GRANT RoleTmpTO imuseO1；使角色生效或失效，DBA可以通过控制角色的生效或失效，来暂时回收用户的一部分 权限。如使RoleTmp角色失效：SET ROLE RoleTmp DISABLE；删除角色，这将会影响到拥有该角色的用户和其它角色的权限。用DROP ROLE命令 删除角色，如：DROP ROLE RoleTmp；5. 更改字符集为中文sqlplus /nolog;SQLconn / 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;查询imuseOl用户所使用的临时表空间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_COO496O; 查看违反唯一索引的列：select column_name from user_ind_columns where index_name= SYS_COO496O;查看编译无效的存储过程：select object_name from user_objects where status=INVALID and object_type=PROCEDURE;查看当前运行的实例名：select instance_name from v$instance;查看使用过的SQL语句：select * from v$sqltextselect contentid,createtime from po_p_contentinfo twhere t.contentid in(4028c78b0ef3c014010ef3c1b5b50003,4028c78b0eef8fdd010ef0e8b121090f,4028c78b0eef8fdd010ef0deeac608c6,4028c78b0eef8fdd010ef0b0f101078e,4028c78b0ee63714010ee68eafc2027a,4028c78b0ee63714010ee9ca3dd017e0,4028c78b0ee58f16010ee59e7d88005e,4028c78b0ee63714010ee6813f3f01ed,4028c78b0ee63714010ee681b4a00202,4028c78b0ee5a73c010ee5cee35500fd) order by createtime asc7. 表空间管理1创建表空间create tablespace IMUSE01datafile /export/home/oracle/oradata/mdspdata/imuse01_dat1size 100M;2增加表空间的大小如将表空间 IMUSE01 增加 100M：alter tablespace IMUSE01add datafile /export/home/oracle/oradata/mdspdata/imuse01_dat2size 100M;3修改表空间的大小如将表空间 IMUSE01 改为 1000M：alter databasedatafile /export/home/oracle/oradata/mdspdata/imuse01_dat1resize 1000M;4 、删除表空间 drop tablespace imuse01 including contents;DROP TABLESPACE portal36 INCLUDING CONTENTS andDATAFILES8. 数据文件被误删后的处理如果不小心物理上删除了一 Oracle 的数据文件，比如说，某应用表空间所对应数据文 件”adc.dbf”，Oracle读控制文件时，和打开数据库时所面对的参数不一致，Oracle数据库将 启动不了，解决这种问题的方法是把该文件对应的表空间先卸下，再删除，以保证控制文件 描述和物理上存在文件一致。以 sys 用户登录并进入 Sql*Plus：SQL startup mountSQL alter database datafile /directory/abc.dbf offline drop;SQL alter database open;SQL drop tablespace abc;9. 查询当前系统的配置参数有三种查询方法：1. 静态查询：即直接查询initXXXX.ora文件（XXXX为O RACLE的SID）。因为有很多系统参数使用的是缺省值，并未在该文件中给出，所以该方法不能看到所有参数及其含义。2. 在 SQL*PLUS 中用命令查询1） 显示所有数据库参数值SQLshow parameters;2）显示含有“sort”的参数的值SQLshow parameter sort;3. 在 SQL*PLUS 中用 SQL 语句查询SQL select name,type,value from v$parameter where name=db_block_buffers；10. 显示当前用户sqlshow user;11. Oracle 排错处理1错误说明ORACLE中出现的错误的格式为：错误类型-错误代码：错误信息，例如：“ORA-1652: unable to extend temp segment by 128 in tablespace TEMP”一般来说，这种错误信息比较简单，但是可以根据这个信息用oerr命令得到更详细的信息。2查看错误详细说明oerr是ORACLE提供的一个在服务器端使用的错误信息帮助命令。使用该命令前，必须先 用O RACLE用户登录到服务器上，命令格式为：oerr 错误类型 错误代码 返回信息格式为： 错误代码， “通用错误信息”/*错误原因 /*应采取的动作如对上面的错误可用如下命令：oerr ora 16523. alert_XXXX.ora（XXXX为0RALE的SID）文件的说明alert_XXXX.ora是ORACLE中一个十分有用的的文件，该文件在服务器的具体位置由 initXXXX.ora中的参数“background_dump_dest的值决定。该文件中的信息有：数据库每次 STARTUP、SHUTDOWN的具体信息；在数据库中进行的各种DML操作；数据库中出现的 各种错误的信息等等，内容十分详细，并且有各种信息发生的具体时间。如果遇到问题，可 以仔细浏览该文件，根据问题发生的时间来寻找相应的信息。12. 查看表结构SQLdesc 表名13. 查看数据库文件共有三种数据库文件：控制文件、数据文件、日志文件1. 查看控制文件select * from v$controlfile;2查看数据文件select status,bytes,name from v$datafile;3查看日志文件select name from v$logfile;14. 将 select 查询出的结果保存至一个文件SQLspool /result.txtSQLselect * from basetab;SQLspool off则从 basetab 查询出的结果都被保存到当前路径下的 result.txt 文件中15. 存储过程1. 存储过程的写法：create or replace procedure proc_name(ifield1 in number, sfield2 out varchar)asv_err_code int;v_err_msg varchar2(2048);beginselect field2 into sfield2 from tabSp where field1 = ifield1; DBMS_OUTPUT.PUT_LINE(sfield2);exceptionwhen others thenbeginv_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. 存储过程的执行sqlexecute 存储过程名字（参数）1如果执行存储过程时提示：必须说明标识符存储过程名，则表明该存储过程不存在或 编译未成功。可用如下命令重新编译该存储过程：SQLalter procedure 存储过程名 compile;2如果执行存储过程时提示：未找到数据在im use01.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 imuseOl/ imuseOl BUFFER=8192（或 64000）FILE=imuse01.dmp 或（磁带设备/dev/rmt0）TABLES=imuse01.basetab（或 imuse01.basetab， imuse01.serviceinfo ）ROWS=YCOMPRESS=NLOG= EXP_IMUSE01 _SERVICEINFO.LOG参数说明：BUFFER缓冲区大小FILE由Export创建的输出文件的名字TABLES将要卸出的表名列表ROWS指明是否卸出表中数据的行数，缺省为“Y” rows=n时只 导出表结构COMPRESS指明在装入期间是否将表中数据压缩到一个区域中。如果 在卸出数据时，指定参数COMPRESS=Y，那么装入时， 就会将数据压缩到一个初始区域中。这种选择可以保持初 始化区域的原始大小。缺省为“Y”LOG指定一个接收有用信息和错误信息的文件主题:如何把数据库的创始脚本导出1、先用exp mdspcnc/mdspcnc file=a.dmp rows=n 把一个空的表结构导出来，rows=n表示不 导出实际数据2、再用st rings a.dmp a.txt 把a.dmp中的内容导到一个文本中就行了10.168.48.158 上 exp lyq/lyq buffer=8192 file=lyq.dmp full=Y rows=Y compress=N log=exp_lyq_db.log然后将lyq.dmp通过ftp传到,执行下面命令，用portal用户导入数据库失败imp portal/portal full=Y file=lyq.dmp log=imp_lyq_db.log rows=Y commit=Y buffer=8192 IGNORE=YIMP-00003: ORACLE error 1918 encountered ORA-01918: user PORTALSDV1 does not existIMP-00017: following statement failed with ORACLE error 1918: ALTER USER PORTALSDV2 DEFAULT ROLE ALLIMP-00003: ORACLE error 1918 encountered ORA-01918: user PORTALSDV2 does not exist . importing OLAPSYSs objects into OLAPSYS . importing WKSYSs objects into WKSYS . importing WK_TESTs objects into WK_TEST . importing SYSMANs objects into SYSMAN . importing FLOWS_030000s objects into FLOWS_030000 . importing SYSDBs objects into SYSDBALTER SESSION SET CURRENT_SCHEMA= SYSDB IMP-00003: ORACLE error 1435 encounteredORA-01435: user does not existIMP-00000: Import terminated unsuccessfully2. 用户模式EXP imuseOl/ imuseOl OWNER= imuseOl BUFFER=8192 （或 64000） FILE= imuseOl.dmp 或（磁带设备/dev/rmtO）ROWS=YCOMPRESS=NLOG= EXP_IMUSE0l .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=NLOG= EXP_IMUSE01_DB.LOG对于数据库备份，建议采用增量备份，即只备份上一次备份以来更改的数据 增量备份命令：EXP ICDMAIN/ICD BUFFER=8192 （或 64000）FILE=EXP_ICDMAIN_DB.DMP （或磁带设备/dev/rmt0）FULL=Y INCTYPE= incremental ROWS=Y COMPRESS=NLOG=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 imuseOl/imuseOl FILE=文件名 LOG=LOG 文件名ROWS=Y COMMIT=Y BUFFER=Y IGNORE=YTABLES=（表名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= imuse01FILE=文件名 LOG=LOG 文件名 ROWS=Y COMMIT=YBUFFER=Y IGNORE=Y参数说明同上。exp_lyq_db.log3. 数据库模式如果备份方式为数据库模式，采用下列恢复方法：IMP system/manager FULL=YFILE=文件名 LOG=LOG 文件名 ROWS=Y COMMIT=YBUFFER=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.dmpDBA 可以排定一个备份日程表，用数据卸出的三个不同方式合理高效地完成数据库的备份 任务。比如DBA作如下安排：星期一：完全卸出(F1)星期二：增量卸出(I1)星期三：增量卸出( 星期四：累积卸出(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=I4A注意：在I1和I2中的信息已包括在C1中了。17. sqlplus /nologoraclePortalONEA:/product/9.2/network/admin sqlplus /nolog SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 11 月 20 20:08:13 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL conn sys/huawei as sysdbaConnected.在Oracle上使用18可以査到收集统计时间内，对数据库进行了哪些操作（执行了哪些SQL语句），各条语句所花的时间 原因： 两个收集点之间生成一个性能报告。收集统计点：SQL connect perfstat/perfstatSQL execute statspack.snap;多次收集统计点之后 可以用SQL connect perfstat/perfstatSQL ?/rdbms/admin/spreport这时会问你要在哪两个点之间生成报告，选择两个统计点，