Capabilities of SELECT statement The 3 main tasks of a SELECT command is: 1
Selection: Selection: restricti restricting ng the no of rows to be displayed displayed in in the result. result.
!ro"ection: !ro"ection: restrictin restricting g the no of columns columns to be shown shown in in the the result. result.
3
#oins: #oins: combining combining the columns columns of multipl multiplee tables tables and showing showing them them as a single single result. result.
Synta$ of the SELECT command: SELECT %column list& ' ( )*+, %tablename&*ules and regulations to be followed to enter an SL command: 1
SL SL is is not not cas case/ e/se sens nsit iti0 i0e. e.
s per the standar standards2 ds2 the keywords keywords must must be typed in in uppercase uppercase and and other other informati information on in lowercase.
3
Each clause clause mus mustt be entered entered on separ separate ate lines. lines.
n SL statement is made up of many clauses. clause is a keyword followed by the reuired information. SELECT employeeid )*+, employees4
!roper !roper align alignmen mentt should should be maintai maintained ned for for better better readabi readabili lity ty..
+uter uery 5 6 7nner uery 8
9
Each Each SL SL sta state teme ment nt mus mustt end end wit with ha-
rithmetic +perator
is used perform mathematical calculations dd Subtract / ;i0ide < ,ultiply (
=ou =ou can use an arithmetic e$pression in the SELECT clause. >rite >rite a uery to show employee id2 last name2 salary and a re0ised salary of 1??@ raise for all employees. SELECT employeeAid2lastAname2salary2 employeeAid2lastAname2salary2salary1?? salary1?? )*+, employees-
To rename the column heading2 use u se an lias n lias is a short name gi0en to a column or to a table.
SL& SELECT employeeAid S BEmployee CodeB2 lastAname S BEmployee ameB2 salary S BDasic SalaryB2 salary1?? S B*e0ised SalaryB 3 )*+, employees-
9
Each Each SL SL sta state teme ment nt mus mustt end end wit with ha-
rithmetic +perator
is used perform mathematical calculations dd Subtract / ;i0ide < ,ultiply (
=ou =ou can use an arithmetic e$pression in the SELECT clause. >rite >rite a uery to show employee id2 last name2 salary and a re0ised salary of 1??@ raise for all employees. SELECT employeeAid2lastAname2salary2 employeeAid2lastAname2salary2salary1?? salary1?? )*+, employees-
To rename the column heading2 use u se an lias n lias is a short name gi0en to a column or to a table.
SL& SELECT employeeAid S BEmployee CodeB2 lastAname S BEmployee ameB2 salary S BDasic SalaryB2 salary1?? S B*e0ised SalaryB 3 )*+, employees-
n alias name should be enclosed within double uotes if: 1
it holds a space
you want want to to use the the same same case case how how it was was typed typed in in the scri script pt
+TE: in an arithmetic e$pression ha0ing all the 4 operations2 the di0ision and multiplication multiplication ha0e more precedence than addition and subtraction. To o0erride the order of precedence2 enclose the e$pression within parentheses 68.
E$ample: >rite >rite a uery to display employee id2 last name2 salary2 re0ised salary of 1??@ raise and the annual re0ised salary of all employees.
SELECT employeeAid2 lastAname2 salary2 salary1?? S S *e0ised2 6salary1??8(1 S nnualSalary )*+, employees-
>orking with LL 0alues: LL is means means no 0alue. 7t cannot be compared with Fero or empty string.
ote: 7n an arithmetic e$pression ha0ing null 0alue2 the result of the e$pression will will also be null.
1??(null 5 null
+racle has a default table called as dual
)or E$ample:
+TE: oracle commands can be abbre0iated. E$ample: conn for connect2 desc for describe comamnd
>rite a uery to display employee id2 last name2 salary2 commission2 and net salary of employees SELECT employeeAid2 lastAname2 salary2 salary1?? S S *e0ised2 6salary1??8(1 S nnualSalary )*+, employees-
SL& SELECT employeeAid2 lastAname2 salary2 commissionApct2 commissionApct2 salary 6salary(commissionApct8 S netsalary )*+, employees-
C+CTET7+ +!E*T+*: Combining the result of two or more columns and displaying them as a single column. This operator is introduced by ''
+TE: character and date 0alue should be enclosed within single uote. SL& SELECT firstAname '' G G '' lastAname S B)ull ameB )*+, employees-
SL& SELECT GThe Employee G '' firstAname '' G G '' lastAname '' G who works in G '' departmentAid '' G was hired on G '' hireAdate S message )*+, employees-
uote +perator 6 +perator8 is used to o0erride the special meaning of single uotes. Synta$: GH"ohnGsIG G#ohnGs salary isG SL& SELECT lastAname '' GHGs G HGs salary isIG '' salary S message )*+, employees-
To remo0e remo0e duplicate 0alue from a column: SL& SELECT ;7ST7CT "obAid )*+, employees;7ST7CT keyword is used to remo0e duplicate 0alues and show only uniue ones.
Comparison < *elational +perator They are used in comparisons and the result of the comparison will be either T*E or )LSE. % Less than & Jreater than %5 Less than or eual to &5 Jreater than or eual to
5 Eual to %& orK 5 or 5
SELECT %column list& ' ( )*+, %table& H>ME*E %condition&I
The >ME*E is an optional clause that is used to limit the no of rows to be shown.
E$ample: 1
>rite >rite a uery to show show employee employeess whose whose salary salary is greate greaterr than than 14???@ 14???@
SL& SELECT employeeAid2 lastAname2 salary )*+, employees 3 >ME*E salary&14???-
>rite >rite a uery uery to to show show employ employees ees who who are are not 7T 7T !rogr !rogramm ammers ers..
SL& SELECT employeeAid2 lastAname2 "obAid2 salary )*+, employees 3 >ME*E "obAid 5 G7TA!*+JG-
+TE: The Character 0alue is case/sensiti0e case/sensiti0e SL& SELECT lastAname2 salary2 firstAname firstAname
)*+, employees 3 >ME*E firstAname 5 G"ohnGno rows selected SL& SELECT lastAname2 salary2 firstAname firstAname )*+, employees 3 >ME*E firstAname 5 G#ohnGLSTA,E SL*= )7*STA,E ///////////////////////// ////////// //////////////////// Chen N?? #ohn Seo O?? #ohn *ussell 14??? #ohn
ote: The date 0alue is )ormat Sensiti0e The +racle default date format is ;;/,+/== SL& SELECT employeeAid2 lastAname2 salary )*+, employees 3 >ME*E hireAdate5G1O/,*/?OGE,!L+=EEA7; LSTA,E SL*= /////////// ///////////////////////// ////////// 1P9 #ones N?? SL& SELECT employeeAid2 lastAname2 salary )*+, employees
3 >ME*E hireAdate5G??OME*E hireAdate5G??O
L+J7CL +perators They are used to "oin two conditions together: Types: 1
;
+*
3
+ T
)or E$amples: >rite a uery to show employees whose salary is greater than N??? and who were hired before ??O.
SL& SELECT employeeAid2 lastAname2 hireAdate2 salary )*+, employees 3 >ME*E hireAdate%G1/#/?OG ; salary & N???-
>rite a uery to display employees belonging to department 1?2 ?2 and 9?.
SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees 3 >ME*E departmentAid51? +* departmentAid5? +* departmentAid59?-
;isplay employees whose salary is between 19?? and 3??? SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees 3 >ME*E salary &5 19?? ; salary %5 3??? +T: used to negate the e$pression
+ther comparison operators: 7: to pro0ide alternate 0alues. 7t is an alternate to Logical +* SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees >ME*E departmentAid 761?2?29?8-
SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees >ME*E departmentAid +T 761?2?29?8-
DET>EE: is used to pro0ide a range. 7t can be a replacement of Logical ;.
SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees >ME*E salary DET>EE 19?? ; 3???-
SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees >ME*E salary +T DET>EE 19?? ; 3???-
L7RE: used to pro0ide a pattern matching. +racle pro0ides wildcard characters for pattern matching is used to ignore any no of characters A is used to ignore 1 character.
)or E$amples ;isplay employees whose first start begin with GG
SL& SELECT employeeAid2 lastAname2 departmentAid2 salary )*+, employees >ME*E salary DET>EE 19?? ; 3???E,!L+=EEA7; LSTA,E ;E!*T,ETA7; SL*= /////////// ///////////////////////// ///////////// ////////// 1PN +Connell 9? Q??
1PP Jrant 9? Q?? 11Q Daida 3? P?? 11O Tobias 3? N?? 11N Mimuro 3? Q?? 11P Colmenares 3? 9?? 1Q ,ikkilineni 9? O?? 1O Landry 9? 4?? 1N ,arkle 9? ?? 13? tkinson 9? N?? 131 ,arlow 9? 9?? E,!L+=EEA7; LSTA,E ;E!*T,ETA7; SL*= /////////// ///////////////////////// ///////////// ////////// 13 +lson 9? 1?? 134 *ogers 9? P?? 139 Jee 9? 4?? 13Q !hiltanker 9? ?? 13P Seo 9? O?? 14? !atel 9? 9?? 143 ,atos 9? Q?? 144 argas 9? 9?? 1N Sulli0an 9? 9?? 1N3 Jeoni 9? N??
1NO Cabrio 9? 3??? E,!L+=EEA7; LSTA,E ;E!*T,ETA7; SL*= /////////// ///////////////////////// ///////////// ////////// 1P? Jates 9? P?? 1P1 !erkins 9? 9?? 1P9 #ones 9? N?? 1PO )eeney 9? 3??? Q rows selected. SL& SL& SL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname L7RE GGSL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname +T L7RE GG-
E,!L+=EEA7; )7*STA,E SL*= /////////// //////////////////// ////////// 1?3 le$ander P??? 119 le$ander 31?? 11 dam N??
14O lberto 1??? 19N llan P??? 1QO mit Q?? 1O9 lyssa NN?? 1N9 le$is 41?? 1NO nthony 3??? 1PQ lana 31?? 1? rows selected. SL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname L7RE GaGE,!L+=EEA7; )7*STA,E SL*= /////////// //////////////////// ////////// 1?1 eena 1O??? 1?O ;iana 4?? 13 Shanta Q9?? 19 #ulia 3?? 1P Laura 33?? 14? #oshua 9?? 141 Trenna 39?? 1Q Clara 1?9?? 1Q4 ,attea O??
1QN Lisa 119?? 1O3 Sundita Q1?? E,!L+=EEA7; )7*STA,E SL*= /////////// //////////////////// ////////// 1O9 lyssa NN?? 1N ,artha 9?? 1N4 andita 4?? 1NQ #ulia 34?? 1PQ lana 31?? 1Q rows selected. SL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname L7RE GaGE,!L+=EEA7; )7*STA,E SL*= /////////// //////////////////// ////////// 1O9 lyssa NN?? 1PQ lana 31?? SL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname L7RE GAAaGE,!L+=EEA7; )7*STA,E SL*= /////////// //////////////////// //////////
1?O ;iana 4?? 11 dam N?? 13 Shanta Q9?? 1Q Clara 1?9?? 1OP Charles Q?? 1N1 #ean 31?? 1PQ lana 31?? O rows selected. SL& SELECT employeeAid2 firstAname2 salary )*+, employees 3 >ME*E firstAname 5 GGno rows selected
4
7S: used for LL comparison only.
;isplay employees who are not gi0en any commission: SL& SELECT employeeAid2 lastAname2 salary2 commissionApct )*+, employees 3 >ME*E commissionApct 7S LL-
SL& SELECT employeeAid2 lastAname2 salary2 commissionApct
)*+, employees 3 >ME*E commissionApct 7S +T LL-
+*;E* D= clause SELECT %column list& ' ( )*+, %table& >ME*E %condition& +*;E* D= %colname&+rder Dy clause is used to sort the resultset in ascending
rite a uery to display employee id2 last name and salary in the ascending order of their last name. SELECT employeeAid2 lastAname2 salary )*+, employees +*;E* D= lastAnameE$ample : ,odify the abo0e uery to show only those employees whose salary is in between 3??? and 9??? SELECT employeeAid2 lastAname2 salary )*+, employees >ME*E salary DET>EE 3??? ; 9??? +*;E* D= lastAname ote: =ou can sort the result using multiple columns E$ample 3: ,odify the abo0e uery to add department id and sort the information first in the ascending order of departments and then in the descending order of they salary.
SELECT employeeAid2 departmentAid2 lastAname2 salary )*+, employees +*;E* D= departmentAid SC2 salary ;ESC ote: the default ordering of +*;E* D= clause is scending. Substitution ariable: is used to hold a 0alue temporarily so that you can retrei0e it later for some calculations. This 0ariable name must begin with U sign. E$ample 4: SL& SELECT lastAname2 salary )*+, employees 3 >ME*E salary & Ubasic7f a substitution 0ariable begin with U sign then its life is only as long as the uery is running. 7f a substitution 0ariable begin with UU sign then its life is as long as the session is acti0e. The moment the session is e$pired the memory for this 0ariable will be released. E$ample 9: SL& SELECT lastAname2 salary )*+, employees 3 >ME*E salary & UUbasicusing ;E)7E command: This command is used to declare a 0ariable. This also a session/specific 0ariable. Synta$: ;E)7E %0ariablename&50alue E$ample Q: SL& ;E)7E sal59??? SL& SELECT employeeAid2 salary )*+, employees 3 >ME*E salary 5 UsalSL& ;E)7E sal
The ;E)7E command will release the memory of the 0ariable declared using the ;E)7E command. )CT7+S ///////////////// +racle ha0e mainly categories of functions: 1. Single *ow )unction pplies on each rows independantly and gi0es one result per row. . ,ultiple *ow )unction 6Jroup )unction8 pplies on each group of rows and gi0es one result per group Single *ow )unctions: Types: 1. Character )unction . umber )unction 3. ;ate )unction 4. Con0ersion )unction 9. Jeneral )unction Character )unction ///////////////////////////// character function passes a character 0alue as a parameter but can return both character or numeric 0alues. Types: 1. CSE/,7!LT7+ a8 !!E*68 : con0ert the character 0alue into upper case b8 L+>E*68 : con0ert the character 0alue into lower case c8 77TC!68 : capitaliFes the first letter of each word E$ample O: SL& SELECT upper6lastAname82 lower6lastAname82 initcap6lastAname8 )*+, employeesE$ample N: SL& SELECT employeeAid2 lastAname2 firstAname )*+, employees 3 >ME*E upper6firstAname85G#+MG-
E,!L+=EEA7; LSTA,E )7*STA,E /////////// ///////////////////////// //////////////////// 11? Chen #ohn 13P Seo #ohn 149 *ussell #ohn . CM*CTE*/,7!LT7+ a8 C+CT68 used to "oin two character 0alues together. E$ample P: SL& SELECT concat6G#ohnG2GSmithG8 )*+, dualC+CT6G# ///////// #ohnSmith E$ample 1?: SL& SELECT concat6G#ohnG2concat6G G2GSmithG88 )*+, dualC+CT6G#+ ////////// #ohn Smith E$ample 11: SL& SELECT concat6firstAname2 concat6G G2lastAname88 S )ullame )*+, employees ester function is a function within another function.
b8 SDST*68 E$tracts a string of determined length. E$ample 1: SL& SELECT substr6firstAname21238 S code )*+, employeesThe first parameter indicate the column name the second parameter indicate the start position
the third parameter indicate the total no of characters to be e$tracted from the gi0en start position. E$ample 13: SL& SELECT upper6substr6firstAname2 12 388 )*+, employeesc8 LEJTM68 return the length the gi0en string. E$ample 14: SL& SELECT firstAname2 length6firstAname8 S totalAchar )*+, employeesd8 L!;68 and *!;68 L!;68 returns an e$presion left/padded to the length of n characters with a character e$pression. *!;68 returns an e$presion right/padded to the length of n characters with a character e$pression. E$ample 19: SL& SELECT rpad6salary21?2GVG8 )*+, employeese8 *E!LCE68 *eplaces a part of the te$t with the gi0en te$t. E$ample 1Q: SL& SELECT replace6G#ack and #ueG2G#G2GDlG8 )*+, dual*E!LCE6G#CR ////////////// Dlack and Dlue
f8 T*7,68 To remo0e the leading and trailing spaces E$ample 1O: SL& SELECT trim6G )*+, dual-
>elcome to +racle
G8 S message
,ESSJE ///////////////// >elcome to +racle
8 umber )unctions: These functions passes numeric 0alues as parameters and return numberic 0alue a8 *+;68 *ounds the decimal places to the nearest number b8 T*C68 Truncates the decimal numbers to the gi0en decimal place c8 ,+;68 *eturns the remainder of a di0ision E$ample 1N: SL& SELECT round6P3.9Q328 )*+, dual*+;6P3.9Q328 /////////////// P3.9Q E$ample 1P: SL& SELECT round6P3.9Q3218 )*+, dual*+;6P3.9Q3218 /////////////// P3.Q E$ample ?: SL& SELECT trunc6P3.9Q3218 )*+, dualT*C6P3.9Q3218 /////////////// P3.9 E$ample ?: SL& SELECT mod61Q??2??8 )*+, dual,+;61Q??2??8
///////////// ? E$ample 1: SL& SELECT mod6428 )*+, dual ,+;6428 ////////// ? E$ample : SL& SELECT mod6328 )*+, dual ,+;6328 ////////// 1 !seudocolumn S=S;TE is used to return the system date 38 ;ate68 These functions passes a date 0alue as a parameter and return the date or numeric 0alue E$ample 3: SL& SELECT sysdate )*+, dualS=S;TE ///////// ?N/SE!/19 E$ample 4: SL& SELECT systimestamp )*+, dualS=ST7,EST,! /////////////////////////////////////////////////////////////////////////// ?N/SE!/19 ?1.?P.43.N9134P !, ?3:?? SL& ote: =ou can perform arithmetic calculation with date 0alues.
E$ample 9: SL& SELECT employeeAid2 lastAname2 hireAdate2 sysdate/hireAdate S totalAdays )*+, employeesE$ample Q: SL& SELECT employeeAid2 lastAname2 hireAdate2 round6sysdate/hireAdate8 S totalAdays )*+, employeesE$ample O: SL& SELECT employeeAid2 lastAname2 hireAdate2 round6sysdate/hireAdate8 S totalAdays2 *+;66sysdate/hireAdate8eeks 3 )*+, employees,+TMSADET>EE68 used to return total no of months between the gi0en dates SL& SELECT employeeAid2 lastAname2 hireAdate2 round6sysdate/hireAdate8 S totalAdays2 *+;66sysdate/hireAdate8eeks2 monthsAbetween6sysdate2 hireAdate8 S 3 TotalA,onths 4 )*+, employees EWTA;=68 : this function will return the date of the first gi0en weekday E$ample N: SL& SELECT employeeAid2 lastAname2 hireAdate2 ne$tAday6hireAdate2GSundayG8 )*+, employees-
LSTA;=68 : returns the last date of the current month E$ample P: SL& SELECT lastAday6sysdate8 )*+, dualLSTA;=6 ///////// 3?/SE!/19
48 Con0ersion )unction: is used to con0ert the 0alue from one type to another. The con0ersion is performed by oracle in two methods: a8 7mplicit Con0ersion
Mere oracle will con0ert the 0alue from one type to another automatically.
E$ample 3?: SL& SELECT G1?G G?G )*+, dual G1?GG?G ////////// 3? E$ample 31: SL& SELECT employeeAid2 lastAname )*+, employees 3 >ME*E hireAdate5G?1/#L/?QGE,!L+=EEA7; LSTA,E /////////// ///////////////////////// 1P4 ,cCain
b8 E$plicit Con0ersion Mere you must use a function to do the con0ersion. i8 T+ACM*68 7t con0erts the gi0en 0alue to a character type. 7t is used to format the output. E$ample 3: SL& SELECT employeeAid2 lastAname2 toAchar6hireAdate2G==== < ,, < ;; MM:,7:SS ,G8 S hiredate )*+, employeesE$ample 33: SL& SELECT toAchar6sysdate2G,onth ;; ==== ;ay MM4:,7:SSG8 S today )*+, dualT+;= //////////////////////////////////// September ?N ?19 Tuesday 13:3?:4 E$ample 34:
SL& SELECT toAchar6sysdate2G,on ;; ==== ;y MM:,7:SS ,G8 S Today )*+, dualT+;= /////////////////////////// Sep ?N ?19 Tue ?1:31:4 !, E$ample 39: SL& SELECT toAchar6sysdate2Gfm;dspth BofB ,onth ==== MM:,7:SS ,G8 )*+, dualT+ACM*6S=S;TE2G),;;S!TMB+)B,+TM====MM:,7: //////////////////////////////////////////// Eighth of September ?19 1:3:91 !, E$ample: ToAchar with umeric type SL& SELECT employeeAid2 lastAname2 departmentAid2 toAchar6salary2G@PP2PPP.??G8 S salary )*+, employeesii8 T+A;TE68 : used to con0ert the character 0alue holding date of any format to a 0alid date 0alue. 7mplicit Con0ersion SL& SELECT employeeAid2 lastAname2 departmentAid2 hireAdate )*+, employees 3 >ME*E hireAdate & G31/;EC/??OGThe below uery will gi0e error as the format of date is not correct: SL& SELECT employeeAid2 lastAname2 departmentAid2 hireAdate )*+, employees 3 >ME*E hireAdate & G1<31<??OG,odify the abo0e uery by using T+A;TE68 SL& SELECT employeeAid2 lastAname2 departmentAid2 hireAdate )*+, employees 3 >ME*E hireAdate & T+A;TE6G1<31<??OG2G,,<;;<====G8iii8 T+A,DE*68: used to con0ert the character to a date 0alue. SL& SELECT G1?G ( GG )*+, dual-
G1?G(GG ////////// ? SL& SELECT toAnumber6G1?G8 ( toAnumber6GG8 )*+, dualT+A,DE*6G1?G8(T+A,DE*6GG8 ////////////////////////////// ?
9. JEE*L )unction: They can be applied on any type of columns: Types: a8 L68 it works with null. 7t is used to con0ert the null 0alue to a gi0en 0alue before performing the calculation. E$ample: >rite a uery to display employee id2last name2 department id2 salary2 commission and net salary of an emlployee. SELECT employeeAid2 lastAname2 departmentAid2 salary2 commissionApct2 salary 6salary(L6commissionApct2?88 S et )*+, employees b8 L68 This function also works with null 0alues. 7t is an e$tended 0ersion of L68 E$ample: >rite a uery to display employee id2 last name2 salary2 commission and income of an employee. The income should show BSalary +nlyB if the employee do not earn any commission. Else it should show BSalary and CommissionB as income. SL& SELECT employeeAid2 lastAname2 salary2 commissionApct2 L6commissionApct2GSalary and CommissionG2GSalary +nlyG8 S 7ncome )*+, employeesc8 LL7)68 : works with null 0alues.
7f compares both the parameters passed and returns LL if both them matches e lse it will return the first parameter. SL& SELECT nullif6G+racleG2GoracleG8 from dual LL7) ////// +racle SL& SELECT nullif6G+racleG2G+racleG8 from dual LL7) ////// d8 C+LESCE68 it operates on null 0alues =ou can pass any no of parameters to this function. SELECT coalesce6null2null2null2null2null2234249292null8 from duale8 7) TME ELSE construct i8 CSE EW!*ESS7+ ii ;EC+;E68
Synta$ of CSE e$pression: CSE %e$pr& >ME %condition& TME %0alue& >ME %condition& TME %0alue& >ME %condition& TME %0alue& ELSE %0alue& E; E$ample: >rite a uery to display employee id2 lastAname2 "obid2 salary2 and bonus of the employee. The Criteria to calculate the bonus is: 7f "obAid 5 7TA!*+J2 Donus549 of Salary 7f "obAid5G)7ACC+TG2 Donus54? Salary 7f "obAid5GSA*E!G2 Donus539 of Salary
)or all other employees bonus5? of salary SELECT employeeAid2 lastAname2 "obAid2 salary2 CSE "obAid >ME G7TA!*+JG TME salary(?.49 >ME G)7ACC+TG TME salary(?.4 >ME GSA*E!G TME salary(?.39 ELSE salary(?. E; S Donus )*+, employees>rite a uery to display employee id2 lastAname2 "obid2 salary2 and bonus of the employee. The Criteria to calculate the bonus is: 7f salary % 19??2 Donus5? 7f salary is between 19?? and 3???2 Donus59 of salary if salary is between 3??? and 9???2 Donus549 of salary else bonus59? of salary
SELECT employeeAid2 lastAname2 "obAid2 salary2 CSE >ME salary%19?? TME ? >ME salary DET>EE 19?? ; 3??? TME salary(?.9 >ME salary DET>EE 3??? ; 9??? TME salary(?.49 ELSE salary(?.9 E; S Donus )*+, employees-
decode68 is an alternate to CSE e$pression >rite a uery to display employee id2 lastAname2 "obid2 salary2 and bonus of the employee. The Criteria to calculate the bonus is: 7f "obAid 5 7TA!*+J2 Donus549 of Salary 7f "obAid5G)7ACC+TG2 Donus54? Salary 7f "obAid5GSA*E!G2 Donus539 of Salary )or all other employees bonus5? of salary SELECT employeeAid2 lastAname2 "obAid2 salary2 decode6"obAid2G7TA!*+JG2salary(?.492G)7ACC+TG2salary(?.42GSA*E!G2salary(?.392salary( ?.8 S Donus
)*+, employeesJroup )unction: +perates on each group and gi0es one result per group: Types: 1. S,68 : get the sum of range of 0alues . C+T68 : get the total count of 0alues in the range 3. ,768 : get the lowest 0alue from range 4. ,W68 : get the highest 0alue from the range 9. J68 : get the a0erage 0alue from the range of 0alues. Dy default the entire table will be considered as one group. Jroup )unction ignores null 0alues: SL& SELECT sum6salary8 S TotASal2 min6salary8 S ,inASal2 ma$6salary8 S ma$Asal2 a0g6salary8 S a0gAsal2 count6salary8 S totAsal 3 )*+, employeesT+TASL ,7ASL ,WASL JASL ////////// ////////// ////////// ////////// ////////// QP191Q 1?? 41?? Q4Q.OQQ3Q 1?O
T+TASL
SL& SELECT count6commissionApct8 )*+, employeesC+T6C+,,7SS7+A!CT8 ///////////////////// 39
J*+! D= clause used to form groups based on a column while using group function. >rite a uery to display department/wise total salary gi0en SL& SELECT departmentAid2 sum6salary8 )*+, employees 3 J*+! D= departmentAidsing +*;E* D= SL& SELECT departmentAid2 sum6salary8 )*+, employees
3 J*+! D= departmentAid 4 +*;E* D= sum6salary8 ;ESC,odify the abo0e uery to remo0e the department 1??2 1?1 and null SL& SELECT departmentAid2 sum6salary8 )*+, employees 3 >ME*E departmentAid +T 761??21?18 ; departmentAid 7S +T LL 4 J*+! D= departmentAid 9 +*;E* D= sum6salary8 ;ESC,odify the abo0e uestion to show only those departments whose total salary is greater than 19??? SL& SELECT departmentAid2 sum6salary8 )*+, employees 3 >ME*E departmentAid +T 761??21?18 ; departmentAid 7S +T LL 4 J*+! D= departmentAid M7J sum6salary8 &19??? 9 +*;E* D= sum6salary8 ;ESC ote: =ou can also group the result based on multiple columns: ;isplay department/wise total no of employees for each "obs. SL& SELECT departmentAid2 "obAid2 count6employeeAid8 )*+, employees 3 J*+! D= departmentAid2 "obAid 4 +*;E* D= departmentAid2 "obAid-
Sub ueries and #oins ///////////////////////////////// Sub uery is used to get result of one table based on the 0alue of some other table. Synta$: +uter uery 5 6 7nner uery 6Sub uery8
8 )or E$ample: ;isplay employees of Shipping department SELECT employeeAid2 lastAname2 salary2 departmentAid )*+, employees >ME*E departmentAid 5 6 SELECT departmentAid )*+, departments >ME*E departmentAname5GShippingG 8 E$ample: >rite a uery to display employees belonging the city Seattle SELECT employeeAid2 lastAname2 salary2 departmentAid )*+, employees >ME*E departmentAid 7 6 SELECT departmentAid )*+, departments >ME*E locationAid5 6 SELECT locationAid )*+, locations >ME*E city5GSeattleG 8 8 Types: subuery is of two types: 1. Single row subuery Mere the inner uery returns only one row 0alue to its parent uery Mere you can use 5 operator . multiple row subuery Mere the inner uery returns more than one row 0alue to its parent uery Mere you cannot use 5 operator
#oins: helps you to combne columns of two or many tables and display them as a single resultset.
Types: 1. T*L #+7 =ou can perform "oins between two tables only if they ha0e common columns. 7n atural #oin2 oracle will take the common column to be used for "oining. Synta$: SELECT %columns of two tables& )*+, %table1& T*L #+7 %table&)or E$ample >rite a uery to show employee id2 last name2 department id and department name for all employees SL& SELECT employeeAid2 lastAname2 departmentAid2 departmentAname )*+, employees T*L #+7 departments*estriction: =ou can perform natural "oin between two tables only of the common columns of both table matches in terms of their data type as well as their name.
. #oining with S7J keyword Sed to instruct oracle2 what common column to be used for "oining. Synta$: SELECT a.employeeAid2 a.firstAname2 b.departmentAname2 departmentAid )*+, employees a #+7 departments b S7J6departmentAid8 3. #oining with + keyword lso used to instruct oracle what common column to be used for "oining SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname2 loc.city )*+, employees emp #+7 departments dep + emp.departmentAid5dep.departmentAid #+7 locations loc + loc.locationAid5dep.locationAid4. SEL) #+7 #oining a table to itslef is called as self "oin.
>rite a uery to show the employee name and his manager name. SL& SELECT emp.firstAname S employee2 mgr.firstAname S manager )*+, employees emp #+7 employees mgr 3 + emp.managerAid5mgr.employeeAid 4 +*;E* D= emp.firstAname9. +TE* #+7 This type of "oin can show unmatched records also Types: a8 Left +uter #oin b8 *ight +uter #oin c8 )ull +uter #oin SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname )*+, employees emp LE)T +TE* #+7 departments dep + emp.departmentAid5dep.departmentAid-
SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname )*+, employees emp *7JMT +TE* #+7 departments dep + emp.departmentAid5dep.departmentAid-
SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname )*+, employees emp )LL +TE* #+7 departments dep + emp.departmentAid5dep.departmentAidQ. C*+SS #+7 is also called as Cartesian product of two tables Table 5 9 rows Table D 5 1? rows Table C*+SS #+7 Table D 5 9 ( 1? 5 9? rows SL& SELECT employeeAid2 lastAname2 departmentAname )*+, employees C*+SS #+7 departmentsSub uery ////////////////// 7 or = can be used if the inner uery returns more than one row of 0alue.
There are 3 types of = 1. %= means less than the ma$imum . &= means more than the minimum 3. 5= is same as 7 operator E$ample: >rite a uery to show employee id2 last name2 "ob id2 and salary of all employees whose salary is less than the salary of all employees with a "ob id of 7TA!*+J. SL& SELECT employeeAid2 lastAname2 "obAid2 salary )*+, employees 3 >ME*E salary %= 4 6 9 SELECT salary Q )*+, employees O >ME*E "obAid5G7TA!*+JG N 8Eui #oin and on/Eui #oin //////////////////////////////////////// SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname )*+, employees emp #+7 departments dep + emp.departmentAid 5 dep.departmentAid-
on/Eui "oin: here the common columns of two tables are "oined using non/euality comparison operator such as %2 &2 DET>EE2 etc. ;isplay employee id2 last name and grade of an employees for only those employees whose salary is within the min and ma$ salary of their grade. SL& SELECT emp.employeeAid2 emp.lastAname2 emp.salary2grd.gradeid )*+, employees emp #+7 grades grd 3 + emp.salary DET>EE grd.minsal ; grd.ma$salSET +!E*T+*S used to combine the result of two or more tables and display as a single resultset. SELECT col42 col2 col3 )*+, table1
%SET +!E*T+* T=!E& SELECT col42 col2 col3)*+, table
Juidelines to be followed to use SET operators 1. The no of columns of both "oined tables should match. . The order of columns in the first uery should match with the second uery 3. The names of the supplied columns can differ Types: 1. 7+ : returns rows from both the ueries after eliminating the dup lications. SL& select ( from testaW= ////////// //////////////////////////////////////// 1 D 3C SL& select ( from testbD C ////////// ///////// //////////////////////////////////////// 1 ?P/SE!/19 ?P/SE!/19 D 9 ?P/SE!/19 ) Q ?P/SE!/19 M SL& SELECT $2y )*+, testa 7+ 3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D 3C 9) QM
SL& C SL& SELECT $2y )*+, testa 7+ LL 3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D 3C 1 D 9) QM O rows selected. b8 7TE*SECT shows only common rows of two tables remo0ing the distinct rows SL& SL& SELECT $2y )*+, testa 7TE*SECT 3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D c8 ,7S: it displays all the rows of first uery that are not a0ailable in the second uery. SL& select ( from testaW= ////////// //////////////////////////////////////// 1 D 3C
SL& select ( from testbD C ////////// ///////// //////////////////////////////////////// 1 ?P/SE!/19 ?P/SE!/19 D 9 ?P/SE!/19 ) Q ?P/SE!/19 M SL& SELECT a2c )*+, testb ,7S 3 SELECT $2y )*+, testaC ////////// //////////////////////////////////////// 9) QM
;atabase +b"ects //////////////////////// 1. Table . iew 3. Seuence 4. Synonym 9. 7nde$ Table: is used to store data permanently in rows and column format. Synta$: C*ETE TDLE %tablename& 6 %colname& %datatype&6siFe82 %colname& datatype&6siFe8 8*ules to follow while naming a database ob"ect: 1. ,ust begin with a letter . De 1 to 3? characters long 3. Can contain /X2 a/F2 ?/P2 A2 @ and Y 4. The ob"ect name cannot be duplicated 9. 7t cannot be a reser0ed word
C*ETE TDLE employee 6 empCode CM*6982 empame *CM*64?82 "oin;ate ;TE2 salary ,DE*6N28 8To insert a new row: 7SE*T 7T+ employee LES6GE,!??1G2G!ETE*G2sysdate249??8+* 7SE*T 7T+ employee 6salary2empame2 empCode2 "oin;ate8 LES63???2G!eterG2GE,!?G2sysdate8To ignore the columns 7SE*T 7T+ employee 6empCode2 empame8 LES 6GE,!?3G2G#ohnG8>hen a column is ignored2oracle will place a LL to the ignored column. =ou can also ignore a column by e$plicitly pro0iding the null keyword. 7SE*T 7T+ employee LES6null2null2null2null8;ata ,anipulation Language 7SE*T ;ELETE !;TE
;ELETE command: used to remo0e one or more rows from a table. Synta$: ;ELETE )*+, %tablename& H>ME*E %condition&I;ELETE )*+, employee >ME*E empCode5GE,!?1G-
!;TE command: used to modify one or more column 0alue. !;TE %tablename& SET %colname&5%new 0al& H>ME*E %cond&I!;TE employee SET salary5salary1?? >ME*E empCode5GE,!?1G-
Constraint: is a rule applied on a column to restrint users from entering in0alid data. Types: 1. +T LL . CMECR 3. 7E 4. ;E)LT property 9. !*7,*= RE= Q. )+*E7J RE= +T LL: used to make a column reuire and cannot be ignored. C*ETE TDLE employee1 6 empCode CM*61?8 +T LL2 empame *CM*69?8 +T LL2 "oin;ate ;TE2 salary number6N28 8SL& desc employee1 ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!C+;E +T LL CM*61?8 E,!,E +T LL *CM*69?8 #+7;TE ;TE SL*= ,DE*6N28
SL& insert into employee1 6empCode2 "oindate2 salary8 0alues6GE,!??1G2sysdate23???8insert into employee1 6empCode2 "oindate2 salary8 ( E**+* at line 1: +*/?14??: cannot insert LL into 6BM*B.BE,!L+=EE1B.BE,!,EB8 ote: ny no of columns in a table can ha0e +T LL constraint. . CMECR is used to allow users to enter only specific 0alues as per the gi0en condition. C*ETE TDLE employee 6 empCode CM*61?8 +T LL2 empame *CM*64?8 +T LL2 gender CM*618 +T LL CMECR6gender 76G,G2G)G882 "oin;ate ;TE2 salary ,DE*6N28 8-
3. 7E: restrict the user from entering duplicate 0alues in a column2 C*ETE TDLE employee3 6 empCode CM*61?8 +T LL 7E2 empame *CM*64?8 +T LL2 gender CM*618 CMECR6gender 76G,G2G)G882 salary ,DE* 6N28 CMECR 6salary DET>EE 9?? and 9???82 "oindate ;TE2 Email *CM*698 7E 8SL& 7SE*T 7T+ employee3 LES6GUempcodeG2GUempameG2GUgenderG2Ubasic2GUhiredateG2GUemailG8-
4. ;E)LT property Dy default when a column is ignored2 oracle will place a null 0alue to that column. The default property is used to insert some other 0alue other than null when it is
ignored. 7t can be applied only to nullable columns. SL& C*ETE TDLE employee4 6 3 empCode CM*61?8 +T LL 7E2 4 empame *CM*64?8 +T LL2 9 "oindate ;TE ;E)LT sysdate2 Q gender CM*618 ;E)LT G,G CMECR 6gender 76G,G2G)G88 O 8SL& insert into employee4 6empCode2 empame8 LES 6GE,!??G2G!eterG81 row created. SL& select ( from employee4E,!C+;E E,!,E #+7;TE J ////////// //////////////////////////////////////// ///////// / E,!??1 ,ike 1?/#/19 , E,!?? !eter ?P/SE!/19 ,
4. !*7,*= RE= table can ha0e only 1 primary key column. it is used to uniuely identofy two rows in a table. >hen a column is set as !*7,*= RE=2 it also gets +T LL and 7E constraints. C*ETE TDLE employee9 6 empCode CM*61?8 !*7,*= RE=2 empame *CM*64?8 +T LL2 gender CM*618 default G,G CMECR6gender 76G,G2G)G88 89. )+*E7J RE= is the primary key column of some other table. ;ept ////// ;ept7d ;eptame 1 dmin
,arketing
Emp /////// Emp7d ;eptCode Empame Salary 1??1 1 !eter 9??? 1?? 1 ,ary 49?? 1??3 1 Jeorge 3?? 1??4 1 !eter 34?? SL& C*ETE TDLE tasafdept 6 3 deptcode ,DE* !*7,*= RE=2 4 deptname *CM*64?8 +T LL 7E 9 8Table created. SL& C*ETE TDLE tasafemp 6 3 empcode CM*6198 !*7,*= RE=2 4 deptid ,DE* *E)E*ECES tasafdept6deptcode82 9 empname *CM*69?8 Q 8Table created. SL& insert into tasafdept 0alues6Udcode2GUdnameG8 Enter 0alue for dcode: 1? Enter 0alue for dname: dmin old 1: insert into tasafdept 0alues6Udcode2GUdnameG8 new 1: insert into tasafdept 0alues61?2GdminG8 1 row created. SL& < Enter 0alue for dcode: ? Enter 0alue for dname: ,arketing old 1: insert into tasafdept 0alues6Udcode2GUdnameG8 new 1: insert into tasafdept 0alues6?2G,arketingG8
1 row created. SL& insert into tasafemp LES6GUecodeG2Udcode2GUenameG8Enter 0alue for ecode: E,!??1 Enter 0alue for dcode: 1? Enter 0alue for ename: ,ike old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??1G21?2G,ikeG8 1 row created. SL& < Enter 0alue for ecode: E,!?? Enter 0alue for dcode: 1? Enter 0alue for ename: !eter old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??G21?2G!eterG8 1 row created. SL& < Enter 0alue for ecode: E,!??3 Enter 0alue for dcode: ? Enter 0alue for ename: lly old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??3G2?2GllyG8 1 row created. SL& SELECT ( )*+, tasafdept;E!TC+;E ;E!T,E ////////// //////////////////////////////////////// 1? dmin ? ,arketing SL& select ( from tasafempE,!C+;E ;E!T7; E,!,E /////////////// ////////// //////////////////////////////////////////////////
E,!??1 E,!?? E,!??3
1? ,ike 1? !eter ? lly
SL& insert into tasafemp 0alues6GE,!??4G24?2G#ohnG8insert into tasafemp 0alues6GE,!??4G24?2G#ohnG8 ( E**+* at line 1: +*/?P1: integrity constraint 6M*.S=SAC??1113O8 0iolated / parent key not found
fter the table creation //////////////////////////////////////// =ou need to use LTE* command to modify the table sructure sing the LTE* command you can: 1. dd a new column Synta$: LTE* TDLE %tablename& ;; 6%colname& %datatype&8SL& LTE* TDLE employee ;;6gender CM*6188+*acle will insert LL to the e$isting rows of the new column. ote: =ou cannot add a new column with +T LL constraint if the table has any row.
. delete an e$isting column LTE* TDLE %tablename& ;*+! C+L, %colname&SL& LTE* TDLE employee ;*+! C+L, gender-
3. add a new constraint LTE* TDLE %tablename& ;; C+ST*7T %consname& %constype& 6%conscol&8-
To make a col uniue ////////////////////////////// SL& LTE* TDLE employee ;; C+ST*7T AE,!L+=EEAE,7L 7E6email8 ote: ensure that the column do not ha0e any duplicate 0alues before adding a 7E constraint. CMECR SL& LTE* TDLE employee ;; C+ST*7T CMRAE,!L+=EEASL*= CMECR6salary DET>EE 9?? and 9???8 ote: Ensure that the 0alues in the column satisfied the criteria defined in the CMECR constraint !*7,*= RE= SL& LTE* TDLE employee ;; C+ST*7T !RAE,!L+=EEAE,!C+;E !*7,*= RE=6empcode8 ote: ensure that the column to be made primary do not ha0e any duplicate
To enable
SL& LTE* TDLE employee EDLE C+ST*7T )RAE,!L+=EEA;E!TSL& LTE* TDLE employee EDLE C+ST*7T )RAE,!L+=EEA;E!TSL& SELECT constraintAname2 constraintAtype2 status )*+, userAconstraints 3 >ME*E tableAname5GE,!L+=EEGC+ST*7TA, C+ST* 7TA,E E C STTS STTS ////////////////////////////// / //////// AE,!L+=EEAE,7L EDLE; !RAE,!L+=EEAE,!C+;E ! EDLE; )RAE,!L+=EEA;E!T * EDLE; SL& LTE* LTE* TDLE employee ;7SDLE C+ST*7T AE,!L+=EEAE,7LTable altered. SL& SELECT constraintAname2 constraintAtype2 status )*+, userAconstraints 3 >ME*E tableAname5GE,!L+=EEGC+ST*7TA, C+ST* 7TA,E E C STTS STTS ////////////////////////////// / //////// AE,!L+=EEAE,7L ;7SDLE; !RAE,!L+=EEAE,!C+;E ! EDLE; )RAE,!L+=EEA;E!T * EDLE;
SET SE; command LTE* TDLE %tableAname& SET SE;6%columnAname&8is used to mark the column for deletion. d eletion. SL& LTE* TDLE employee SET SE; 6email8-
To drop all unused columns from the gi0en table: SL& LTE* TDLE employee ;*+! SE; C+L,S-
LTE* TDLE %tableAname& ;*+! SE; C+L,S+ ;ELETE CSC;E ////////////////////////////// >ill delete all the referencing rows from the child tables when you attempt to delete the parent table rows. LTE* TDLE Emp ;; C+ST*7T empAdtAfk )+*E7J RE= 6;epartmentAid8 *E)E*ECES departments + ;ELETE CSC;E8;eferring Constraints Constraints can ha0e the following attributes: [ ;E)E**DLE or +T ;E)E**DLE [ 77T7LL= ;E)E**E; or 77T7LL= 7,,E;7TE LTE* TDLE dept ;; C+ST*7T deptAidApk !*7,*= RE= 6departmentAid8 ;E)E**DLE 77T7LL= ;E)E**E; SL& LTE* TDLE testemp1 ;; C+ST*7T C+ST*7T !RATESTE,!1AEC+;E !*7,*= !*7,*= RE=6ecode8 ;E)E**DLE 77T7LL= ;E)E**E;To change the beha0iour of a deferrable constraint: SET C+ST*7T %consname& 7,,E;7T 7,,E;7TE';E)E**E;SL& SET C+ST*7T !RATESTE,!1AEC+;E 7,,E;7TE7,,E;7TECascading Constraints [ The CSC;E C+ST*7TS clause is used along with the ;*+! C+L, clause. [ The CSC;E C+ST*7TS clause drops all
referential integrity constraints that refer to the primary and uniue keys defined on the dropped columns. [ The CSC;E C+ST*7TS clause also drops all multicolumn constraints defined on the dropped columns.
LTE* TDLE emp ;*+! C+L, employeeAid CSC;E C+ST*7TSThe abo0e command will drop the primary key column and also all the foreign key constraints referencing this column. Table altered. LTE* LTE* TDLE test1 te st1 ;*+! 6pk2 fk2 col18 CSC;E C+ST*7TS-
9. modify an e$isting column2 type2 siFe2 or constraint LTE* TDLE %tablename& ,+;7)= 6%colname& %newdatatype&6%newsiFe&8SL& LTE* TDLE tasafemp ,+;7)=6empname *CM*69988-
To ;rop a table: ;*+! TDLE TDLE %tablename&-
7E>S ////////// helps to restrict users from 0iewing sensiti0e information of a table. 0iew do not ha0e ha0 e data of its own. Synta$: C*ETE 7E> %0iewname& S %select statement&Types:
1. Simple iew 7s one where only 1 table is used as the base table. SL& C*ETE 7E> my01 S SELECT employeeAid2 departmentAid2 lastAname2 salary 3 )*+, employees=ou =ou can perform all ;,L operation on the table though the 0iew if it is a simple 0iew. . Comple$ iew is one that uses "oins2 sub uery2 group functions SL& C*ETE 7E> my03 S SELECT emploeyeAid2 lastAname2 salary2 departmentAid 3 )*+, employees 4 >ME*E departmentAid 7 9 6 Q SELECT departmentAid O )*+, departments N >ME*E locationAid5 P 6 1? SELECT locationAid 11 )*+, locations 1 >ME*E city5GSeattleG 13 88SL& C*ETE 7E> my04 S SELECT departmentAid2 count6employeeAid8 S totalAemp 3 )*+, employees 4 J*+! D= departmentAid 9 +*;E* D= departmentAid-
To ;rop a 0iew ;*+! 7E> %0iewname&-
3. SEECE used to generate numbers. Synta$:
C*ETE SEECE %sename&SL& C*ETE SEECE myse1SL& SELECT myse1.EWTL from dualSL& SELECT myse1.C**L )*+, dualSL& C*ETE SEECE myse ST*T >7TM 1???SL& C*ETE SEECE myse3 ST*T >7TM 1??? 3 7C*E,ET D= SL& C*ETE SEECE myse9 ,WLE ? 3 C=CLE +CCME-
S=+=, ///////////////// they are used another name to a database ob"ect. SL& C*ETE S=+=, mike )+* employees7;EW //////// are used to retrei0e data faster. To create an inde$ : C*ETE 7;EW %indname& + %tablename&6colname8pply inde$ on a column only if: 1. 7f the table is 0ery large< . if the column holds large no of uniue 0alues.
)unction based inde$:
SL& C*ETE 7;EW indempname1 + tasafemp6upper6empname88To ;*+! an inde$ ;*+! 7;EW %indname&-
>hat happend when a table is dropped. )LSMDCR TDLE testemp1 T+ DE)+*E ;*+!To drop table permanently: SL& ;*+! TDLE testemp1 !*JE-
E$ternal Tables Creating a ;irectory for the E$ternal Table Create a ;7*ECT+*= ob"ect that corresponds to the directory on the file system where the e$ternal data source resides. C*ETE +* *E!LCE ;7*ECT+*= empAdir S G<...
J*T *E; + ;7*ECT+*= empAdir T+ hrC*ETE TDLE oldemp 6 fname char6982 lname CM*6988 +*J7XT7+ EWTE*L 6T=!E +*CLEAL+;E* ;E)LT ;7*ECT+*= empAdir CCESS !*,ETE*S 6*EC+*;S ;EL7,7TE; D= E>L7E +D;)7LE +L+J)7LE )7EL;S TE*,7TE; D= G2G 6fname !+S7T7+ 6 1:?8 CM*2 lname !+S7T7+ 6:418 CM*88 L+CT7+ 6Gemp.datG88 !*LLEL 9
*E#ECT L7,7T ??-
Command to drop the table from the recyclebin SL& !*JE TDLE BD7@M1R)>X!SngR"7c455@?B-
T*CTE: this command deletes all the rows from a table and cannot be rolled back. SL& T*CTE TDLE testb-
C*ET7J TDLE D= using another tabe: SL& 7SE*T 7T+ mydept 6employeeAid2 lastAname2 departmentAid2 salary8 SELECT employeeAid2 lastAname2 departmentAid2 salary 3 )*+, employees 4 >ME*E salary DET>EE 9??? ; O???14 rows created. SL& C*ETE TDLE mydept S SELECT ( )*+, employeesTable created. SL& T*CTE TDLE mydeptTable truncated. SL& 7SE*T 7T+ mydept SELECT ( )*+, employees1?N rows created.
Types of ,ultitable 7SE*T Statements The different types of multitable 7SE*T statements are: [ nconditional 7SE*T
[ Conditional LL 7SE*T [ Conditional )7*ST 7SE*T [ !i0oting 7SE*T nconditional 7SE*T LL [Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7nsert these 0alues into the SLAM7ST+*= and ,J*AM7ST+*= tables using a multitable 7SE*T.
7SE*T LL 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2 hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T LL [ Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7f the SL*= is greater than @1?2???2 insert these 0alues into the SLAM7ST+*= table using a conditional multitable 7SE*T statement. [ 7f the ,JE*A7; is greater than ??2 insert these 0alues into the ,J*AM7ST+*= table using a conditional multitable 7SE*T statement.
Conditional 7SE*T LL 7SE*T LL >ME SL & 1???? TME 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8 >ME ,J* & ??
TME 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T )7*ST [Select the ;E!*T,ETA7;2 S,6SL*=82 and ,W6M7*EA;TE8 from the E,!L+=EES table. [7f the S,6SL*=8 is greater than @92???2 then insert these 0alues into the S!EC7LASL2 using a conditional )7*ST multitable 7SE*T. [7f the first >ME clause e0aluates to true2 then the subseuent >ME clauses for this row should be skipped. [)or the rows that do not satisfy the first >ME condition2 insert into the M7*E;TEAM7ST+*=A??2 M7*E;TEAM7ST+*=APP2 or M7*E;TEAM7ST+*= tables2 based on the 0alue in the M7*EA;TE column using a conditional multitable 7SE*T.
Conditional 7SE*T )7*ST 7SE*T )7*ST >ME SL & 9??? TME 7T+ specialAsal LES6;E!T7;2 SL8 >ME M7*E;TE like 6G??G8 TME 7T+ hiredateAhistoryA?? LES6;E!T7;2M7*E;TE8 >ME M7*E;TE like 6GPPG8 TME 7T+ hiredateAhistoryAPP LES6;E!T7;2 M7*E;TE8 ELSE 7T+ hiredateAhistory LES6;E!T7;2 M7*E;TE8 SELECT departmentAid ;E!T7;2 S,6salary8 SL2 ,W6hireAdate8 M7*E;TE )*+, employees
J*+! D= departmentAid-
!i0oting 7SE*T [ Suppose you recei0e a set of sales records from a nonrelational database table2 SLESAS+*CEA;T2 in the following format: E,!L+=EEA7;2 >EERA7;2 SLESA,+2 SLESATE2 SLESA>E;2 SLESATM*2 SLESA)*7 [ =ou want to store these records in the SLESA7)+ table in a more typical relational format: E,!L+=EEA7;2 >EER2 SLES sing a pi0oting 7SE*T2 con0ert the set of sales records from the nonrelational database table to relational format.
!i0oting 7SE*T 7SE*T LL 7T+ salesAinfo LES 6employeeAid2weekAid2salesA,+8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATE8 7T+ salesAinfo LES 6employeeAid2weekAid2salesA>E;8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATM*8 7T+ salesAinfo LES 6employeeAid2weekAid2 salesA)*78 SELECT E,!L+=EEA7;2 weekAid2 salesA,+2 salesATE2 salesA>E;2 salesATM*2salesA)*7 )*+, salesAsourceAdata-
The ,E*JE Statement [!ro0ides the ability to conditionally update or insert data into a database table [!erforms an !;TE if the row e$ists2 and an 7SE*T if it is a new row: \ 0oids separate updates \ 7ncreases performance and ease of use \ 7s useful in data warehousing applications
,erging *ows 7nsert or update rows in the E,!L3 table to match the E,!L+=EES table. ,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET c.firstAname 5 e.firstAname2 c.lastAname 5 e.lastAname2 ... c.departmentAid 5 e.departmentAid >ME +T ,TCME; TME 7SE*T LES6e.employeeAid2 e.firstAname2 e.lastAname2 e.email2 e.phoneAnumber2 e.hireAdate2 e."obAid2 e.salary2 e.commissionApct2 e.managerAid2 e.departmentAid8-
,erging *ows T*CTE TDLE empl3SELECT ( )*+, empl3no rows selected ,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET ... >ME +T ,TCME; TME 7SE*T LES...SELECT ( )*+, empl3-
Column comparisons in a multiple/column subuery can be: [ !airwise comparisons [ onpairwise comparisons !airwise Comparison Subuery ;isplay the details of the employees who are managed by the same manager and work in the same department as the employees with E,!L+=EEA7; 1PP or 1O4. SELECT employeeAid2 managerAid2 departmentAid )*+, employees >ME*E 6managerAid2 departmentAid8 7 6SELECT managerAid2 departmentAid )*+, employees >ME*E employeeAid 7 61PP21O488 ; employeeAid +T 7 61PP21O48-
onpairwise Comparison Subuery ;isplay the details of the employees who are managed by the same manager as the employees with E,!L+=EEA7; 1O4 or 1PP and work in the same department as the employees with E,!L+=EEA7; 1O4 or 1PP. SELECT employeeAid2 managerAid2 departmentAid )*+, employees >ME*E managerAid 7 ; 6SELECT managerAid )*+, employees >ME*E employeeAid 7 61O421PP88 ; departmentAid 7 6SELECT departmentAid )*+, employees >ME*E employeeAid 7 61O421PP88 employeeAid +T 761O421PP8Scalar Subuery E$pressions [ scalar subuery e$pression is a subuery that returns e$actly one column 0alue from one row. [Scalar subueries can be used in:
\ Condition and e$pression part of ;EC+;E and CSE \ ll clauses of SELECT e$cept J*+! D= Scalar Subueries: E$amples [ Scalar subueries in CSE e$pressions SELECT employeeAid2 lastAname2 6CSE ? >ME departmentAid 5 6SELECT departmentAid )*+, departments >ME*E locationAid 5 1N??8 TME GCanadaG ELSE GSG E;8 location )*+, employees[ Scalar subueries in +*;E* D= clause SELECT employeeAid2 lastAname )*+, employees e +*;E* D= 6SELECT departmentAname )*+, departments d >ME*E e.departmentAid 5 d.departmentAid8Correlated Subueries Correlated subueries are used for row/by/row processing. Each subuery is e$ecuted once for e0ery row of the outer uery.
Correlated Subueries The subuery references a column from a table in the parent uery. SELECT column12 column2 ... outer )*+, table1 >ME*E column1 operator 6SELECT
)*+, >ME*E column12 column table e$pr1 5 outer .e$pr8-
sing Correlated Subueries )ind all employees who earn more than the a0erage salary in their department. SELECT lastAname2 salary2 departmentAid )*+, employees outer >ME*E salary & 6SELECT J6salary8 )*+, employees >ME*E departmentAid 5 outer.departmentAid8sing Correlated Subueries ;isplay details of those employees who ha0e changed "obs at least twice. SELECT e.employeeAid2 lastAname2e."obAid )*+, employees e >ME*E %5 6SELECT C+T6(8 )*+, "obAhistory >ME*E employeeAid 5 e.employeeAid8-
sing the EW7STS +perator [The EW7STS operator tests for e$istence of rows in the results set of the subuery. [7f a subuery row 0alue is found: \ The search does not continue in the inner uery \ The condition is flagged T*E [7f a subuery row 0alue is not found:
\ The condition is flagged )LSE \ The search continues in the inner uery
)ind Employees >ho Ma0e at Least +ne !erson *eporting to Them SELECT employeeAid2 lastAname2 "obAid2 departmentAid )*+, employees outer >ME*E EW7STS 6 SELECT GWG )*+, employees >ME*E managerAid 5 outer.employeeAid8-
)ind ll ;epartments That ;o ot Ma0e ny Employees SELECT departmentAid2 departmentAname )*+, departments d >ME*E +T EW7STS 6SELECT GWG )*+, employees >ME*E departmentAid 5 d.departmentAid8-
Correlated !;TE se a correlated subuery to update rows in one table based on rows from another table.
LTE* TDLE emplQ ;;6departmentAname *CM*6988!;TE emplQ e SET departmentAname 5 6SELECT departmentAname )*+, departments d >ME*E e.departmentAid 5 d.departmentAid8-
Correlated ;ELETE se a correlated subuery to delete rows in one table based on rows from another table. ;ELETE )*+, table1 alias1 >ME*E column operator 6SELECT e$pression )*+, table alias >ME*E alias1.column 5 alias.column8-
se a correlated subuery to delete only those rows from the E,!LQ table that also e$ist in the E,!AM7ST+*= table. ;ELETE )*+, emplQ E >ME*E employeeAid 5 6SELECT employeeAid )*+, empAhistory >ME*E employeeAid 5 E.employeeAid8-
The >7TM Clause [sing the >7TM clause2 you can use the same uery block in a SELECT statement when it occurs more than once within a comple$ uery. [The >7TM clause retrie0es the results of a uery block and stores it in the user]s temporary tablespace. [The >7TM clause impro0es performance.
sing the >7TM clause2 write a uery to display the department name and total salaries for those departments whose total salary is greater than the a0erage salary across departments. >7TM deptAcosts S 6
SELECT d.departmentAname2 S,6e.salary8 S deptAtotal )*+, employees e #+7 departments d + e.departmentAid 5 d.departmentAid J*+! D= d.departmentAname82 a0gAcost S 6 SELECT S,6deptAtotal8ME*E deptAtotal & 6SELECT deptAa0g )*+, a0gAcost8 +*;E* D= departmentAname-
SE* ,JE,ET ///////////////////////////// ;atabase: is a collection of logical storge units called as Tablespaces. SE*S 6default8 EW,!LE S=STE, S=SW TE,! 6default8 ;+ Tablespace is of 3 types: 1. !E*,ET: stores data permanently . TE,!+**=: stored data temporarily 3. ;+: stores uncommitted data To create a new user:
C*ETE SE* %username& 7;ET7)7E; D= %password&-
SL& C*ETE SE* peter 7;ET7)7E; D= oracle-
!*i0ilege: is a right gi0en to a user to perform 0arious database acti0ities on ob"ects belonging to him or other users. Types: 1. S=STE, !*77LEJE means gi0ing a pri0ilege to a user to perform database operation his ob"ects. . +D#ECT !*77LEJE allowing one user to access and manipulate the ob"ects belonging to another user.
To gi0e a pri0ilege to a user: J*T %pri0name& T+ %username&SL& C*ETE SE* peter 7;ET7)7E; D= oracleser created. SL& conn peter
>arning: =ou are no longer connected to +*CLE. SL& conn sys
SL& conn peter
SL& conn sys
SL& conn sys
SL& LTE* SE* peter +T , + usersser altered. SL& conn sys
To remo0e a pri0ilege from the user SL& *E+RE create table )*+, peter-
*+LES : a role is a group of pri0ileges that can be granted to
To drop a role: ;*+! *+LE %rolename&+b"ect !ri0ileges ///////////////////////// Synta$: J*T %pri0name& + %ob"name& T+ %username&SL& J*T SELECT2 7SE*T + M*."obs 3 T+ peterSL& 7SE*T 7T+ hr."obs LES6G;DG2G;atabase dministratorG2Q???21???8To re0oke an ob"ect pri0ilege:
SL& *E+RE select + hr."obs 3 )*+, peterSL& C*ETE SE* "erry 7;ET7)7E; D= oracle 3 ;E)LT TDLES!CE e$ample 4 +T L7,7TE; + e$ampleser created.
To create a new tablespace: SL& C*ETE TDLES!CE !urchaseTbs ;T)7LE G
SL& l1 1( create user ally SL& c
To delete only the logical structrue from the database: SL& ;*+! TDLES!CE purchasetbs 7CL;7J C+TETS-
To delete the logical as well as the physical structure: SL& drop tablespace admin including contents and datafilesSub uery ////////////////// 7 or = can be used if the inner uery returns more than one row of 0alue. There are 3 types of = 1. %= means less than the ma$imum . &= means more than the minimum 3. 5= is same as 7 operator E$ample: >rite a uery to show employee id2 last name2 "ob id2 and salary of all employees whose salary is less than the salary of all employees with a "ob id of 7TA!*+J. SL& SELECT employeeAid2 lastAname2 "obAid2 salary )*+, employees 3 >ME*E salary %= 4 6 9 SELECT salary Q )*+, employees O >ME*E "obAid5G7TA!*+JG N 8Eui #oin and on/Eui #oin //////////////////////////////////////// SELECT emp.employeeAid2 emp.lastAname2 dep.departmentAname )*+, employees emp #+7 departments dep + emp.departmentAid 5 dep.departmentAid-
on/Eui "oin: here the common columns of two tables are "oined using non/euality comparison operator such as %2 &2 DET>EE2 etc. ;isplay employee id2 last name and grade of an employees for only those employees whose salary is within the min and ma$ salary of their grade.
SL& SELECT emp.employeeAid2 emp.lastAname2 emp.salary2grd.gradeid )*+, employees emp #+7 grades grd 3 + emp.salary DET>EE grd.minsal ; grd.ma$salSET +!E*T+*S used to combine the result of two or more tables and display as a single resultset. SELECT col42 col2 col3 )*+, table1 %SET +!E*T+* T=!E& SELECT col42 col2 col3)*+, table
Juidelines to be followed to use SET operators 1. The no of columns of both "oined tables should match. . The order of columns in the first uery should match with the second uery 3. The names of the supplied columns can differ Types: 1. 7+ : returns rows from both the ueries after eliminating the dup lications. SL& select ( from testaW= ////////// //////////////////////////////////////// 1 D 3C SL& select ( from testbD C ////////// ///////// //////////////////////////////////////// 1 ?P/SE!/19 ?P/SE!/19 D 9 ?P/SE!/19 ) Q ?P/SE!/19 M SL& SELECT $2y )*+, testa 7+
3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D 3C 9) QM SL& C SL& SELECT $2y )*+, testa 7+ LL 3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D 3C 1 D 9) QM O rows selected. b8 7TE*SECT shows only common rows of two tables remo0ing the distinct rows SL& SL& SELECT $2y )*+, testa 7TE*SECT 3 SELECT a2c )*+, testbW= ////////// //////////////////////////////////////// 1 D c8 ,7S:
it displays all the rows of first uery that are not a0ailable in the second uery. SL& select ( from testaW= ////////// //////////////////////////////////////// 1 D 3C SL& select ( from testbD C ////////// ///////// //////////////////////////////////////// 1 ?P/SE!/19 ?P/SE!/19 D 9 ?P/SE!/19 ) Q ?P/SE!/19 M SL& SELECT a2c )*+, testb ,7S 3 SELECT $2y )*+, testaC ////////// //////////////////////////////////////// 9) QM
;atabase +b"ects //////////////////////// 1. Table . iew 3. Seuence 4. Synonym 9. 7nde$ Table: is used to store data permanently in rows and column format. Synta$: C*ETE TDLE %tablename&
6 %colname& %datatype&6siFe82 %colname& datatype&6siFe8 8*ules to follow while naming a database ob"ect: 1. ,ust begin with a letter . De 1 to 3? characters long 3. Can contain /X2 a/F2 ?/P2 A2 @ and Y 4. The ob"ect name cannot be duplicated 9. 7t cannot be a reser0ed word C*ETE TDLE employee 6 empCode CM*6982 empame *CM*64?82 "oin;ate ;TE2 salary ,DE*6N28 8To insert a new row: 7SE*T 7T+ employee LES6GE,!??1G2G!ETE*G2sysdate249??8+* 7SE*T 7T+ employee 6salary2empame2 empCode2 "oin;ate8 LES63???2G!eterG2GE,!?G2sysdate8To ignore the columns 7SE*T 7T+ employee 6empCode2 empame8 LES 6GE,!?3G2G#ohnG8>hen a column is ignored2oracle will place a LL to the ignored column. =ou can also ignore a column by e$plicitly pro0iding the null keyword. 7SE*T 7T+ employee LES6null2null2null2null8;ata ,anipulation Language 7SE*T ;ELETE !;TE
;ELETE command: used to remo0e one or more rows from a table. Synta$: ;ELETE )*+, %tablename& H>ME*E %condition&I;ELETE )*+, employee >ME*E empCode5GE,!?1G!;TE command: used to modify one or more column 0alue. !;TE %tablename& SET %colname&5%new 0al& H>ME*E %cond&I!;TE employee SET salary5salary1?? >ME*E empCode5GE,!?1G-
Constraint: is a rule applied on a column to restrint users from entering in0alid data. Types: 1. +T LL . CMECR 3. 7E 4. ;E)LT property 9. !*7,*= RE= Q. )+*E7J RE= +T LL: used to make a column reuire and cannot be ignored. C*ETE TDLE employee1 6 empCode CM*61?8 +T LL2 empame *CM*69?8 +T LL2 "oin;ate ;TE2 salary number6N28
8SL& desc employee1 ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!C+;E +T LL CM*61?8 E,!,E +T LL *CM*69?8 #+7;TE ;TE SL*= ,DE*6N28 SL& insert into employee1 6empCode2 "oindate2 salary8 0alues6GE,!??1G2sysdate23???8insert into employee1 6empCode2 "oindate2 salary8 ( E**+* at line 1: +*/?14??: cannot insert LL into 6BM*B.BE,!L+=EE1B.BE,!,EB8 ote: ny no of columns in a table can ha0e +T LL constraint. . CMECR is used to allow users to enter only specific 0alues as per the gi0en condition. C*ETE TDLE employee 6 empCode CM*61?8 +T LL2 empame *CM*64?8 +T LL2 gender CM*618 +T LL CMECR6gender 76G,G2G)G882 "oin;ate ;TE2 salary ,DE*6N28 8-
3. 7E: restrict the user from entering duplicate 0alues in a column2 C*ETE TDLE employee3 6 empCode CM*61?8 +T LL 7E2 empame *CM*64?8 +T LL2 gender CM*618 CMECR6gender 76G,G2G)G882 salary ,DE* 6N28 CMECR 6salary DET>EE 9?? and 9???82 "oindate ;TE2
Email *CM*698 7E 8SL& 7SE*T 7T+ employee3 LES6GUempcodeG2GUempameG2GUgenderG2Ubasic2GUhiredateG2GUemailG8-
4. ;E)LT property Dy default when a column is ignored2 oracle will place a null 0alue to that column. The default property is used to insert some other 0alue other than null when it is ignored. 7t can be applied only to nullable columns. SL& C*ETE TDLE employee4 6 3 empCode CM*61?8 +T LL 7E2 4 empame *CM*64?8 +T LL2 9 "oindate ;TE ;E)LT sysdate2 Q gender CM*618 ;E)LT G,G CMECR 6gender 76G,G2G)G88 O 8SL& insert into employee4 6empCode2 empame8 LES 6GE,!??G2G!eterG81 row created. SL& select ( from employee4E,!C+;E E,!,E #+7;TE J ////////// //////////////////////////////////////// ///////// / E,!??1 ,ike 1?/#/19 , E,!?? !eter ?P/SE!/19 ,
4. !*7,*= RE= table can ha0e only 1 primary key column. it is used to uniuely identofy two rows in a table. >hen a column is set as !*7,*= RE=2 it also gets +T LL and 7E constraints. C*ETE TDLE employee9 6 empCode CM*61?8 !*7,*= RE=2 empame *CM*64?8 +T LL2
gender CM*618 default G,G CMECR6gender 76G,G2G)G88 89. )+*E7J RE= is the primary key column of some other table. ;ept ////// ;ept7d ;eptame 1 dmin ,arketing Emp /////// Emp7d ;eptCode Empame Salary 1??1 1 !eter 9??? 1?? 1 ,ary 49?? 1??3 1 Jeorge 3?? 1??4 1 !eter 34?? SL& C*ETE TDLE tasafdept 6 3 deptcode ,DE* !*7,*= RE=2 4 deptname *CM*64?8 +T LL 7E 9 8Table created. SL& C*ETE TDLE tasafemp 6 3 empcode CM*6198 !*7,*= RE=2 4 deptid ,DE* *E)E*ECES tasafdept6deptcode82 9 empname *CM*69?8 Q 8Table created. SL& insert into tasafdept 0alues6Udcode2GUdnameG8 Enter 0alue for dcode: 1? Enter 0alue for dname: dmin
old 1: insert into tasafdept 0alues6Udcode2GUdnameG8 new 1: insert into tasafdept 0alues61?2GdminG8 1 row created. SL& < Enter 0alue for dcode: ? Enter 0alue for dname: ,arketing old 1: insert into tasafdept 0alues6Udcode2GUdnameG8 new 1: insert into tasafdept 0alues6?2G,arketingG8 1 row created. SL& insert into tasafemp LES6GUecodeG2Udcode2GUenameG8Enter 0alue for ecode: E,!??1 Enter 0alue for dcode: 1? Enter 0alue for ename: ,ike old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??1G21?2G,ikeG8 1 row created. SL& < Enter 0alue for ecode: E,!?? Enter 0alue for dcode: 1? Enter 0alue for ename: !eter old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??G21?2G!eterG8 1 row created. SL& < Enter 0alue for ecode: E,!??3 Enter 0alue for dcode: ? Enter 0alue for ename: lly old 1: insert into tasafemp LES6GUecodeG2Udcode2GUenameG8 new 1: insert into tasafemp LES6GE,!??3G2?2GllyG8 1 row created. SL& SELECT ( )*+, tasafdept-
;E!TC+;E ;E!T,E ////////// //////////////////////////////////////// 1? dmin ? ,arketing SL& select ( from tasafempE,!C+;E ;E!T7; E,!,E /////////////// ////////// ////////////////////////////////////////////////// E,!??1 1? ,ike E,!?? 1? !eter E,!??3 ? lly SL& insert into tasafemp 0alues6GE,!??4G24?2G#ohnG8insert into tasafemp 0alues6GE,!??4G24?2G#ohnG8 ( E**+* at line 1: +*/?P1: integrity constraint 6M*.S=SAC??1113O8 0iolated / parent key not found
fter the table creation //////////////////////////////////////// =ou need to use LTE* command to modify the table sructure sing the LTE* command you can: 1. dd a new column Synta$: LTE* TDLE %tablename& ;; 6%colname& %datatype&8SL& LTE* TDLE employee ;;6gender CM*6188+*acle will insert LL to the e$isting rows of the new column. ote: =ou cannot add a new column with +T LL constraint if the table has any row.
. delete an e$isting column LTE* TDLE %tablename& ;*+! C+L, %colname&-
SL& LTE* TDLE employee ;*+! C+L, gender-
3. add a new constraint LTE* TDLE %tablename& ;; C+ST*7T %consname& %constype& 6%conscol&8To make a col uniue ////////////////////////////// SL& LTE* TDLE employee ;; C+ST*7T AE,!L+=EEAE,7L 7E6email8 ote: ensure that the column do not ha0e any duplicate 0alues before adding a 7E constraint. CMECR SL& LTE* TDLE employee ;; C+ST*7T CMRAE,!L+=EEASL*= CMECR6salary DET>EE 9?? and 9???8 ote: Ensure that the 0alues in the column satisfied the criteria defined in the CMECR constraint !*7,*= RE= SL& LTE* TDLE employee ;; C+ST*7T !RAE,!L+=EEAE,!C+;E !*7,*= RE=6empcode8 ote: ensure that the column to be made primary do not ha0e any duplicate
LTE* TDLE %tablename& ;*+! C+ST*7T %consname&SL& LTE* TDLE employee ;*+! C+ST*7T CMRAE,!L+=EEASL*=Table altered.
To enableME*E tableAname5GE,!L+=EEGC+ST*7TA,E C STTS ////////////////////////////// / //////// AE,!L+=EEAE,7L EDLE; !RAE,!L+=EEAE,!C+;E ! EDLE; )RAE,!L+=EEA;E!T * EDLE; SL& LTE* TDLE employee ;7SDLE C+ST*7T AE,!L+=EEAE,7LTable altered. SL& SELECT constraintAname2 constraintAtype2 status )*+, userAconstraints 3 >ME*E tableAname5GE,!L+=EEGC+ST*7TA,E C STTS ////////////////////////////// / //////// AE,!L+=EEAE,7L ;7SDLE; !RAE,!L+=EEAE,!C+;E ! EDLE; )RAE,!L+=EEA;E!T * EDLE;
SET SE; command LTE* TDLE %tableAname& SET SE;6%columnAname&8is used to mark the column for deletion. SL& LTE* TDLE employee SET SE; 6email8-
To drop all unused columns from the gi0en table: SL& LTE* TDLE employee ;*+! SE; C+L,S-
LTE* TDLE %tableAname& ;*+! SE; C+L,S+ ;ELETE CSC;E ////////////////////////////// >ill delete all the referencing rows from the child tables when you attempt to delete the parent table rows. LTE* TDLE Emp ;; C+ST*7T empAdtAfk )+*E7J RE= 6;epartmentAid8 *E)E*ECES departments + ;ELETE CSC;E8;eferring Constraints Constraints can ha0e the following attributes: [ ;E)E**DLE or +T ;E)E**DLE [ 77T7LL= ;E)E**E; or 77T7LL= 7,,E;7TE LTE* TDLE dept ;; C+ST*7T deptAidApk !*7,*= RE= 6departmentAid8 ;E)E**DLE 77T7LL= ;E)E**E; SL& LTE* TDLE testemp1 ;; C+ST*7T !RATESTE,!1AEC+;E !*7,*= RE=6ecode8 ;E)E**DLE 77T7LL= ;E)E**E;-
To change the beha0iour of a deferrable constraint: SET C+ST*7T %consname& 7,,E;7TE';E)E**E;SL& SET C+ST*7T !RATESTE,!1AEC+;E 7,,E;7TECascading Constraints [ The CSC;E C+ST*7TS clause is used along with the ;*+! C+L, clause. [ The CSC;E C+ST*7TS clause drops all referential integrity constraints that refer to the primary and uniue keys defined on the dropped columns. [ The CSC;E C+ST*7TS clause also drops all multicolumn constraints defined on the dropped columns.
LTE* TDLE emp ;*+! C+L, employeeAid CSC;E C+ST*7TSThe abo0e command will drop the primary key column and also all the foreign key constraints referencing this column. Table altered. LTE* TDLE test1 ;*+! 6pk2 fk2 col18 CSC;E C+ST*7TS-
9. modify an e$isting column2 type2 siFe2 or constraint LTE* TDLE %tablename& ,+;7)= 6%colname& %newdatatype&6%newsiFe&8SL& LTE* TDLE tasafemp ,+;7)=6empname *CM*69988-
To ;rop a table: ;*+! TDLE %tablename&-
7E>S ////////// helps to restrict users from 0iewing sensiti0e information of a table. 0iew do not ha0e data of its own. Synta$: C*ETE 7E> %0iewname& S %select statement&Types: 1. Simple iew 7s one where only 1 table is used as the base table. SL& C*ETE 7E> my01 S SELECT employeeAid2 departmentAid2 lastAname2 salary 3 )*+, employees=ou can perform all ;,L operation on the table though the 0iew if it is a simple 0iew. . Comple$ iew is one that uses "oins2 sub uery2 group functions SL& C*ETE 7E> my03 S SELECT emploeyeAid2 lastAname2 salary2 departmentAid 3 )*+, employees 4 >ME*E departmentAid 7 9 6 Q SELECT departmentAid O )*+, departments N >ME*E locationAid5 P 6 1? SELECT locationAid 11 )*+, locations 1 >ME*E city5GSeattleG 13 88SL& C*ETE 7E> my04 S SELECT departmentAid2 count6employeeAid8 S totalAemp 3 )*+, employees 4 J*+! D= departmentAid 9 +*;E* D= departmentAid-
To ;rop a 0iew ;*+! 7E> %0iewname&-
3. SEECE used to generate numbers. Synta$: C*ETE SEECE %sename&SL& C*ETE SEECE myse1SL& SELECT myse1.EWTL from dualSL& SELECT myse1.C**L )*+, dualSL& C*ETE SEECE myse ST*T >7TM 1???SL& C*ETE SEECE myse3 ST*T >7TM 1??? 3 7C*E,ET D= SL& C*ETE SEECE myse9 ,WLE ? 3 C=CLE +CCME-
S=+=, ///////////////// they are used another name to a database ob"ect. SL& C*ETE S=+=, mike )+* employees7;EW //////// are used to retrei0e data faster. To create an inde$ :
C*ETE 7;EW %indname& + %tablename&6colname8pply inde$ on a column only if: 1. 7f the table is 0ery large< . if the column holds large no of uniue 0alues.
)unction based inde$:
SL& C*ETE 7;EW indempname1 + tasafemp6upper6empname88To ;*+! an inde$ ;*+! 7;EW %indname&-
>hat happend when a table is dropped. )LSMDCR TDLE testemp1 T+ DE)+*E ;*+!To drop table permanently: SL& ;*+! TDLE testemp1 !*JE-
E$ternal Tables Creating a ;irectory for the E$ternal Table Create a ;7*ECT+*= ob"ect that corresponds to the directory on the file system where the e$ternal data source resides. C*ETE +* *E!LCE ;7*ECT+*= empAdir S G<...
J*T *E; + ;7*ECT+*= empAdir T+ hrC*ETE TDLE oldemp 6 fname char6982 lname CM*6988 +*J7XT7+ EWTE*L 6T=!E +*CLEAL+;E*
;E)LT ;7*ECT+*= empAdir CCESS !*,ETE*S 6*EC+*;S ;EL7,7TE; D= E>L7E +D;)7LE +L+J)7LE )7EL;S TE*,7TE; D= G2G 6fname !+S7T7+ 6 1:?8 CM*2 lname !+S7T7+ 6:418 CM*88 L+CT7+ 6Gemp.datG88 !*LLEL 9 *E#ECT L7,7T ??-
Command to drop the table from the recyclebin SL& !*JE TDLE BD7@M1R)>X!SngR"7c455@?B-
T*CTE: this command deletes all the rows from a table and cannot be rolled back. SL& T*CTE TDLE testb-
C*ET7J TDLE D= using another tabe: SL& 7SE*T 7T+ mydept 6employeeAid2 lastAname2 departmentAid2 salary8 SELECT employeeAid2 lastAname2 departmentAid2 salary 3 )*+, employees 4 >ME*E salary DET>EE 9??? ; O???14 rows created. SL& C*ETE TDLE mydept S SELECT ( )*+, employeesTable created. SL& T*CTE TDLE mydeptTable truncated. SL& 7SE*T 7T+ mydept
SELECT ( )*+, employees1?N rows created. ,ultiple 7SE*T Statement: Types of ,ultitable 7SE*T Statements The different types of multitable 7SE*T statements are: [ nconditional 7SE*T [ Conditional LL 7SE*T [ Conditional )7*ST 7SE*T [ !i0oting 7SE*T nconditional 7SE*T LL [Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7nsert these 0alues into the SLAM7ST+*= and ,J*AM7ST+*= tables using a multitable 7SE*T.
7SE*T LL 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2 hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T LL [ Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7f the SL*= is greater than @1?2???2 insert these 0alues into the SLAM7ST+*= table using a conditional multitable 7SE*T statement. [ 7f the ,JE*A7; is greater than ??2 insert these
0alues into the ,J*AM7ST+*= table using a conditional multitable 7SE*T statement. SL& C*ETE TDLE ,J* S SELECT employeeAid2 lastAname2 managerAid2 salary 3 )*+, employeesTable created. SL& truncate table mgrTable truncated. SL& C*ETE TDLE SL S SELECT employeeAid2 departmentAid2 salary2 commissionApct 3 )*+, employeesTable created. SL& truncate table salTable truncated. SL& desc mgr ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!L+=EEA7; ,DE*6Q8 LSTA,E +T LL *CM*698 ,JE*A7; ,DE*6Q8 SL*= ,DE*6N28 SL& desc sal ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!L+=EEA7; ,DE*6Q8 ;E!*T,ETA7; ,DE*648 SL*= ,DE*6N28 C+,,7SS7+A!CT ,DE*628 SL& select from mgrselect from mgr
( E**+* at line 1: +*/??P3Q: missing e$pression
SL& select ( from mgrno rows selected SL& select ( from salno rows selected SL& SL& SL& 7SE*T LL 7T+ mgr LES6employeeAid2 lastAname2 managerAid2 salary8 3 7T+ sal LES6employeeAid2 departmentAid2 salary2 commissionApct8 4 SELECT employeeAid2 lastAname2 managerAid2 salary2departmentAid2 commissionApct 9 )*+, employees Q >ME*E hireAdate&G31/;EC/?NG rows created. SL& select ( from mgrE,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// #ohn
SL*=
SL& select ( from salE,!L+=EEA7; ;E!*T,ETA7; SL*= C+,,7SS7+A!CT /////////// ///////////// ////////// ////////////// SL& C*ETE TDLE "obdet S SELECT employeeAid2 departmentAid2 "obAid2 hireAdate 3 )*+, employeesTable created.
SL& truncate table "obdetTable truncated. SL& truncate table salTable truncated. SL& truncate table mgrTable truncated. SL& desc sal ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!L+=EEA7; ,DE*6Q8 ;E!*T,ETA7; ,DE*648 SL*= ,DE*6N28 C+,,7SS7+A!CT ,DE*628 SL& desc mgr ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!L+=EEA7; ,DE*6Q8 LSTA,E +T LL *CM*698 ,JE*A7; ,DE*6Q8 SL*= ,DE*6N28 SL& desc "obdet ame ullZ Type ///////////////////////////////////////// //////// //////////////////////////// E,!L+=EEA7; ,DE*6Q8 ;E!*T,ETA7; ,DE*648 #+DA7; +T LL *CM*61?8 M7*EA;TE +T LL ;TE SL& 7SE*T LL >ME salary & 14??? TME 3 7T+ sal LES6employeeAid2 departmentAid2 salary2 commissionApct8 4 >ME managerAid DET>EE 1?? ; 19? TME
9 7T+ mgr LES6employeeAid2 lastAname2managerAid2 salary8 Q >ME hireA;ate DET>EE G1/#/?9G ; G31/;EC/?QG TME O 7T+ "obdet LES6employeeAid2 departmentAid2 "obAid2 hireAdate8 N SELECT employeeAid2 departmentAid2 salary2 commissionApct2 lastAname2 managerAid2 P "obAid2 hireAdate 1? )*+, employees1Q? rows created.
SL& 7SE*T )7*ST >ME salary & 14??? TME 3 7T+ sal LES6employeeAid2 departmentAid2 salary2 commissionApct8 4 >ME managerAid DET>EE 1?? ; 19? TME 9 7T+ mgr LES6employeeAid2 lastAname2managerAid2 salary8 Q >ME hireA;ate DET>EE G1/#/?9G ; G31/;EC/?QG TME O 7T+ "obdet LES6employeeAid2 departmentAid2 "obAid2 hireAdate8 N SELECT employeeAid2 departmentAid2 salary2 commissionApct2 lastAname2 managerAid2 P "obAid2 hireAdate 1? )*+, employeesSL& SELECT ( from salE,!L+=EEA7; ;E!*T,ETA7; SL*= C+,,7SS7+A!CT /////////// ///////////// ////////// ////////////// 1?? P? 41?? 1?1 P? 1O??? 1? P? 1O??? SL& SELECT ( )*+, mgrE,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1PN +Connell 14 Q?? 1PP Jrant 14 Q?? ?? >halen 1?1 44?? ?1 Martstein 1?? 13??? ?3 ,a0ris 1?1 Q9?? ?4 Daer 1?1 1???? ?9 Miggins 1?1 1??N 1?1 Rochhar 1?? 1O???
SL*=
1? ;e Maan 1?3 Munold 1?4 Ernst
1?? 1O??? 1? P??? 1?3 Q???
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1?9 ustin 1?3 4N?? 1?Q !ataballa 1?3 4N?? 1?O LorentF 1?3 4?? 1?N Jreenberg 1?1 1??N 1?P )a0iet 1?N P??? 11? Chen 1?N N?? 111 Sciarra 1?N OO?? 11 rman 1?N ON?? 113 !opp 1?N QP?? 114 *aphaely 1?? 11??? 119 Rhoo 114 31??
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 11Q Daida 114 P?? 11O Tobias 114 N?? 11N Mimuro 114 Q?? 11P Colmenares 114 9?? 1? >eiss 1?? N??? 11 )ripp 1?? N?? 1 Raufling 1?? OP?? 13 ollman 1?? Q9?? 14 ,ourgos 1?? 9N?? 19 ayer 1? 3?? 1Q ,ikkilineni 1? O??
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1O Landry 1? 4?? 1N ,arkle 1? ?? 1P Dissot 11 33?? 13? tkinson 11 N?? 131 ,arlow 11 9?? 13 +lson 11 1?? 133 ,allin 1 33??
SL*=
134 *ogers 139 Jee 13Q !hiltanker 13O Ladwig
1 P?? 1 4?? 1 ?? 13 3Q??
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 13N Stiles 13 3?? 13P Seo 13 O?? 14? !atel 13 9?? 141 *a"s 14 39?? 14 ;a0ies 14 31?? 143 ,atos 14 Q?? 144 argas 14 9?? 149 *ussell 1?? 14??? 14Q !artners 1?? 139?? 14O ErraFuriF 1?? 1??? 14N Cambrault 1?? 11???
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 14P Xlotkey 1?? 1?9?? 19? Tucker 149 1???? 191 Dernstein 149 P9?? 19 Mall 149 P??? 193 +lsen 149 N??? 194 Cambrault 149 O9?? 199 Tu0ault 149 O??? 19Q Ring 14Q 1???? 19O Sully 14Q P9?? 19N ,cEwen 14Q P??? 19P Smith 14Q N???
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1Q? ;oran 14Q O9?? 1Q1 Sewall 14Q O??? 1Q ishney 14O 1?9?? 1Q3 Jreene 14O P9?? 1Q4 ,ar0ins 14O O?? 1Q9 Lee 14O QN??
SL*=
1QQ nde 1QO Danda 1QN +Fer 1QP Dloom 1O? )o$
14O Q4?? 14O Q?? 14N 119?? 14N 1???? 14N PQ??
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1O1 Smith 14N O4?? 1O Dates 14N O3?? 1O3 Rumar 14N Q1?? 1O4 bel 14P 11??? 1O9 Mutton 14P NN?? 1OQ Taylor 14P NQ?? 1OO Li0ingston 14P N4?? 1ON Jrant 14P O??? 1OP #ohnson 14P Q?? 1N? Taylor 1? 3?? 1N1 )leaur 1? 31??
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1N Sulli0an 1? 9?? 1N3 Jeoni 1? N?? 1N4 Sarchand 11 4?? 1N9 Dull 11 41?? 1NQ ;ellinger 11 34?? 1NO Cabrio 11 3??? 1NN Chung 1 3N?? 1NP ;illy 1 3Q?? 1P? Jates 1 P?? 1P1 !erkins 1 9?? 1P Dell 13 4???
SL*=
E,!L+=EEA7; LSTA,E ,JE*A7; /////////// ///////////////////////// ////////// ////////// 1P3 E0erett 13 3P?? 1P4 ,cCain 13 3?? 1P9 #ones 13 N?? 1PQ >alsh 14 31?? 1PO )eeney 14 3???
SL*=
1?4 rows selected. SL& select ( from "obdetE,!L+=EEA7; ;E!*T,ETA7; #+DA7; /////////// ///////////// ////////// ///////// ? ? ,RA*E! 1O/J/?9 1?1 P? ;A! 1/SE!/?9 1?3 Q? 7TA!*+J ?3/#/?Q 1?9 Q? 7TA!*+J 9/#/?9 1?Q Q? 7TA!*+J ?9/)ED/?Q 11? 1?? )7ACC+T N/SE!/?9 111 1?? )7ACC+T 3?/SE!/?9 11 1?? )7ACC+T ?O/,*/?Q 11Q 3? !ACLE*R 4/;EC/?9 11O 3? !ACLE*R 4/#L/?9 11N 3? !ACLE*R 19/+/?Q
M7*EA;TE
E,!L+=EEA7; ;E!*T,ETA7; #+DA7; /////////// ///////////// ////////// ///////// 11 9? STA, 1?/!*/?9 13 9? STA, 1?/+CT/?9 19 9? STACLE*R 1Q/#L/?9 1Q 9? STACLE*R N/SE!/?Q 1P 9? STACLE*R ?/J/?9 13? 9? STACLE*R 3?/+CT/?9 131 9? STACLE*R 1Q/)ED/?9 134 9? STACLE*R Q/J/?Q 13N 9? STACLE*R Q/+CT/?9 13P 9? STACLE*R 1/)ED/?Q 14? 9? STACLE*R ?Q/!*/?Q
M7*EA;TE
E,!L+=EEA7; ;E!*T,ETA7; #+DA7; /////////// ///////////// ////////// ///////// 14 9? STACLE*R P/#/?9 143 9? STACLE*R 19/,*/?Q 144 9? STACLE*R ?P/#L/?Q 14Q N? SA, ?9/#/?9 14O N? SA, 1?/,*/?9 19? N? SA*E! 3?/#/?9
M7*EA;TE
191 19 193 194 19P
N? SA*E! N? SA*E! N? SA*E! N? SA*E! N? SA*E!
4/,*/?9 ?/J/?9 3?/,*/?Q ?P/;EC/?Q 1?/,*/?9
E,!L+=EEA7; ;E!*T,ETA7; #+DA7; /////////// ///////////// ////////// ///////// 1Q? N? SA*E! 19/;EC/?9 1Q1 N? SA*E! ?3/+/?Q 1Q N? SA*E! 11/+/?9 1QN N? SA*E! 11/,*/?9 1QP N? SA*E! 3/,*/?Q 1O? N? SA*E! 4/#/?Q 1O9 N? SA*E! 1P/,*/?9 1OQ N? SA*E! 4/,*/?Q 1OO N? SA*E! 3/!*/?Q 1N? 9? SMACLE*R 4/#/?Q 1N1 9? SMACLE*R 3/)ED/?Q
M7*EA;TE
E,!L+=EEA7; ;E!*T,ETA7; #+DA7; /////////// ///////////// ////////// ///////// 1N9 9? SMACLE*R ?/)ED/?9 1NQ 9? SMACLE*R 4/#/?Q 1NN 9? SMACLE*R 14/#/?9 1NP 9? SMACLE*R 13/J/?9 1P? 9? SMACLE*R 11/#L/?Q 1P3 9? SMACLE*R ?3/,*/?9 1P4 9? SMACLE*R ?1/#L/?Q 1PQ 9? SMACLE*R 4/!*/?Q 1PO 9? SMACLE*R 3/,=/?Q
M7*EA;TE
93 rows selected. SL&
Conditional 7SE*T LL 7SE*T LL >ME SL & 1???? TME 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8
>ME ,J* & ?? TME 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T )7*ST [Select the ;E!*T,ETA7;2 S,6SL*=82 and ,W6M7*EA;TE8 from the E,!L+=EES table. [7f the S,6SL*=8 is greater than @92???2 then insert these 0alues into the S!EC7LASL2 using a conditional )7*ST multitable 7SE*T. [7f the first >ME clause e0aluates to true2 then the subseuent >ME clauses for this row should be skipped. [)or the rows that do not satisfy the first >ME condition2 insert into the M7*E;TEAM7ST+*=A??2 M7*E;TEAM7ST+*=APP2 or M7*E;TEAM7ST+*= tables2 based on the 0alue in the M7*EA;TE column using a conditional multitable 7SE*T.
Conditional 7SE*T )7*ST 7SE*T )7*ST >ME SL & 9??? TME 7T+ specialAsal LES6;E!T7;2 SL8 >ME M7*E;TE like 6G??G8 TME 7T+ hiredateAhistoryA?? LES6;E!T7;2M7*E;TE8 >ME M7*E;TE like 6GPPG8 TME 7T+ hiredateAhistoryAPP LES6;E!T7;2 M7*E;TE8 ELSE 7T+ hiredateAhistory LES6;E!T7;2 M7*E;TE8 SELECT departmentAid ;E!T7;2 S,6salary8 SL2 ,W6hireAdate8 M7*E;TE )*+,
employees J*+! D= departmentAid-
!i0oting 7SE*T [ Suppose you recei0e a set of sales records from a nonrelational database table2 SLESAS+*CEA;T2 in the following format: E,!L+=EEA7;2 >EERA7;2 SLESA,+2 SLESATE2 SLESA>E;2 SLESATM*2 SLESA)*7 [ =ou want to store these records in the SLESA7)+ table in a more typical relational format: E,!L+=EEA7;2 >EER2 SLES sing a pi0oting 7SE*T2 con0ert the set of sales records from the nonrelational database table to relational format.
!i0oting 7SE*T 7SE*T LL 7T+ salesAinfo LES 6employeeAid2weekAid2salesA,+8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATE8 7T+ salesAinfo LES 6employeeAid2weekAid2salesA>E;8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATM*8 7T+ salesAinfo LES 6employeeAid2weekAid2 salesA)*78 SELECT E,!L+=EEA7;2 weekAid2 salesA,+2 salesATE2 salesA>E;2 salesATM*2salesA)*7 )*+, salesAsourceAdata-
The ,E*JE Statement [!ro0ides the ability to conditionally update or insert data into a database table [!erforms an !;TE if the row e$ists2 and an 7SE*T if it is a new row: \ 0oids separate updates \ 7ncreases performance and ease of use
\ 7s useful in data warehousing applications
,erging *ows 7nsert or update rows in the E,!L3 table to match the E,!L+=EES table.
,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET c.firstAname 5 e.firstAname2 c.lastAname 5 e.lastAname2 ... c.departmentAid 5 e.departmentAid >ME +T ,TCME; TME 7SE*T LES6e.employeeAid2 e.firstAname2 e.lastAname2 e.email2 e.phoneAnumber2 e.hireAdate2 e."obAid2 e.salary2 e.commissionApct2 e.managerAid2 e.departmentAid8SL& ,E*JE 7T+ empdup dup S7J employees emp 3 + 6dup.employeeAid5emp.employeeAid8 4 >ME ,TCME; TME 9 !;TE SET Q dup.firstAname5emp.firstAname2 O dup.lastAname5emp.lastAname2 N dup.email5emp.email2 P dup.phoneAnumber5emp.phoneAnumber2 1? dup.hireAdate5emp.hireAdate2 11 dup."obAid5emp."obAid2 1 dup.salary5emp.salary2 13 dup.commissionApct5emp.commissionApct2 14 dup.managerAid5emp.managerAid2
19 dup.departmentAid5emp.departmentAid 1Q >ME +T ,TCME; TME 1O 7SE*T LES6emp.employeeAid2emp.firstAname2 emp.lastAname2emp.email2emp.phoneAnumber2 1N emp.hireAdate2 emp."obAid2 emp.salary2 emp.commissionApct2 emp.managerAid2 1P emp.departmentAid8-
,erging *ows T*CTE TDLE empl3SELECT ( )*+, empl3no rows selected ,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET ... >ME +T ,TCME; TME 7SE*T LES...SELECT ( )*+, empl3-
Column comparisons in a multiple/column subuery can be: [ !airwise comparisons [ onpairwise comparisons !airwise Comparison Subuery ;isplay the details of the employees who are managed by the same manager and work in the same department as the employees with E,!L+=EEA7; 1PP.
SL& SELECT employeeAid2 lastAname2 departmentAid2 managerAid2 firstAname
)*+, employees 3 >ME*E managerAid 7 4 6 9 SELECT managerAid Q )*+, employees O >ME*E upper6firstAname85G#+MG N 8 P ; departmentAid 7 1? 6 11 SELECT departmentAid 1 )*+, employees 13 >ME*E upper6firstAname85G#+MG 14 8E$ample of !air wise comparison: SELECT employeeAid2 managerAid2 departmentAid )*+, employees >ME*E 6managerAid2 departmentAid8 7 6SELECT managerAid2 departmentAid )*+, employees >ME*E employeeAid 7 61PP21O488 ; employeeAid +T 7 61PP21O48-
onpairwise Comparison Subuery ;isplay the details of the employees who are managed by the same manager as the employees with E,!L+=EEA7; 1O4 or 1PP and work in the same department as the employees with E,!L+=EEA7; 1O4 or 1PP. SELECT employeeAid2 managerAid2 departmentAid )*+, employees >ME*E managerAid 7 ; 6SELECT managerAid )*+, employees >ME*E employeeAid 7 61O421PP88 ; departmentAid 7 6SELECT departmentAid )*+, employees >ME*E employeeAid 7 61O421PP88 employeeAid +T 761O421PP8-
Scalar Subuery E$pressions [ scalar subuery e$pression is a subuery that returns e$actly one column 0alue from one row. [Scalar subueries can be used in: \ Condition and e$pression part of ;EC+;E and CSE \ ll clauses of SELECT e$cept J*+! D= Scalar Subueries: E$amples [ Scalar subueries in CSE e$pressions SELECT employeeAid2 lastAname2 6CSE ? >ME departmentAid 5 6SELECT departmentAid )*+, departments >ME*E locationAid 5 1N??8 TME GCanadaG ELSE GSG E;8 location )*+, employees[ Scalar subueries in +*;E* D= clause SELECT employeeAid2 lastAname )*+, employees e +*;E* D= 6SELECT departmentAname )*+, departments d >ME*E e.departmentAid 5 d.departmentAid8Correlated Subueries Correlated subueries are used for row/by/row processing. Each subuery is e$ecuted once for e0ery row of the outer uery.
Correlated Subueries The subuery references a column from a table in the parent uery. SELECT column12 column2 ...
outer )*+, table1 >ME*E column1 operator 6SELECT )*+, >ME*E column12 column table e$pr1 5 outer .e$pr8-
sing Correlated Subueries )ind all employees who earn more than the a0erage salary in their department. SELECT lastAname2 salary2 departmentAid )*+, employees outer >ME*E salary & 6SELECT J6salary8 )*+, employees >ME*E departmentAid 5 outer.departmentAid8sing Correlated Subueries ;isplay details of those employees who ha0e changed "obs at least twice. SELECT e.employeeAid2 lastAname2e."obAid )*+, employees e >ME*E %5 6SELECT C+T6(8 )*+, "obAhistory >ME*E employeeAid 5 e.employeeAid8C+/*ELTE; !;T !;TE E
SL& !;TE myemp outer SET departmentAname5 3 6 4 SELECT departmentAname 9 )*+, departments Q >ME*E departmentAid5outer.departmentAid O 8C+/*ELTE; ;ELETE SL& ;ELETE )*+, myemp outer >ME*E employeeAid 7 3 6 4 SELECT employeeAid 9 )*+, "obAhistory Q >ME*E employeeAid5outer.employeeAid O 8-
sing the EW7STS +perator [The EW7STS operator tests for e$istence of rows in the results set of the subuery. [7f a subuery row 0alue is found: \ The search does not continue in the inner uery \ The condition is flagged T*E [7f a subuery row 0alue is not found: found : \ The condition is flagged )LSE )LSE \ The search continues in the inner uery
)ind Employees >ho Ma0e at Least +ne !erson *eporting to Them SELECT employeeAid2 lastAname2 "obAid2 departmentAid )*+, employees outer >ME*E EW7STS 6 SELECT GWG )*+, employees >ME*E managerAid 5 outer.employeeAid8-
)ind ll ;epartments That ;o ot Ma0e ny Employees SELECT departmentAid2 departmentAname )*+, departments d >ME*E +T EW7STS 6SELECT GWG )*+, employees >ME*E departmentAid 5 d.departmentAid8-
Correlated !;TE se a correlated subuery to update rows in one table based on rows from another table.
LTE* TDLE emplQ ;;6departmentAname *CM*6988!;TE emplQ e SET departmentAname 5 6SELECT departmentAname )*+, departments d >ME*E e.departmentAid 5 d.departmentAid8-
Correlated ;ELETE se a correlated subuery to delete rows in one table based on rows from another table. ;ELETE )*+, table1 alias1 >ME*E column operator 6SELECT e$pression )*+, table alias >ME*E alias1.column 5 alias.column8-
se a correlated subuery to delete only on ly those rows from the E,!LQ table that also e$ist in the
E,!AM7ST+*= table. ;ELETE )*+, emplQ E >ME*E employeeAid 5 6SELECT employeeAid )*+, empAhistory >ME*E employeeAid 5 E.employeeAid8-
The >7TM Clause [sing the >7TM clause2 you can use the same uery block in a SELECT statement when it occurs more than once within a comple$ uery. [The >7TM clause retrie0es the results of a uery block and stores it in the user]s temporary temporary tablespace. [The >7TM clause impro0es performance.
sing the >7TM clause2 write a uery to display the department name and total salaries for those departments whose total salary is greater than the a0erage salary across departments. >7TM deptAcosts S 6 SELECT d.departmentAname2 S,6e.salary8 S deptAtotal )*+, employees e #+7 departments d + e.departmentAid 5 d.departmentAid J*+! D= d.departmentAname82 a0gAcost S 6 SELECT S,6deptAtotal8ME*E deptAtotal &
6SELECT deptAa0g )*+, a0gAcost8 +*;E* D= departmentAname-
SE* ,JE,ET ///////////////////////////// ;atabase: is a collection of logical storge units called as Tablespaces. SE*S 6default8 EW,!LE S=STE, S=SW TE,! 6default8 ;+ Tablespace is of 3 types: 1. !E*,ET: stores data permanently . TE,!+**=: stored data temporarily 3. ;+: stores uncommitted data To create a new user: C*ETE SE* %username& 7;ET7)7E; D= %password&-
SL& C*ETE SE* peter 7;ET7)7E; D= oracle-
!*i0ilege: is a right gi0en to a user to perform 0arious database acti0ities on ob"ects belonging to him or other users. Types: 1. S=STE, !*77LEJE means gi0ing a pri0ilege to a user to perform database operation his ob"ects.
. +D#ECT !*77LEJE allowing one user to access and manipulate the ob"ects belonging to another user.
To gi0e a pri0ilege to a user: J*T %pri0name& T+ %username&SL& C*ETE SE* peter 7;ET7)7E; D= oracleser created. SL& conn peter
>arning: =ou are no longer connected to +*CLE. SL& conn sys
SL& conn sys
Jrant succeeded. SL& conn peter
SL& conn sys
To remo0e a pri0ilege from the user SL& *E+RE create table )*+, peter-
*+LES : a role is a group of pri0ileges that can be granted to
To drop a role: ;*+! *+LE %rolename&+b"ect !ri0ileges ///////////////////////// Synta$: J*T %pri0name& + %ob"name& T+ %username&SL& J*T SELECT2 7SE*T + M*."obs 3 T+ peterSL& 7SE*T 7T+ hr."obs LES6G;DG2G;atabase dministratorG2Q???21???8To re0oke an ob"ect pri0ilege: SL& *E+RE select + hr."obs 3 )*+, peterSL& C*ETE SE* "erry 7;ET7)7E; D= oracle 3 ;E)LT TDLES!CE e$ample 4 +T L7,7TE; + e$ampleser created.
To create a new tablespace: SL& C*ETE TDLES!CE !urchaseTbs ;T)7LE G
3 S7XE 1??,Tablespace created. SL& LTE* TDLES!CE !urchaseTbs ;; ;T)7LE G
SL& l1 1( create user ally SL& c
To delete the logical as well as the physical structure: SL& drop tablespace admin including contents and datafiles-
To start start enterprise manager: +racle Enterprise ,anager 6+E,8: is a web/based application that allows the ;D to carry out 0arious admin acti0ities.
HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5orcl HoracleVra"i0 ;esktopI@ lsnrctl start LS*CTL for Linu$: ersion 11..?.1.? / !roduction on 1?/SE!/?19 1Q:?1:? Copyright 6c8 1PP12 ??P2 +racle. ll rights reser0ed. TS/?11?Q: Listener using listener name L7STEE* has already been started HoracleVra"i0 ;esktopI@ emctl start dbconsole +racle Enterprise ,anager 11g ;atabase Control *elease 11..?.1.? Copyright 6c8 1PPQ2 ??P +racle Corporation. ll rights reser0ed. https:<
Connected to: +racle ;atabase 11g Enterprise Edition *elease 11..?.1.? / !roduction >ith the !artitioning2 +L!2 ;ata ,ining and *eal pplication Testing options SL& startup To shutdown the instance: SL& shutdown immediate ;atabase closed. ;atabase dismounted. +*CLE instance shut down. SL& e$it ;isconnected from +racle ;atabase 11g Enterprise Edition *elease 11..?.1.? / !roduction >ith the !artitioning2 +L!2 ;ata ,ining and *eal pplication Testing options HoracleVra"i0 ;esktopI@ emctl stop dbconsole +racle Enterprise ,anager 11g ;atabase Control *elease 11..?.1.? Copyright 6c8 1PPQ2 ??P +racle Corporation. ll rights reser0ed. https:<
Stopping +racle Enterprise ,anager 11g ;atabase Control ... ... Stopped. HoracleVra"i0 ;esktopI@ lsnrctl stop LS*CTL for Linu$: ersion 11..?.1.? / !roduction on 1?/SE!/?19 1Q:?4:1 Copyright 6c8 1PP12 ??P2 +racle. ll rights reser0ed. Connecting to 6;ESC*7!T7+56;;*ESS56!*+T+C+L57!C86RE=5EWT!*+C191888 The command completed successfully
database is a collection of se0eral logical storage units called as Tablespaces Tablespace is shared among many users tablespace must be associated with atleast one database file 6.dbf8 file. tablespace can be of 3 types: 1. !ermanent . Temporary 3. ndo tablespace is made of logical units called as segments segment is a database ob"ect owned by a user. segment is a collection of many E$tents n E$tent is used to store one strip of data of the same type. n e$tent is a collection of many data blocks. ;ata block is the smallest unit of a database. single data block can store one piece of data
1. Configuring +racle network
tnsnames.ora listener.ora
To create a user with limited dba pri0ilege: Step1: 1. create a user with ;D2 C+ECT and *ES+*CE roles.
SL& C*ETE SE* lily 7;ET7)7E; D= oracleser created. SL& J*T dba2 resource2 connect T+ lilyJrant succeeded. Step : Connect to +E, as sys user //click setup //Click dministrator //Click Create //Type the user name //click re0iew !E*)+*,7J DCR! using +E, ////////////////////////////////////////////// The reco0ery files are located at
To check the status of *CM7EL+J mode: SL& *CM7E L+J L7ST sing +E,: 1. Connection to +E, as sys . Click on 0ailability Tab 3. nder setting region click *eco0ery Setting link 4. Check<ncheck the *CM7EL+J check bo$ to change the status 9. Click pply. Click +R to confirm shutting down the instance. Q. Enter the Most and ;atabase Credentials. O. Click +R to continue. >ait for the datbase to shutdown and restart with the change. To increase the )LSM *EC+E*= *E: LTE* S=STE, SET dbAreco0eryAfileAdestAsiFe 5 9J SC+!E5D+TM n +racle database ser0er consists of an +racle database and one or more database instances. The instance consists of memory structures and background processes. E0ery time an instance is started2 a shared memory area called the System Jlobal rea 6SJ8 is allocated and the background processes are started. The database consists of both physical structures and logical structures. Decause the physical and logical structures are separate2 the physical storage of data c an be managed without affecting access to logical storage structures.
The Shared ,emory rea or SJ is the shared by all the background processes. The !J or the !rogram Jlobal rea is shared by a user process. ,ounting the database means associating a database with an instance. ST*T! //+,+T //,+T //+!E To start the database in the +,+T stateL SL& ST*T! +,+T-
1. +racle will look for a parameter file called spfile%S7;&.ora 6spfile+*CL.ora8. 7f this file is not found2 it will then look for another file called spfile.ora. 7f this is also not found2 then it will look for init%S7;&.ora 6init+*CL.ora8 M=Z 1. )or certain database creation . )or recreation of control files. 3. )or certain Dackup and reco0ery operations. To start the database in the ,+T state: 7f you db is in 7;LE state: ST*T! ,+T 7f the db is in +,+T state: SL& LTE* ;TDSE ,+T1. +racle associate the database with a pre0iously started instance. . 7t will read the control files. fter reading the control files2 it will check the status of the online redolog files and database files from these controlfiles.
>M=: 1. To rename the database file . To perform certain backup and reco0ery operation. 3. Enabling
To switch to +!E state:
SL& LTE* ;TDSE +!E1. ll the database files and online redo log files are being read one by one.
>M=: 1. To enables the database users to connect to the instance.
SMT;+> ,+;ES /////////////////////////////// SMT;+> +*,L 1. o new connections will be allowed to be made. . +racle will wait for the users to disconnect the session. 3. >hen all the user processes are terminated2 the database and redo buffers will be written to disk. 4. The backgroup processes will be termianted and the SJ will be remo0ed from the instance. 9. The database instance will then be dismounted after closing all the control files2 redo log files and datbase files. Q. The ne$t startup will not reuire any instance reco0ery. SMT;+> T*CT7+L 1. o new connections will be allowed to be made. . The users who are not running any transactions will get immediately disconnected. 3. +racle will wait for the running transactions to complete. +n ce the transaction finishes2 the user will get disconnected. 4. o new transaction will be allowed to be made. 9. >hen all the user processes are terminated2 oracle will initiate the SMT;+> process. Q. The ne$t startup will not reuire any instance reco0ery SMT;+> 7,,E;7TE 1. o new connections will be allowed to be made. . The users who are not running any transactions will get immediately disconnected. 3. The currently running transactions will be rolled back and session will be disconnected
immediately 4. >hen all the user processes are terminated2 oracle will initiate the SMT;+> process. 9. The ne$t startup will not reuire any instance reco0ery SMT;+> D+*T 1. The ser process will not terminate . The transactions will not get rolled back 3. The database and redo buffers will bot be written to disk. 4. +*acle will close the instance without dismounting it. To take a backup using +E,: 1. Login to +E, as sys . Click on 0ailability tab
To !erform *eco0ery on/Critical *eco0ery 7f a Temp file is lost or damaged. Sol: =ou do not reuire a reco0ery file to reco0er a temp file. =ou need to recreate the temp file. ,ethod 1: shutdown and restart the database instance ,ethod : recreate the temp file SL& LTE* TDLES!CE TE,! ;; TE,!)7LE G
;T)7LE *EC+E*= 6ob"ect Le0el *eco0ery8 /////////////////////////////////////////////////////////////////// 7t reuires a L7; backup.
STE!S: 1. Connect to +E, as sys . Click 0ailability Tab. 3. nder ,anage region click !erform *eco0ery link 4. Select *eco0ery Scope as ;atafiles 9. +peration time as *Eco0er to current time Q. Enter the host credentials. Click the *eco0er button O. 7n step1 select the datafile to be reco0ered.click ne$t N. 7n step decide if you want the reco0ered file to be restored on a new location or not the default. click ne$t P. 7n re0iew step2 re0iew the *, scripts and click submit. ,J7J SCME, ; 7TS +D#ECTS /////////////////////////////////////////////////////// To create a database ob"ect: 1. Click on the schema tab
E$porting and 7mporting of data: Step: Create a user with ;D2 C+ECT and *ES+*CE role. SL& conn sys
//Connect to +E, as sys user //Click on setup link //Click dministrators menu //Click Create //Enter the user in the name field //Click *e0iew //Click )inish //Log out and login as the ;D user
To perform e$port: 1. Click ;ata ,o0ement Tab . under ,o0e *ow ;ata region2 click E$port to E$port files 3. Select the e$port type as Schema 4. Enter the host credentials 9. Click Continue to start the ;ata !ump >iFard Q. Click the dd button in step 1 and select the M* schema to be e$ported and click select O. Click e$t N. nder +ptional )ile region2 change the dir ob"ect to ;TA!,!A;7* and type the log file name and click ne$t P. 7n ne$t step2 change the directory ob"ect to ;TA!,!A;7*2 change the file name to ha0e a proper format and click ne$t. 1?. 7n the schedule step2 type the "ob name and description. Click ne$t 11. Click )inish to complete the "ob. Click on the "ob link to monitor the scheduled "ob.
To perform import 1. Click ;ata ,o0ement Tab . under ,o0e *ow ;ata region2 click 7mport from e$port files 3. Select the directory ob"ect as ;TA!,!A;7* abd enter the ;,! file name in the file name field 4. Select the import type as schema and enter the host credentials. 9. 7n step 12 add the schem to be imported. select the schema to be imported and click select and ne$t. Q. nder *e/,ap Schemas click dd another row. ensure that the source and destination schema is same. Click ne$t. O. 7n this step2select the directory ob"ect as ;TA!,!A;7* and enter the log file name. Click ne$t N. 7n this step2 type the "ob name and description.Lea0e the schedule for the default and click ne$t.
P. Click Submit to finish the "ob.
To perform e$porting
To import: HoracleVra"i0 dpdumpI@ impdp testemp
,anaging Manging issues //////////////////////////////////////// 1. Connect to +E, as sys .Click on the !Erformance Tab 3. Scroll down to additional monitoring links and click Dlocking sessions. 4.Click the Session id link 9. Click on !re0ious SL hash 0alue. Jo back and kill session if reuired.
;atabase uditing //////////////////////////// To enable auditing: 1. connec to +E, as sys . Click ser0er tab 3. nder ;atabase Configuration region click 7nitialiFation parameters link.
4. Click on S!)ile tab 9. Type the name audit in the name field and click go to 0iew 6;7TAT*7L8 Q. Change the auditAtrail parameter from db to $ml O. Click pply N. *estart the database to change the settings. Steps to set the ob"ect for auditing: 1. Click on the ser0er tab . nder Security region click udit Settings link. 3. Click on the udited ob"ects tab. 4. Click dd button. 9. Select the tables to be audited by clicking on the lookup icon. Q. ,o0e the reuired statements to be audited from a0ailable to selected statements list. O. Click +R to apply. SL& create user test19 identified by oracleser created. SL& grant connect to test19Jrant succeeded. SL& grant insert2 update2 delete on hr."obs 3 to test19Jrant succeeded. SL& grant select on hr."obs to test19-
To monitor the audited ob"ects //////////////////////////////////////////// 1. +n the +E, home2 click on the ser0er tab. . nder security2 click udit Settings link 3. nder udit Trails region click udited +b"ects link. ote: to disable auditing2 change the ;7TAT*7L parameter to +E and restart the database.
,J7J ;+ ;T ////////////////////////////////////////////
Dackground: =ou need to create a user account for #enny Joodman2 the new human resources department manager. There are also two new clerks in the human resources department2 ;a0id Mamby and *achel !andya. ll three must be able to log in to the orcl database and to select data from2 and update records in2 the M*.E,!L+=EES table. The manager also needs to be able to insert and delete new employee records. Ensure that if the new users forget to log out at the end of the day2 they are automatically logged out after 19 minutes. =ou also need to create a new user account for the in0entory application that you are installing. 7n this practice2 you create the 7ET+*= user to own the new 7n0entory application. =ou create a profile to limit the idle time of users. 7f a user is idle or forgets to log out after 19 minutes2 the user session is ended. slplus < as sysdba %% E+) drop user in0entory cascadecreate user in0entory identified by 0erysecure
default tablespace in0entorygrant connect2 resource to in0entoryCreate a profile named M*!*+)7LE that allows only 19 minutes idle time. a8 7n0oke Enterprise ,anager as the S=S user in the S=S;D role for your orcl database. b8 Click the Ser0er tab2 and then click !rofiles in the Security section. c8 Click the Create button. d8 Enter M*!*+)7LE in the ame field. e8 Enter 19 in the 7dle Time 6,inutes8 field. f8 Lea0e all the other fields set to ;E)LT. g8 Click the !assword tab2 and re0iew the !assword options2 which are currently all set to ;E)LT. h8 +ptionally2 click the Show SL button2 re0iew your underlying SL statement2 and then click *eturn. i8 )inally2 click +R to create your profile
Set the *ES+*CEAL7,7T initialiFation parameter to T*E so that your profile limits are enforced. a8 Click the Ser0er tab2 and then click 7nitialiFation !arameters in the ;atabase Configuration section. b8 Enter resourceAlimit in the ame field2 and then click Jo. c8 Select T*E from the alue drop/down list2 and then click pply.
Create the role named M*CLE*R with SELECT and !;TE permissions on the M*.E,!L+=EES table. a8 Click the Ser0er tab and then click *oles in the Security section. b8 Click the Create button. c8 Enter M*CLE*R in the ame field. This role is not authenticated. d8 Click +b"ect !ri0ileges tab. e8 Select Table from the Select +b"ect Type drop/down list2 and then click dd. f8 Enter M*.E,!L+=EES in the Select Table +b"ects field. g8 ,o0e the SELECT and !;TE pri0ileges to the Selected !ri0ileges bo$. Click +R. h8 Click the Show SL button2 and re0iew your underlying SL statement. i8 Click *eturn2 and then click +R to create the role.
Create the role named M*,JE* with 7SE*T and ;ELETE permissions on the M*.E,!L+=EES table. Jrant the M*CLE*R role to the M*,JE* role. a8 Click the Ser0er tab2 and then click *oles in the Security section. b8 Click Create. c8 Enter M*,JE* in the ame field. This role is not authenticated. d8 Click +b"ect !ri0ileges tab. e8 Select Table from the Select +b"ect Type drop/down list2 and then click dd. f8 Enter M*.E,!L+=EES in the Select Table +b"ects field. g8 ,o0e the 7SE*T and ;ELETE pri0ileges to the Selected !ri0ileges bo$. Click +R. h8 Click the *oles tab2 and then click Edit List. i8 ,o0e the M*CLE*R role into the Selected *oles bo$2 and then click +R. "8 Click the Show SL button2 and re0iew your underlying SL statement. k8 Click *eturn2 and then click +R to create the role.
,J7J SCME,
,J7J MJ7J SESS7+S The Melp desk "ust recei0ed a call from Susan ,a0ris2 an M* representati0e2 complaining that the database is ^froFen._ pon uestioning the user2 you find that she was trying to update #ohn Chen]s personnel record with his new phone number2 but when she entered the new data2 her session froFe and she could not do anything else. SL script files are pro0ided for you in the
1. run the labA?PA?1.sl script. This script first creates the users 6sma0ris and ngreenberg8 that are in0ol0ed in this practice and the hremployee role that will gi0e these new users access to the hr.employee table. 7t then logs in to SL(!lus as the ngreenberg user and performs an update on the hr.employee table. The script does not perform a commit2 lea0ing the update uncommitted in this session.
Lea0e this session connected in the state that it is currently. ;o not e$it at this time.
,ake an attempt to update the same row in a separate session by running2 in a separate terminal window2 the labA?PA?.sl script. ,ake sure that you see the message ^pdate is being attempted now_ before mo0ing on. ;o not worry if the session seems to ^hang_`this is the condition that you are trying to create.
otice that this session appears to be hung. Lea0e this session as is and mo0e on to the ne$t step.
sing Enterprise ,anager2 click the Dlocking Sessions link on the !erformance page and detect which session is causing the locking conflict. a8 7n Enterprise ,anager2 click the !erformance page. a8 Select the J*EEDE*J session2 and then click iew Session. b8 Click the hash 0alue link named ^!re0ious SL._ c8 ote the SL that was most recently run. a8 Click the browser]s Dack button. b8 ow2 on the Session ;etails: J*EEDE*J page2 click Rill Session. c8 Lea0e the +ptions set to Rill 7mmediate2 and then click Show SL to see the statement that is going to be e$ecuted to kill the session. d8 Click *eturn2 and then click =es to carry out the R7LL SESS7+ command. *eturn to the SL(!lus command window2 and note that S,*7S]s update has now completed successfully. 7t may take a few seconds for the success message to appear.
,J7J ;+ ;T The business users and management in your organiFation decide2 that they need to ha0e a 4N/hour retention of undo in the +racle database to support their flashback needs. =our task is to configure the orcl database to support this reuirement. 7n this practice2 you first 0iew your system acti0ity regarding undo2 and then you configure the orcl database to support 4N/hour retention for flashback operations. 18 7n Enterprise ,anage r2 as the ;D1 user2 0iew the undo related system acti0ity. a8 Click the Ser0er tabbed page and select ^utomatic ndo ,anagement_ in the ;atabase Configuration section. b8 Click the System cti0ity tabbed page. ote: =our information will look different on all analysis screenshots2 based on your analysis period and the system acti0ity during this period. c8 uestion: Looking at the preceding screenshot2 how many errors did this system encounterZ nswer: one d8 uestion: Looking at the preceding screenshot2 what is the duration of the longest running ueryZ nswer: N minutes 6=our answer may be different.8 e8 Click the !lus icon to show related graphs. f8 uestion: Mow many graphs are displayedZ nswer: Three. 6ndo Tablespace sage2 ndo *etention uto/Tuning2 and ndo Jeneration *ate8 g8 uestion: Looking at the preceding ndo *etention uto/Tuning graph2 could this system support flashback abo0e and beyond the current longest running ueryZ nswer: =es2 6but most likely not enough to support the reuired 4N hours8. 8 ,odify the undo retention time and calculate the undo tablespace siFe to support the reuested 4N/hour retention. a8 Click the Jeneral tab to go back to the Jeneral utomatic ndo ,anagement page. b8 nder the ndo d0isor section2 select ^Specified manually to allow for longer duration ueries or flashback._ c8 Enter 4N hours as ;uration and click the *un nalysis button. d8 >hen the ndo d0isor is finished2 take a look at the results. 7t looks like the undo tablespace is 0ery close to the recommended undo tablespace siFe. This is okay for most workloads2 but the recommendation is to set your undo tablespace siFe to be three times the minimum siFe. This means that you should change your undo tablespace siFe to be N4Q ,D. ote: =our recommended siFe might be different from what is shown here2 so
ad"ust the siFe accordingly. e8 Click the Show SL button in the upper/right corner of the Jeneral utomatic ndo ,anagement page. f8 This command will change the undo retention to support the 4N/hour reuirement. *e0iew the SL statement and click *eturn. g8 Click pply to make the change to undo retention. h8 ow ad"ust the undo tablespace siFe by clicking the Edit ndo Tablespace button. i8 Scroll down to ;atafiles and click Edit to make a change to the undotbs?1.dgf file siFe. "8 Change the file siFe to N4Q ,D and click Continue. k8 erify the SL commands that will be e$ecuted by clicking Show SL. Click *eturn. l8 Click pply to change the tablespace siFe. 38 Jo back to the utomatic ndo ,anagement to see the results of the changes you "ust made. =ou see that the undo retention time has increased to support the 4N hours reuirement. =our undo tablespace siFe has also increased based on the changes you made to the siFe of the datafile for the undo tablespace. a8 uestion: >hich )lashback operations are potentially affected by this changeZ nswer: )lashback uery2 )lashback transaction2 and )lashback table. b8 uestion: ;o undo data sur0i0e the shutdown of a databaseZ nswer: =es2 undo is persistent.
;7T7J Dackground: =ou ha0e "ust been informed of suspicious acti0ities in the M*.#+DS table in your orcl database. The highest salaries seem to fluctuate in a strange way. =ou decide to enable standard database auditing and monitor data manipulation language 6;,L8 acti0ities in this table. Log in as the ;D1 user 6with oracle password2 connect as S=S;D8 and perform the necessary tasks either through Enterprise ,anager ;atabase Control or through SL(!lus. ll scripts for this practice are in the
e8 Enter audit in the ame field and then click Jo. f8 )or the auditAtrail parameter2 select the W,L 0alue. g8 Click Show SL. h8 *e0iew the statement and then click *eturn. i8 +n the 7nitialiFation !arameters page2 click pply.
8 Decause you changed a static parameter2 you must restart the database. ;o so by running the labA11A?.sh script. a8 7n a terminal window2 enter: b8 Continue with the ne$t step when you see that the database is restarted and the script has e$ited SL(!lus. 38 Dack in Enterprise ,anager2 select M*.#+DS as the audited ob"ect and ;ELETE2 7SE*T2 and !;TE as Selected Statements. Jather audit information by session. Decause the database has been restarted2 you ha0e to log in to Enterprise ,anager again as the ;D user. a8 Click logout in the upper/right corner of the Enterprise ,anager window. b8 Log in as the ;D1 user in the S=S;D role for your orcl database. c8 Click the ;atabase home page tab to ensure that Enterprise ,anager had time to update the status of the database and its agent connections. d8 Click the Ser0er tab2 and then click udit Settings in the Security section. e8 Click the udited +b"ects tab at the bottom of the page2 and then click the dd button. 8 +n the dd udited +b"ect page2 ensure that the +b"ect Type is Table2 and enter M*.#+DS in the Table field 6or use the flashlight icon to retrie0e this table8. g8 ,o0e ;ELETE2 7SE*T2 and !;TE into the Selected Statements area by double/clicking each of them. h8 Click Show SL. i8 *e0iew the statement2 and then click *eturn. "8 Click +R to acti0ate this audit. 48 !ro0ide input for the audit2 by e$ecuting the labA11A?4.sh script. This script creates the ;7TASE* user2 connects to SL(!lus as this user2 and multiplies the 0alues in the ,WASL*= column by 1?. Then the M* user connects and di0ides the column 0alues by 1?. )inally2 the ;7TASE* user is dropped again. a8 7n a terminal window2 enter: 98 7n Enterprise ,anager2 re0iew the audited ob"ects. a8 Click the Ser0er tab2 and then click udit Settings in the Security section. b8 Click udited +b"ects in the udit Trails area2 which is on the right side of the
page. c8 +n the udited +b"ects page2 re0iew the collected information. uestion: Can you tell which user increased and which user decreased the salariesZ nswer: o2 the standard audit records only show which user accessed the table. d8 Click *eturn. Q8 ndo your audit settings for M*.#+DS2 disable database auditing2 and then restart the database by using the labA11A?Q.sh script. a8 +n the udit Settings page2 click the udited +b"ects tab at the bottom of the page. b8 Enter M* as Schema2 and then click Search. c8 Select all three rows2 and then click *emo0e. d8 +n the Confirmation page2 click Show SL. e8 *e0iew the statements2 and then click =es to confirm your remo0al. f8 +n the udit Settings page2 click W,L in the Configuration region. g8 +n the 7nitialiFation !arameters page2 click the S!) ile tab. h8 +n the S!)ile page2 enter audit in the ame field2 and then click Jo. i8 )or the auditAtrail parameter2 select the ;D 0alue. "8 Click Show SL. k8 *e0iew the statement2 and then click *eturn. l8 +n the 7nitialiFation !arameters page2 click pply. m8 Decause you changed a static parameter2 you must restart the database. ;o so by running the labA11A?Q.sh script. 7n a terminal window2 enter:
O8 ,aintain your audit trail: Decause you are completely finished with this task2 backup and delete all audit files from the
DCR! and *EC+E*= Dackground: =our orcl database is ready to mo0e from test or de0elopment into production. Configure your database to reduce the chances of failure or data loss. To do so2 perform the following tasks: [ Ensure redundancy of control files and backup the control file to trace
[ *e0iew the flash reco0ery area configuration [ Ensure that there are at least two redo log members in each group [ !lace your database in *CM7EL+J mode [ Configure redundant archi0e log destinations 7n this practice2 you configure your database to reduce the chances of failure or data loss. ote: Completing this practice is a prereuisite for all following backup and reco0ery practices. 18 erify that you ha0e at least two control files to ensure redundancy and backup the control file to trace. a8 7n0oke Enterprise ,anager as the ;D1 user in the S=S;D role for your orcl database. b8 Click Ser0er & Control )iles 6in the Storage section8. uestion 1: +n the Control )iles: Jeneral page2 how many control files do you ha0eZ nswer: Three 6in the preceding e$ample8. uestion : Mow would you add another control file if you needed toZ nswer: dding a control file is a manual operation. To perform this2 you must: [ Shut down the database [ se the operating system to copy an e$isting control file to the location where you want your new file to be. [ Start the database by using Enterprise ,anager. nlike a normal startup2 you would use d0anced +ptions to select a different startup mode. Select ^Start the instance_ to lea0e the instance in the +,+T state. [ Edit the C+T*+LA)7LES initialiFation parameter to point to the new control file. [ Continue the ST*T! database operation until the database is in an open state. ote [ [ This answer does not apply to an +,) database because the control files in that case would ha0e to all be re/created. lternati0ely2 if you did not want to use Enterprise ,anager to perform the steps2 you could perform the steps outlined in the ,ultiple$ing Control )iles slide in the ^Dackup and *eco0er Concepts_ lesson. c8 Click Dackup to Trace. d8 >hen you recei0e the success message2 note the trace directory location2 and then click +R. e8 +ptionally2 use a terminal window2 logged in as the oracle user to 0iew the trace file name at the end of the alert log by e$ecuting the following command:
cd hich essential ;D tasks can you perform in this sectionZ nswer: =ou can change the location2 siFe or retention time for the flash reco0ery area2 as well as enable the )lashback ;atabase functionality. f8 uestion: ;oes changing the siFe of the flash reco0ery area reuire the database to be restartedZ nswer: o2 a restart is not reuired for this change. g8 Change the siFe of the )lash *eco0ery rea to N JD2 by entering N into the ^)lash *eco0ery rea SiFe_ field. h8 +ptionally2 click Show SL2 re0iew the statement and click *eturn. i8 Click pply. 38 Check how many members each redo log group has. Ensure that there are at least two redo log members in each group. +ne set of members should be stored in the flash reco0ery area. a8 Click Ser0er & *edo Log Jroups2 and note how many members are in the ^Y of ,embers_ column. nswer: There is only one member in each group. b8 To add a member to each group2 perform the following steps for each group: i8 Select the group 6for e$ample2 18 and click the Edit button. ii8 +n the Edit *edo Log Jroup page2 note the )ile ame2 for e$ample ^redo?1.log_ and click the dd button. iii8 +n the Edit *edo Log Jroup: dd *edo Log ,ember page2 enter a file name by adding the letter ^b_ to the end of the name 6before the dot8. )or e$ample2 enter redo?1b.log as )ile ame and enter your flash reco0ery area2 for e$ample2
i08 Click Continue. 08 +n the Edit *edo Log Jroup page2 re0iew the current members. ote: 7f you forget to add the last slash to the directory name2 the system does it for you. 0i8 +ptionally2 click Show SL to re0iew the command2 and then *eturn. 0ii8 Click pply. 0iii8 =ou should recei0e a success message. *eturn to the *edo Log Jroups page and repeat the preceding seuence of steps 6under 3 b8 for each of the remaining redo log groups. c8 =ou are finished with this task2 when the Y of members displays for each group. ote: 7n a production database2 you want to ensure that the two members are on different hard dri0es2 preferably with different disk controllers2 to minimiFe the risk of any single hardware failure destroying an entire log group. 48 =ou notice that2 for each log group2 the rchi0ed column has a 0alue of o. This means that your database is not retaining copies of redo logs to use for database reco0ery2 and in the e0ent of a failure2 you will lose all data since your last backup. !lace your database in *CM7EL+J mode2 so that redo logs are archi0ed. ote: =ou must continue with step 92 so that your changes are applied. a8 7n Enterprise ,anager2 select 0ailability & *eco0ery Settings in the Setup section. b8 7n the ,edia *eco0ery region2 select the *CM7EL+J ,ode check bo$. c8 erify that Log rchi0e )ilename )ormat contains t2 s2 and r. d8 otice the current configuration of redundant archi0e log destinations`one to the flash reco0ery area and the other to hen prompted whether you want to restart the database now2 click =es. g8 Enter the credentials to restart the database 6oracle as the Most Credentials2 and syshen asked to confirm2 click =es again. i8 Should you recei0e an error during the shutdown and startup acti0ity2 click +R to acknowledge the error2 and then click *efresh again. 6=ou might ha0e been simply faster than the database.8 98 +ptionally2 use SL(!lus to check whether your database is in *CM7EL+J mode. 7n a terminal window2 log in to SL(!lus as S=S;D and run the archi0e log
list command. ow that your database is in *CM7EL+J mode2 it will continually archi0e a copy of each online redo log file before reusing it for additional redo data. ote: *emember that this consumes space on the disk and that you must regularly back up older archi0e logs to some other storage.
=our database is ready to mo0e from de0elopment and testing into production. Ensure that your database is configured so that reco0ery is possible without loss of data. Establish the backup policy to automatically back up the S!)7LE and control file. !erform an immediate backup to disk and schedule nightly backup "obs that repeat indefinitely. 7n this practice2 you perform an immediate backup to disk and schedule a nightly backup "ob. 18 >hat is the difference between a backup set and an image copyZ nswer: backup set contains data and archi0e log files packed in an +racle proprietary format. )iles must be e$tracted before use. 7mage copies are the eui0alent of operating system file copies and can be used for restore operations immediately. 8 >hat is the destination of any disk backups that are doneZ a8 Log in to Enterprise ,anager as the ;D1 user in the S=S;D role and select 0ailability & Dackup Settings. b8 ote the message under the ;isk Dackup Location that says the flash reco0ery area is the current disk backup location. 38 Establish the backup policy to automatically back up the S!)7LE and control file. a8 Click the !olicy tab under the Dackup Settings pages. b8 Click utomatically backup the control file and ser0er parameter file 6S!)7LE8 with e0ery backup and database structural change. c8 Scroll to the bottom and enter oracle and oracle for Most Credentials sername and !assword for your ser0er2 and click ^Sa0e as !referred Credential._ 48 Test making a backup to disk2 as a backup set2 with oracle for Most Credentials. a8 Click the ;e0ice tab under the Dackup Settings pages. b8 Select Dackup Set as your ;isk Dackup Type. c8 Scroll to the bottom and ensure the Most Credentials are set to oracle. d8 Scroll to the top of the page and click Test ;isk Dackup. e8 processing message appears. >hen the test finishes2 and you see the ;isk Dackup Test SuccessfulK] message2 click +R. 98 Dack up your entire database2 with archi0e logs2 while the database is open for user
acti0ity. This backup should be the base for an incremental backup strategy. a8 uestion: >hat prereuisite must be met to create a 0alid backu p of a database without shutting it downZ nswer: The database must be in *CM7EL+J mode. Dackups made with the database open2 but not in *CM7EL+J mode2 cannot be used for reco0ery. b8 Select 0ailability & Schedule Dackup 6in the ,anage section8. 7f you find that the +racle/Suggested Dackup strategy fits your needs e$actly2 you would chose this option. )or practice purposes2 you will schedule a customiFed backup c8 Select >hole ;atabase as the ob"ect to be backed up. d8 Confirm or enter oracle and oracle for Most Credentials sername and !assword for your ser0er. e8 Click Schedule CustomiFed Dackup. f8 +n the Schedule CustomiFed Dackup: +ptions page2 select )ull Dackup for your Dackup Type2 and select the se as the base of an incremental backup strategy check bo$. g8 Select +nline Dackup as Dackup ,ode. h8 7n the d0anced section2 select ^lso back up all archi0ed logs on disk_ and ^;elete all archi0ed logs from disk after they are successfully backed up_2 and then click e$t to continue. i8 +n the Schedule CustomiFed Dackup: Settings page2 select ;isk for your backup location. 6otice that your ;isk Dackup Location is retained and that you could o0erride the current settings for a one/off backup. Dut do not click it this time.8 "8 Click e$t. k8 ccept all the defaults on the Schedule CustomiFed Dackup: S chedule page and then click e$t to continue. ote: Schedule Type should be +ne Time 67mmediately8. l8 +n the Schedule CustomiFed Dackup: *e0iew page2 re0iew the *, script2 and then click Submit #ob. m8 Click iew #ob to monitor the status of the backup "ob. The time for this backup depends on your hardware and system resources. n8 Click your browser]s *efresh or *euery button until the "ob is co mpleted. Q8 Schedule nightly disk/based incremental online backups for your whole database2 without archi0ed logs. Schedule it for e$ecution at 11:?? !,. The schedule should be in effect indefinitely. a8 7n Enterprise ,anager2 select 0ailability & Schedule Dackup 6in the ,anage section8. b8 Select >hole ;atabase as the ob"ect to be backed up. c8 Confirm or enter oracle and oracle for Most Credentials sername and !assword for your ser0er2 and then click Schedule CustomiFed Dackup. d8 +n the Schedule CustomiFed Dackup: +ptions page2 select 7ncremental Dackup
as your Dackup Type. e8 Select +nline Dackup as Dackup ,ode. f8 7n the d0anced region2 select ^lso backup all archi0ed logs on disk_ and ^;elete all archi0ed logs from disk after they are successfully backed up_2 and then click e$t to continue. g8 +n the Schedule CustomiFed Dackup: Settings page2 select ;isk as your backup location2 and then click e$t to continue. h8 +n the Schedule CustomiFed Dackup: Schedule page2 change #ob ame to ightlyADackup and accept the default 0alue for #ob ;escription. i8 Select *epeating in the Schedule region. otice how additional conte$t sensiti0e details are displayed. "8 Select Dy ;ays from the )reuency Type drop/down list2 enter 1 in the *epeat E0ery field2 confirm that 7ndefinite is selected as the *epeat ntil 0alue2 and enter 11:?? !, as Time. k8 Click e$t to continue. l8 +n the Schedule CustomiFed Dackup: *e0iew page2 re0iew your Settings and *, script. m8 Click Submit #ob2 and then click +R. n8 Click #obs on the 0ailability page in the *elated Links section to see the scheduled "ob in the #ob cti0ity list.
,any failures of the +racle database can be traced to some sort of media failure2 such as disk or controller failure. 7n this practice2 you encounter a number of problems from which you need to reco0er the database. [ *eco0er from the loss of a control file [ *eco0er from the loss of a data file [ *eco0er from the loss of a redo log member [ *eco0er from the loss of a file in the S=STE, tablespace SL script files are pro0ided for you in the
18 Defore setting up an indi0idual problem2 you need to na0igate to your labs directory and 6in SL(!lus8 e$ecute the labA1QA?1.sl script as the S=S user. This script prepares some procedures to be called by the rest of this practice. 7n this practice2 your system e$periences the loss of a control file. =ou then go through the steps to reco0er from this loss. 18 Continue in your SL(!lus session as the S=S user. E$ecute the labA1QA?.sl script. This script deletes one of your control files. 8 The Melp desk begins recei0ing calls saying that the database appears to be down. Troubleshoot and reco0er as necessary. se Enterprise ,anager to try to start up the database2 and use SL(!lus if needed. a8 7n Enterprise ,anager2 na0igate to the ;atabase home page. 7t reports that the database is down and offers you the chance to start it up again. ote: you may see a message stating 7nternal Error has occurred.] 7f so2 keep trying to connect to using the Enterprise ,anager *L. E0entually it will display the database home page. b8 Click Startup. 7f you see a Connection *efused message2 ignore it- the connection will e0entually be established. c8 Enter oracle as sername and !assword for Most Credentials and click +R. d8 Click =es to confirm your attempted startup. e8 The startup of the instance fails with Enterprise ,anager. Click iew ;etails for more information. f8 ote the following2 and then click +R: +*/???9: error in identifying control file2 check alert log for more info g8 lternati0ely2 in a new SL(!lus session2 check the current status of the instance as the S=S user and attempt to mount it with the following commands: select status from 0@instancealter database mount38 The instance cannot mo0e to the mount stage because it cannot find one of the control files. To find the locations of the alert log and of diagnostic information2 enter the following SELECT statement: SELECT ,E2 LE )*+, @;7JA7)+48 Look at the last 9 lines in the log.$ml file to see if you can find out what the problem is. Still inside your SL(!lus session2 enter the following command 6on one line8: host tail /9
host cp hy did you ha0e to use two commands to mo0e the instance state from +,+T to +!EZ nswer: Decause the LTE* ;TDSE command enables you to change only one state le0el for each command b8 >hy did you use operating system commands to restore the control file instead of using +racle *eco0ery ,anagerZ nswer: Decause all control files are identical. s long as any one control file is intact2 it can be used to restore the others. N8 E$it all sessions and close all windows
*eco0ering from the Loss of a ;ata )ile 18 7n a SL(!lus session2 as the S=S user2 e$ecute the labA1QA?3.sl script from your labs directory. This script deletes one of your application data files. 8 The Melp desk has recei0ed a call from a user who is unable to access the C+T*7ES table in the M* application schema. Count the rows in the table to determine whether there is a problem. 38 Troubleshoot and reco0er as necessary. The error message suggests that the e$ample?1.dbf data file is corrupt or missing. a8 7n Enterprise ,anager2 click 0ailability & !erform *eco0ery. b8 Click d0ise and *eco0er. c8 +n the iew and ,anage )ailures page2 click the plus 68 icon under the failure description. =ou should see a failure like the following: ote: 7f you do not see the nonsystem datafile failure2 keep refreshing the page until it shows up. d8 >ith the failures selected2 click d0ise. e8 Decause the file was not "ust renamed or mo0ed2 but deleted2 you continue by clicking ^Continue with d0ise._ f8 +n the *eco0ery d0ise page2 you see the *, script. Click Continue. g8 +n the *e0iew page2 you see the failure and the suggested solution. Click ^Submit *eco0ery #ob._
h8 !rocessing window appears2 followed by the #ob cti0ity page. =ou should see a message that the "ob was successfully created. 6=our link name is probably different.8 i8 Click the "ob name link. "8 +n the #ob *un page2 check the Status in the Summary section. 7f it is *unning2 use you browser]s *efresh or *euery button until the "ob is completed. k8 7n your SL(!lus session2 0erify that the M*.C+T*7ES table is now accessible.
7n this practice2 your system e$periences the loss of a redo log member. =ou then go through the steps to reco0er from this loss. 18 ,ake sure that you are in your labs directory. sing SL(!lus2 e$ecute the labA1QA?4.sl script as the S=S user. The labA1QA?4.sl script deletes one of your redo log files. See the error in the alert log and reco0er from it. 8 The database continues to function normally2 and no users are complaining. Log in to Enterprise ,anager with the ;D1 username as S=S;D. +n the ;atabase home page2 0iew alerts similar to the following ones: 7f you do not see similar alerts2 you may need to wait a few minutes and refresh the page. +ne of the failures listed may be left o0er from the data file reco0ery you performed in the pre0ious practice. 38 Click 0ailability & !erform *eco0ery 6in the ,anage section8. 48 +n the !erform *eco0ery page2 you see the )ailure ;escription and could directly begin correcting the failure. Dut for practice purposes2 you follow the steps in the ;ata *eco0ery d0isor. Scroll down and ensure that your host credentials are set 6oracle for both username and password8. Then click the ^d0ise and *eco0er_ button 6which is one of the ways to in0oke the ;ata *eco0ery d0isor8. 98 +n the iew and ,anage )ailures page2 ensure that the failure is selected2 an d click d0ise. Q8 The ,anual ctions page suggests to manually restore it. 7n the preceding e$ample2 redo?3.log is deleted. ;o not click any button at this point in time. O8 7n a new terminal window2 as the oracle user2 copy an e$isting redo log from the same redo log group to the missing file. ote: The actual redo log member that was lost on your machine may be different than the one shown here. ,ake sure that you are replacing the file names as appropriate for your failure. cd
ls e$it N8 ow return to your ,anual ctions page in Enterprise ,anager and click the *e/ assess )ailures button. a8 ote that there are now no failures found. b8 uestion: >hy did the database not crashZ nswer: Decause a single missing member is noncritical and does no t affect the operation of the database. s long as there is at least one good member for each log group2 the database operation continues. 7n this practice2 your system e$periences the loss of a file in the S=STE, tablespace. =ou then go through the steps to reco0er from this loss. 18 >hy is reco0ery from the loss of a system data file or a data file belonging to an undo tablespace different from reco0ering an application data fileZ nswer: Decause reco0ery of system or undo data files must be done with the database closed2 whereas reco0ery of an application data file can be done with the database open and a0ailable to users 8 s the S=S user2 e$ecute the labA1QA?9.sl script in your labs directory. This script deletes the system data file. 38 7n Enterprise ,anager2 re0iew the ;atabase home page. 7f you see a message that says the connection was refused2 dismiss it and reenter the E, home page *L in the browser. =ou may need to try se0eral times before you see the ;atabase home page. 48 The database is shut down. ttempt to start your database. a8 Click Startup to try to open it. b8 +n the _Startup
default location_ and then click e$t. f8 +n the !erform +b"ect Le0el *eco0ery: *e0iew page2 0iew your current options and the data file. Click Edit *, Script to re0iew the *, commands. g8 *e0iew the *, commands and click Submit. h8 processing page appears2 followed by the !erform *eco0ery: *esult page. The duration of this operation depends on your system resources. The reco0ery operation should be successful. i8 +n the !erform *eco0ery: *esult page2 click +pen ;atabase. "8 fter you see the success message2 click +R. k8 erify that the database is open and operating normally by logging in to E, as your ;D1 user as S=S;D2 and re0iewing the ;atabase home page.
EW!+*T7J<7,!+*T7J n the recent past2 you recei0ed a number of uestions about the M* schema. To analyFe them without interfering in daily acti0ities2 you dec ide to use the ;ata !ump >iFard to e$port the M* schema to file. >hen you perform the e$port2 you are not sure into which database you will be importing this schema. 7n the end2 you learn that the only database for which management appro0es an import is the orcl database. So you perform the import with the ;ata !ump >iFard2 remapping the M* schema to ;D1 schema. Then you recei0e two data load reuests for which you decide to use SL(Loader. 7n this practice2 you first grant the ;D1 user the pri0ileges necessary to pro0ide access to the ;TA!,!A;7* directory. =ou then e$port the M* schema so that you can then import the tables you want into the ;D1 schema. 7n the practice2 you import only the E,!L+=EES table at this time. 18 )irst2 you need to grant the ;D1 user the appropriate pri0ileges on the ;TA!,!A;7* directory and create the users and roles reuired for this practice. script e$ists that performs all the steps reuired to configure your en0ironment for this practice. a8 *e0iew the labA1OA?1.sl script2 which grants the ;D1 user pri0ileges on the ;TA!,!A;7* directory and performs other configurations to your en0ironment2 by e$ecuting the following in your labs directory: @ cat labA1OA?1.sl b8 The labA1OA?1.sh script calls the labA1OA?1.sl script. E$ecute the labA1OA?1.sh script now: 8 Log in to Enterprise ,anager as the ;D1 user in the ormal role and e$port the M* schema. a8 7n0oke Enterprise ,anager as the ;D1 user as the ormal role for your orcl
database. The Connect s setting should be ormal. b8 Select ;ata ,o0ement & ,o0e *ow ;ata & E$port to E$port )iles. c8 Select Schemas2 enter oracle as sername and !assword2 select Sa0e as !referred Credential2 and then click Continue. d8 +n the E$port: Schemas page2 click dd2 select the M* schema2 and then click the Select button. e8 =ou see that M* is now in the list of schemas. Click e$t. f8 +n the ^E$port: +ptions_ page2 select ;TA!,!A;7* from the ;irectory +b"ects drop/down list2 and enter hre$p.log as Log )ile. g8 *e0iew d0anced +ptions 6but do not change82 and then click e$t. h8 +n the ^E$port: )iles_ page2 select ;TA!,!A;7* from the ;irectory +b"ect drop/down list2 enter M*EW!.;,! as )ile ame2 and then click e$t. i8 +n the ^E$port: Schedule_ page2 enter hre$p as #ob ame and E$port M* schema as ;escription2 accept the immediate "ob start time2 and then click e$t. "8 +n the ^E$port: *e0iew_ page2 click Show !LiFard helped you to create. k8 Click Submit #ob to submit the "ob. l8 Click the link to the M*EW! "ob to monitor the progress. >hen the "ob shows as successfully completed2 mo0e on to the ne$t step. 38 ow2 import the E,!L+=EES table from the e$ported M* schema into the ;D1 schema. To get a feeling for the command/line interface2 you can use the impdp utility from the command line to import the E,!L+=EES table into the ;D1 user schema. a8 Enter the following entire command string. ;o not press HEnterI before reaching the end of the command: impdp dba1
7n this practice2 you load data into the !*+;CTA,STE* table by using SL(Loader 0ia Enterprise ,anager ;atabase Control. ;ata and control files are pro0ided. 18 s the ;D1 user2 use Enterprise ,anager to load the labA1OA?A?1.dat data file. This data file contains rows of data for the !*+;CTA,STE* table. The labA1OA?A?1.ctl file is the control file for this load. +ptionally2 0iew the labA1OA?A?1.dat and labA1OA?A?1.ctl files to learn more about their structure before going further. a8 7n0oke Enterprise ,anager as the ;D1 user as the ormal role for your orcl database. b8 Select ;ata ,o0ement & ,o0e *ow ;ata & Load ;ata from ser )iles. c8 Click se E$isting Control )ile. 7f not already entered2 enter oracle as sername and as !assword2 click Sa0e as !referred Credential2 and then click Continue. d8 +n the ^Load ;ata: Control )ile_ page2 enter
a8 +pen a terminal window and na0igate to the
E$porting and 7mporting of data: Step: Create a user with ;D2 C+ECT and *ES+*CE role. SL& conn sys
//Connect to +E, as sys user //Click on setup link //Click dministrators menu //Click Create //Enter the user in the name field //Click *e0iew //Click )inish //Log out and login as the ;D user
To perform e$port:
1. Click ;ata ,o0ement Tab . under ,o0e *ow ;ata region2 click E$port to E$port files 3. Select the e$port type as Schema 4. Enter the host credentials 9. Click Continue to start the ;ata !ump >iFard Q. Click the dd button in step 1 and select the M* schema to be e$ported and click select O. Click e$t N. nder +ptional )ile region2 change the dir ob"ect to ;TA!,!A;7* and type the log file name and click ne$t P. 7n ne$t step2 change the directory ob"ect to ;TA!,!A;7*2 change the file name to ha0e a proper format and click ne$t. 1?. 7n the schedule step2 type the "ob name and description. Click ne$t 11. Click )inish to complete the "ob. Click on the "ob link to monitor the scheduled "ob.
To perform import 1. Click ;ata ,o0ement Tab . under ,o0e *ow ;ata region2 click 7mport from e$port files 3. Select the directory ob"ect as ;TA!,!A;7* abd enter the ;,! file name in the file name field 4. Select the import type as schema and enter the host credentials. 9. 7n step 12 add the schem to be imported. select the schema to be imported and click select and ne$t. Q. nder *e/,ap Schemas click dd another row. ensure that the source and destination schema is same. Click ne$t. O. 7n this step2select the directory ob"ect as ;TA!,!A;7* and enter the log file name. Click ne$t N. 7n this step2 type the "ob name and description.Lea0e the schedule for the default and click ne$t. P. Click Submit to finish the "ob.
To perform e$porting
HoracleVra"i0 dpdumpI@ e$pdp prince
To import: HoracleVra"i0 dpdumpI@ impdp testemp
,anaging Manging issues //////////////////////////////////////// 1. Connect to +E, as sys .Click on the !Erformance Tab 3. Scroll down to additional monitoring links and click Dlocking sessions. 4.Click the Session id link 9. Click on !re0ious SL hash 0alue. Jo back and kill session if reuired.
;atabase uditing //////////////////////////// To enable auditing: 1. connec to +E, as sys . Click ser0er tab 3. nder ;atabase Configuration region click 7nitialiFation parameters link. 4. Click on S!)ile tab 9. Type the name audit in the name field and click go to 0iew 6;7TAT*7L8 Q. Change the auditAtrail parameter from db to $ml O. Click pply N. *estart the database to change the settings. Steps to set the ob"ect for auditing: 1. Click on the ser0er tab . nder Security region click udit Settings link. 3. Click on the udited ob"ects tab. 4. Click dd button.
9. Select the tables to be audited by clicking on the lookup icon. Q. ,o0e the reuired statements to be audited from a0ailable to selected statements list. O. Click +R to apply. SL& create user test19 identified by oracleser created. SL& grant connect to test19Jrant succeeded. SL& grant insert2 update2 delete on hr."obs 3 to test19Jrant succeeded. SL& grant select on hr."obs to test19-
To monitor the audited ob"ects //////////////////////////////////////////// 1. +n the +E, home2 click on the ser0er tab. . nder security2 click udit Settings link 3. nder udit Trails region click udited +b"ects link. ote: to disable auditing2 change the ;7TAT*7L parameter to +E and restart the database. ,J7J ;+ ;T ////////////////////////////////////////////
Dackground: =ou need to create a user account for #enny Joodman2 the new human resources department manager. There are also two new clerks in the human resources department2 ;a0id Mamby and *achel !andya. ll three must be able to log in to the orcl database and to select data from2 and update records in2 the M*.E,!L+=EES table. The manager also needs to be able to insert and delete new employee records. Ensure that if the new users forget to log out at the end of the day2 they are automatically logged out after 19 minutes. =ou also need to create a new user account for the in0entory application that you are installing. 7n this practice2 you create the 7ET+*= user to own the new 7n0entory application. =ou create a profile to limit the idle time of users. 7f a user is idle or forgets to log out after 19 minutes2 the user session is ended. slplus < as sysdba %% E+) drop user in0entory cascadecreate user in0entory identified by 0erysecure default tablespace in0entorygrant connect2 resource to in0entoryCreate a profile named M*!*+)7LE that allows only 19 minutes idle time. a8 7n0oke Enterprise ,anager as the S=S user in the S=S;D role for your orcl database. b8 Click the Ser0er tab2 and then click !rofiles in the Security section. c8 Click the Create button. d8 Enter M*!*+)7LE in the ame field. e8 Enter 19 in the 7dle Time 6,inutes8 field. f8 Lea0e all the other fields set to ;E)LT.
g8 Click the !assword tab2 and re0iew the !assword options2 which are currently all set to ;E)LT. h8 +ptionally2 click the Show SL button2 re0iew your underlying SL statement2 and then click *eturn. i8 )inally2 click +R to create your profile
Set the *ES+*CEAL7,7T initialiFation parameter to T*E so that your profile limits are enforced. a8 Click the Ser0er tab2 and then click 7nitialiFation !arameters in the ;atabase Configuration section. b8 Enter resourceAlimit in the ame field2 and then click Jo. c8 Select T*E from the alue drop/down list2 and then click pply.
Create the role named M*CLE*R with SELECT and !;TE permissions on the M*.E,!L+=EES table. a8 Click the Ser0er tab and then click *oles in the Security section. b8 Click the Create button. c8 Enter M*CLE*R in the ame field. This role is not authenticated. d8 Click +b"ect !ri0ileges tab. e8 Select Table from the Select +b"ect Type drop/down list2 and then click dd. f8 Enter M*.E,!L+=EES in the Select Table +b"ects field. g8 ,o0e the SELECT and !;TE pri0ileges to the Selected !ri0ileges bo$. Click +R. h8 Click the Show SL button2 and re0iew your underlying SL statement. i8 Click *eturn2 and then click +R to create the role.
Create the role named M*,JE* with 7SE*T and ;ELETE permissions on the M*.E,!L+=EES table. Jrant the M*CLE*R role to the M*,JE* role. a8 Click the Ser0er tab2 and then click *oles in the Security section. b8 Click Create. c8 Enter M*,JE* in the ame field. This role is not authenticated. d8 Click +b"ect !ri0ileges tab. e8 Select Table from the Select +b"ect Type drop/down list2 and then click dd. f8 Enter M*.E,!L+=EES in the Select Table +b"ects field. g8 ,o0e the 7SE*T and ;ELETE pri0ileges to the Selected !ri0ileges bo$. Click +R. h8 Click the *oles tab2 and then click Edit List.
i8 ,o0e the M*CLE*R role into the Selected *oles bo$2 and then click +R. "8 Click the Show SL button2 and re0iew your underlying SL statement. k8 Click *eturn2 and then click +R to create the role.
,J7J SCME,
,J7J MJ7J SESS7+S The Melp desk "ust recei0ed a call from Susan ,a0ris2 an M* representati0e2 complaining that the database is ^froFen._ pon uestioning the user2 you find that she was trying to update #ohn Chen]s personnel record with his new phone number2 but when she entered the new data2 her session froFe and she could not do anything else. SL script files are pro0ided for you in the
Lea0e this session connected in the state that it is currently. ;o not e$it at this time.
,ake an attempt to update the same row in a separate session by running2 in a separate terminal window2 the labA?PA?.sl script. ,ake sure that you see the message ^pdate is being attempted now_ before mo0ing on. ;o not worry if the session seems to ^hang_`this is the condition that you are trying to create.
otice that this session appears to be hung. Lea0e this session as is and mo0e on to the ne$t step.
sing Enterprise ,anager2 click the Dlocking Sessions link on the !erformance page and detect which session is causing the locking conflict. a8 7n Enterprise ,anager2 click the !erformance page. a8 Select the J*EEDE*J session2 and then click iew Session. b8 Click the hash 0alue link named ^!re0ious SL._ c8 ote the SL that was most recently run. a8 Click the browser]s Dack button. b8 ow2 on the Session ;etails: J*EEDE*J page2 click Rill Session. c8 Lea0e the +ptions set to Rill 7mmediate2 and then click Show SL to see the statement that is going to be e$ecuted to kill the session. d8 Click *eturn2 and then click =es to carry out the R7LL SESS7+ command. *eturn to the SL(!lus command window2 and note that S,*7S]s update has now completed successfully. 7t may take a few seconds for the success message to appear.
,J7J ;+ ;T The business users and management in your organiFation decide2 that they need to ha0e a 4N/hour retention of undo in the +racle database to support their flashback needs. =our task is to configure the orcl database to support this reuirement. 7n this practice2 you first 0iew your system acti0ity regarding undo2 and then you configure the orcl database to support 4N/hour retention for flashback operations. 18 7n Enterprise ,anage r2 as the ;D1 user2 0iew the undo related system acti0ity. a8 Click the Ser0er tabbed page and select ^utomatic ndo ,anagement_ in the ;atabase Configuration section. b8 Click the System cti0ity tabbed page.
ote: =our information will look different on all analysis screenshots2 based on your analysis period and the system acti0ity during this period. c8 uestion: Looking at the preceding screenshot2 how many errors did this system encounterZ nswer: one d8 uestion: Looking at the preceding screenshot2 what is the duration of the longest running ueryZ nswer: N minutes 6=our answer may be different.8 e8 Click the !lus icon to show related graphs. f8 uestion: Mow many graphs are displayedZ nswer: Three. 6ndo Tablespace sage2 ndo *etention uto/Tuning2 and ndo Jeneration *ate8 g8 uestion: Looking at the preceding ndo *etention uto/Tuning graph2 could this system support flashback abo0e and beyond the current longest running ueryZ nswer: =es2 6but most likely not enough to support the reuired 4N hours8. 8 ,odify the undo retention time and calculate the undo tablespace siFe to support the reuested 4N/hour retention. a8 Click the Jeneral tab to go back to the Jeneral utomatic ndo ,anagement page. b8 nder the ndo d0isor section2 select ^Specified manually to allow for longer duration ueries or flashback._ c8 Enter 4N hours as ;uration and click the *un nalysis button. d8 >hen the ndo d0isor is finished2 take a look at the results. 7t looks like the undo tablespace is 0ery close to the recommended undo tablespace siFe. This is okay for most workloads2 but the recommendation is to set your undo tablespace siFe to be three times the minimum siFe. This means that you should change your undo tablespace siFe to be N4Q ,D. ote: =our recommended siFe might be different from what is shown here2 so ad"ust the siFe accordingly. e8 Click the Show SL button in the upper/right corner of the Jeneral utomatic ndo ,anagement page. f8 This command will change the undo retention to support the 4N/hour reuirement. *e0iew the SL statement and click *eturn. g8 Click pply to make the change to undo retention. h8 ow ad"ust the undo tablespace siFe by clicking the Edit ndo Tablespace button. i8 Scroll down to ;atafiles and click Edit to make a change to the undotbs?1.dgf file siFe. "8 Change the file siFe to N4Q ,D and click Continue. k8 erify the SL commands that will be e$ecuted by clicking Show SL.
Click *eturn. l8 Click pply to change the tablespace siFe. 38 Jo back to the utomatic ndo ,anagement to see the results of the changes you "ust made. =ou see that the undo retention time has increased to support the 4N hours reuirement. =our undo tablespace siFe has also increased based on the changes you made to the siFe of the datafile for the undo tablespace. a8 uestion: >hich )lashback operations are potentially affected by this changeZ nswer: )lashback uery2 )lashback transaction2 and )lashback table. b8 uestion: ;o undo data sur0i0e the shutdown of a databaseZ nswer: =es2 undo is persistent.
;7T7J Dackground: =ou ha0e "ust been informed of suspicious acti0ities in the M*.#+DS table in your orcl database. The highest salaries seem to fluctuate in a strange way. =ou decide to enable standard database auditing and monitor data manipulation language 6;,L8 acti0ities in this table. Log in as the ;D1 user 6with oracle password2 connect as S=S;D8 and perform the necessary tasks either through Enterprise ,anager ;atabase Control or through SL(!lus. ll scripts for this practice are in the
8 Decause you changed a static parameter2 you must restart the database. ;o so by running the labA11A?.sh script. a8 7n a terminal window2 enter: b8 Continue with the ne$t step when you see that the database is restarted and the
script has e$ited SL(!lus. 38 Dack in Enterprise ,anager2 select M*.#+DS as the audited ob"ect and ;ELETE2 7SE*T2 and !;TE as Selected Statements. Jather audit information by session. Decause the database has been restarted2 you ha0e to log in to Enterprise ,anager again as the ;D user. a8 Click logout in the upper/right corner of the Enterprise ,anager window. b8 Log in as the ;D1 user in the S=S;D role for your orcl database. c8 Click the ;atabase home page tab to ensure that Enterprise ,anager had time to update the status of the database and its agent connections. d8 Click the Ser0er tab2 and then click udit Settings in the Security section. e8 Click the udited +b"ects tab at the bottom of the page2 and then click the dd button. 8 +n the dd udited +b"ect page2 ensure that the +b"ect Type is Table2 and enter M*.#+DS in the Table field 6or use the flashlight icon to retrie0e this table8. g8 ,o0e ;ELETE2 7SE*T2 and !;TE into the Selected Statements area by double/clicking each of them. h8 Click Show SL. i8 *e0iew the statement2 and then click *eturn. "8 Click +R to acti0ate this audit. 48 !ro0ide input for the audit2 by e$ecuting the labA11A?4.sh script. This script creates the ;7TASE* user2 connects to SL(!lus as this user2 and multiplies the 0alues in the ,WASL*= column by 1?. Then the M* user connects and di0ides the column 0alues by 1?. )inally2 the ;7TASE* user is dropped again. a8 7n a terminal window2 enter: 98 7n Enterprise ,anager2 re0iew the audited ob"ects. a8 Click the Ser0er tab2 and then click udit Settings in the Security section. b8 Click udited +b"ects in the udit Trails area2 which is on the right side of the page. c8 +n the udited +b"ects page2 re0iew the collected information. uestion: Can you tell which user increased and which user decreased the salariesZ nswer: o2 the standard audit records only show which user accessed the table. d8 Click *eturn. Q8 ndo your audit settings for M*.#+DS2 disable database auditing2 and then restart the database by using the labA11A?Q.sh script. a8 +n the udit Settings page2 click the udited +b"ects tab at the bottom of the page.
b8 Enter M* as Schema2 and then click Search. c8 Select all three rows2 and then click *emo0e. d8 +n the Confirmation page2 click Show SL. e8 *e0iew the statements2 and then click =es to confirm your remo0al. f8 +n the udit Settings page2 click W,L in the Configuration region. g8 +n the 7nitialiFation !arameters page2 click the S!) ile tab. h8 +n the S!)ile page2 enter audit in the ame field2 and then click Jo. i8 )or the auditAtrail parameter2 select the ;D 0alue. "8 Click Show SL. k8 *e0iew the statement2 and then click *eturn. l8 +n the 7nitialiFation !arameters page2 click pply. m8 Decause you changed a static parameter2 you must restart the database. ;o so by running the labA11A?Q.sh script. 7n a terminal window2 enter:
O8 ,aintain your audit trail: Decause you are completely finished with this task2 backup and delete all audit files from the
DCR! and *EC+E*= Dackground: =our orcl database is ready to mo0e from test or de0elopment into production. Configure your database to reduce the chances of failure or data loss. To do so2 perform the following tasks: [ Ensure redundancy of control files and backup the control file to trace [ *e0iew the flash reco0ery area configuration [ Ensure that there are at least two redo log members in each group [ !lace your database in *CM7EL+J mode [ Configure redundant archi0e log destinations 7n this practice2 you configure your database to reduce the chances of failure or data loss. ote: Completing this practice is a prereuisite for all following backup and reco0ery practices. 18 erify that you ha0e at least two control files to ensure redundancy and backup the control file to trace. a8 7n0oke Enterprise ,anager as the ;D1 user in the S=S;D role for your orcl
database. b8 Click Ser0er & Control )iles 6in the Storage section8. uestion 1: +n the Control )iles: Jeneral page2 how many control files do you ha0eZ nswer: Three 6in the preceding e$ample8. uestion : Mow would you add another control file if you needed toZ nswer: dding a control file is a manual operation. To perform this2 you must: [ Shut down the database [ se the operating system to copy an e$isting control file to the location where you want your new file to be. [ Start the database by using Enterprise ,anager. nlike a normal startup2 you would use d0anced +ptions to select a different startup mode. Select ^Start the instance_ to lea0e the instance in the +,+T state. [ Edit the C+T*+LA)7LES initialiFation parameter to point to the new control file. [ Continue the ST*T! database operation until the database is in an open state. ote [ [ This answer does not apply to an +,) database because the control files in that case would ha0e to all be re/created. lternati0ely2 if you did not want to use Enterprise ,anager to perform the steps2 you could perform the steps outlined in the ,ultiple$ing Control )iles slide in the ^Dackup and *eco0er Concepts_ lesson. c8 Click Dackup to Trace. d8 >hen you recei0e the success message2 note the trace directory location2 and then click +R. e8 +ptionally2 use a terminal window2 logged in as the oracle user to 0iew the trace file name at the end of the alert log by e$ecuting the following command: cd
c8 uestion: 7s the flash reco0ery area enabledZ nswer: =es2 by default. d8 ote the location of the flash reco0ery area. )or e$ample: hich essential ;D tasks can you perform in this sectionZ nswer: =ou can change the location2 siFe or retention time for the flash reco0ery area2 as well as enable the )lashback ;atabase functionality. f8 uestion: ;oes changing the siFe of the flash reco0ery area reuire the database to be restartedZ nswer: o2 a restart is not reuired for this change. g8 Change the siFe of the )lash *eco0ery rea to N JD2 by entering N into the ^)lash *eco0ery rea SiFe_ field. h8 +ptionally2 click Show SL2 re0iew the statement and click *eturn. i8 Click pply. 38 Check how many members each redo log group has. Ensure that there are at least two redo log members in each group. +ne set of members should be stored in the flash reco0ery area. a8 Click Ser0er & *edo Log Jroups2 and note how many members are in the ^Y of ,embers_ column. nswer: There is only one member in each group. b8 To add a member to each group2 perform the following steps for each group: i8 Select the group 6for e$ample2 18 and click the Edit button. ii8 +n the Edit *edo Log Jroup page2 note the )ile ame2 for e$ample ^redo?1.log_ and click the dd button. iii8 +n the Edit *edo Log Jroup: dd *edo Log ,ember page2 enter a file name by adding the letter ^b_ to the end of the name 6before the dot8. )or e$ample2 enter redo?1b.log as )ile ame and enter your flash reco0ery area2 for e$ample2
different hard dri0es2 preferably with different disk controllers2 to minimiFe the risk of any single hardware failure destroying an entire log group. 48 =ou notice that2 for each log group2 the rchi0ed column has a 0alue of o. This means that your database is not retaining copies of redo logs to use for database reco0ery2 and in the e0ent of a failure2 you will lose all data since your last backup. !lace your database in *CM7EL+J mode2 so that redo logs are archi0ed. ote: =ou must continue with step 92 so that your changes are applied. a8 7n Enterprise ,anager2 select 0ailability & *eco0ery Settings in the Setup section. b8 7n the ,edia *eco0ery region2 select the *CM7EL+J ,ode check bo$. c8 erify that Log rchi0e )ilename )ormat contains t2 s2 and r. d8 otice the current configuration of redundant archi0e log destinations`one to the flash reco0ery area and the other to hen prompted whether you want to restart the database now2 click =es. g8 Enter the credentials to restart the database 6oracle as the Most Credentials2 and syshen asked to confirm2 click =es again. i8 Should you recei0e an error during the shutdown and startup acti0ity2 click +R to acknowledge the error2 and then click *efresh again. 6=ou might ha0e been simply faster than the database.8 98 +ptionally2 use SL(!lus to check whether your database is in *CM7EL+J mode. 7n a terminal window2 log in to SL(!lus as S=S;D and run the archi0e log list command. ow that your database is in *CM7EL+J mode2 it will continually archi0e a copy of each online redo log file before reusing it for additional redo data. ote: *emember that this consumes space on the disk and that you must regularly back up older archi0e logs to some other storage.
=our database is ready to mo0e from de0elopment and testing into production. Ensure that your database is configured so that reco0ery is possible without
loss of data. Establish the backup policy to automatically back up the S!)7LE and control file. !erform an immediate backup to disk and schedule nightly backup "obs that repeat indefinitely. 7n this practice2 you perform an immediate backup to disk and schedule a nightly backup "ob. 18 >hat is the difference between a backup set and an image copyZ nswer: backup set contains data and archi0e log files packed in an +racle proprietary format. )iles must be e$tracted before use. 7mage copies are the eui0alent of operating system file copies and can be used for restore operations immediately. 8 >hat is the destination of any disk backups that are doneZ a8 Log in to Enterprise ,anager as the ;D1 user in the S=S;D role and select 0ailability & Dackup Settings. b8 ote the message under the ;isk Dackup Location that says the flash reco0ery area is the current disk backup location. 38 Establish the backup policy to automatically back up the S!)7LE and control file. a8 Click the !olicy tab under the Dackup Settings pages. b8 Click utomatically backup the control file and ser0er parameter file 6S!)7LE8 with e0ery backup and database structural change. c8 Scroll to the bottom and enter oracle and oracle for Most Credentials sername and !assword for your ser0er2 and click ^Sa0e as !referred Credential._ 48 Test making a backup to disk2 as a backup set2 with oracle for Most Credentials. a8 Click the ;e0ice tab under the Dackup Settings pages. b8 Select Dackup Set as your ;isk Dackup Type. c8 Scroll to the bottom and ensure the Most Credentials are set to oracle. d8 Scroll to the top of the page and click Test ;isk Dackup. e8 processing message appears. >hen the test finishes2 and you see the ;isk Dackup Test SuccessfulK] message2 click +R. 98 Dack up your entire database2 with archi0e logs2 while the database is open for user acti0ity. This backup should be the base for an incremental backup strategy. a8 uestion: >hat prereuisite must be met to create a 0alid backu p of a database without shutting it downZ nswer: The database must be in *CM7EL+J mode. Dackups made with the database open2 but not in *CM7EL+J mode2 cannot be used for reco0ery. b8 Select 0ailability & Schedule Dackup 6in the ,anage section8. 7f you find that the +racle/Suggested Dackup strategy fits your needs e$actly2 you would chose this option. )or practice purposes2 you will schedule a customiFed backup c8 Select >hole ;atabase as the ob"ect to be backed up. d8 Confirm or enter oracle and oracle for Most Credentials sername and
!assword for your ser0er. e8 Click Schedule CustomiFed Dackup. f8 +n the Schedule CustomiFed Dackup: +ptions page2 select )ull Dackup for your Dackup Type2 and select the se as the base of an incremental backup strategy check bo$. g8 Select +nline Dackup as Dackup ,ode. h8 7n the d0anced section2 select ^lso back up all archi0ed logs on disk_ and ^;elete all archi0ed logs from disk after they are successfully backed up_2 and then click e$t to continue. i8 +n the Schedule CustomiFed Dackup: Settings page2 select ;isk for your backup location. 6otice that your ;isk Dackup Location is retained and that you could o0erride the current settings for a one/off backup. Dut do not click it this time.8 "8 Click e$t. k8 ccept all the defaults on the Schedule CustomiFed Dackup: S chedule page and then click e$t to continue. ote: Schedule Type should be +ne Time 67mmediately8. l8 +n the Schedule CustomiFed Dackup: *e0iew page2 re0iew the *, script2 and then click Submit #ob. m8 Click iew #ob to monitor the status of the backup "ob. The time for this backup depends on your hardware and system resources. n8 Click your browser]s *efresh or *euery button until the "ob is co mpleted. Q8 Schedule nightly disk/based incremental online backups for your whole database2 without archi0ed logs. Schedule it for e$ecution at 11:?? !,. The schedule should be in effect indefinitely. a8 7n Enterprise ,anager2 select 0ailability & Schedule Dackup 6in the ,anage section8. b8 Select >hole ;atabase as the ob"ect to be backed up. c8 Confirm or enter oracle and oracle for Most Credentials sername and !assword for your ser0er2 and then click Schedule CustomiFed Dackup. d8 +n the Schedule CustomiFed Dackup: +ptions page2 select 7ncremental Dackup as your Dackup Type. e8 Select +nline Dackup as Dackup ,ode. f8 7n the d0anced region2 select ^lso backup all archi0ed logs on disk_ and ^;elete all archi0ed logs from disk after they are successfully backed up_2 and then click e$t to continue. g8 +n the Schedule CustomiFed Dackup: Settings page2 select ;isk as your backup location2 and then click e$t to continue. h8 +n the Schedule CustomiFed Dackup: Schedule page2 change #ob ame to ightlyADackup and accept the default 0alue for #ob ;escription. i8 Select *epeating in the Schedule region. otice how additional conte$t sensiti0e details are displayed.
"8 Select Dy ;ays from the )reuency Type drop/down list2 enter 1 in the *epeat E0ery field2 confirm that 7ndefinite is selected as the *epeat ntil 0alue2 and enter 11:?? !, as Time. k8 Click e$t to continue. l8 +n the Schedule CustomiFed Dackup: *e0iew page2 re0iew your Settings and *, script. m8 Click Submit #ob2 and then click +R. n8 Click #obs on the 0ailability page in the *elated Links section to see the scheduled "ob in the #ob cti0ity list.
,any failures of the +racle database can be traced to some sort of media failure2 such as disk or controller failure. 7n this practice2 you encounter a number of problems from which you need to reco0er the database. [ *eco0er from the loss of a control file [ *eco0er from the loss of a data file [ *eco0er from the loss of a redo log member [ *eco0er from the loss of a file in the S=STE, tablespace SL script files are pro0ided for you in the
database is down and offers you the chance to start it up again. ote: you may see a message stating 7nternal Error has occurred.] 7f so2 keep trying to connect to using the Enterprise ,anager *L. E0entually it will display the database home page. b8 Click Startup. 7f you see a Connection *efused message2 ignore it- the connection will e0entually be established. c8 Enter oracle as sername and !assword for Most Credentials and click +R. d8 Click =es to confirm your attempted startup. e8 The startup of the instance fails with Enterprise ,anager. Click iew ;etails for more information. f8 ote the following2 and then click +R: +*/???9: error in identifying control file2 check alert log for more info g8 lternati0ely2 in a new SL(!lus session2 check the current status of the instance as the S=S user and attempt to mount it with the following commands: select status from 0@instancealter database mount38 The instance cannot mo0e to the mount stage because it cannot find one of the control files. To find the locations of the alert log and of diagnostic information2 enter the following SELECT statement: SELECT ,E2 LE )*+, @;7JA7)+48 Look at the last 9 lines in the log.$ml file to see if you can find out what the problem is. Still inside your SL(!lus session2 enter the following command 6on one line8: host tail /9 hy did you ha0e to use two commands to mo0e the instance state from +,+T to +!EZ nswer: Decause the LTE* ;TDSE command enables you to change only
one state le0el for each command b8 >hy did you use operating system commands to restore the control file instead of using +racle *eco0ery ,anagerZ nswer: Decause all control files are identical. s long as any one control file is intact2 it can be used to restore the others. N8 E$it all sessions and close all windows
*eco0ering from the Loss of a ;ata )ile 18 7n a SL(!lus session2 as the S=S user2 e$ecute the labA1QA?3.sl script from your labs directory. This script deletes one of your application data files. 8 The Melp desk has recei0ed a call from a user who is unable to access the C+T*7ES table in the M* application schema. Count the rows in the table to determine whether there is a problem. 38 Troubleshoot and reco0er as necessary. The error message suggests that the e$ample?1.dbf data file is corrupt or missing. a8 7n Enterprise ,anager2 click 0ailability & !erform *eco0ery. b8 Click d0ise and *eco0er. c8 +n the iew and ,anage )ailures page2 click the plus 68 icon under the failure description. =ou should see a failure like the following: ote: 7f you do not see the nonsystem datafile failure2 keep refreshing the page until it shows up. d8 >ith the failures selected2 click d0ise. e8 Decause the file was not "ust renamed or mo0ed2 but deleted2 you continue by clicking ^Continue with d0ise._ f8 +n the *eco0ery d0ise page2 you see the *, script. Click Continue. g8 +n the *e0iew page2 you see the failure and the suggested solution. Click ^Submit *eco0ery #ob._ h8 !rocessing window appears2 followed by the #ob cti0ity page. =ou should see a message that the "ob was successfully created. 6=our link name is probably different.8 i8 Click the "ob name link. "8 +n the #ob *un page2 check the Status in the Summary section. 7f it is *unning2 use you browser]s *efresh or *euery button until the "ob is completed. k8 7n your SL(!lus session2 0erify that the M*.C+T*7ES table is now accessible.
7n this practice2 your system e$periences the loss of a redo log member. =ou then go through the steps to reco0er from this loss. 18 ,ake sure that you are in your labs directory. sing SL(!lus2 e$ecute the labA1QA?4.sl script as the S=S user. The labA1QA?4.sl script deletes one of your redo log files. See the error in the alert log and reco0er from it. 8 The database continues to function normally2 and no users are complaining. Log in to Enterprise ,anager with the ;D1 username as S=S;D. +n the ;atabase home page2 0iew alerts similar to the following ones: 7f you do not see similar alerts2 you may need to wait a few minutes and refresh the page. +ne of the failures listed may be left o0er from the data file reco0ery you performed in the pre0ious practice. 38 Click 0ailability & !erform *eco0ery 6in the ,anage section8. 48 +n the !erform *eco0ery page2 you see the )ailure ;escription and could directly begin correcting the failure. Dut for practice purposes2 you follow the steps in the ;ata *eco0ery d0isor. Scroll down and ensure that your host credentials are set 6oracle for both username and password8. Then click the ^d0ise and *eco0er_ button 6which is one of the ways to in0oke the ;ata *eco0ery d0isor8. 98 +n the iew and ,anage )ailures page2 ensure that the failure is selected2 an d click d0ise. Q8 The ,anual ctions page suggests to manually restore it. 7n the preceding e$ample2 redo?3.log is deleted. ;o not click any button at this point in time. O8 7n a new terminal window2 as the oracle user2 copy an e$isting redo log from the same redo log group to the missing file. ote: The actual redo log member that was lost on your machine may be different than the one shown here. ,ake sure that you are replacing the file names as appropriate for your failure. cd hy did the database not crashZ nswer: Decause a single missing member is noncritical and does no t affect the operation of the database. s long as there is at least one good member for each log group2 the database operation continues. 7n this practice2 your system e$periences the loss of a file in the S=STE, tablespace. =ou then go through the steps to reco0er from this loss.
18 >hy is reco0ery from the loss of a system data file or a data file belonging to an undo tablespace different from reco0ering an application data fileZ nswer: Decause reco0ery of system or undo data files must be done with the database closed2 whereas reco0ery of an application data file can be done with the database open and a0ailable to users 8 s the S=S user2 e$ecute the labA1QA?9.sl script in your labs directory. This script deletes the system data file. 38 7n Enterprise ,anager2 re0iew the ;atabase home page. 7f you see a message that says the connection was refused2 dismiss it and reenter the E, home page *L in the browser. =ou may need to try se0eral times before you see the ;atabase home page. 48 The database is shut down. ttempt to start your database. a8 Click Startup to try to open it. b8 +n the _Startup
EW!+*T7J<7,!+*T7J n the recent past2 you recei0ed a number of uestions about the M* schema. To analyFe them without interfering in daily acti0ities2 you dec ide to use the ;ata !ump >iFard to e$port the M* schema to file. >hen you perform the e$port2 you are not sure into which database you will be importing this schema. 7n the end2 you learn that the only database for which management appro0es an import is the orcl database. So you perform the import with the ;ata !ump >iFard2 remapping the M* schema to ;D1 schema. Then you recei0e two data load reuests for which you decide to use SL(Loader. 7n this practice2 you first grant the ;D1 user the pri0ileges necessary to pro0ide access to the ;TA!,!A;7* directory. =ou then e$port the M* schema so that you can then import the tables you want into the ;D1 schema. 7n the practice2 you import only the E,!L+=EES table at this time. 18 )irst2 you need to grant the ;D1 user the appropriate pri0ileges on the ;TA!,!A;7* directory and create the users and roles reuired for this practice. script e$ists that performs all the steps reuired to configure your en0ironment for this practice. a8 *e0iew the labA1OA?1.sl script2 which grants the ;D1 user pri0ileges on the ;TA!,!A;7* directory and performs other configurations to your en0ironment2 by e$ecuting the following in your labs directory: @ cat labA1OA?1.sl b8 The labA1OA?1.sh script calls the labA1OA?1.sl script. E$ecute the labA1OA?1.sh script now: 8 Log in to Enterprise ,anager as the ;D1 user in the ormal role and e$port the M* schema. a8 7n0oke Enterprise ,anager as the ;D1 user as the ormal role for your orcl database. The Connect s setting should be ormal. b8 Select ;ata ,o0ement & ,o0e *ow ;ata & E$port to E$port )iles. c8 Select Schemas2 enter oracle as sername and !assword2 select Sa0e as !referred Credential2 and then click Continue. d8 +n the E$port: Schemas page2 click dd2 select the M* schema2 and then click the Select button. e8 =ou see that M* is now in the list of schemas. Click e$t. f8 +n the ^E$port: +ptions_ page2 select ;TA!,!A;7* from the ;irectory +b"ects drop/down list2 and enter hre$p.log as Log )ile. g8 *e0iew d0anced +ptions 6but do not change82 and then click e$t. h8 +n the ^E$port: )iles_ page2 select ;TA!,!A;7* from the ;irectory
+b"ect drop/down list2 enter M*EW!.;,! as )ile ame2 and then click e$t. i8 +n the ^E$port: Schedule_ page2 enter hre$p as #ob ame and E$port M* schema as ;escription2 accept the immediate "ob start time2 and then click e$t. "8 +n the ^E$port: *e0iew_ page2 click Show !LiFard helped you to create. k8 Click Submit #ob to submit the "ob. l8 Click the link to the M*EW! "ob to monitor the progress. >hen the "ob shows as successfully completed2 mo0e on to the ne$t step. 38 ow2 import the E,!L+=EES table from the e$ported M* schema into the ;D1 schema. To get a feeling for the command/line interface2 you can use the impdp utility from the command line to import the E,!L+=EES table into the ;D1 user schema. a8 Enter the following entire command string. ;o not press HEnterI before reaching the end of the command: impdp dba1
7n this practice2 you load data into the !*+;CTA,STE* table by using SL(Loader 0ia Enterprise ,anager ;atabase Control. ;ata and control files are pro0ided. 18 s the ;D1 user2 use Enterprise ,anager to load the labA1OA?A?1.dat data file. This data file contains rows of data for the !*+;CTA,STE* table. The labA1OA?A?1.ctl file is the control file for this load. +ptionally2 0iew the labA1OA?A?1.dat and labA1OA?A?1.ctl files to learn more about their structure before going further. a8 7n0oke Enterprise ,anager as the ;D1 user as the ormal role for your orcl database. b8 Select ;ata ,o0ement & ,o0e *ow ;ata & Load ;ata from ser )iles. c8 Click se E$isting Control )ile. 7f not already entered2 enter oracle as
sername and as !assword2 click Sa0e as !referred Credential2 and then click Continue. d8 +n the ^Load ;ata: Control )ile_ page2 enter
;; 6"obAid *CM*6P88Table altered. [ The new column becomes the last column.
,odifying a Column [ =ou can change a column]s data type2 siFe2 and default 0alue. LTE* TDLE deptN? ,+;7)= 6lastAname *CM*63?88Table altered. [ change to the default 0alue affects only subseuent insertions to the table.
;ropping a Column se the ;*+! C+L, clause to drop columns you no longer need from the table. LTE* TDLE deptN? ;*+! C+L, "obAidTable altered.
The SET SE; +ption [ =ou use the SET SE; option to mark one or more columns as unused. [ =ou use the ;*+! SE; C+L,S option to remo0e the columns that are marked as unused.
LTE* TDLE %tableAname& SET SE;6%columnAname&8+* LTE* TDLE %tableAname&
SET SE; C+L, %columnAname<E* TDLE %tableAname& ;*+! SE; C+L,S-
dding a Constraint Synta$ se the LTE* TDLE statement to: [ dd or drop a constraint2 but not modify its structure [ Enable or disable constraints [ dd a +T LL constraint by using the ,+;7)= clause
LTE* TDLE %tableAname& ;; HC+ST*7T %constraintAname&I type 6%columnAname&8dding a Constraint dd a )+*E7J RE= constraint to the E,! table indicating that a manager must already e$ist as a 0alid employee in the E,! table. LTE* TDLE emp modify employeeAid !rimary ReyTable altered. LTE* TDLE emp ;; C+ST*7T empAmgrAfk )+*E7J RE=6managerAid8 *E)E*ECES emp6employeeAid8Table altered.
+ ;ELETE CSC;E ;elete child rows when a parent key is deleted. LTE* TDLE Emp ;; C+ST*7T empAdtAfk )+*E7J RE= 6;epartmentAid8 *E)E*ECES departments + ;ELETE CSC;E8Table altered.
;eferring Constraints Constraints can ha0e the following attributes: [ ;E)E**DLE or +T ;E)E**DLE [ 77T7LL= ;E)E**E; or 77T7LL= 7,,E;7TE LTE* TDLE dept ;; C+ST*7T deptAidApk !*7,*= RE= 6departmentAid8 ;E)E**DLE 77T7LL= ;E)E**E; ;eferring constraint on creation SET C+ST*7TS deptAidApk 7,,E;7TE LTE* SESS7+ SET C+ST*7TS5 7,,E;7TE
;ropping a Constraint [ *emo0e the manager constraint from the E,! table. LTE* TDLE emp ;*+! C+ST*7T empAmgrAfkTable altered. [ *emo0e the !*7,*= RE= constraint on the ;E!T table and drop the associated )+*E7J RE= constraint on the E,!.;E!*T,ETA7; column. LTE* TDLE dept ;*+! !*7,*= RE= CSC;ETable altered.
;isabling Constraints [ E$ecute the ;7SDLE clause of the LTE* TDLE statement to deacti0ate an integrity constraint. [ pply the CSC;E option to disable dependent integrity constraints.
LTE* TDLE emp ;7SDLE C+ST*7T empAdtAfkTable altered.
Enabling Constraints [ cti0ate an integrity constraint currently disabled in the table definition by using the EDLE clause. LTE* TDLE emp EDLE C+ST*7T empAdtAfkTable altered. [ 7E inde$ is automatically created if you enable a 7E key or !*7,*= RE= constraint.
Cascading Constraints [ The CSC;E C+ST*7TS clause is used along with the ;*+! C+L, clause. [ The CSC;E C+ST*7TS clause drops all referential integrity constraints that refer to the primary and uniue keys defined on the dropped columns. [ The CSC;E C+ST*7TS clause also drops all multicolumn constraints defined on the dropped columns.
LTE* TDLE emp ;*+! C+L, employeeAid CSC;E C+ST*7TSTable altered. LTE* TDLE test1 ;*+! 6pk2 fk2 col18 CSC;E C+ST*7TS-
+0er0iew of 7nde$es 7nde$es are created: [ utomatically \ !*7,*= RE= creation \ 7E RE= creation [
,anually \ C*ETE 7;EW statement \ C*ETE TDLE statement C*ETE 7;EW with C*ETE TDLE Statement C*ETE TDLE E>AE,! 6employeeAid ,DE*6Q8 !*7,*= RE= S7J 7;EW 6C*ETE 7;EW empAidAid$ + E>AE,!6employeeAid882 firstAname *CM*6?82 lastAname *CM*6988Table created. SELECT 7;EWA,E2 TDLEA,E )*+, SE*A7;EWES >ME*E TDLEA,E 5 GE>AE,!G-
)unction/Dased 7nde$es [ function/based inde$ is based on e$pressions. [ The inde$ e$pression is built from table columns2 constants2 SL functions2 and user/defined functions.
C*ETE 7;EW upperAdeptAnameAid$ + dept6!!E*6departmentAname887nde$ created. SELECT ( )*+, dept >ME*E !!E*6departmentAname8 5 GSLESG-
*emo0ing an 7nde$ [ *emo0e an inde$ from the data dictionary by using the ;*+! 7;EW command. ;*+! 7;EW inde$[ *emo0e the !!E*A;E!TA,EA7;W inde$ from
the data dictionary. ;*+! 7;EW upperAdeptAnameAid$7nde$ dropped. [ To drop an inde$2 you must be the owner of the inde$ or ha0e the ;*+! = 7;EW pri0ilege. ;*+! TDLE ... !*JE ;*+! TDLE deptN? !*JE-
The )LSMDCR TDLE Statement ;*+! TDLE empTable dropped SELECT originalAname2 operation2 droptime2 )*+, recyclebin)LSMDCR TDLE emp T+ DE)+*E ;*+!-
E$ternal Tables Creating a ;irectory for the E$ternal Table Create a ;7*ECT+*= ob"ect that corresponds to the directory on the file system where the e$ternal data source resides. C*ETE +* *E!LCE ;7*ECT+*= empAdir S G<...L7E +D;)7LE +L+J)7LE )7EL;S TE*,7TE; D= G2G 6fname !+S7T7+ 6 1:?8 CM*2 lname !+S7T7+ 6:418 CM*88 L+CT7+ 6Gemp.datG88 !*LLEL 9
*E#ECT L7,7T ??-
=ou can use subueries in ;,L statements to: [ Copy data from one table to another [ *etrie0e data from an inline 0iew [ pdate data in one table based on the 0alues of another table [ ;elete rows from one table based on rows in a another table
Copying *ows from nother Table >rite your 7SE*T statement with a subuery. 7SE*T 7T+ salesAreps6id2 name2 salary2 commissionApct8 SELECT employeeAid2 lastAname2 salary2 commissionApct )*+, employees >ME*E "obAid L7RE G*E!G33 rows created. [;o not use the LES clause. [,atch the number of columns in the 7SE*T clause with that in the subuery. 7nserting sing a Subuery as a Target 7SE*T 7T+ 6SELECT employeeAid2 lastAname2 email2 hireAdate2 "obAid2 salary2 departmentAid )*+, empl3 >ME*E departmentAid 5 9?8 LES 6PPPPP2 GTaylorG2 G;T=L+*G2 T+A;TE6G?O/#/PPG2 G;;/,+/**G82 GSTACLE*RG2 9???2 9?8-
7nserting sing a Subuery as a Target erify the results. SELECT employeeAid2 lastAname2 email2 hireAdate2
"obAid2 salary2 departmentAid )*+, employees >ME*E departmentAid 5 9?*etrie0ing ;ata with a Subuery as Source SELECT a.lastAname2 a.salary2a.departmentAid2 b.sala0g )*+, employees a2 6SELECT departmentAid2 J6salary8 sala0g )*+, employees J*+! D= departmentAid8 b >ME*E a.departmentAid 5 b.departmentAid ; a.salary & b.sala0g-
pdating Two Columns with a Subuery pdate the "ob and salary of employee 114 to match the "ob of employee ?9 and the salary of employee 1QN. !;TE SET empl3 "obAid 5 6SELECT "obAid )*+, employees >ME*E employeeAid 5 ?982 salary 5 6SELECT salary )*+, employees >ME*E employeeAid 5 1QN8 >ME*E employeeAid 5 114-
pdating *ows Dased on nother Table
se subueries in !;TE statements to update rows in a table based on 0alues from another table. !;TE empl3 SET departmentAid >ME*E "obAid 5 6SELECT departmentAid )*+, employees >ME*E employeeAid 5 1??8 5 6SELECT "obAid )*+, employees >ME*E employeeAid 5 ??8-
;eleting *ows Dased on nother Table se subueries in ;ELETE statements to remo0e rows from a table based on 0alues from another table. ;ELETE )*+, empl3 >ME*E departmentAid 5 6SELECT departmentAid )*+, departments >ME*E departmentAname L7RE G!ublicG8-
sing E$plicit ;efault alues [ ;E)LT with 7SE*T: 7SE*T 7T+ deptm3 6departmentAid2 departmentAname2 managerAid8 LES 63??2 GEngineeringG2 ;E)LT8[ ;E)LT with !;TE: !;TE deptm3 SET managerAid 5 ;E)LT >ME*E departmentAid 5 1?-
Types of ,ultitable 7SE*T Statements The different types of multitable 7SE*T statements are: [ nconditional 7SE*T [ Conditional LL 7SE*T [ Conditional )7*ST 7SE*T [ !i0oting 7SE*T nconditional 7SE*T LL [Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7nsert these 0alues into the SLAM7ST+*= and ,J*AM7ST+*= tables using a multitable 7SE*T.
7SE*T LL 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2 hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T LL [ Select the E,!L+=EEA7;2 M7*EA;TE2 SL*=2 and ,JE*A7; 0alues from the E,!L+=EES table for those employees whose E,!L+=EEA7; is greater than ??. [ 7f the SL*= is greater than @1?2???2 insert these 0alues into the SLAM7ST+*= table using a conditional multitable 7SE*T statement. [ 7f the ,JE*A7; is greater than ??2 insert these 0alues into the ,J*AM7ST+*= table using a conditional multitable 7SE*T statement.
Conditional 7SE*T LL 7SE*T LL >ME SL & 1???? TME 7T+ salAhistory LES6E,!7;2M7*E;TE2SL8 >ME ,J* & ?? TME 7T+ mgrAhistory LES6E,!7;2,J*2SL8 SELECT employeeAid E,!7;2hireAdate M7*E;TE2 salary SL2 managerAid ,J* )*+, employees >ME*E employeeAid & ??-
Conditional 7SE*T )7*ST [Select the ;E!*T,ETA7;2 S,6SL*=82 and ,W6M7*EA;TE8 from the E,!L+=EES table. [7f the S,6SL*=8 is greater than @92???2 then insert these 0alues into the S!EC7LASL2 using a conditional )7*ST multitable 7SE*T. [7f the first >ME clause e0aluates to true2 then the subseuent >ME clauses for this row should be skipped. [)or the rows that do not satisfy the first >ME condition2 insert into the M7*E;TEAM7ST+*=A??2 M7*E;TEAM7ST+*=APP2 or M7*E;TEAM7ST+*= tables2 based on the 0alue in the M7*EA;TE column using a conditional multitable 7SE*T.
Conditional 7SE*T )7*ST 7SE*T )7*ST >ME SL & 9??? TME 7T+ specialAsal LES6;E!T7;2 SL8 >ME M7*E;TE like 6G??G8 TME 7T+ hiredateAhistoryA?? LES6;E!T7;2M7*E;TE8 >ME M7*E;TE like 6GPPG8 TME 7T+ hiredateAhistoryAPP LES6;E!T7;2 M7*E;TE8
ELSE 7T+ hiredateAhistory LES6;E!T7;2 M7*E;TE8 SELECT departmentAid ;E!T7;2 S,6salary8 SL2 ,W6hireAdate8 M7*E;TE )*+, employees J*+! D= departmentAid-
!i0oting 7SE*T [ Suppose you recei0e a set of sales records from a nonrelational database table2 SLESAS+*CEA;T2 in the following format: E,!L+=EEA7;2 >EERA7;2 SLESA,+2 SLESATE2 SLESA>E;2 SLESATM*2 SLESA)*7 [ =ou want to store these records in the SLESA7)+ table in a more typical relational format: E,!L+=EEA7;2 >EER2 SLES sing a pi0oting 7SE*T2 con0ert the set of sales records from the nonrelational database table to relational format.
!i0oting 7SE*T 7SE*T LL 7T+ salesAinfo LES 6employeeAid2weekAid2salesA,+8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATE8 7T+ salesAinfo LES 6employeeAid2weekAid2salesA>E;8 7T+ salesAinfo LES 6employeeAid2weekAid2salesATM*8 7T+ salesAinfo LES 6employeeAid2weekAid2 salesA)*78 SELECT E,!L+=EEA7;2 weekAid2 salesA,+2 salesATE2 salesA>E;2 salesATM*2salesA)*7 )*+, salesAsourceAdata-
The ,E*JE Statement [!ro0ides the ability to conditionally update or insert data into a database table
[!erforms an !;TE if the row e$ists2 and an 7SE*T if it is a new row: \ 0oids separate updates \ 7ncreases performance and ease of use \ 7s useful in data warehousing applications
,erging *ows 7nsert or update rows in the E,!L3 table to match the E,!L+=EES table. ,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET c.firstAname 5 e.firstAname2 c.lastAname 5 e.lastAname2 ... c.departmentAid 5 e.departmentAid >ME +T ,TCME; TME 7SE*T LES6e.employeeAid2 e.firstAname2 e.lastAname2 e.email2 e.phoneAnumber2 e.hireAdate2 e."obAid2 e.salary2 e.commissionApct2 e.managerAid2 e.departmentAid8-
,erging *ows T*CTE TDLE empl3SELECT ( )*+, empl3no rows selected ,E*JE 7T+ empl3 c S7J employees e + 6c.employeeAid 5 e.employeeAid8 >ME ,TCME; TME !;TE SET
... >ME +T ,TCME; TME 7SE*T LES...SELECT ( )*+, empl3-
;atabase 7nstance is a collection of ,emory structures and Dackground processes. +racle ,Emory is categoriFed into two types: 1. System Jlobal rea 6SJ8 SJ is a shared memory area that is allocated during startup of the instance. This memory is shared by all ser0er and user processes. . !rogram Jlobal rea 6!J8 !J is a pri0ate memory area that is alloted to a specific user process.
The SJ is a collection of following data structures: 1. ;atabase Duffer Cache: is a temporary memory area that caches the data reuested by the user. 7t helps in faster retrei0al of data. . *edo log buffer: it caches redo information. 7T will be helpful during instance reco0ery. 3. Shared !ool: 7t caches information that are shareable among users. 4. #a0a !ool: 7t caches the information that reuires "a0a 0irtual machine 6#,8. 9. Large !ool: This part of the memory wil be used when a large amt of memory allocation is reuired. )or e$ample during certain backup and reco0ery operations. Dackground !rocesses: 1. S,+ 6System ,onitor8 : This process is used during an instance reco0ery. . !,+ 6!rocess ,onitor8 : This process does the cleanup "ob to release memory of those user processes that are no longer a0ailable. 3. ;D>n 6;atabase >riter8: is reponsible for writing each blocks of data from the redo log
buffer to the physical database files. 4. LJ>* 6Log >riter8 : this process is responsible to do a log entry to the control file following a log switch. 9. *Cn 6*CM7E*8: Each and e0ery block of data is processed by the rchi0er.
*EC+E*= ,JE* 6*,8 : is a tool that allowes the user to perform backup and reco0ery operations. To start *,: TE*,7L 1: e$port +*CLEAS7;5ra" lsnrctl start emctl start dbconsole slplus sys
1. Ensure that the database is in *CM7EL+J mode: SL& *CM7E L+J L7ST if the ;atabase *CM7EL+J is disabled: SL& SMT;+> 7,,E;7TE SL& ST*T! ,+T SL& LTE* ;TDSE *CM7EL+JSL& LTE* ;TDSE +!E-
LTE* S=STE, SET dbAreco0eryAfileAdestAsiFe 5 9?NOQP?O9 SC+!E5D+TM . Startup the database instance: SL& SELECT STTS )*+, @7STCESTTS //////////// +!E To connect to *,2 on a different terminal window: HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog To see the structure of the database: *,& *E!+*T SCME,To see a list of backupsets taken: *,& L7ST DCR! +) ;TDSE-
To see a list of image copies taken: *,& L7ST C+!= +) ;TDSETo display outdated backups: *,& *E!+*T +DS+LETETo delete the outdated backups: *,& ;ELETE +!*+,!T +DS+LETETo see a list of configuration settings for *,: *,& SM+> LL-
To set the Control )ile utobackup +n: *,& C+)7J*E C+T*+L)7LE T+DCR! +Configure *, to change the retention policy to redundancy 9. *,& C+)7J*E *ETET7+ !+L7C= T+ *E;;C= 9Configure the *, to set the retention policy to 3? days. *,& C+)7J*E *ETET7+ !+L7C= T+ *EC+E*= >7;+> +) 3? ;=S-
To take a full database backup: *,& DCR! S DCR!SET ;TDSETo delete the backupsets: *,& ;ELETE +!*+,!T DCR!SET 2OTo take an image copy of the database: *,& DCR! S C+!= ;TDSETo delete an image copy: *,& ;ELETE +!*+,!T ;T)7LEC+!= 232Q2N29242OTo take a compressed backupset: *,& DCR! S C+,!*ESSE; DCR!SET ;TDSETo list the datafiles that are reuired to be backed up: *,& *E!+*T EE; DCR!To e$clude a tablespace from full database backup: *,& C+)7J*E EWCL;E )+* TDLES!CE tbsalertTo 0iew a list of tablespaces that are e$cluded: *,& SM+> EWCL;ETo take a full backup by including the e$cluded tablespace: *,& DCR! +EWCL;E S DCR!SET ;TDSETo take a full backup of database in compressed mode by including the e$cluded tablespace along with the rchi0e log also: *,& DCR! +EWCL;E S C+,!*ESSE; DCR!SET ;TDSE !LS *CM7EL+J-
Change the configuration setting to include the e$cluded tablespace for full backups: *,& C+)7J*E EWCL;E )+* TDLES!CE TDSLE*T CLE*SL& LTE* S=STE, SET dbAreco0eryAfileAdestASiFe59J SC+!E5both-
To take an incremental Dackup: *,& DCR! S DCR!SET 7C*E,ETL LEEL ? ;TDSEThe perform LEEL 1 backup: *,& DCR! S DCR!SET 7C*E,ETL LEEL 1 ;TDSETo take a full backup of e$ample tablespace: *,& DCR! S DCR!SET TDLES!CE e$ampleTo take an image copy of e$ample tablespace: *,& DCR! S C+!= TDLES!CE e$ampleTo take an offline backup: 1. Shutdown the database instance . *estart the instance in ,+T state SL& SMT;+> 7,,E;7TE SL& ST*T! ,+T 3. Connect to reco0ery manager HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& backup as backupset database4. +pen the database instance: SL& alter database open-
list backup of databasedelete noprompt backupset 324292Q backup noe$clude as backupset database-
;atabase *eco0ery ////////////////////////// *eco0ering ;atabase with )LSMDCR command: Steps to enable )lashback ;atabase:
1. Connect to +E, as sys. . Click 0ailability Tab. 3. Click *eco0ery Settings 4. Check the Enable )lashback ;atabase check bo$. Enter the flashback retention time period. Click pply.
SL& conn hr
S,6SL*=8 /////////// QP141Q SL& conn sys
/////////// 1?9?3QQ
SL& SELECT sum6salary8 )*+, hr.employeesS,6SL*=8 /////////// O?11Q >e need to flashback the database to the pre0ious transaction id: Soln: 1. Shutdown the database instance. SL& SMT;+> 7,,E;7TE . *estart the database in ,+T state SL& ST*T! ,+T 3. Connect to *eco0ery ,anager HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& )LSMDCR ;TDSE T+ SC51?9?314. +pen the ;atabase instance ote that when you try to open the database it will gi0e you an error. )or any incomplete reco0ery open the database with *ESETL+JS option to update the control files with the database change. SL& LTE* ;TDSE +!E *ESETL+JSTo enable manage transactions: SL& LTE* ;TDSE ;; S!!LE,ETL L+J ;T;atabase altered. SL& LTE* ;TDSE ;; S!!LE,ETL L+J ;T 6!*7,*= RE=8 C+L,S-
nother e$ample 7ncomplete *eco0ery ///////////////////////////////////////////////////////////
!+7T/7 T7,E *EC+E*= 6T7,E/DSE; *EC+E*=8 SL& SELECT systimestamp )*+, dualS=ST7,EST,! /////////////////////////////////////////////////////////////////////////// 1/SE!/19 ?Q.3?.4.9ON39 !, ?3:?? SL& conn hr
;E!*T,ETA7;
SL& select sum6salary8 from employeesS,6SL*=8 /////////// O49?1Q SL& commitCommit complete. SL& select count6(8 from employees C+T6(8 ////////// 1?O SL& delete from employees where "obAid5GSTACLE*RG? rows deleted. SL& commitCommit complete. SL& select count6(8 from employees C+T6(8 ////////// NO
Sln: 1. Shutdown and restart the instance in ,+T state SL& SMT;+> 7,,E;7TE ;atabase closed. ;atabase dismounted. +*CLE instance shut down. SL& ST*T! ,+T
. Connect to the reco0ery manager: @ e$port LSA;TEA)+*,T5Byyyy/mm/dd hh4:mi:ssB @ e$port LSALJ5americanAamerica.weNisoNN9Pp19 HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& run & 3& SET T7L T7,E5G?19/?P/1 1N:3?:??G4& *EST+*E ;TDSE9& *EC+E* ;TDSEQ&
3. +pen the database instance with *ESETL+JS option SL& LTE* ;TDSE +!E *ESETL+JSSL& C+ hr
+D#ECT/LEEL *EC+E*= /////////////////////////////////////////// >hen a datafile is lost or damaged2 you can reco0er it using two methods: +TE: =+ ,ST ME L7; DCR! +) TME ;TDSE T+ *EC+E*= ;T)7LE Scenario: ;elete the datafiles e$ample?1.dbf and users?1.dbf from oradata directory: ;atabase location: ithout shutting down the instance: ////////////////////////////////////////////////////////////////////// Step 1: ,ake the datafiles offline as sys user: SL& LTE* ;TDSE ;T)7LE 9 +))L7ESL& LTE* ;TDSE ;T)7LE 4 +))L7EStep : Connect to *, HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra"
HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& run & 3& *EST+*E ;T)7LE 4294& *EC+E* ;T)7LE 4299& Step 3: fter the reco0ery2 make the datafiles online SL& LTE* ;TDSE ;T)7LE 9 +L7E;atabase altered. SL& LTE* ;TDSE ;T)7LE 4 +L7E;atabase altered.
,ethod : Shutdown the instance and restart in ,+T state: SL& SMT;+> D+*T+*CLE instance shut down. SL& ST*T! ,+T Connect to *, HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& run & 3& *EST+*E ;T)7LE 94& *EC+E* ;T)7LE 99& +pen the ;atabase instance SL& LTE* ;TDSE +!E-
;TDL+CR *EC+E*= /////////////////////////////////////// STE!S: 1. Startup the database instance:
e$port +*CLEAS7;5ra" lsnrctl start emctl start dbconsole slplus sysME*E segmentAname5G;E!*T,ETSG and owner5GM*G )7LEA7; DL+CRA7; ////////// ////////// 9 1QN SL& e$it . Ensure that your current working directory is desktop where you ha0e copied mycorrupt.sh file. *un the script from the command prompt: HoracleVra"i0 ;esktopI@ .
;7SSTE* *EC+E*= //////////////////////////////////////// ;D7;5N3OQ11NQ4 instance name: ra"
STE!S: Ensure that:
1. =ou ha0e your database id . controlfile autobackup is on 3. take a full database backups DCR! +EWCL;E S DCR!SET ;TDSE !LS *CM7EL+J4. delete all files related to the database.
1. shutdown the database instance and startup in +,+T state. SL& shutdown immediate >7LL J7E E**+* S+2 SL& SMT;+> D+*TSL& ST*T! +,+T. *eco0er the control files +pen another terminal window and connect to *,. HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog 3. Set the ;atabase 7; *,& SET ;D7;5N3OQ11NQ4 4. *eco0er the control file *,& *EST+*E C+T*+L)7LE )*+, T+DCR!9. Try mounting the database: SL& LTE* ;TDSE ,+TQ. Connect to reco0ery manager and reco0er the database files. HoracleVra"i0 ;esktopI@ e$port +*CLEAS7;5ra" HoracleVra"i0 ;esktopI@ rman target < nocatalog *,& run & 3& *EST+*E ;TDSE4& *EC+E* ;TDSE9& O. +pen the database instance with *ESETL+JS option. SL& LTE* ;TDSE +!E *ESETL+JS-
Take a fresh backup after the reco0ery: *,& list backup of database*,& backup noe$clude as backupset database plus archi0elog-
Loss of one control file ///////////////////////////////// 7f only one control file is lost2 you can reco0ery it by making the duplicate copy of the e$isting one.P
,anaging *esources ///////////////////////////////// Steps : 18 Connect to +E, as S=S TSR Create a resource group called appAuser / +n the +E,2 click ser0er tab2 then click consumer group link / Click create2 enter the resource group name as appAuser2 optionally enter the description /Ensure that Scheduling policy is selected as *ound *obin TSR D dd the appAuser and lowAgroup consumer groups to defaultAplan resource plan Change the le0el 3 cpu resource allocation percentages as below 18 Q? for appAuser 8 4? for lowAgroup Steps: / +n the +E,2 click ser0er tab2 then click on the plans link. / Select the plan called defaultAplan2 then click edit / Click modify button2 mo0e the appAuser and lowAgroup resorce groups to the plan2 click ok. / nder le0el 32 for appAuser add Q?2 for lowAgroup add 4?2 click apply. TSR C Configure the consumer groups mappings such that the M* user belongs to appAuser group2 scott user belongs to lowAgroup consumer group. lso for the scott user confirm that his oracleAuser attribute has a higher priority than clientAosAuser attribute. Steps: 18 +n the +E, click the ser0er tab2 then click the consumer group mappings. 8 Select oracleAuser and click dd rule for selected type button.
38 Select the consumer group as appAuser and shift M* from a0ailable users to selected users. 48 Select the consumer group as lowAgroup and shift SC+TT from a0ailable users to selected users. and click ok. 98 Click on the priorities tab and ensure that oracle user is before the clientAosAuser in the list. TSR ; Configure consumer group mappings such that the oracleAosAuser belongs to the sysAgroup consumet group. Steps: 18 +n the +E,2 click ser0er tab2 then click on the consumer group mappings link 8 Select clientAosAuser and click the dd rule for selected type button. 38 Select consumer group as sysAgroup2 then mo0e the oracle user to the selected user then click ok2 apply. TSR E ssign the pm user to the following consumer groups 18 appAuser 8 lowAgroup 38 sysAgroup Steps: 18 +n the +E,2 click the ser0er tab2 click users link 8 Select pm user 2 click edit2 click consumers group pri0ileges tab. 38 Click edit list2 mo0e appAuser2lowAgroup2 sysAgroup to the a0ailable list2 click ok2 apply. CT7TE TME !L : ;E)LTA!L Steps: 18 +n the +E,2 click the ser0er tab2 on the resource manager click plans. 8 Select defaultAplan2 ensure that the actions drop down list has acti0ate option2 click go. 38 >hen asked for confirmation2 click yes.
Creating Scheduler components /////////////////////////////////////////////////////////// TSR : Create a "ob called "obAcreateAsessionAhistory2 this "ob should create a table called sessionAhistory with two columns6snapAtime timestamp with local time Fone2 numAsessions number8 Steps: 18 +n the +E,2 click ser0er tab2 under oracle scheduler click "obs2 click create 8 Type the "ob name 38 nder command region enter the following procedure begin
e$ecute immediate6GC*ETE TDLE M*.SESS7+AM7ST+*=6S!AT7,E T7,EST,! >7TM L+CL T7,E X+E2 ,ASESS7+S ,DE*8G8endLea0e all 0alue for default2 then click ok. TSR D: Create a program called prgAsession2 this program will take the total count of acti0e sessions and do a log entry into sessionAhistory table. Steps: 18 +n +E,2 click ser0er tab2 click programs link. 8 Type the program name 38 nder source type the following block declare $ number begin SELECT C+T6(8 7T+ $ )*+, @SESS7+7SE*T 7T+ M*.SESS7+AM7ST+*= LES6S=ST7,EST,!2 W8 C+,,7TendTSR C: Create a schedule called schAsession2 this schedule should run e0ery 9 seconds. Steps: 18 +n +E,2 click ser0er tab2 click schedules link. 8 Click create2 then gi0e a name of the schedule 38 nder repeat select the inter0al as 9 seconds2 let the schedule be immediate2 click +R. TSR ;: Create a "ob called "obAsession2 this "ob should run the program prgAsession e0ery fi0e seconds Steps: 18 +n +E,2 click ser0er tab2 click "obs link2 click create2 type the "ob name 8 nder command region2 click the change command type button and pick the prgAsession from the look up icon 38 Likewise attach the pre defined schedule by clicking on the schedules tab2 then click ok.
,anaging Schema +b"ects 5555555555555555555555555 =our company is going through a merging process. s a result2 you e$pect some dramatic growth in a few tables of the databases for which you are responsible. To proacti0ely monitor table and inde$ space usage2 you create a test case and perform the tasks2 which you
e$pect for your production system. @ .hen inserting rows for this table2 the +racle database will keep 1? of the block free for future updates . !opulate the TESTA*EJ7+S table by running the labA1?A?.sh script. @ .hile you look at the segment information for the M*.TESTA*EJ7+S table2 you notice that the table segment has a ^>asted Space 68_ of o0er ?. =ou decide to reorganiFe the tablespace usage. fter doing that2 confirm that your "ob succeeded and 0iew the current space usage again. ;id it increase or decreaseZ a. 7n Enterprise ,anager2 select dministration & Tables. Select the TESTA*EJ7+S table and click Edit. b. Click the Segments tabbed page and re0iew the table]s segment information. c. otice ra">asted Space 68. =our 0alues may differ from those shown here. >hat is the cause of the wasted spaceZ d. Select *eorganiFe from the ctions drop/down list and click Jo. e. ccept the defaults on the following pages and click e$t. +n the Schedule page2 enter
oracle as sername and !assword for Most Credentials. f. +n the *e0iew page2 click Submit #ob. g. 7f you see the "ob running2 click *efresh or *eload in your browser window until it is completed and disappears from the *esults list. h. Then click the "ob name link in the Confirmation window. The status of the "ob run should be ^Succeeded._ i. *eturn to the Tables page for the M* schema. Select the TESTA*EJ7+S table2 click edit and 0iew the >asted Space 68 on the Segments tab. The 0alue should be significantly lower than the pre0ious 0alue.
,anaging Storage 55555555555555555 Create a new tablespace called TDSLE*T with a 1? ,D file called alert1.dbf. ,ake sure that this tablespace is locally managed and uses utomatic Segment Space ,anagement. ;o not make it autoe$tensible2 and do not specify any thresholds for this tablespace. se Enterprise ,anager ;atabase Control to create it. 7f this tablespace already e$ists in your database2 drop it first2 including its files. a. 7n Enterprise ,anager2 select Ser0er Tab & Tablespaces. b. Click the Create button. c. Enter TDSLE*T as ame2 and click the dd button in the ;atafiles region. d. Enter alert1.dbf as )ile ame and 1? ,D as )ile SiFe2 and select *euse E$isiting )ile. e. Click Continue2 and then click +R. 4. 7n Enterprise ,anager2 change the Tablespace Space sage thresholds of the TDSLE*T tablespace. Set its warning le0el to 99 percent and its critical le0el to O? percent. a. +n the Tablespaces page2 select TDSLE*T2 click Edit2 and then click Thresholds. b. Select Specify Thresholds2 and enter 99 as >arning 68 a nd O? as Critical 68. c. +ptionally2 click Show SL2 re0iew the statement2 and click *eturn. d. Click pply to modify the threshold 0alues. 9. sing SL(!lus2 check the new threshold 0alues for the TDSLE*T tablespace. a. 7n your SL(!lus session2 enter:
select warningA0alue2criticalA0alue from dbaAthresholds where metricsAname5GTablespace Space sageG and ob"ectAname5GTDSLE*TG-
Q. Select the reason and resolution columns from ;DALE*TAM7ST+*= for the TDSLE*T tablespace a. 7n your SL(!lus session2 enter: select reason2resolution from dbaAalertAhistory where ob"ectAname5GTDSLE*TGO. E$ecute the labA11A?O.sh script that tcreates and populates new tables in the TDSLE*T tablespace. N. Check the fullness le0el of the TDSLE*T tablespace by using either ;atabase Control or SL(!lus. The current le0el should be around Q?. >ait for appro$imately 1? minutes2 and check that the warning le0el is reached for the TDSLE*T tablespace. a. 7n Enterprise ,anager on the Tablespaces page2 see sed 68. b. a0igate to the ;atabase home page. =ou should see the new alert in the Space summary section. c. 7n SL(!lus2 enter: select sum6bytes8 (1?? <19NP1? from dbaAe$tents where tablespaceAname5GTDSLE*TGd. Enter the following command: select reason from dbaAoutstandingAalerts where ob"ectAname5GTDSLE*TGP. E$ecute the labA11A?PAa.sh script to add data to TDSLE*T. >ait for 1? minutes and 0iew the critical le0el in both the database and ;atabase Control. erify that TDSLE*T fullness is around O9. insert into employees4 select ( from employees4commitinsert into employees9 select ( from employees9commita. Enter the following command in a terminal window: @ .ait for 1? minutes and 0iew the critical le0el in both the database and ;atabase Control. erify that TDSLE*T fullnesshas is around O9. 7n SL(!lus2 enter: select sum6bytes8 (1??<19NP1? from dbaAe$tents where tablespaceAname5GTDSLE*TG-
c. 7n SL(!lus2 enter: select reason2 messageAle0el from dbaAoutstandingAalerts where ob"ectAname5GTDSLE*TGd. 7n Enterprise ,anager2 on the Tablespaces page2 see sed 68. e. a0igate to the ;atabase home page. =ou should see the new alert in the Space Summary region. otice the red flag instead of the yellow one. 1?. E$ecute labA11A1?.sh to delete few rows .ait for 1? mins and check that there are no more alerts outstanding for TDSLE*T tablespace. a. a0igate to the ;atabase homeipage. =ou should see !roblem Tablespaces ?.
