oracle日常维护常用的55条语句

[b]1.如何查看数据库的状态[/b] unix下 ps -ef | grep ora windows下 看服务是否起来 是否可以连上数据库 SQL> select status, instance_role from v$instance; 通过此语句可以核查数据库是否出于open状态 2.如何查有多少个数据库实例 　 SQL>SELECT * FROM V$INSTANCE; 3.怎样查得数据库的SID select name from v$database; 也可以直接查看 init.ora文件 4.查看表空间占用情况 select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes, (b.bytes-sum(nvl(a.bytes,0))) used, sum(nvl(a.bytes,0)) free, sum(nvl(a.bytes,0))/(b.bytes)*100 Percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id; 5如何.获取表空间信息 select * from dba_tablespaces(或者v$tablespace); 6.如何改变表空间的大小 通过手动改变数据文件大小来改变表空间大小： 　Alter database datafile ‘/opt/oracle/data/datafilename.dbf’ resize 500m; 通过在表空间中增加数据文件来改变表空间大小： 　Alter tablespace tablespace_name add datafile ‘opt/oracle/data/newdatafile.dbf’ size 300m; 7.如何增加临时表空间大小 如果原来的用户缺省临时表空间大小不够，此时首先用如下语句创建一足够大的临时表空间： 　CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M; 然后用如下语句改变用户的缺省临时表空间： 　alter user username temporary tablespace new_temporary_tablespace_name; 8. 如何查看数据文件的信息 　　　数据文件信息： 　　　Select * from dba_data_files(v$datafile); 临时数据文件信息： 　　　Select * from dba_temp_files(v$tempfile) 9.如何将表移动之表空间 ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME; 10.如何查看回滚段名称及大小 　　select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ; 11.如何察看回滚段竞争情况 Select * from v$undostat; 返回结果中nospaceerrcnt字段的值应该为０，如果持续出现非０，建议增加回滚段表空间大小． 12.如何查看控制文件. select name from v$controlfile; 13.如何查看日志文件 select member from v$logfile; 14.如何查看日志文件的切换时间 　SQL>select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') change_time from v$log_history; 15.查看数据库版本 select * from v$version 16.查看会话情况 select machine,terminal from v$session; 17.如何查看系统最大会话数 Select * from v$parameter where name like ‘proc%’ Show parameter processes Select * from v$license 18. 如何查看系统被锁的事务时间 select * from v$locked_object 19.查看数据库的创建日期和归档方式 select created,log_mode,log_mode from v$database 20.如何以archivelog的方式运行oracle init.ora log_archive_start = true RESTART DATABASE 21.如何获取有哪些用户在使用数据库 select username from v$session; 22.如何显示当前连接用户 SHOW USER 23.如何获取用户相关信息 Select * from dba_users(dba_ts_quotas); 从结果中可以查看用户的缺省临时表空间等信息． 24.如何查看每个用户的权限 SELECT * FROM DBA_SYS_PRIVS; 25. 如何知道使用CPU多的用户session 　 11是cpu used by this session select a.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc; 26.unix 下怎么调整数据库的时间？ su -root date -u 08010000 27.如何查看当前数据库里锁的情况 以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句： select object_id,session_id,locked_mode from v$locked_object; select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁： alter system kill session 'sid,serial#'; 如果出现了锁的问题, 某个DML操作可能等待很久没有反应。 28.如何解锁 ALTER SYSTEM KILL SESSION ‘SID,SERIR#’; 29.如何获取错误信息 SELECT * FROM USER_ERRORS; 30.如何获取连接状况 Select * from DBA_DB_LINKS; 31、如何查看sga情况 Select name, bytes from SYS.V_$SGASTAT ORDER BY NAME ASC 32.如何检查job状态 Select * from dba_jobs(user_jobs); Broken列应该为N；如果Broken列为Y，检查oracle告警日志,分析job失败的原因。解决后运行： exec dbms_job.run(job); 33.如何增加oracle连接数 ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下： sessions=(1.1*process+5) 但是我们增加process数时，往往数据库不能启动了。这因为我们还漏调了一个unix系统参数：它是/etc/system/ 中semmns,这是unix系统的信号量参数。每个process会占用一个信号量。semmns调整后，需要重新启动unix操作系统，参数才能生效。不过它的大小会受制于硬件的内存或ORACLE SGA。范围可从200——2000不等。 semmns的计算公式为：SEMMNS>processes+instance_processes+system processes=数据库参数processes的值 instance_processes=5(smon,pmon,dbwr,lgwr,arch) system=系统所占用信号量。系统所占用信号量可用下列命令查出：#ipcs -sb 其中列NSEMS显示系统已占用信号量。 其它一些跟连接有关的参数，如 licence_max_sessions, licence_sessions_warning 等默认设置都为 零，也就是没有限制。我们可以放心大胆地使用数据库了。 34.如何创建SPFILE SQL> connect / as sysdba SQL> select * from v$version; SQL> create pfile from spfile; SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora'; 35.内核参数的作用 shmmax 　　含义：这个设置并不决定究竟Oracle数据库或者操作系统使用多少物理内存，只决定了最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。 　　设置方法：0.5*物理内存 　　例子：Set shmsys:shminfo_shmmax=10485760 　　shmmin 　　含义：共享内存的最小大小。 　　设置方法：一般都设置成为1。 　　例子：Set shmsys:shminfo_shmmin=1： 　　shmmni 　　含义：系统中共享内存段的最大个数。 　　例子：Set shmsys:shminfo_shmmni=100 　　shmseg 　　含义：每个用户进程可以使用的最多的共享内存段的数目。 　　例子：Set shmsys:shminfo_shmseg=20： 　　semmni 　　含义：系统中semaphore identifierer的最大个数。 　　设置方法：把这个变量的值设置为这个系统上的所有Oracle的实例的init.ora中的最大的那个processes的那个值加10。 　　例子：Set semsys:seminfo_semmni=100 　　semmns 　　含义：系统中emaphores的最大个数。 　　设置方法：这个值可以通过以下方式计算得到：各个Oracle实例的initSID.ora里边的processes的值的总和（除去最大的Processes参数）＋最大的那个Processes×2＋10×Oracle实例的个数。 　　例子：Set semsys:seminfo_semmns=200 　　semmsl: 　　含义：一个set中semaphore的最大个数。 　　设置方法：设置成为10＋所有Oracle实例的InitSID.ora中最大的Processes的值。 　　例子：Set semsys:seminfo_semmsl=-200 36.如何单独备份一个或多个表 exp 用户/密码 tables=(表1,…,表2) 37.如何单独备份一个或多个用户 exp system/manager owner=(用户1,用户2,…,用户n) file=导出文件 38.Oracle常用系统文件有哪些 通过以下视图显示这些文件信息：v$database,v$datafile,v$logfile v$controlfile v$parameter 39.如何快速清空一个大表 SQL>truncate table table_name 40.如何查看系统有多少个表 　select * from all_tables; 41.查看用户下所有的表 SQL>select * from user_tables; 42.如何查看sql语句执行所用的时间 SQL>set timing on ; SQL>select * from tablename; 43.怎么把select出来的结果导到一个文本文件中 SQL>SPOOL C:\ABCD.TXT; SQL>select * from table; SQL >spool off; 44.怎样估算SQL执行的I/O数 SQL>SET AUTOTRACE ON ; SQL>SELECT * FROM TABLE; 或者 SQL>SELECT * FROM v$filestat ; 可以查看IO数 45.如何使用伪表dual dual是oracle数据库中的一个伪表，任何用户均可读取。 select user from dual;//察看当前连接用户 select sysdate from dual;//察看数据库时间 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;//察看数据库时间 46.如何建立一个与现存数据库相同，但不包含数据的空库 exp system/manager full=Y rows=N file=full.dmp imp system/manager full=Y rows=N file=full.dmp 47.如何搜索出前N条记录 SELECT * FROM empLOYEE WHERE ROWNUM SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and　 table_name='TABLE_NAME'; 49.事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数MAXEXTENTS的值（ORA-01628）的解决办法。 向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。 50.如何监控 SGA 中共享缓存区的命中率，应该小于1% 　 select sum(pins) "Total Pins", sum(reloads) "Total Reloads", 　 sum(reloads)/sum(pins) *100 libcache 　 from v$librarycache; 51.如何监控当前数据库谁在运行什幺SQL语句 　 SELECT osuser, username, sql_text from v$session a, v$sqltext b 　 where a.sql_address =b.address order by address, piece; 52.修改sqlnet.ora,实现客户端IP限制 如果要在网络上做一些IP地址的限制，一般情况下我们首先想到的是用网络层的防火墙软件。要找网管来设置。 但是如果网管不在，或者仅仅想在数据库层来实现IP地址的限制，DBA们只要修改Server端的一个网络配置文件 sqlnet.ora文件就可以了。 Oracle9i以上版本，在目录$ORACLE_HOME/network/admin 或者 %ORACLE_HOME%\network\admin 下)增加如下内容： tcp.validnode_checking=yes tcp.invited_nodes =(ip1,ip2,……) #允许访问的ip tcp.excluded_nodes=(ip1,ip2,……) #不允许访问的ip 修改sqlnet.ora后，重新启动listener服务，改动就可以生效了。 如果我们从未允许的IP客户端连接过来，会出现以下错误： ERROR: ORA-12537: TNS: 连接已关闭 53.如何察看还没提交的事物 select * from v$locked_object; 　 select * from v$transaction; 54.如何察看错误码的详细信息 在oracle里面我们可以使用oerr来打印关于错误码的描述。 比如错误码为：ORA-00074: no process has been specified 此时应该输入：oerr ora 00074 打印结果为： 00074, 00000, "no process has been specified" // *Cause: No debug process has been specified. // *Action: Specify a valid process. 55、如何捕捉运行很久的SQL column username format a12 column opname format a16 column progress format a8 select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text from v$session_longops , v$sql where time_remaining 0 and sql_address = address and sql_hash_value = hash_value;