oracle@jyrac1 database]$ ./runInstaller -silent -force -responseFile /home/oracle/enterprisejy.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 9408 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1929 MB Passed
Preparing to launch Oracle Universal Installer from /u01/tmp/OraInstall2014-04-08_03-35-12PM. Please wait ...
使用操作系统审计进行连接
一个用户作为管理员通过操作系统审计来连接到本地数据库可以通过以下命令来实现:
connect / as sysdba
connect / as sysoper
对于windows平台,远程操作系统审计是一个安全连接被支持.但是必须指定远程数据库的网络服务名
CONNECT /@net_service_name AS SYSDBA
CONNECT /@net_service_name AS SYSOPER
而且客户端计算机和数据库主机必须在一个windows域中
-------------------------------------------------------------
平台 要求的名称 要求的位置
-------------------------------------------------------------
unix and linux orapwORACL_SID ORACLE_HOME/dbs
Windows PWDORACLE_SID.ora ORACLE_HOME\database
-------------------------------------------------------------
[oracle@jyrac1 ~]$ orapwd file=orapworcl entries=30
Enter password for SYS:
OPW-00005: File with same name exists - please delete or rename
[oracle@jyrac1 ~]$ orapwd file=orapworcl entries=30 force=n
Enter password for SYS:
OPW-00005: File with same name exists - please delete or rename
[oracle@jyrac1 ~]$ orapwd file=orapworcl entries=30 force=y
Enter password for SYS:
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string NONE
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
no rows selected
SQL> select * from V$PWFILE_USERS;
no rows selected
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 4 10:15:06 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/zzh_2046@231_jycs as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
远程登录是不能连接到数据库
当remote_login_passwordfile为exclusive时
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string EXCLUSIVE
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 2560 Apr 4 09:56 spfilejycs.ora
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs
将密码文件orapwjycs删除
[oracle@jyrac1 dbs]$ mv orapwjycs orapwjycs.bak
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 2560 Apr 4 09:56 spfilejycs.ora
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs.bak
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 4 09:59:46 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/zzh_2046@231_jycs as sysdba
ERROR:
ORA-01031: insufficient privileges
远程登录时是连接不了数据库的
现在恢复密码文件orapwjycs
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 4 09:58:49 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/zzh_2046@231_jycs as sysdba
已连接。
远程登录是能连接数据库
当remote_login_passwordfile为shared时
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string SHARED
删除密码文件orapwjycs
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
[oracle@jyrac1 dbs]$ mv orapwjycs orapwjycs.bak
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs.bak
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 4 10:10:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/zzh_2046@231_jycs as sysdba
ERROR:
ORA-01031: insufficient privileges
远程登录时是连接不了数据库
现在恢复密码文件orapwjycs
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs.bak
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
[oracle@jyrac1 dbs]$ mv orapwjycs.bak orapwjycs
[oracle@jyrac1 dbs]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:27 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 24 13:31 hc_jycs.dat
-rw-r----- 1 oracle oinstall 24 Mar 24 13:32 lkJYCS
-rw-r----- 1 oracle oinstall 1536 Apr 4 10:01 orapwjycs
-rw-r----- 1 oracle oinstall 2560 Apr 4 10:08 spfilejycs.ora
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 4月 4 10:12:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/zzh_2046@231_jycs as sysdba
已连接。
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- -------------
remote_login_passwordfile string SHARED
SQL> alter user sys identified by "zzh_2046";
alter user sys identified by "zzh_2046"
*
ERROR at line 1:
ORA-28046: Password change for SYS disallowed
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
OE TRUE FALSE FALSE
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
USERNAME
------------------------------
OE
2.回收这些非sys用户的sysdba权限然后再重新授予sysdba权限
SQL> revoke sysdba from oe;
Revoke succeeded.
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
no rows selected
SQL> grant sysdba to oe;
Grant succeeded.
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
USERNAME
------------------------------
OE
3.找出所有已经被授予sysoper权限的所有用户
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != ‘SYS’ AND SYSOPER=’TRUE’;
4.回收非sys用户的sysoper权限然后再重新授予sysoper权限
REVOKE SYSOPER FROM non-SYS-user;
GRANT SYSOPER TO non-SYS-user;
SQL> startup
ORACLE instance started.
Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 385880016 bytes
Database Buffers 234881024 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
5.创建必要的用户授予sysdba或sysoper权限
SQL> create user test identified by test;
User created.
SQL> grant sysdba to test;
Grant succeeded.
SQL> grant sysoper to test;
Grant succeeded.
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
OE TRUE FALSE FALSE
TEST TRUE TRUE FALSE
授予和回收sysdab和sysoper权限
如果你的服务器使用一个exclusive密码文件,使用grant语句给用户授予sysdba或sysoper系统权限,例如:
SQL>grant sysdba to oe;
使用revoke语句来回收用户的sysdba或sysoper系统权限,例如:
SQL>revoke sysdba from oe;
什么是执行计划
执行计划显示了执行一个sql语句所需步骤的详细信息.这些步骤代表了一组数据库操作它们会消费和生产行数据.这些操作的顺序以及它们的实现取决于查询优化器对查询转换和物理优化技术的联合使用.执行计划通常是以表格形式来显示,这个执行计划实际上是一个树形结构.例如下面是一个基于sh方案的查询:
SELECT prod_category, AVG(amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;
下面的表格是上面语句的执行计划:
Table access by rowed—行的rowid指定的数据文件,数据块,以及行在数据块中的位置.oracle首先从where子句的谓词或者从表中的一个索引或多个索引中获得rowid.oracle然后会基于获得的rowid来回表定位所选的每一行记录的位置再一行一行访问.
Index unique scan—扫描唯一索引只会返回一行记录.在等值谓词用于一个唯一索引或一个主键列上会使用唯一索引扫描
Index range scan—oracle访问相邻的索引条目然后使用索引中的rowid值来从表中检索相关的行记录.索引范围扫描可以是有边界也可以是无边界.当对一个非唯一索引键使用等值谓词或者对一个唯一键使用非等值或范围谓词时将会使用索引范围扫描(=,< ,>,like),数据会以索引列的升序返回.
Index range scan descending 索引范围降序扫描—与索引范围扫描的概念是相同的,当order by … descending子句中的列是某个索引列表的子集时就会使用.
Index skip scan –正常情况下为了使用一个索引,索引键的前缀(索引的前导列)将要在查询中被引用.然而,除了索引中的第一列外其它的列在语句中被引用,oracle可以进行索引跳跃扫描来跳过索引的第一列而使用剩下的列.如果在一个复合索引中前导列只有几个不同的值而在非前导列中有大量不同值时是有用的.
Full index scan—完全索引扫描不会读取索引结构中的每一个索引块.索引完全扫描会处理索引的所有叶子块,但只在足够的分支块中找到第一个叶子块.当查询语句中所引用的列都在索引列中存在这时使用完全索引扫描比扫描表成本更低.在以下情况下可能使用单块IO:
一个orader by子句有索引中所有的列且顺序和索引相同(也可以是索引列中的子集)
查询要求执行一个排序合并连接且查询中所引用的所有列都出现在索引列中
查询中引用列的顺序与索引前导列的顺序相同
一个group by 子句出现在查询中,group by子句中的列出现在索引列中.
Fast full index scan—这是一种替代的完全表扫描当索引包含查询所需要的所有列时且在索引键中至少有一个列有not null约束.它不能用来消除一个排序操作,因为数据访问不遵循索引键.它将使用多块读来读取索引中的所有索引块,与完全索引扫描不一样.
Index join—连接相同表中的 多个索引这个集合包含了查询中所引用的所有列.如果索引连接被使用那么就不需要进行表访问.因为所有相关的列都能从索引中得到,索引连接操作不能消除排序操作.
Join order
连接顺序是在一个多表sql语句中每一个表被加入连接的一个顺序.为了判断一个执行计划中表的连接顺序可以查看operation列中表的缩进,在下面的图表中sales和products表的缩进是相同的且它们都比customers表更缩进.因此sales和products表首先使用一个哈希连接进行连接然后它们的连接结果再与customers表进行哈希连接.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (43200 = 30 Days).
-- Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
创建一个awr基线:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
sys@JINGYONG> show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
注意:在有些例子中可能设置了’tracefile_identifier’来帮助找到输出的跟踪文件
会话跟踪
可以在用户会话执行sql语句之前对会话启用跟踪,在会话级别收集10046跟踪
sys@JINGYONG> alter session set timed_statistics=true;
会话已更改。
sys@JINGYONG> alter session set statistics_level=all;
会话已更改。
sys@JINGYONG> alter session set max_dump_file_size=unlimited;
会话已更改。
sys@JINGYONG> alter session set events '10046 trace name context forever,level 1
2';
会话已更改。
sys@JINGYONG> select * from dual;
D
-
X
sys@JINGYONG>exit
如果会话没有退出可以执行以下语句来禁用10046跟踪
sys@JINGYONG> alter session set events '10046 trace name context off';
会话已更改。
sys@JINGYONG> select * from v$diag_info ;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
1 Diag Enabled
TRUE
1 ADR Base
/u01/app/oracle
1 ADR Home
/u01/app/oracle/diag/rdbms/jingyong/jingyong
1 Diag Trace
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace
1 Diag Alert
/u01/app/oracle/diag/rdbms/jingyong/jingyong/alert
1 Diag Incident
/u01/app/oracle/diag/rdbms/jingyong/jingyong/incident
1 Diag Cdump
/u01/app/oracle/diag/rdbms/jingyong/jingyong/cdump
1 Health Monitor
/u01/app/oracle/diag/rdbms/jingyong/jingyong/hm
1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2572_10046.trc
1 Active Problem Count
0
1 Active Incident Count
0
已选择11行。
sys@JINGYONG> select * from v$diag_info where name='Default Trace File';
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
1 Default Trace File
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2572_10046.trc
跟踪一个已经启动的进程
如果要跟踪一个已经存在的会话可以使用oradebug来连接到会话初始化10046跟踪
1.通过某种方法来确定要被跟踪的会话
例如在sql*plus中启动一个会话然后找到这个会话的操作系统进行id(spid):
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
column line format a79
set heading off
select 'ospid: ' || p.spid ||' pid: '||p.pid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
执行结果如下:
sys@JINGYONG> column line format a79
sys@JINGYONG> set heading off
sys@JINGYONG> select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||'''
'||
2 s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
3 from v$session s , v$process p
4 where p.addr = s.paddr
5 and s.username <> ' ';
ospid: 2529 # '30,32' Administrator WORKGROUP\JINGYONG SYS sqlplus.exe
启用系统级别的10046跟踪:
alter system set events ‘10046 trace name context forever,level 12’;
对所有会话禁有系统级别的10046跟踪:
alter system set events ‘10046 trace name context off’;
初始化参数的设置:
当实例重新启动后对每一个会话启用10046跟踪.
event=”10046 trace name context forever,level 12″
要禁用实例级别的10046跟踪可以删除这个初始化参数然后重启实例或者使用alter system语句
alter system set events ‘10046 trace name context off’;
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/
注意:为了能跟踪会话用户执行触发器需要显式的被授予’alter session’权限:
grant alter session to username;
这篇文章介绍在oracle数据文件中出现一个或多个数据块坏块时的处理方法.当出现数据块坏块出误时对于每一个坏块都提供了以下信息:
1.包含这个坏块的数据文件的绝对文件号可以标示为”AFN”.
2.包含这个坏块的数据文件的文件名可以标示为”FILENAME”(如果知道文件号但不知道文件名那么可以执行select name from v$datafile where file#=&AFN来得到文件名,如果文件号在v$datafile中没有记录且AFN比参数db_files参数的值还大那么这个文件可能是临时文件.如果是这种情况可以执行select name from v$tempfile where file#=(&AFN-&DB_FILES_value)
3.数据文件中坏块的块号可以标示为”BL”
4.受坏块影响的表空间号和表空间名称可以标示为”TSN”和”TABLESPACE_NAME”.可以执行select ts# “TSN” from v$datafile where file#=&AFN;select tablespace_name from dba_data_files where file_id=&AFN来查询.
5.出现坏块的表空间的数据块大小可以标示为”TS_BLOCK_SIZE”.对于oracle9i来说可以执行select block_size from dba_tablespace where tablespace_name=(select tablespace_name from dba_data_files where file_id=&AFN);来查询数据块大小.对于oracle7.8.0和8.1在数据库中每一个表空间都有相同的数据块大小.对于这些版本可以使用show parameter db_block_size来显示数据块大小.
例如:ora-1578错识信息
ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
ORA-01110: data file 22: /oracle1/oradata/V816/oradata/V816/users01.dbf
从上面的错识信息可知:绝对文件号AFN是22,相对文件号RFN是7,数据块BL是12698,文件名FILENAME是/oracle1/oradata/V816/oradata/V816/users01.dbf,表空间号和表空间名可以用上面的查询得到.
下列查询将显示数据库中数据文件的绝对和相关文件号：
SELECT tablespace_name, file_id “AFN”, relative_fno “RFN” FROM dba_data_files;
在Oracle8i/9i/10g中:除了上述关于Oracle8 的说明外,从 Oracle8i开始将拥有临时文件.下列查询将显示数据库中临时文件的绝对和相关文件号：
SELECT tablespace_name, file_id+value “AFN”, relative_fno “RFN”
FROM dba_temp_files, v$parameter WHERE name=’db_files’;
在Oracle7中:“绝对文件号”和“相关文件号”使用相同的文件号
“段类型”,“所有者”,“名称”和“表空间”
在给定坏块的绝对文件号“&AFN”和块编号“&BL”的情况下,下列查询将显示对象的段类型,所有者和名称,数据库必须打开才能使用此查询：
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks – 1;
INDEX PARTITION
如果段类型为INDEX PARTITION,请记录名称和所有者,然后确定哪些分区受到影响：
SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks – 1;
然后按照处理INDEX段的步骤继续下面的操作.
恢复选项：
使用下列语句可以重建索引分区：
ALTER INDEX xxx REBUILD PARTITION ppp;
INDEX
如果段类型为INDEX,对于非字典INDEX或INDEX PARTITION,确定索引位于哪个表中:
例如:
SELECT table_owner, table_name
FROM dba_indexes
WHERE owner=’&OWNER’
AND index_name=’&SEGMENT_NAME’;
并确定索引是否支持约束：
例如:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner=’&TABLE_OWNER’
AND constraint_name=’&INDEX_NAME’;
CONSTRAINT_TYPE 的可能值包括：
P 索引支持主键约束。
U 索引支持唯一约束。
如果索引支持主键约束（类型“P”），则确认主键是否被任何外键约束引用：
例如：
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner=’&TABLE_OWNER’
AND r_constraint_name=’&INDEX_NAME’
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典索引,可能的选项包括：
恢复或 重建索引（任何相关联的约束会随之禁用/启用）
TABLE PARTITION
如果段类型为TABLE PARTITION,请记录名称和所有者,然后确定哪些分区受到影响：
SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks – 1;
然后按照处理TABLE段的步骤继续下面的操作.
选项：
如果所有坏块均位于同一个分区，则此时可以采取的一个做法是用一个空表EXCHANGE坏块所在的分区,这可以让应用程序继续运行(无法访问坏块所在的分区中的数据),然后可以从之前的空表中提取任何未损坏的数据
TABLE
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典 TABLE 或 TABLE PARTITION，确定表中存在哪些索引：
例如：
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE table_owner=’&OWNER’ AND table_name=’&SEGMENT_NAME’;
并确定表中是否存在任何主键：
例如:SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner=’&OWNER’
AND table_name=’&SEGMENT_NAME’ AND constraint_type=’P’;
如果存在主键，则确认它是否被任何外键约束引用：
例如：
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner=’&OWNER’
AND r_constraint_name=’&CONSTRAINT_NAME’;
选项：
如果所有者为“SYS”,可能需要恢复数据库。
对于非字典表，可能的选项包括：
恢复或 抢救表(或分区)中的数据,然后重新创建表(或分区)或忽略坏块(例如:使用DBMS_REPAIR标记需要跳过的问题块)
对于任何段，如果您拥有坏块的绝对文件号和块号，则可使用以下快速提取对象 DDL 的方法：
set long 64000
select dbms_metadata.get_ddl(segment_type, segment_name, owner)
FROM dba_extents
WHERE file_id=&AFN AND &BL BETWEEN block_id AND block_id + blocks -1;
数据库是10.2.0.4 操作系统是aix,在执行expdp导出多个方案对象时报ORA-39014,ORA-39029,ORA-31671,ORA-39079,ORA-06512,ORA-04031:错误信息如下:
Export: Release 10.2.0.4.0 – 64bit Production on Monday, 17 February, 2014 9:46:52
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=dump_RLZY dumpfile=ybcwfull_140217_0946.dmp logfile=ybcwfull_140217_0946.log schemas=ZW
1001,ZW1002,ZW1003,ZW1004,ZW1005,ZW1006,ZW1101,ZW1102,ZW1103,ZW1104,ZW1105,ZW1106,ZW1201,ZW1202,ZW1203,ZW1204,ZW1205,ZW1206,ZW1301,ZW1302,ZW1303,ZW1304,ZW13
05,ZW1306,ZW2001,ZW2002,ZW2003,ZW2004,ZW2005,ZW2006,ZW3001,ZW3002,ZW3003,ZW3004,ZW3005,ZW3006,ZW4001,ZW4002,ZW4003,ZW4004,ZW4005,ZW4006,ZW5001,ZW5002,ZW5003
,ZW5004,ZW5005,ZW5006,ZW6001,ZW6002,ZW6003,ZW6004,ZW6005,ZW6006,ZW7001,ZW7002,ZW7003,ZW7004,ZW7005,ZW7006,ZW8001,ZW8002,ZW8003,ZW8004,ZW8005,ZW8006,ZW9001,Z
W9002,ZW9003,ZW9004,ZW9005,ZW9006,ZW9999
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 997.3 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
………………
. . exported “ZW9005″.”GLSP_KMYS_TEMP” 0 KB 0 rows
. . exported “ZW9005″.”GLSP_KMZJS_TEMP” 0 KB 0 rows
. . exported “ZW9005″.”GLSP_KMZXFX_TEMP” 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP1″ 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP2″ 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP3″ 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP4″ 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP5″ 0 KB 0 rows
. . exported “ZW9005”.”GLSP_PZYJZ_TEMP6″ 0 KB 0 rows
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW01” prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-39079: unable to enqueue message DG,KUPC$C_1_20140217094653,KUPC$A_1_20140217094654,MCP,50443,Y
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPC$QUE_INT”, line 924
ORA-04031: unable to allocate 2072 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”kodpaih3 image”)
ORA-06512: at “SYS.KUPW$WORKER”, line 1397
ORA-06512: at line 2
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ stopped due to fatal error at 10:28:18
从错误信息中可以看到ORA-04031: unable to allocate 2072 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”kodpaih3 image”)
从字面上理解是在给streams pool分配内存时出错造成的,MOS上有一篇文件档
DataPump Export (EXPDP) Fails With Error ORA-4031 (“streams pool”, …) (文档 ID 457724.1)
In this Document
Symptoms
Cause
Solution
References
——————————————————————————–
Applies to:
Oracle Database – Enterprise Edition – Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 16-MAY-2012***
Symptoms
DataPump export (EXPDP) reports the following errors:
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 600
ORA-39080: failed to create queues “KUPC$C_1_20070823095248” and “KUPC$S_1_20070
823095248” for Data Pump job
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 1580
ORA-04031: unable to allocate 4194344 bytes of shared memory (“streams pool”,”unknown object”,”streams pool”,”fixed allocation callback
Cause
The problem seems initially caused by having set the STREAMS_POOL_SIZE instance parameter to 0.
The first argument of the ORA-4031 error message also indicates a problem with the Streams pool.
The streams pool is used exclusively by Oracle Streams, see http://docs.oracle.com/cd/E11882_01/server.112/e25789/memory.htm#CNCPT1235
Also, Data Pump export and import operations initialize the Oracle Streams pool because these operations use buffered queues.
For information about the streams pool, refer to http://docs.oracle.com/cd/E11882_01/server.112/e10705/prep_rep.htm#STREP202
The size of the streams pool grows dynamically as required by Oracle Streams.
The (initial) size also depends on usage of ASMM, AMM or manual (minimum) settings.
That means that the parameter STREAMS_POOL_SIZE=0 is not the real root cause but the memory management cannot provide the automatic increase for the DataPump action at this time.
Setting STREAMS_POOL_SIZE>0 will guarantee a minimum size for the streams pool when using ASMM or AMM, hence avoiding the ORA-4031.
Solution
Set the STREAMS_POOL_SIZE instance parameter to at least 48MB to guarantuee a minimum size using:
SQL>connect / as sysdba
SQL> show parameter stream
NAME TYPE VALUE
———————————— ———– —————————–
streams_pool_size big integer 0
SQL>alter system set streams_pool_size=48m scope=both
Note:
For a large database and/or high workload using streams, the STREAMS_POOL_SIZE parameter may need to be higher (i.e. 150 MB) in order to avoid the ORA-4031 error.
References
NOTE:396940.1 – Troubleshooting and Diagnosing ORA-4031 Error [Video]
在设置streams_pool_size之后再来执行expdp导出正常导出
Export: Release 10.2.0.4.0 – 64bit Production on Monday, 17 February, 2014 11:01:52
SQL>select p.spid
from v$session s,v$process p,v$sqlarea c
where s.username is not null and s.PADDR=p.ADDR and s.sql_id=c.sql_id
and s.sql_fulltext like'%UPDATE t_config_info%'
SPID
----------
14483524
CBO与直方图histograms
从一个行源中评估返回行数所占的比例这就是选择率,选择率在CBO的查询优化中起着重要作用.选择率的取值范围是0到1之间.粗略的讲,如果满足谓词条件的只有少量的行记录那么CBO将更喜欢使用索引扫描,如果谓词条件要从表中获取大量数据那么CBO将更喜欢使用全表扫描.比如下面的查询获取deptno等于10的所有雇员信息如果返回少量的记录查询将会更倾向于使用索引扫描:
select * from emp where deptno=10;
[oracle@jingyong ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000;
Table created.
SQL> update t1 set skew=all_distinct where rownum< =10;
10 rows updated.
SQL> commit;
Commit complete.
SQL> select skew,count(*) from t1 group by skew order by skew;
SKEW COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
10000 9990
11 rows selected.
使用dbms_stata.gather_table_stats来收集统计信息是生成直方图是由参数method_opt来控制的method_opt参数的语法是由多个部分组成的.前两个部分是强制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column…]
method_opt语法中的主要部分控制哪此列将收集列的统计信息(min,max,ndv,nulls).缺省是for all columns,它将会对表中所有的列(包括隐藏列)收集基本的列统计信息.
for all indexed columns将只对哪些包含索引的列进收集列统计信息.
for all hidden columns将只会对哪些虚拟列收集列统计信息.这意味着在对表收集统计时真实列是不会生成列统计信息的.这个值不能用于通常的统计信息收集.它只能用在当基表列的统计信息精确收集后在表中创建新的虚拟列.然后对新的虚拟列收集列统计信息时才使用它.
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select column_name,num_distinct,density from user_tab_col_statistics where
2 table_name='T1';
COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ALL_DISTINCT 10000 .0001
SKEW 11 .090909091
如果没有直方图,列的density统计信息代表了它的选择率它是通过去时1/num_distinct=1/11=0.09090901来计算出来的.在有直方图的情况下,density的计算依赖于直方图的类型和oracle的版本.density值的范围是0到1之间.当查询使用这个列作谓词条件时优化器将会使用这个列的density统计信息来评估将要返回的行数.所以 cardinality(基数)=selectivity(选择率)* number of rows(表的行数)
下面来检查一下在谓词条件中列的数据分布存在倾斜而没有直方图的情况下其基数评估的情况:
SQL> explain plan for select * from t1 where skew=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 909 | 6363 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=1)
SQL> explain plan for select * from t1 where skew=10000;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 909 | 6363 | 7 (15)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 909 | 6363 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("SKEW"=10000)
SQL> SELECT bucket_number, max(skew) AS endpoint_value
2 FROM (
3 SELECT skew, ntile(5) OVER (ORDER BY skew) AS bucket_number
4 FROM t1)
5 GROUP BY bucket_number
6 ORDER BY bucket_number;
BUCKET_NUMBER ENDPOINT_VALUE
------------- --------------
1 10000
2 10000
3 10000
4 10000
5 10000
这里ntile(5)是一个分析函数,它将一个有序的数据集划分到5个桶中.
所以简而言之,在高度平衡直方图中,数据被划分到不同的桶中除了最后一个桶每一个桶包含相同的数据.每一个桶中的最大值被记录为endpoint_value而第一个桶中的最小值也被记录(bucket 0).endpoint_number代表桶数.一旦数据被记录到桶中将会识别为2种类型的数据:
Non popular values和popular values.
Popular values是哪些作为endpoint value出现多次的值.例如在前面的例子中3是一个popular值,在上面的例子中skew 10000是一个popular value.non popular value是哪些没有作为endpoint values出现或者只作为endpoint values出现一次的值.popular value和non popular value不是固定的它依赖于直方图桶的大小,改变桶的大小会出现不同的popular值.
