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

1.如何查看数据库的状态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 tempTEMPFILE '/u01/oradata/temp01.dbf' SIZE 500MEXTENT 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$version16.查看会话情况select machine,terminal from v$session;17.如何查看系统最大会话数Select * from v$parameter where name like ‘proc%’Show parameter processesSelect * from v$license18. 如何查看系统被锁的事务时间 select * from v$locked_object19.查看数据库的创建日期和归档方式select created,log_mode,log_mode from v$database20.如何以archivelog的方式运行oracleinit.ora log_archive_start = true RESTART DATABASE21.如何获取有哪些用户在使用数据库 select username from v$session;22.如何显示当前连接用户SHOW  USER23.如何获取用户相关信息Select * from dba_users(dba_ts_quotas);从结果中可以查看用户的缺省临时表空间等信息．24.如何查看每个用户的权限SELECT *  FROM DBA_SYS_PRIVS;25. 如何知道使用CPU多的用户session　 11是cpu used by this session selecta.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 0801000027.如何查看当前数据库里锁的情况以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+systemprocesses=数据库参数processes的值         instance_processes=5(smon,pmon,dbwr,lgwr,arch)  system=系统所占用信号量。系统所占用信号量可用下列命令查出：#ipcs -sb   其中列NSEMS显示系统已占用信号量。其它一些跟连接有关的参数，如 licence_max_sessions, licence_sessions_warning 等默认设置都为零，也就是没有限制。我们可以放心大胆地使用数据库了。34.如何创建SPFILESQL> 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=-20036.如何单独备份一个或多个表exp 用户/密码 tables=(表1,…,表2)37.如何单独备份一个或多个用户exp system/manager owner=(用户1,用户2,…,用户n) file=导出文件38.Oracle常用系统文件有哪些通过以下视图显示这些文件信息：v$database,v$datafile,v$logfile v$controlfile v$parameter39.如何快速清空一个大表SQL>truncate table table_name40.如何查看系统有多少个表　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.如何使用伪表dualdual是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.dmpimp system/manager full=Y rows=N file=full.dmp47.如何搜索出前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=yestcp.invited_nodes =(ip1,ip2,……) #允许访问的iptcp.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;