oracle 运营维护_总结几个ORACLE数据库日常运维常用的命令（持续更新）

1、重建DBLINK不知道用户密码，不用知道密码，密文在SYS.LINK$存着呢，以sysdba的权限才能取出来

然后通过SQL创建DBLINK

CREATE DATABASE LINK

"DBLINK_wwwwwwwww"

CONNECT TO "username_zzzzzzzzzzz" IDENTIFIED BY VALUES

'05994DC6A7D4DCD81243536F3A00B85032'

--------密文

USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = yyyyyy)

)

)'

注意：数据库用户迁移后，job要重新定义建立

前面需加declare  job number。。。。，存储过程要重新编译，DBLINK要重新建立

2、oracle密码失效解决办法：

登录成功以后查询DBA用户状态SQL->select username,account_status from dba_users;查看其中常用的用户状态是否是EXPIRED(失效)

还是LOCKED

如果sysman状态是过期，修改密码方法：

语句查询密码的有效期设置，

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND

resource_name='PASSWORD_LIFE_TIME';

LIMIT字段是密码有效天数。在密码将要过期或已经过期时可通过

ALTER USER 用户名IDENTIFIED BY密码;

语句进行修改密码，密码修改后该用户可正常连接数据库。

长久对应可通过

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

语句将口令有效期默认值180天修改成“无限制”。出于数据库安全性考虑，不建议将PASSWORD_LIFE_TIME值设置成UNLIMITED，

再试下SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND

resource_name='PASSWORD_LIFE_TIME';看密码有效期是否为unlimit

3、生成AWR报告

Sqlplus / as sysdba

@?/rdbms/admin/awrrpt.sql

默认html回车、查看的天数选1天、选择开始和结束时间点用Snap Id、选择名字默认回车、生成至oracle家目录

4、查看操作系统使用CPU程对应的正在执行的SQL

SELECT/*+ ORDERED */

sql_text

FROM v$sqltext a

WHERE (a.hash_value, a.address) IN (

SELECT DECODE (sql_hash_value,

0, prev_hash_value,

sql_hash_value

),

DECODE (sql_hash_value, 0, prev_sql_addr,

sql_address)

FROM v$session b

WHERE b.paddr = (SELECT addr

FROM v$process c

WHERE c.spid = '&pid'))

ORDER BY piece ASC

/

写入PID

5、根据SQL ID查找SQL中绑定变量传参值

select name,t.VALUE_STRING from v$sql_bind_capture

t where t.SQL_ID=''

6、查看oracle字符集

select userenv('language') from dual

7、查找一个表的主键被哪些表字段的外键引用，外键约束(被引用的值主表不能删除，附表添加的值必须在主表中存在，附表值可以随意删除)

先查找主键约束名：

select * from user_constraints

a  where

a.table_name = '表名'

然后

select

b.table_name,b.column_name from user_constraints a

inner join user_cons_columns b

on a.constraint_name =

b.constraint_name where

a.r_constraint_name='主键约束名'

8、批量杀死非活跃会话

select

'Alter system kill session '''

|| se.sid ||',' || se.serial# || ''';',

sid,

serial#

--select *

from v$session se WHERE

se.status in ('INACTIVE','KILLED') and se.USERNAME='TSM'

复制出来执行即可，如果利用上面的命令杀死一个进程后，Oracle进程状态被置为"killed",但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程(线程)，首先获得进程(线程)号(注意要排除掉主机名是本身的进程，以免杀死oracle的后台进程)：

select

'kill -9 '|| p.SPID ||''

from v$session s, v$process p

where s.paddr = p.addr and s.STATUS in ('INACTIVE','KILLED') and s.MACHINE not in ('CNBPMD101')

9、查看锁进程并查杀

select

'alter system kill session '''|| sess.sid ||''||','|| sess.serial# ||''';',

sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo,

dba_objects ao,

v$session sess

where

ao.object_id = lo.object_id and lo.session_id = sess.sid;

10、查看表空间使用率

SELECT

total.tablespace_name,

Round(total.MB, 2)           AS Total_MB,

Round(total.MB - free.MB, 2) AS Used_MB,

Round(( 1 - free.MB / total.MB ) * 100, 2)

|| '%'                       AS Used_Pct

FROM   (SELECT tablespace_name,

Sum(bytes) / 1024 / 1024 AS MB

FROM

dba_free_space

GROUP

BY tablespace_name)

free,

(SELECT tablespace_name,

Sum(bytes) / 1024 / 1024 AS MB

FROM

dba_data_files

GROUP

BY tablespace_name)

total

WHERE  free.tablespace_name = total.tablespace_name;

11、闪回表

先在闪回区查看历史时刻表数据

select * from t_cas_feetype AS

OF TIMESTAMP to_timestamp('20150322 00:00:00','yyyymmdd hh24:mi:ss');

利用flashback table恢复表到过去某一时刻

alter

table tab_test enable row movement;

flashback

table tab_test to timestamp ('20140917 10:00:00','yyyymmdd hh24:mi:ss');

alter

table tab_test disable row movement;

查看过去某一段时间内对表的操作，以确认需要恢复到的时间点

select

SQL_TEXT,LAST_ACTIVE_TIME from v$sqlarea where LAST_ACTIVE_TIME

>to_date('20140917 10:00:00','yyyymmdd hh24:mi:ss') and SQL_TEXT like

'%tab_test%';

确认是否开启数据库闪回

select

log_mode,flashback_on from v$database;

12、查看表大小排序

select

table_name,blocks*8/1024

from

user_tables

where owner not like '%SYS%' and table_name not like '%$%'

order by blocks desc;

14、Oracle中查找阻塞与被阻塞SID的方法

在Oracle中,会经常遇到阻塞与被阻塞的情况.

查找阻塞与被阻塞的方法主要有下面几种:

一.通过查找v$lock和v$locked_object

这是最常用的也是最直接的方法

SQL> select sid,block from v$lock

where block=1;

SID BLOCK

---------- ----------

252 1

SQL>

SQL> select object_id,session_id from v$locked_object where object_id in

(select object_id from v$locked_object where session_id=252);

OBJECT_ID SESSION_ID

---------- ----------

63833 252

63833 269

SQL>

由上面第一条语句可以看出,SID=252的session阻塞了其他的session

由第二条语句可以得出,SID=252的session阻塞了SID=269的session

二.通过查找dba_waiters和dba_blockers

SQL> select * from dba_blockers;

HOLDING_SESSION

---------------

252

SQL>

SQL>

SQL> select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION

--------------- ---------------

269 252

从dba_blockers视图中,可以看到,SID=252的session阻塞了别的session,而从dba_waiters可以看到,HOLDING_SESSION为252,等待的WAITING_SESSION为269.

三.在Oracle 10G中可以通过v$session中的blocking_session字段查找

SQL> select sid,blocking_session from v$session where blocking_session is

not null;

SID BLOCKING_SESSION

---------- ----------------

269 252

15、查看当前活跃连接数正在执行的SQL

select s.sql_id,s.sql_text from

v$sql s,gv$session g

where s.SQL_ID=g.SQL_ID and

g.USERNAME='ECOLOGY'

and g.status='ACTIVE'

都是平时运维经常需要用到的SQL，后面再总结会持续更新进去