第44和45次：2015-08-22星期六上下午

botang

上完1Z0-052第8章        （52-37）
上完1Z0-052第9章        （52-38）
上完1Z0-052第10章        （52-39）

2015-08-22a.sql：

1. select  distinct  sp.privilege  from
   
2.   dba_sys_privs sp ;
   
3.   
   
4.   
   
5.   
   
6.   
   
7.   select  *
   
8.      from dba_sys_privs sp where sp.grantee='U1';
   
9. 
   
10. select      *
    
11.      from dba_tab_privs tp where tp.grantee='U1';
    
12.   
    
13. select  *
    
14.   from dba_col_privs cp where cp.grantee='U1';
    
15. 
    
16. select     *
    
17.      from dba_role_privs rp where rp.grantee='U1';
    
18.      
    
19.      
    
20.      
    
21.      revoke select  on hr.employees from u1;
    
22.      
    
23.    select  *
    
24.      from dba_sys_privs sp where sp.grantee='U2';   
    
25.      
    
26.      
    
27.   select      *
    
28.      from dba_tab_privs tp where tp.grantee='U2';
    
29.   
    
30. select  *
    
31.   from dba_col_privs cp where cp.grantee='U2';
    
32. 
    
33. select     *
    
34.      from dba_role_privs rp where rp.grantee='U2';   
    
35.      
    
36.   grant select any table to u1  with admin option;
    
37.   
    
38.   revoke select any table from u1;
    
39.   
    
40.   revoke select any table from u2;
    
41.   
    
42.   grant select  on hr.employees to u1 with grant option;
    
43.   
    
44.   revoke select  on hr.employees  from u1;
    
45.   
    
46.   select  * from dba_roles order by 1 ;
    
47.   
    
48.   create role r1 ;
    
49.   
    
50.   select  * from role_role_privs rrp where rrp.role='R1';
    
51.   
    
52.   select  * from role_tab_privs rtp where rtp.role='R1';
    
53.   
    
54.   select  * from role_sys_privs rsp where rsp.role='R1';
    
55.   
    
56.   grant select  on hr.employees to r1;
    
57.   
    
58.   grant r1 to u1 with admin option;
    
59.   
    
60.   revoke r1 from u1;
    
61.   
    
62.   revoke select  on hr.employees from r1;
    
63.   
    
64.    revoke r1 from u2;
    
65.    
    
66.    drop role r1;
    
67.   
    
68.     create role r1  identified by oracle_4U ;
    
69.    
    
70.     grant select  on hr.employees to r1;
    
71.    
    
72.     grant r1 to u1 ;
    
73.   
    
74.   alter role r1 identified by redhat_4U;
    
75.   
    
76.   alter system kill session '201,35' immediate;
    
77.   
    
78.   select * from v$session where username='U1';
    
79.   
    
80.   select     *
    
81.      from dba_role_privs rp where rp.grantee='U1';   
    
82.   
    
83.   create role r2 ;
    
84.   
    
85.   grant  create any index to r2;
    
86.   
    
87.    grant r2 to u1;
    
88.   
    
89.   alter user u1 default role none;
    
90.   
    
91.   alter user u1 default role connect,r1;
    
92.   
    
93.   grant r2 to r1;
    
94.   
    
95.   ----
    
96.   
    
97.      select  *
    
98.      from dba_sys_privs sp where sp.grantee='U3';   
    
99.      
    
100.      
     
101.   select      *
     
102.      from dba_tab_privs tp where tp.grantee='U3';
     
103.   
     
104. select  *
     
105.   from dba_col_privs cp where cp.grantee='U3';
     
106. 
     
107. select     *
     
108.      from dba_role_privs rp where rp.grantee='U3';   
     
109.   
     
110. 
     
111. create role r3 ;
     
112. 
     
113. grant select  on hr.employees to r3;
     
114. 
     
115. grant r3 to u3;
     
116. 
     
117. grant create procedure to u3;
     
118. 
     
119. revoke r3 from u3;
     
120. 
     
121. grant select  on hr.employees to u3;

复制代码

2015-08-22b.sql：

1. CREATE PROFILE "PROFILE1" LIMIT
   
2. FAILED_LOGIN_ATTEMPTS 1;
   
3. 
   
4. select  * from dba_profiles p where p.profile='PROFILE1' and p.resource_type='PASSWORD';
   
5. 
   
6. select  * from dba_users  where username='HR';
   
7. 
   
8. alter user hr profile profile1;
   
9. 
   
10. alter user hr account unlock;
    
11. 
    
12. ALTER PROFILE "PROFILE1" LIMIT PASSWORD_LOCK_TIME 0.000694444;
    
13. 
    
14. ALTER PROFILE "PROFILE1" LIMIT  PASSWORD_REUSE_MAX 1
    
15. PASSWORD_REUSE_TIME 0.000694444;
    
16. 
    
17. ALTER PROFILE "PROFILE1" LIMIT  PASSWORD_verify_function  verify_function_11g;
    

复制代码

2015-08-22c.sql：

1. select  * from dba_tablespaces;
   
2. 
   
3. create temporary  tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/temp2.dbf'
   
4. size 15G;
   
5. 
   
6. select  * from database_properties;
   
7. 
   
8. alter database default temporary tablespace temp2;
   
9. 
   
10. 
    
11. create undo tablespace undotbs1   datafile size 100M  autoextend on maxsize 20G;
    
12. 
    
13. drop tablespace undotbs1;
    
14. 
    
15. select  * from dba_rollback_segs;
    
16. 
    
17. select  * from v$transaction ;
    
18. 
    
19. select  * from v$session where taddr='00000001EAEBE200';
    
20. 
    
21. alter system kill session '8,3111' immediate;
    
22. 
    
23. 
    
24. select  * from dba_tablespaces;
    
25. 
    
26. 
    
27. alter tablespace undotbs1 retention guarantee;
    
28. 
    
29. 
    
30. select  * from dict where table_name like '%UNDO%'  or  table_name like '%ROLLBACK%';
    
31. 
    
32. 
    
33. select  * from DBA_HIST_UNDOSTAT;
    
34. 
    
35. 
    
36. select  * from v$undostat;

复制代码