Oracle常用脚本

Oracle常用脚本

1 查看所有数据文件

select file_name from dba_data_files

union

select file_name from dba_temp_files

union

select name from v$controlfile

union

select value from v$parameter where name='spfile'

union

select member from v$logfile;

2 查看正在运行的job

select 

  djr.sid                        sess, 

  djr.job                        jid, 

  dj.log_user                    subu, 

  dj.priv_user                   secd, 

  dj.what                        proc, 

  to_char(djr.last_date,'MM/DD') lsd, 

  substr(djr.last_sec,1,5)       lst, 

  to_char(djr.this_date,'MM/DD') nrd, 

  substr(djr.this_sec,1,5)       nrt, 

  djr.failures                   fail 

from 

  sys.dba_jobs dj, 

  sys.dba_jobs_running djr 

where 

  djr.job = dj.job 

/

3 根据sid查找进程相关信息

select sid,serial#,sql_hash_value,PREV_HASH_VALUE,username,program,machine,process 

from v$session where sid=&sid;

4 根据spid查找进程相关信息

select 

a.sid,a.serial#,a.sql_hash_value,a.PREV_HASH_VALUE,

a.username,a.program,a.machine,a.process 

from v$session a,v$process where a.paddr=b.addr and spid=&spid;

5 通过hash_value获取sql语句

Select sql_text from v$sqltext where hash_value=&hash_value order by piece;

6 使用explain plan for 获取执行计划

Explain plan for &sql;   这里对应具体sql语句

  Select * from table(dbms_xplan.display);

7 使用shell脚本+hash_value获取执行计划以及sql语句

------------shell script -------------------

$ORACLE_HOME/bin/sqlplus -s /nolog<

connect / as sysdba;

set lines 121

set pages 999

col sql_text format a80

select sql_text from 

v\$sqltext_with_newlines 

where hash_value=$1

order by piece;

set heading off

select '--------------------------------------------------------------------------------' from dual

union all

select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual

union all

select '--------------------------------------------------------------------------------' from dual

union all

select *

from (select

rpad('|'||substr(lpad(' ',1*(depth-1))||operation||

decode(options, null,'',' '||options), 1, 62), 63, ' ')||'|'||

rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'

, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)

||' ',1, 20)), 21, ' ')||'|'||

lpad(decode(cardinality,null,' ',

decode(sign(cardinality-10000), -1, cardinality||' ', 

decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', 

decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', 

trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||

lpad(decode(bytes,null,' ',

decode(sign(bytes-1024), -1, bytes||' ', 

decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', 

decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', 

trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||

lpad(decode(cost,null,' ',

decode(sign(cost-10000000), -1, cost||' ', 

decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', 

trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"

from v\$sql_plan

where hash_value = $1)

union all

select '--------------------------------------------------------------------------------' from dual;

exit

EOF

8 使用shell脚本+spid获取执行计划以及sql语句

$ORACLE_HOME/bin/sqlplus -s /nolog<

connect / as sysdba;

set lines 121

set pages 999

col sql_text format a80

select sql_text from 

v\$sqltext_with_newlines a,v\$session b,v\$process c 

where a.hash_value=b.sql_hash_value and b.paddr=c.addr

and c.spid=$1

order by a.piece;

 

set heading off

select '--------------------------------------------------------------------------------' from dual

union all

select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual

union all

select '--------------------------------------------------------------------------------' from dual

union all

select *

from (select

rpad('|'||substr(lpad(' ',1*(a.depth-1))||a.operation||

decode(a.options, null,'',' '||a.options), 1, 62), 63, ' ')||'|'||

rpad(decode(a.id, 0, '----- '||to_char(a.hash_value)||' -----'

, substr(decode(substr(a.object_name, 1, 7), 'SYS_LE_', null, a.object_name)

||' ',1, 20)), 21, ' ')||'|'||

lpad(decode(a.cardinality,null,' ',

decode(sign(a.cardinality-10000), -1, a.cardinality||' ', 

decode(sign(a.cardinality-1000000), -1, trunc(a.cardinality/1000)||'K', 

decode(sign(a.cardinality-1000000000), -1, trunc(a.cardinality/1000000)||'M', 

trunc(a.cardinality/1000000000)||'G')))), 7, ' ') || '|' ||

lpad(decode(a.bytes,null,' ',

decode(sign(a.bytes-1024), -1, a.bytes||' ', 

decode(sign(a.bytes-1048576), -1, trunc(a.bytes/1024)||'K', 

decode(sign(a.bytes-1073741824), -1, trunc(a.bytes/1048576)||'M', 

trunc(a.bytes/1073741824)||'G')))), 6, ' ') || '|' ||

lpad(decode(a.cost,null,' ',

decode(sign(a.cost-10000000), -1, a.cost||' ', 

decode(sign(a.cost-1000000000), -1, trunc(a.cost/1000000)||'M', 

trunc(a.cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"

from v\$sql_plan a,v\$session b,v\$process c

where a.hash_value =b.sql_hash_value and b.paddr=c.addr and c.spid=$1 )

union all

select '--------------------------------------------------------------------------------' from dual;

exit

EOF

9 查找当前活动进程以及对应具体操作

Select sid,serial#,username,status,command,machine,sql_hash_value,prev_hash_value

From v$session where status=’ACTIVE’ and type<>’BACKGROUND’;

然后通过sql_hash_value查找具体语句

10 查看数据版本以及位数

10.1 使用file

cd $ORACLE_HOME/bin

[oracle@localhost bin]$ file oracle

oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped

10.2 查看视图

/home/oracle> sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Sep 29 16:07:04 2008

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning and Real Application Clusters options

JServer Release 9.2.0.7.0 - Production

 

SQL> select * from v$version;

 

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

PL/SQL Release 9.2.0.7.0 - Production

CORE    9.2.0.7.0       Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production

NLSRTL Version 9.2.0.7.0 - Production

 

10.3 检查lib,lib32

cd $ORACLE_HOME/lib

cd $ORACLE_HOME/lib32

如果这两个目录都存在就说明是64位的,如果只有一个lib目录说明是32位的。

 

10.4 查看登录信息

 

bilut42:/app/orarac#sqlplus "/ as sysdba"                                                                                       

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 29 15:14:00 2008

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 – Production

 

10.5 通过kopm$查看

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 16 16:05:54 2009

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

idle>conn sys/admin@emrep as sysdba

已连接。

sys@EMREP>select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for HPUX: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

sys@EMREP>select metadata from kopm$;

 

METADATA

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

0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B047008303690367130000

0

000000000000000000000000000000000000000000000000000000000000000000000

 

sys@EMREP>conn sys/admin@ora11g as sysdba

已连接。

sys@ORCL>select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

 

sys@ORCL>select metadata from kopm$;

 

METADATA

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

0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472323081123081141B0230083035407D0030000

0000000000000000000000000000000000000000000000000000000000000000000000

B047代表  64bit

B023代表 32bit

11 查看数据库锁信息

以上sql语句查询出的都是堵塞进程,ctime代表堵塞的时间

select * from v$lock where block=1;

通过以下sql可以查看对应堵塞进程在做什么操作

Select a.sql_text from v$sqltext a,v$session b,v$lock c 

Where a.hash_value=b.sql_hash_value and b.sid=c.sid and c.block=1;

12 杀数据库进程

Select sid,serial# from v$session where sid=&sid;

Select b.spid from v$session a,v$process b where a.paddr=b.addr and a.sid=&sid;

方法一：从数据库杀进程

Alter system kill session ‘&sid,&serial#’;

方法二：从操作系统杀掉进程

kill -9 spid

注意使用这种方法一定要确认所杀的进程是不是数据库后台进程，方法如下：

ps –ef|grep spid如果输出是带有LOCAL=NO信息可以杀掉，

如果不是需要在数据库里面进一步确认：

select a.type from v$session a,v$process b 

where a.paddr=b.addr and b.spid=&spid;

如果没输出就不是后台进程。

13 监控索引

启用索引监控

Alter index ind_name monitoring usage;

停止索引监控：

Alter index ind_name nomonitoring usage;

查看索引是否使用：

Select * from v$object_usage where index_name=’&index_name’;

打开索引一段时间后，查看以上视图，如果monitoring=no说明索引没有使用,可以删掉

14 查看undo表空间使用情况

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

       NVL(s.username, 'None') orauser,

       s.program,

       r.name undoseg,

       t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"

  FROM sys.v_$rollname    r,

       sys.v_$session     s,

       sys.v_$transaction t,

       sys.v_$parameter   x

 WHERE s.taddr = t.addr

   AND r.usn   = t.xidusn(+)

   AND x.name  = 'db_block_size';

15 查看temp表空间排序情况

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$sort_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

16 查看表空间使用百分比

SELECT d.tablespace_name Name, 

       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M, 

     TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999')||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') Used_M, 

     TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')||'%' Used

FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) 

 AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 

UNION ALL 

SELECT d.tablespace_name Name, 

     TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') Size_M, 

     TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') Used_M, 

     TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00')||'%' Used 

FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 

(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 

 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'

 order by 4 desc;

17 查找无效的对象

select owner,object_type,object_name from dba_objects where status='INVALID';

select constraint_name,table_name from dba_constraints where status='INVALID';

18 查找未定义索引的表

select table_name from user_tables 

where table_name not in (select table_name from user_ind_columns);

19 查找某个进程，并对它进行跟踪

select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;

exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);--开始跟踪

exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);--结束跟踪

结束跟踪后会在$ORACLE_BASE/admin/$SID/udump目录下产生一个文件，这个文件里面包括相关sql的信息

20 显示用户正在执行什么操作

SELECT OSUSER,SERIAL#,SQL_TEXT
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS 

AND V$SESSION.STATUS = 'ACTIVE';

21 查找消耗CPU资源高的语句

Select hash_value,LIO

From (

Select HASH_VALUE，BUFFER_GETS/EXECUTIONS LIO from v$sqlarea

Where EXECUTIONS<>0 order by 2 desc)

Where rownum<11;

通过hash_value定位sql语句

22 查找消耗磁盘IO高的语句

Select hash_value,WIO

From (

Select HASH_VALUE，DISK_READS/EXECUTIONS WIO from v$sqlarea

Where EXECUTIONS<>0 order by 2 desc)

Where rownum<11;

通过hash_value定位sql语句

23 查看正在执行全表扫描语句相关信息

SELECT 

sid,serial#,target,opname,sofar,totalwork,round((sofar/totalwork)*100,2)   completed,sql_hash_value

FROM gv$session_longops 

WHERE sofar<>totalwork and totalwork<>0 and opname not like 'RMAN%'

order by 8;

 

通过sid可以查找对应进程信息。

通过sql_hash_value可以查找对应sql语句。

24 查看数据库是否启用归档

Select log_mode from v$database;

Noarchivelog代表未启用归档

Archivelog   代表启用归档

 

也可以在sys用户下执行：archive log list;

 

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /app/oracle/product/10.2.0//dbs/arch

Oldest online log sequence     9

Current log sequence           10

25 查看数据库使用什么优化器

SQL> show parameter optimizer_mode

 

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

修改优化器：

alter system set optimizer_mode=rule;

26 导入导出规则(exp/imp)

 

 

 

导出规则:使用低版本exp导出

导入规则:使用目标版本imp导入

具体规则以及一些问题处理见附件。

         

+-----------+------------------------------+ 

|  EXPORT |      IMPORT into:         | 

|   from   +---------+---------+---------+ 

|    \/     | 9.2.0   | 10.1.0  | 10.2.0  | 

+-----------+---------+---------+---------+ 

|  8.0.3    |EXP803 | EXP803 | EXP803 | 

|  8.0.4    |EXP804 | EXP804 | EXP804 | 

|  8.0.5    |EXP805 | EXP805 | EXP805 | 

|  8.0.6    |EXP806 | EXP806 | EXP806 | 

+-----------+---------+---------+---------+

|  8.1.5    |EXP815 | EXP815 | EXP815 | 

|  8.1.6    |EXP816 | EXP816 | EXP816 | 

|  8.1.7    |EXP817 | EXP817 | EXP817 | 

+-----------+---------+---------+---------+

|  9.0.1    |EXP901 | EXP901 | EXP901 | 

|  9.2.0    |EXP920 | EXP920 | EXP920 |

+-----------+---------+---------+---------+

| 10.1.0    |EXP920 |             1)    | 

| 10.2.0    |EXP920 |             1)    |

+-----------+---------+---------+---------+

 

注:

1) 出于性能考虑建议使用expdp和impdp导入导出

2) Oracle Database 11g不支持exp和imp导入,可以使用,但是出现问题oracle不会解决该问题

 

+-----------+---------------------------------+ 

|  EXPORT |       IMPORT into:          | 

|   from   +---------+----------+----------+ 

|    \/     | 9.2.0   | 10.1.0   | 10.2.0  | 

+-----------+---------+----------+----------+ 

|  8.0.3    |IMP920 | IMP1010 | IMP1020 | 

|  8.0.4    |IMP920 | IMP1010 | IMP1020 | 

|  8.0.5    |IMP920 | IMP1010 | IMP1020 | 

|  8.0.6    |IMP920 | IMP1010 | IMP1020 | 

+-----------+---------+----------+----------+ 

|  8.1.5    |IMP920 | IMP1010 | IMP1020 | 

|  8.1.6    |IMP920 | IMP1010 | IMP1020 | 

|  8.1.7    |IMP920 | IMP1010 | IMP1020 | 

+-----------+---------+----------+----------+ 

|  9.0.1    |IMP920 | IMP1010 | IMP1020 | 

|  9.2.0    |IMP920 | IMP1010 | IMP1020 | 

+-----------+---------+----------+----------+ 

| 10.1.0    |IMP920 |             1)      | 

| 10.2.0    |IMP920 |             1)      | 

+-----------+---------+----------+----------+ 

 

注:

1) 出于性能考虑建议使用expdp和impdp导入导出

2) Oracle Database 11g不支持exp和imp导入,可以使用,但是出现问题oracle不会解决该问题

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29320885/viewspace-1225636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-1225636/