【多图杀猫】课程第13次（2016-04-25星一期）：集操作符，DML，旧值和读一致性

botang

课程第13次（2016-04-25星期一）：集操作符，DML，旧值和读一致性
【上完1Z0-051的第8章】：集操作符
【1Z0-051】：共9章
【1Z0-052】：共3章
2016-04-25.sql：

1. select  * from job_history   order by 1,2;
   
2. 
   
3. select  * from employees where employee_id=200;
   
4. 
   
5. ---
   
6. select  employee_id  , count(*)  from
   
7. 
   
8. (SELECT employee_id, job_id
   
9. FROM   employees
   
10. UNION
    
11. SELECT employee_id, job_id
    
12. FROM   job_history)
    
13. 
    
14. group by employee_id
    
15. having  count(*) >=3;
    
16. 
    
17. 
    
18. ----
    
19. 
    
20. SELECT employee_id, job_id
    
21. FROM   employees
    
22. intersect
    
23. SELECT employee_id, job_id
    
24. FROM   job_history;
    
25. 
    
26. ----
    
27. 
    
28. SELECT employee_id
    
29. FROM   employees
    
30. MINUS
    
31. SELECT employee_id
    
32. FROM   job_history;
    
33. 
    
34. ---
    
35. 
    
36. SELECT location_id, department_name "Department",
    
37.    'X'   "Warehouse location"  
    
38. FROM departments
    
39. UNION
    
40. SELECT location_id,   'Y'   "Department2",
    
41.    state_province
    
42. FROM locations
    
43. order by "Department2" ;
    
44. 
    
45. 
    
46. create table t05108_a ( a  number ) ;
    
47. 
    
48. insert into t05108_a values ( 3) ;
    
49. 
    
50. 
    
51. 
    
52. 
    
53. insert into t05108_b values (2) ;
    
54. insert into t05108_b values (3) ;
    
55. 
    
56. 
    
57. create table t05108_b ( b number ) ;
    
58. 
    
59. 
    
60. 
    
61. create table t05108_c ( c number ) ;
    
62. 
    
63. insert into t05108_c values (3) ;
    
64. 
    
65. select  * from t05108_a
    
66. minus
    
67. (select  * from t05108_b
    
68. intersect
    
69. select  * from t05108_c);
    
70. 
    
71. ----
    
72. select * from v$transaction;
    
73. 
    
74. select  * from dba_rollback_segs;
    
75. 
    
76. select  * from v$session where  terminal='pts/5';
    
77. 
    
78. select  * from v$process;
    
79. ---
    
80. 
    
81. select   file_name,
    
82.   bytes/1024/1024 from dba_temp_files where tablespace_name='TEMP';
    
83.   
    
84. alter tablespace temp add tempfile '/home/oracle/temp02.dbf' size 10G ;
    
85. ---
    
86. create undo tablespace undotbs2 datafile size 256K autoextend off;
    
87. 
    
88. ---
    
89. select  * from dba_rollback_segs;
    

复制代码

2016-04-25.txt：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 25 21:42:07 2016
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show parameter undo
   
10. 
    
11. NAME                                     TYPE         VALUE
    
12. ------------------------------------ ----------- ------------------------------
    
13. undo_management                      string         AUTO
    
14. undo_retention                             integer         900
    
15. undo_tablespace                      string         UNDOTBS1
    
16. SQL> alter system set undo_tablespace=UNDOTBS2 ;
    
17. 
    
18. System altered.
    
19. 
    
20. SQL> drop tablespace  UNDOTBS1;
    
21. 
    
22. Tablespace dropped.
    
23. 
    

复制代码

快照太旧