1)you have been brought in to a project to do an oracle upgrade from 9i to 11g.The current sql code has been working great and much effort has been put into tuning it using hints,and making sure the execution plans are performing well under the existing versions rule!based optimi"er. you do not have permission to change any of the current sql code in this phase of the project.
#ased on the scenario above,which approach do you take to avoid the risk of the execution plans being changed by the upgrade$ upgrade$ %&'igrate the execution plans along with sql using plan stability features. #&(se the rule!based optimi"er in the upgraded 11g 11g database C)Recompile the sql after the upgrade and re tune the sql to have the same execution plans as in the order version. )&)o not upgrade and then fix any poorly performing queries on a case!by!case basis *&+reate a test database with the new 11g version and run the sql remotely on it before doing the upgrade !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!! 2)%ssume serveroutput is -. )*+%/* T0* months2array 34 5%//%0617& 8 4T/3-6: +;%/&< month '-T;42%//%0=> months2array 6jan,feb,mar,apr,may,jun, jul,aug,sep,oct,nov,dec&< #*3??34T2'-T;4@@ 8/ 'onth-o 3- /*5*/4* A..17 )#'42(T(T.(T2 )#'42(T(T.(T23-*6month6'onth-o&&< 3-*6month6'onth-o&&< +-T3-(* 34T2'-T;4 B;*- 'onth-o?9< *-) < *-)< #ased on the sample code above,what is the last line output when you execute the anonymous block$ %&Can B)Jun +&%ug )&4ep *&)ec !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!! :&0ou :&0ou need to import the '(43+23T*' table data from a production export dump into the D% database. 1&The table space that the '(43+23T*'4 table is created in production6'(43+2T#4+&does not exist in D%. %nd 7&The tcmusers default table space in D% is T+')8T2 T+')8T2T#4+ T#4+ #ased on the scenario above,which script do you use to import the data into the D% database$ %&3mport the table space s pace '(43+2T#4+ into the D% database using the following command impdp tcmuserEtcmpswdFtcdb
/*'%2T%#*4%+*>music2db.dmp #&3mport the '(43+23T*' table to the D% database using the following command impdp tcmuserEtcmpswdFtcdb T%#*4>music2tblspc=tcmdflt2tblspc dumpfile>music2db.dmp +&3mport objects from the '(43+2T#4+ tablespace to the D% database using the following command impdp tcmuserEtcmpswdFtcdb T%#*4%+*4>music2tblspc dumpfile>music2db.dmp )&3mport the tablespace '(43+2T#4+ into the D% database using the following command impdb tcmuserEtcmpswdFtcdb T/%-4/T2T%#*4%+*>music2tblspc dumpfile>music2db.dmp network2link>tcdb2prod *&+reate the '(43+2T#4+ in the D% database and i mport using the following command impdb tcmuserEtcmpswdFtcdb T%#*4>music2item dumpfile>music2db.dmp G&)*+%/* some2error *H+*T3-= #*33-4*/T 3-T tc2tmp2rec 4**+T tc2msg2id,tc2msg2text 8/' tc2perm2rec B;*/* tc2id>1II< /%34* some2error< ??lbl2*///2C('@@ )#'42(T(T.(T23-*6*H+*T3-= 3-4*/T 8ailed&< T lbl2*///2C('< *-)< what is the result when you execute the sample code above$ %&The code gives a syntax error #&The string J*rror abel=3-4*/T 8ailedKLis printed on the scre en C)The string “!C"T#$%&#%'RT (ailed* is printed on the screen )&The code gives a run time error *&The code goes into an interface loop,displaying both the strings one after another. M&Bhich cursor attribute do you reference to determine the number of records that were deleted by a )**T* 4tatement$ %&4DN-('/B4 #&4DN)**T*)2+(-T +&4DN/B2+(-T +)',-R$/C$0%T *&4DN+(-T A&38 tax2amount>I T;* -(< *4* calculate2tax6tax2amount&< *-) 38< #ased on the sample code above,what is the purpose of the J-(Lstatement$ %&3t sets the value in the Jtax2amountLvariable to J-(L #&3t resets all variables in the module to =-(Lvalues C)#t passes the control to the next executale statement ithout performing an3 dataase operations. )&3t causes the module to stop execution and return to the calling module
*&3t assigns a value of J-(Lto the 38 statements O&0ou are using )#'42(T(T in one of your procedures,and you are testing the procedure from 4DP(4. 0ou do not see any of the expected output from the )#'42(T(T statements. #ased on the scenario above,what has caused the problem$ 4)The 'R5R$0T"0T option has not een set #&The )#'42(T(T@8(4; statement has not been invoked +&The 4*/5*/(T(T option has been set but without the si"e option )&The )#'42(T(T.)34%#* option has not been invoked *&The )#'42(T(T.*-%#* option has been invoked Q&The reason that you place an JothersLexception handler in the outermost block of a E4D program unit is because %&Bhen it is placed before explicitly!stated exception handlers,it will catch most exceptions and override the ability of the explicit handlers to execute B)#t provides a method for catching all propagated unhandled exceptions from ithinn the entire program unit. +&3t is invoked by the oracle runtime engine last )&Bhen used first it provides the least possible information about the exceptions trapped *&3t can be placed in any order ,but is most efficient when place last 9&Bhat output text does 4D*//' contain when you execute a script which completes normally$ 4)$R467777&normal8successful completion #&-ormal completion +&-( )&#lank 4tring *&/%!IIII=-o exception 1I&To enhance the access of a data warehouse fact table that contains data accessed by months,data warehouse administrator has decided to partition the table based on date #ased on the above scenario,when table partitioning technique do you recommend$ %&/# %/T3T3- based on date #&34T %/T3T3- based on date C)R4%9 "4RT#T#$% ased on date )&4*/33R*) %/T3T3- based on date *&+'43T* %/T3T3- based on date 11&Bhich pair of collection types can both be created and stored in database tables$ 4)%ested Tales and 5arra3s #&%ssociative %rrays and -ested Tables +&%ssociative %rrays and complex %rrays )&'ultidimensional %rrays and -ested Tables *&%ssociative %rrays and 5arrays 17&0ou intend to add a statement in a code block to provide a buffer for use with the )#'42(T(T statements The code will provide the buffer si"e in the following form= )#'42(T(T.*-%#*6buffer2si"e&< where buffer2si"e will be replaced with a positive integer value #ased on the scenario above,what is the maximum buffer si"e that can be set in bytes by replacing buffer2si"e with an appropriate integer value$ %&7,III #&G,III +&1I,III +)1778777 *&1,III,III
1:&
)*+%/* H -('#*/< max2num -('#*/=>1I< number2error *H+*T3-< 8(-+T3- get2number6anl -('#*/&/*T(/- -('#*/ 34 y -('#*/< #*3y => anl PG 38 y @ max2num T;*/%34* number2error< *-) 38< /*T(/- y< *-)< #*3x => get2numbet6G&< )#'42(T(T.(T23-*6-56x,-(&&< *H+*T3B;*- number2error T;*)#'42(T(T.(T23-*6J-ebr *rror&< B;*- rogram2error T;*)#'42(T(T.(T23-*6rogarm *rror&< B;*- T;*/4 T;*)#'42(T(T.(T23-*6thers error&< *-)<
#ased on the sample code above ,what is the result when you execute the code$ %&The code does not compile #&1A is printed +&rogram *rror is printed +)%umer rror is "rinted *&thers *rror is rinted 1G&+/*T* T%#* T+242T#63) -('#*/64& -T -(, +2)3T -('#*/64& -T -(, 42)4+ 5%/+;%/6AG& -(& )ata in the table T+242T#, 3) 42)3T 42)4+ S !!!!!!!!!!!! !!!!!!!!!!!!! 1 11 7 7 17 99 : 1: 1 #ased on the table in the sample code above,which 4D 4**+T statement performs an implicit conversion$ %&4**+T P 8/' T+242T# B;*/* 3)>1 %-) 42)3T>1 #& 4**+T P 8/' T+2+2T# B;*/* 42)4+>1
+&4**+T P 8/' T+242T# B;*/* 3)>1 )&4**+T P 8/' T+242T# B;*/* 42)4+>1 *&4**+T P 8/' T+242T# B;*/* 42)3T>1 1M&#*3?8/ 4tatement@ )#'42(T(T.(T23-*6n&< *-) < *-)< #ased on the sample code above and given that 4*/5*/(T(T is set -,which set of J8or statementsLprints the integers from 1I to 1 in descending order$ %&8/ n 3- /*5*/4* 1..1I #&8/ n 3- 1I..1 4T* !1 +&8/ n 3- 1..1I /*5*/4* +)($R n #% R5R' 17..1 -$$" *&8// n 3- 1..1I 4T* !1 1A&Bhich statement causes an effort when you execute it$ %&/#%+< #&+''3T B/< C)'T TR4%'4CT#$% %4: ;TR4%'<71;= )&4%5*3-T 4I1< *&+''3T ()%T*< 1O&+/*T%* / /*%+* %+%* my2pkg %4 /+*)(/* ist2'easurements6n0ards -('#*/&< *-) my2pkg< iven the package specification in the code above ,which argument type is nyards$ %&3t depends on the declaration of ist2'easurements6& in the package body #&3- (T +&(T +)#% *&(T 31Q&Bhen executing 4D from E4D,the E4D *ngine interacts with %&)atabase 4erver B)',> ngine +&rocedural statement *xecutor )&istener *&4D rocessor 19&Bhich text snipper of the *H%3- %- output for an 4D statement indicates that a 4**+T )34T3-+T is being done in the 4D being executed$ %&4/T /)*/ #0 #&4/T /( #0 +&4/T %/*%T* +)'$RT 0%#,0 *&4/T C37I&+/*T* T/3*/ stage2trigger %8T*/ ()%T* - container 8/ *%+; /B )*+%/* v2id -('#*/< +(/4/ +1 34 4**+T ps.id,ps.container,ps.fframe,ps.lframe 8/' project2status ps
B;*/* ps.container > = new.container< c1rec c1N/BT0*< #*338 ()%T3- T;*38 =new.case>TB*/T;**- c1< 8*T+; c1 3-T c1rec< v2id=>c1res.id< 6etc.& iven that the above sample code fragment is complete,you describe the behavior of the code fragment as= %&an insert trigger that is invoked before the insert #&an instead of trigger that is invoked after the update C)ro level trigger that is invo?ed after the update )&a complex trigger that is invoked after the update *&a row level trigger that is invoked before the update 71&Bhat two options do you use with the - +''3T clause when creating a #% T*'/%/0 T%#*$ %&/*4*/5*/ /B4 and )**T* /B4 #&** /B4 and T/(-+%T* /B4 C)"R'#'T R$/' and TR0%C4T R$/' )&** /B4 and (/* /B4 *&*/434T /B4 and )**T* /B4 77&+/*T%* / /*%+* 8(-+T3- *T)%T* /*T(/- 5%/+;%/7 %4 #*3/*T(/- 404)%T*< *-)< #ased on the sample code above and assuming that the -42)%T*28/'%T is set the default date locale and -42T*//3T/0 locale is set to %'*/3+%,which 4D statement executes successfully! assuming the current date is %((4T 19,7I1I$ %&4**+T 0*48/' )(% B;*/* *T)%T*6& > 19.IQ.1I< #&4**+T 0*48/' )(% B;*/* *T)%T*6& > 19!%(!1I< C)'-CT ;@';(R$: +04- /AR 9T+4T) ;7D.1E.17;= )&4**+T 0*48/' )(% B;*/* *T)%T*6& > IQ197I1I< *&4**+T 0*48/' )(% B;*/* *T)%T*6& > 19IQ7I1I< 7:&Bhich command do you execute to enable 4D trace for the current session$ %&%T*/ 4*443- *-%#* 4D2T/%+* #&%T*/ 4*443- 4*T 4D2T/%+*>4;B +&%T*/ 4*443- 4*T 4D2T/%+*>T/(* )&%T*/ 4*443- *-%#* 4D2T/%+* )4-TR '''#$% ',-
+&** )&/%%T* *&--)*T*/'3-34T3+ 7A&0ou wish to have the following code assign the next sequence value to seq for the 3) to be added to table product2tab. +/*%T* T%#* product2tab63) 3-T,)*4+ 5%/+;%/6MI&&< +/*%T* 4*D(*-+* product2seq< )*+%/* seq -('#*/< #*3!!%dd code below to %ssign the sq in the next line )#'42(T(T.(T23-*6%dding UUT2+;%/6seq&&< 3-4*/T 3-T product2tab 5%(*46seq,+;**4*&< *-)< #ased on the scenario above,which line do you add after the commented line to achieve your objective$ 4)seq & procedure procedure2seq.-*HT4*D< +&seq => procedure2seq.-*HT24*D< )&seq => procedure2seq.-*HT25%(*< *&seq => procedure2seq.-*HT24*D(*-+*< 7O&The concept of overloading within a package includes having= 4):ore than one procedure defined ith the same name and a different numer or t3pe of parameters #&multiple cursors defined in a single module +&more than one function defined with the same name and same parameters )&more than one procedure defined with the same number of parameters but different names *&more than one procedure defined with multiple cursors. 7Q&Bhich cursor attribute provides you with the number of records affected by the last )' statement$ %&4DN*/// #&4DN8(-) +&4DN-('2/B4 +)',-R$/C$0%T *&4DN+(-T 79&5alues in #H2/+T2T%# 3) !!! 1II 1I1 1I7
/*+*3T2)T !!!!!!!!!!!!!!!!!!!!! I9!C%-!9Q IQ!C%-!9Q I9!C%-!9Q
)*+%/* v2id -umber67&< #*34**+T id 3-T v2id 8/' box2rcpt2tab B;*/* id>1IM<
*-)< E Bhich exception is raised when you execute the code block in the scenario above$ 4)%$<+4T4<($0%+ #&5%(*2*/// +&)(25%2-23-)*H )&3-5%3)2-('#*/ *&4T/%*2*///
