没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
********************************************************************
ORACLE Interview Questions
********************************************************************
Question No : 0
Replication Troubleshooting using DBMS_REFRESH:
Answer :
Replication Troubleshooting
While transferring data from materialized view site end. I was faced the below error message.
ORA-12012: error on auto execute of job 2
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Oracle suggest to fix this error automatically using ?refresh_after_errors= true?
MviewAdmin@Materialized view site.
BEGIN
DBMS_REFRESH.CHANGE(
name => '"MVIEWADMIN"."MIS_REFG"',
next_date => to_date('01-01-2020 06:06:08','DD-MM-YYYY HH24:MI:SS'),
interval => 'SYSDATE + 1 + (1/24)',
implicit_destroy => FALSE,
rollback_seg => 'NULL',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);
END;
By logically it?s not good. You should be find why data?s not transfer? Any problem in your
database?
Sys@MasterSite
Here you can find out those problems using the following data dictionary views,
SQL> select deferred_tran_id, destination, error_number, error_msg from deferror;
Using deferred transaction id, you can find out which transaction not updated in materialized view
site. Fix those errors. After that only transfer your data?s.
Question No : 1
How to work with Oracle Data Pump?
Answer :
Oracle Data Pump
Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and
metadata movement between Oracle databases. Oracle Data Pump provides new high-speed,
parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise
Manager interface.
Data Pump Export and Import utilities are typically much faster than the original Export and Import
Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data
Pump Import is 15-45 times fast than original Import.
Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary
or involuntary.
Data Pump jobs support fine-grained object selection. Virtually any type of object can be included
or excluded in a Data Pump job.
Data Pump supports the ability to load one instance directly from another (network import) and
unload a remote instance (network export).
Data Pump Export (expdp) :-
For this example, once your export your database before that you must be give privilege on this
user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you
can give " IMP_FULL_DATABASE "
SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA
Connected.
SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16;
Grant succeeded.
SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16;
Grant succeeded.
SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16;
Grant succeeded.
Table level Export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER
DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing
tables.
Schema level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE
SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP
LOGFILE=ORTHONOVC16.LOG
DataBase level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y
DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG
Other export's :
Exclude = View, Proceudre, Function , Constraint , Index
Include = Table: " in ( 'emp') "
Content = ALL (by default ) / data_only / metadata_only
Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow
parameter " ESTIMATE_ONLY = Y "
Question No : 2
dbv: DBVERIFY utility in oracle:
Answer :
dbv
The DBVERIFY utility, available since in oracle 7.3, Check the logical integrity of an offline
database. The file can only be datafiles. DEVERIFY can't analyze online redo log files or archived
redo log files. In pervious version of Oracle DBV could analyze all of TS Datafiles in parallel by
spawning multiple DBV cmd's. However, becuase Bigfile TS has only one datafile, DBV has been
enhanced to analyze parts of a bigfile TS's Datafiles in parallel.
SQL> host dbv help=y
DBVERIFY: Release 9.0.1.1.1 - Production on Fri Jul 13 22:30:36 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (2048)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
SQL> select file#,blocks,name from v$datafile;
FILE# BLOCKS NAME
---------- ---------- ---------------------------------------------------------
1 83200 E:\SP2\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
2 51200 E:\SP2\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
3 5120 E:\SP2\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
4 5120 E:\SP2\ORACLE\ORADATA\ORCL\DRSYS01.DBF
5 39040 E:\SP2\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
6 6400 E:\SP2\ORACLE\ORADATA\ORCL\INDX01.DBF
7 2560 E:\SP2\ORACLE\ORADATA\ORCL\TOOLS01.DBF
8 6400 E:\SP2\ORACLE\ORADATA\ORCL\USERS01.DBF
8 rows selected.
SQL> host dbv file=E:\SP2\oracle\oradata\orcl\UNDOTBS01.DBF BLOCKSIZE=4096
DBVERIFY: Release 9.0.1.1.1 - Production on Fri Jul 13 22:46:46 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = E:\SP2\oracle\oradata\orcl\UNDOTBS01.D
BF
DBVERIFY - Verification complete
Total Pages Examined : 51200
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 30224
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 20976
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
剩余164页未读,继续阅读
资源评论
- ericyzhou2013-01-06题不是很全,关于SQL的题太少了
麦叔的小屋
- 粉丝: 0
- 资源: 3
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功