数据库维护管理-数据库相关

1、培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究2培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究备份与恢复备份与恢复数据库检查数据库检查维护常用的维护常用的SQL3培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、OracleOracle数据库运行日志所在的位置。数据库运行日志所在的位置。$ORACLE_BASE/admin/$ORACLE_SID/bdump$ORACLE_BASE/admin/$ORACLE_SID/Udump$ORACLE

2、_BASE/admin/$ORACLE_SID/Cdump2 2、主要关注告警文件、主要关注告警文件$ORACLE_HOMEadmin$ORACLE_SIDbdumpalertSID.log如：/home/oracle/admin/ora921/bdump/alertora921.log3、关注告警文件中的、关注告警文件中的ORA-XXXXX错误错误检查数据库告警日志检查数据库告警日志4培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究从告警日志中可以得到信息从告警日志中可以得到信息1 1、数据库的异常情况、数据库的异常情况Thu Jun 2 16

3、:28:30 2005Errors in file /database/oracle/admin/ora92/bdump/ora921_dbw0_26367.trc:ORA-01157: cannot identify/lock data file 5 - see DBWR trace fileORA-01110: data file 5: /database/oracle/oradata/rdata10ORA-27037: unable to obtain file statusHP-UX Error: 2: No such file or directoryAdditional infor

4、mation: 32 2、redo logredo log的切换情况的切换情况3、数据库的参数设置、数据库的参数设置processes = 150 timed_statistics = TRUE shared_pool_size = 318767104 large_pool_size = 167772165培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、查看系统视图、查看系统视图select name, value from v$parmaterName value- -processes 150Sessions 170cpu_count

5、 82 2、检查参数文件、检查参数文件可以是文本文件，如intiora921.ora。或者是二进制文件，通过create pfile=/tmp/ora921.ora from spfile;3 3、通过命令行、通过命令行sqlplus / as sysdba“SQLshow parameter cluster;6培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究select * from v$nls_parmaterparameter value- -NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CUR

6、RENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_CHARACTERSET US7ASCIINLS_SORT BINARYNLS_DUAL_CURRENCY $NLS_NCHAR_CHARACTERSET US7ASCIINLS_COMP BINARY7培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1、Windows操作系统操作系统

7、通过regedit程序，查看HKEY_LOCAL_MACHINESOFTWAREORACLE及其子项下的NLS_LANG的值。2、UNIX通过检查环境变量NLS_LANG的值获得。8培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、查看每个表空间剩余空间、查看每个表空间剩余空间select TABLESPACE_NAME, sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name2 2、查看每个表空间总的尺寸、查看每个表空间总的尺寸select

8、 tablespace_name, sum(bytes) / 1048576 from dba_data_files group by tablespace_name3 3、给表空间添加数据文件、给表空间添加数据文件alter tablespace service_core_dat add datafile /dev/vgdata/rlv_dat010 size 4000M, /dev/vgdata/rlv_dat011 size 4000M9培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、查找失效的对象、查找失效的对象select ob

9、ject_name from user_objects where status = INVALID2 2、编译失效的对象、编译失效的对象alter procedure p_my_proc compile3 3、借助第三方工具、借助第三方工具PL/SQL DEVELOPERPL/SQL DEVELOPER10培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、通过命令、通过命令sarsar或者或者vmstatvmstatsar 1 1015:56:15 %usr %sys %wio %idle physc15:56:16 0 1 0 99 2

10、.0115:56:17 31 1 0 68 1.962 2、通过、通过top(HPUX)/topas(AIX)top(HPUX)/topas(AIX)11培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1 1、查找耗用资源高的、查找耗用资源高的SQLSQL语句语句select SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS,DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_DISK,BUFFER_GETS / DECODE(EXECUTI

11、ONS, 0, 1, EXECUTIONS) as AVER_BUFFfrom v$sqlarea order by AVER_BUFF desc2 2、获得、获得SQLSQL语句的执行计划语句的执行计划SQL: explain plan for select * from t_mytable where f1 = :1;select * from plan_table;PLSQL DEVELOPER:在SQL Window中输入SQL语句并按F5键。12培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究备份与恢复备份与恢复数据库检查数据库检查维护

12、常用的维护常用的SQL13培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1、物理备份工具、物理备份工具rman（Recovery Manager）rman功能强大,操作相对复杂一些,对普通的开发人员来说，在日常工作当中一般不需要使用，感兴趣的同事可以通过查看Oracle相关的资料了解。2 2、逻辑备份工具、逻辑备份工具exp & impexp & impexp & imp工具程序使用简单、方便，一般与数据库有关的开发人员都可能要使用到。本课程将主要介绍exp & imp。3 3、数据装载工具、数据装载工具SQL

13、LoaderSQL LoaderSQL Loader主要用于将文本文件装载到数据库中。14培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1、使用、使用exp & imp 需要注意的事项需要注意的事项使用exp和imp需要保证服务器和客户端的字符集一致，通过exp导出时，客户端的版本最好和服务器一致，通过imp恢复时的数据库版本不能比原有的数据库版本低。2 2、备份指定的表、备份指定的表exp icd/icdorauidb tables=tbilllog1 rows=y indexes=n file=tbilllog1.dmp 3 3、按

14、用户备份、按用户备份exp icd/icdorauidb owner=icd feedback=5000 file=icd.dmp4 4、全库备份、全库备份exp icd/icdorauidb feedback=5000 full=y file=full.dmp15培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究5、备份指定的表空间、备份指定的表空间exp icd/icdorauidb tablespaces=service_core_dat file=service_core_dat.dmp 6 6、按条件导出、按条件导出exp icd/icd

15、orauidb tables=tbilllog1 query=”where logdateto_date(2005-06-01,yyyy-mm-dd) file=tbilllog1.dmp7 7、从备份文件中恢复指定的表的数据、从备份文件中恢复指定的表的数据imp icd/icdorauidb tables=tbilllog1 ignore=y feedback=5000 file=icd.dmp8 8、恢复整个备份文件、恢复整个备份文件imp icd/icdorauidb feedback=5000 full=y file=full.dmp16培训中心版权所有，未经许可不得向外传播，违者必究

16、培训中心版权所有，未经许可不得向外传播，违者必究1 1、基本步骤、基本步骤 使用SQL Loader工具迁移数据一般分为三个步骤： 第一、生成文本文件。 第二、编写控制文件。 第三、导入的命令。2 2、编写控制文件、编写控制文件LOAD DATA INFILE D:t_card_bill.txt APPEND INTO TABLE t_Card_bill FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY (uaccountno,billtype, fee,serviceno,fee_date date YYYY-MM-DD HH24:MI:SS, fe

17、e_enddate date YYYY-MM-DD HH24:MI:SS, caller_number, called_number, duration) 3、导入的命令、导入的命令 sqlldr username/passworddatabase control=控制文件所在路径控制文件名 readsize=5000000 bindsize=5000000 rows=1000 17培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究备份与恢复备份与恢复数据库检查数据库检查维护常用的维护常用的SQL18培训中心版权所有，未经许可不得向外传播，违者必究

18、培训中心版权所有，未经许可不得向外传播，违者必究1 1、查看所有的会话、查看所有的会话 select * from v$session2 2、查看会话的等待事件、查看会话的等待事件select * from v$session_wait该语句在数据库会话被阻塞时用来收集会话被阻塞的原因。3 3、查看被锁的对象、查看被锁的对象select * from v$locked_object4 4、查看、查看SGASGA的状态的状态select * from v$SGA5 5、查看数据库使用的数据文件、查看数据库使用的数据文件select name, bytes from v$datafile19培训中

19、心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究6 6、查看表、查看表/ /索引所占的空间索引所占的空间 select sum(bytes) / 1048676 from user_extents where segment_name=T_PUB_COMMONINFO7 7、查看所有的表空间、查看所有的表空间select * from dba_tablespaces8 8、查看当前用户下所有的对象、查看当前用户下所有的对象select * from user_objects9 9、查看当前用户下所有的约束、查看当前用户下所有的约束select * fr

20、om user_constraints20培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究1010、重建索引、重建索引 alter index idx_commoninfo rebuild alter index idx_commoninfo rebuild online1111、添加日志组、添加日志组Alter database add logfile group 5(/dev/vgdata/rlv_redo51, /dev/vgdata/rlv_redo52) size 200M1212、删除日志组、删除日志组Alter database d

21、rop logfile group 51313、手工切换正在使用的日志组、手工切换正在使用的日志组Alter system switch logfile21培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何查询表中的某一行记录查数据库中表的任n行的记录:select * from ( select rownum x ,tab.* from tab) where x in (2,3); 查询出数据库中表大于1000的记录: select * from ( select rownum x ,tab.* from tab) where x100023

22、培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究使用一条sql可以insert到多个表中INSERT ALL INTO sales VALUES(product_id, customer_id,weekly_start_date,P, 501,q_sun,sales_sun) INTO sales VALUES(product_id, customer_id,weekly_start_date+1,P, 501,q_mon,sales_mon) INTO sales VALUES(product_id, customer_id,weekly_st

23、art_date+2,P, 501,q_tue,sales_tue) INTO sales VALUES(product_id, customer_id,weekly_start_date+3,P, 501,q_wed,sales_wed) INTO sales VALUES(product_id, customer_id,weekly_start_date+4,P, 501,q_thu,sales_thu)SELECT * FROM sales_input_table;24培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究层次查询 1 selec

24、t level, 2 empno,ename,mgr 3 from emp 4 start with mgr is null 5 connect by 6* prior empno = mgrSQL / LEVEL EMPNO ENAME MGR- - - - 1 7839 KING 2 7566 JONES 7839 3 7902 FORD 7566 4 7369 SMITH 7902 2 7698 BLAKE 7839 3 7499 ALLEN 7698 3 7521 WARD 7698 3 7654 MARTIN 7698 3 7844 TURNER 7698 3 7900 JAMES

25、7698 2 7782 CLARK 7839 LEVEL EMPNO ENAME MGR- - - - 3 7934 MILLER 7782已选择已选择12行。行。25培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究在group by 中不能使用别名SQL select a1,a2,count(*) from test group by a1,a2; A1 A2 COUNT(*)- - - 1 1 3 1 2 1 1 3 1SQL select a1 a ,a2,count(*) from test group by a ,a2;select a

26、1 a ,a2,count(*) from test group by a ,a2 *ERROR at line 1:ORA-00904: invalid column name26培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何访问远程数据库上的表SQL create database link dsmp 2 connect to imuse01 identified by imuse01 3 using dsmpdb;Database link created.SQL select count(*) from basetabdsmp 2

27、; COUNT(*)- 2000SQLSQL create synonym basetab for basetabdsmp;Synonym created.SQL select count(*) from basetab; COUNT(*)- 2000SQL27培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何插入字 SQL select from dual; SQL select ascii() from dual; SQL select chr(39) from dual; 28培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所

28、有，未经许可不得向外传播，违者必究如何删除表中的重记录SQL select * from test; A1 A2- - 1 1 1 1 1 1 1 2 1 3SQL select * from test a where rowid( select min(rowid) from test b where a.a1=b.a1 and a.a2=b.a2); A1 A2- - 1 1 1 129培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何把表中的记录横放30培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播

29、，违者必究31培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何查询表空间中剩余的空间select TABLESPACE_NAME ,sum(bytes)/1024/1024,sum(blocks) from dba_free_space group by tablespace_name TABLESPACE_NAME SUM(BYTES)/1024/1024 SUM(BLOCKS)- - -DRSYS 15.8671875 2031IMUSE01 1795.0625 229768IMUSE01_INDEX 1999.4375 255928IM

30、USE01_TEMP 199.484375 25534INDX 19.9921875 2559RBS 156.992188 20095SYSTEM 10.25 1312TEMP 19.9921875 2559TOOLS 4.8671875 623USERS 22.4921875 287932培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何知道数据库使用的字符集select value from nls_database_parameters Where PARAMETER= NLS_CHARACTERSETVALUE-ZHS16GBK33培训

31、中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何修改数据库中的字符集以sysdba用户的权限登陆数据库sqlplus “ / 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;SQL SHUTD

32、OWN IMMEDIATE; - or NORMALSQL STARTUP;34培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如果错误删除了某个数据库文件，数据库起不来如何办SQL startup ORACLE instance started. Total System Global Area 96393244 bytes Fixed Size 75804 bytes Variable Size 56918016 bytes Database Buffers 39321600 bytes Redo Buffers 77824 bytes Da

33、tabase mounted. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file ORA- 01110: data file 10: D:TEST.DBF SQL alter database datafile d: test.dbf offline drop; Database altered. SQL alter database open;35培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为何数据库不能显示中文与数据库的字符集有关。 一般在安装数据库时选择

34、的字符集为zhs16gbk。 在客户端的字符集的选择是在注册表中（对于windows的机器），如果在UNIX中 则要设置环境变量。 服务器与客户端的字符集一定要一样。 如果在更换字符集则要重安数据库，但可以用 alter database set character 命令将字符集设为超集 36培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何知道当前用户操作权限SQL select * from session_roles; DBASELECT_CATALOG_ROLEHS_ADMIN_ROLE EXECUTE_CATALOG_ROLEDELE

35、TE_CATALOG_ROLEEXP_FULL_DATABASEIMP_FULL_DATABASE AQ_ADMINISTRATOR_ROLE37培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如果kill掉一个数据库中的用户Select sid,serial# from v$session where sid6 SID SERIAL#- - 7 1779 8 79 9 23 10 19 11 19 12 24 13 23 15 16 16 15Alter system kill session 13,2338培训中心版权所有，未经许可不得向外传

36、播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何输出sqlplus中的结果Sql spool c:a.txtSql select * from tab;Sql spool off39培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何在sqlplus中执行一个操作系统命令使用使用!可以执行操作系统中的命令可以执行操作系统中的命令Sql ! LsSql ! Mv a.dat b.dat40培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何生成自动脚本 充分利用数据字典 例子如下：SQ

37、L set heading offSQL set feedback offSQL set pagesize 0SQL select drop | tabtype | | tname | ; from tabdrop TABLE BONUS;drop TABLE DEPT;drop TABLE DUMMY;drop TABLE EMP;drop TABLE EMP1;drop TABLE EMP2;drop TABLE SALGRADE;41培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何在进入sqlplus时自动执行一个脚本 $ORACLE_

38、HOMEsqlplusadminglogin.sql 42培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究你知道以下概念吗？ 实例 Sid Db_name Servicenames43培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为何数据库连接不上fzj :/export/home/fzjsqlplus system/managerdsmpSQL*Plus: Release 8.1.7.0.0 - Production on 星期四 7月 17 16:27:45 2003(c) Copyright

39、 2000 Oracle Corporation. All rights reserved.ERROR:ORA-12154: TNS:could not resolve service nameEnter user-name:44培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为何数据库连接不上 请关注你客户机器上以下的文件：$ORACLE_HOME/network/admin/sqlnet.ora$ORACLE_HOME/network/admin/tnsnames.ora 请关注你服务器上以下的文件：$ORACLE_HOME/network/

40、admin/listener.ora45培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为什么数据库启动非常慢关心你的服务器上以下目录是否存在 Admin/cdump Admin/bdump Admin/udump46培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为什么listener启动非常慢 请关注以下文件：$ORACLE_HOME/network/admin/listener.ora/etc/hosts47培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播

41、，违者必究如何把表从一个表空间移到另一个表空间 Alter table emp move tablespace others; 在移动之后要重新rebuild索引48培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究如何rebuild索引 Alter index index_name rebuild tablespace inx;49培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究为什么索引没有pctused这个参数 SQL desc user_indexes INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING50培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究B-Tree Index51培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究Coalescing Indexes52培训中心版权所有，未经许可不得向外传播，违者必究培训中心版权所有，未经许可不得向外传播，违者必究还有其它的index你